Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql

[Lukasz Szybalski]

On Thursday, January 18, 2018 at 11:49:39 AM UTC-6, Mike Bayer wrote:
>
>
>
> On Thu, Jan 18, 2018 at 12:31 PM, Lukasz Szybalski  > wrote:
>
>> Hello,
>> I have a query in sqlalchemy like below where I lookup contract# in 
>> mssql. How do I enforce the varchar instead of nvarchar? I tried converting 
>> my field to "str(mycurrent)" but that didn't do anything. Is there some 
>> other spot to force VARCHAR to be sent?
>>
>
>
> this just came up in 
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case,
>  
> in that case they are using typed parameters:
>
> String(50, convert_unicode='force')
>
> if you're dealing w/ the raw string like that try this:
>
> execute(text("select ... where foo = 
> :mycurrent").bindparams(bindparam("mycurrent", 
> type_=String(convert_unicode='force')))
>
> let me know if that works b.c. this has to be in the docs
>



Hello,
Sorry for late response. I'm struggling with the syntax here for the bind 
param. How do I convert from session.execut(stmt,params) to your 
bindparams? I can't just cast the variable I'm passing to correct format? 
Knowing that the value I will be passing is either some sa stored procedure 
sqlalchemy object.columname or some other system 
mycontract=someobject.contract_no

 From
 p=session.execute("select PZ.p_id,PZ.pimage_num from dbo.P with(nolock) 
inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id  inner join dbo.D D 
with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num where 
p.contract=:mycontract and D.srtype_id 
=5",params={'mycontract':str(mycontract)}).fetchall()

 To:
from sqlalchmy import bindparams
##mycurrent=someobject.contract_no
mycurrent='ABC123'


(bad)  p=session.execute(text("select PZ.p_id,PZ.pimage_num from dbo.P 
with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id  inner 
join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num 
where p.contract=:mycontract and D.srtype_id 
=5").bindparam('mycontract'),params={'mycontract':str(mycontract)}).fetchall()
AttributeError: 'TextClause' object has no attribute 'bindparam'

(bad)  p=session.execute("select PZ.p_id,PZ.pimage_num from dbo.P 
with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id  inner 
join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num 
where p.contract=:mycontract and D.srtype_id 
=5").bindparam('mycontract').fetchall()
A value is required for bind parameter 'mycontract' 


- What is the final working syntax, I can't find any examples of how to fix 
this in the link 

  indicated. 
Page says that a "String(50).with_variant(String(50, 
convert_unicode='force'), 'mssql')" has worked for Jan K. but it doesn't 
really say how does somebody converts their variable into correct type 
string:
like: 
mycontract=cast(mycontract, String(convert_unicode='force'))


Since I'm passing params right after my statement should the bindparam be 
in params section? 
something along: 
params={'mycontract':sqlalchemy.string(mycontract).convert_unicode='force')})

The easiest way would be to convert mycontract into proper 
String(convert_unicode='force') and pass mycontract as in my original query?

Thank you
Lucas
__
http://lucasmanual.com



>
>>
>> You can find that the query that uses NVARCHAR does an index scan has 
>> 30,909 logical reads on the dbo.P table.  It also uses 890 ms of CPU and 
>> has a total elapsed time of 938 ms.
>>
>> The query that uses VARCHAR does an index seek and has 7 logical reads on 
>> the dbo.P table.  It uses 0 ms of CPU and has a total elapsed time of 11 ms.
>>
>>
>>  p=*session.execute*("select PZ.p_id,PZ.pimage_num from dbo.P 
>> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id  inner 
>> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num 
>> where p.current=:mycurrent and D.srtype_id 
>> =5",params={'mycurrent':str(mycurrent)}).fetchall()
>>
>>
>>
>>
>>
>> [image: Inline image 1]
>>
>>
>>
>>
>> Thank you
>>
>> Lucas
>>
>>
>>
>>
>> -- 
>> http://lucasmanual.com/ 
>>
>> -- 
>> SQLAlchemy - 
>> The Python SQL Toolkit and Object Relational Mapper
>>  
>> http://www.sqlalchemy.org/
>>  
>> To post example code, please provide an MCVE: Minimal, Complete, and 
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
>> description.
>> --- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to sqlalchemy+...@googlegroups.com .
>> To post to this group, send email to sqlal...@googlegroups.com 
>> .
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Jeremy Flowers]

Finally with some fluff removed...

#!/usr/bin/env python
import sqlalchemy
from sqlalchemy import text, create_engine
stmt = text('WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE,
LVL) AS (' +
'SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1 '
+
'FROM TIDAL.JOBMST ' +
'WHERE JOBMST_PRNTID IS NULL ' +
'UNION ALL ' +
'SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID,
J2.JOBMST_TYPE, ' +
'J1.LVL + 1 ' +
'FROM TIDAL.JOBMST J2 ' +
'INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID '
'WHERE J2.JOBMST_PRNTID IS NOT NULL ' +
') SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ ' +
'SELECT * ' +
'FROM ( SELECT A.*, rownum rn ' +
'FROM ( SELECT * '
'FROM J1 ' +
'ORDER BY DISP_SEQ) A ' +
'WHERE rownum <= :f) ' +
'WHERE rn >= :s')
engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
echo=False) #echo='debug'
connection = engine.connect()
offset = 0 #42735  #42736 records.. ROWCOUNT starts from 1, not 0 in
ORACLE database.
limit = 2
more_results = True
while more_results:
start = offset + 1
finish = offset + limit
print('Start: {0}, Finish {1}'.format(start,finish))
partial_results = connection.execute(stmt, s = start, f =
finish).fetchall()
offset += limit
if partial_results == []:
more_results = False
else:
for row in partial_results:
print(row.items())
if  offset == 4:
more_results = False
connection.close()

On Fri, Apr 27, 2018 at 6:43 PM, Jeremy Flowers 
wrote:

> Thanks.
>
> On Fri, Apr 27, 2018 at 6:08 PM, Mike Bayer 
> wrote:
>
>> you don't really need the columns(...) part if you are sending the
>> text() object to connection.execute().   That's mostly a thing to help
>> when using the ORM which I don't think you want to be using here.
>>
>> Also set echo='debug' on create_engine which will log all the rows
>> coming back.
>>
>> On Fri, Apr 27, 2018 at 12:12 PM, Jeremy Flowers
>>  wrote:
>> > Hah.. Seems Oracle ROWNUM starts from 1, not 0..
>> > When you select between 3 and 4 - nothing returns. There's an Oracle
>> > quirk...
>> >
>> > On Fri, Apr 27, 2018 at 5:03 PM, Jeremy Flowers <
>> jeremy.g.flow...@gmail.com>
>> > wrote:
>> >>
>> >> Have tried dropping session.execute and using connection.execute -
>> where
>> >> the method of parameter binding seems to work...
>> >>
>> >> ==
>> >>
>> >> #!/usr/bin/env python
>> >> import sqlalchemy
>> >> from sqlalchemy.types import Integer
>> >> from sqlalchemy.sql.expression import bindparam
>> >> from sqlalchemy import text, create_engine, column
>> >> stmt = text('WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID,
>> JOBMST_TYPE,
>> >> LVL) AS (' +
>> >> 'SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID,
>> JOBMST_TYPE, 1
>> >> ' +
>> >> 'FROM TIDAL.JOBMST ' +
>> >> 'WHERE JOBMST_PRNTID IS NULL ' +
>> >> 'UNION ALL ' +
>> >> 'SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID,
>> >> J2.JOBMST_TYPE, ' +
>> >> 'J1.LVL + 1 ' +
>> >> 'FROM TIDAL.JOBMST J2 ' +
>> >> 'INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID '
>> >> 'WHERE J2.JOBMST_PRNTID IS NOT NULL ' +
>> >> ') SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ ' +
>> >> 'SELECT * ' +
>> >> 'FROM J1 '+
>> >> 'WHERE ROWNUM BETWEEN :s AND :f ' +
>> >> 'ORDER BY DISP_SEQ ')
>> >> stmt.columns(column('JOBMST_ID'), column('JOBMST_NAME'),
>> >> column('JOBMST_PRNT_ID'), column('LVL'),column('DISP_SEQ'))
>> >> stmt.bindparams(bindparam('s', type_=Integer), bindparam('f',
>> >> type_=Integer))
>> >> engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL
>> ',
>> >> echo=True)
>> >> connection = engine.connect()
>> >> offset = 0
>> >> limit = 2
>> >> more_results = True
>> >> while more_results:
>> >> start = offset
>> >> finish = offset + limit - 1
>> >> print('Start: {0}, Finish {1}'.format(start,finish))
>> >> partial_results = connection.execute(stmt, s = start, f =
>> >> finish).fetchall()
>> >> offset += limit
>> >> if partial_results == [] or offset == 4:
>> >> more_results = False
>> >> for row in partial_results:
>> >> print(row.items())
>> >> connection.close()
>> >>
>> >> ===
>> >>
>> >> C:\opt\tidalconversion>jobwalktext.py
>> >> 2018-04-27 16:44:58,037 INFO sqlalchemy.engine.base.Engine SELECT USER
>> >> FROM DUAL
>> >> 2018-04-27 16:44:58,037 INFO sqlalchemy.engine.base.Engine {}
>> >> 2018-04-27 16:44:58,040 INFO sqlalchemy.engine.base.Engine SELECT
>> >> CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
>> >> 2018-04-27 16:44:58,040 INFO sqlalchemy.engine.base.Engine 

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Jeremy Flowers]

Thanks.

On Fri, Apr 27, 2018 at 6:08 PM, Mike Bayer 
wrote:

> you don't really need the columns(...) part if you are sending the
> text() object to connection.execute().   That's mostly a thing to help
> when using the ORM which I don't think you want to be using here.
>
> Also set echo='debug' on create_engine which will log all the rows
> coming back.
>
> On Fri, Apr 27, 2018 at 12:12 PM, Jeremy Flowers
>  wrote:
> > Hah.. Seems Oracle ROWNUM starts from 1, not 0..
> > When you select between 3 and 4 - nothing returns. There's an Oracle
> > quirk...
> >
> > On Fri, Apr 27, 2018 at 5:03 PM, Jeremy Flowers <
> jeremy.g.flow...@gmail.com>
> > wrote:
> >>
> >> Have tried dropping session.execute and using connection.execute - where
> >> the method of parameter binding seems to work...
> >>
> >> ==
> >>
> >> #!/usr/bin/env python
> >> import sqlalchemy
> >> from sqlalchemy.types import Integer
> >> from sqlalchemy.sql.expression import bindparam
> >> from sqlalchemy import text, create_engine, column
> >> stmt = text('WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE,
> >> LVL) AS (' +
> >> 'SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE,
> 1
> >> ' +
> >> 'FROM TIDAL.JOBMST ' +
> >> 'WHERE JOBMST_PRNTID IS NULL ' +
> >> 'UNION ALL ' +
> >> 'SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID,
> >> J2.JOBMST_TYPE, ' +
> >> 'J1.LVL + 1 ' +
> >> 'FROM TIDAL.JOBMST J2 ' +
> >> 'INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID '
> >> 'WHERE J2.JOBMST_PRNTID IS NOT NULL ' +
> >> ') SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ ' +
> >> 'SELECT * ' +
> >> 'FROM J1 '+
> >> 'WHERE ROWNUM BETWEEN :s AND :f ' +
> >> 'ORDER BY DISP_SEQ ')
> >> stmt.columns(column('JOBMST_ID'), column('JOBMST_NAME'),
> >> column('JOBMST_PRNT_ID'), column('LVL'),column('DISP_SEQ'))
> >> stmt.bindparams(bindparam('s', type_=Integer), bindparam('f',
> >> type_=Integer))
> >> engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
> >> echo=True)
> >> connection = engine.connect()
> >> offset = 0
> >> limit = 2
> >> more_results = True
> >> while more_results:
> >> start = offset
> >> finish = offset + limit - 1
> >> print('Start: {0}, Finish {1}'.format(start,finish))
> >> partial_results = connection.execute(stmt, s = start, f =
> >> finish).fetchall()
> >> offset += limit
> >> if partial_results == [] or offset == 4:
> >> more_results = False
> >> for row in partial_results:
> >> print(row.items())
> >> connection.close()
> >>
> >> ===
> >>
> >> C:\opt\tidalconversion>jobwalktext.py
> >> 2018-04-27 16:44:58,037 INFO sqlalchemy.engine.base.Engine SELECT USER
> >> FROM DUAL
> >> 2018-04-27 16:44:58,037 INFO sqlalchemy.engine.base.Engine {}
> >> 2018-04-27 16:44:58,040 INFO sqlalchemy.engine.base.Engine SELECT
> >> CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
> >> 2018-04-27 16:44:58,040 INFO sqlalchemy.engine.base.Engine {}
> >> 2018-04-27 16:44:58,041 INFO sqlalchemy.engine.base.Engine SELECT
> >> CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
> >>
> >> C:\opt\tidalconversion>jobwalktext.py
> >> 2018-04-27 16:58:13,199 INFO sqlalchemy.engine.base.Engine SELECT USER
> >> FROM DUAL
> >> 2018-04-27 16:58:13,199 INFO sqlalchemy.engine.base.Engine {}
> >> 2018-04-27 16:58:13,202 INFO sqlalchemy.engine.base.Engine SELECT
> >> CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM
> DUAL2018-04-27
> >> 16:58:13,202 INFO sqlalchemy.engine.base.Engine {}2018-04-27
> 16:58:13,203
> >> INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS
> >> NVARCHAR2(60)) AS anon_1 FROM DUAL2018-04-27 16:58:13,203 INFO
> >> sqlalchemy.engine.base.Engine {}2018-04-27 16:58:13,204 INFO
> >> sqlalchemy.engine.base.Engine select value from nls_session_parameters
> where
> >> parameter = 'NLS_NUMERIC_CHARACTERS'
> >> 2018-04-27 16:58:13,204 INFO sqlalchemy.engine.base.Engine {}
> >> Start: 0, Finish 1
> >> 2018-04-27 16:58:13,206 INFO sqlalchemy.engine.base.Engine WITH
> >> J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS (SELECT
> >> JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1 FROM TIDAL.JOBMST
> >> WHERE JOBMST_PRNTID IS NULL UNION ALL SELECT J2.JOBMST_ID,
> J2.JOBMST_NAME,
> >> J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1 FROM TIDAL.JOBMST J2 INNER
> JOIN
> >> J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID WHERE J2.JOBMST_PRNTID IS NOT
> NULL )
> >> SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ SELECT * FROM J1 WHERE
> ROWNUM
> >> BETWEEN :s AND :f ORDER BY DISP_SEQ2018-04-27 16:58:13,207 INFO
> >> sqlalchemy.engine.base.Engine {'s': 0, 'f': 1}[('jobmst_id', 57020),
> >> ('jobmst_name', '6G_ZAFIN_PROD'), ('jobmst_prntid', None),
> ('jobmst_type',
> >> 1), ('lvl', 1), ('disp_seq', 

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Jeremy Flowers]

So.. Here it is working at last:
#!/usr/bin/env python
import sqlalchemy
from sqlalchemy.types import Integer
from sqlalchemy.sql.expression import bindparam
from sqlalchemy import text, create_engine, column
stmt = text('WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE,
LVL) AS (' +
'SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1 '
+
'FROM TIDAL.JOBMST ' +
'WHERE JOBMST_PRNTID IS NULL ' +
'UNION ALL ' +
'SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID,
J2.JOBMST_TYPE, ' +
'J1.LVL + 1 ' +
'FROM TIDAL.JOBMST J2 ' +
'INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID '
'WHERE J2.JOBMST_PRNTID IS NOT NULL ' +
') SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ ' +
'SELECT * ' +
'FROM ( SELECT A.*, rownum rn ' +
'FROM ( SELECT * '
'FROM J1 ' +
'ORDER BY DISP_SEQ) A ' +
'WHERE rownum <= :f) ' +
'WHERE rn >= :s')
stmt.columns(column('JOBMST_ID'), column('JOBMST_NAME'),
column('JOBMST_PRNT_ID'), column('LVL'),column('DISP_SEQ'))
stmt.bindparams(bindparam('s', type_=Integer), bindparam('f',
type_=Integer))
engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
echo=False)
connection = engine.connect()
offset = 42735 #42735  #42736 records.. ROWCOUNT starts from 1, not 0
in ORACLE database.
limit = 2
more_results = True
while more_results:
start = offset + 1
finish = offset + limit
print('Start: {0}, Finish {1}'.format(start,finish))
partial_results = connection.execute(stmt, s = start, f =
finish).fetchall()
offset += limit
#print('partial_results: {0}, type:{1}, empty:{2}
'.format(partial_results,type(partial_results), partial_results == []))
if partial_results == []:
more_results = False
else:
for row in partial_results:
print(row.items())
if  offset == 4:
more_results = False
connection.close()

On Fri, Apr 27, 2018 at 6:15 PM, Jeremy Flowers 
wrote:

> SELECT *
> FROM ( SELECT A.*, rownum  rn
>FROM ( SELECT *
> FROM J1
> ORDER BY DISP_SEQ) A
> WHERE rownum <= 4)
> WHERE rn >= 3
>
> This is the crazy type of syntax required...
>
> On Fri, Apr 27, 2018 at 5:12 PM, Jeremy Flowers <
> jeremy.g.flow...@gmail.com> wrote:
>
>> Hah.. Seems Oracle ROWNUM starts from 1, not 0..
>> When you select between 3 and 4 - nothing returns. There's an Oracle
>> quirk...
>>
>> On Fri, Apr 27, 2018 at 5:03 PM, Jeremy Flowers <
>> jeremy.g.flow...@gmail.com> wrote:
>>
>>> Have tried dropping session.execute and using connection.execute - where
>>> the method of parameter binding seems to work...
>>>
>>> ==
>>>
>>> #!/usr/bin/env python
>>> import sqlalchemy
>>> from sqlalchemy.types import Integer
>>> from sqlalchemy.sql.expression import bindparam
>>> from sqlalchemy import text, create_engine, column
>>> stmt = text('WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE,
>>> LVL) AS (' +
>>> 'SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE,
>>> 1 ' +
>>> 'FROM TIDAL.JOBMST ' +
>>> 'WHERE JOBMST_PRNTID IS NULL ' +
>>> 'UNION ALL ' +
>>> 'SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID,
>>> J2.JOBMST_TYPE, ' +
>>> 'J1.LVL + 1 ' +
>>> 'FROM TIDAL.JOBMST J2 ' +
>>> 'INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID '
>>> 'WHERE J2.JOBMST_PRNTID IS NOT NULL ' +
>>> ') SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ ' +
>>> 'SELECT * ' +
>>> 'FROM J1 '+
>>> 'WHERE ROWNUM BETWEEN :s AND :f ' +
>>> 'ORDER BY DISP_SEQ ')
>>> stmt.columns(column('JOBMST_ID'), column('JOBMST_NAME'),
>>> column('JOBMST_PRNT_ID'), column('LVL'),column('DISP_SEQ'))
>>> stmt.bindparams(bindparam('s', type_=Integer), bindparam('f',
>>> type_=Integer))
>>> engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
>>> echo=True)
>>> connection = engine.connect()
>>> offset = 0
>>> limit = 2
>>> more_results = True
>>> while more_results:
>>> start = offset
>>> finish = offset + limit - 1
>>> print('Start: {0}, Finish {1}'.format(start,finish))
>>> partial_results = connection.execute(stmt, s = start, f =
>>> finish).fetchall()
>>> offset += limit
>>> if partial_results == [] or offset == 4:
>>> more_results = False
>>> for row in partial_results:
>>> print(row.items())
>>> connection.close()
>>>
>>> ===
>>>
>>> C:\opt\tidalconversion>jobwalktext.py
>>> 2018-04-27 16:44:58,037 INFO sqlalchemy.engine.base.Engine SELECT USER
>>> FROM DUAL
>>> 2018-04-27 16:44:58,037 INFO sqlalchemy.engine.base.Engine {}
>>> 2018-04-27 16:44:58,040 INFO sqlalchemy.engine.base.Engine SELECT
>>> CAST('test plain returns' AS VARCHAR(60 CHAR)) AS 

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Jeremy Flowers]

SELECT *
FROM ( SELECT A.*, rownum  rn
   FROM ( SELECT *
FROM J1
ORDER BY DISP_SEQ) A
WHERE rownum <= 4)
WHERE rn >= 3

This is the crazy type of syntax required...

On Fri, Apr 27, 2018 at 5:12 PM, Jeremy Flowers 
wrote:

> Hah.. Seems Oracle ROWNUM starts from 1, not 0..
> When you select between 3 and 4 - nothing returns. There's an Oracle
> quirk...
>
> On Fri, Apr 27, 2018 at 5:03 PM, Jeremy Flowers <
> jeremy.g.flow...@gmail.com> wrote:
>
>> Have tried dropping session.execute and using connection.execute - where
>> the method of parameter binding seems to work...
>>
>> ==
>>
>> #!/usr/bin/env python
>> import sqlalchemy
>> from sqlalchemy.types import Integer
>> from sqlalchemy.sql.expression import bindparam
>> from sqlalchemy import text, create_engine, column
>> stmt = text('WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE,
>> LVL) AS (' +
>> 'SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1
>> ' +
>> 'FROM TIDAL.JOBMST ' +
>> 'WHERE JOBMST_PRNTID IS NULL ' +
>> 'UNION ALL ' +
>> 'SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID,
>> J2.JOBMST_TYPE, ' +
>> 'J1.LVL + 1 ' +
>> 'FROM TIDAL.JOBMST J2 ' +
>> 'INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID '
>> 'WHERE J2.JOBMST_PRNTID IS NOT NULL ' +
>> ') SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ ' +
>> 'SELECT * ' +
>> 'FROM J1 '+
>> 'WHERE ROWNUM BETWEEN :s AND :f ' +
>> 'ORDER BY DISP_SEQ ')
>> stmt.columns(column('JOBMST_ID'), column('JOBMST_NAME'),
>> column('JOBMST_PRNT_ID'), column('LVL'),column('DISP_SEQ'))
>> stmt.bindparams(bindparam('s', type_=Integer), bindparam('f',
>> type_=Integer))
>> engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
>> echo=True)
>> connection = engine.connect()
>> offset = 0
>> limit = 2
>> more_results = True
>> while more_results:
>> start = offset
>> finish = offset + limit - 1
>> print('Start: {0}, Finish {1}'.format(start,finish))
>> partial_results = connection.execute(stmt, s = start, f =
>> finish).fetchall()
>> offset += limit
>> if partial_results == [] or offset == 4:
>> more_results = False
>> for row in partial_results:
>> print(row.items())
>> connection.close()
>>
>> ===
>>
>> C:\opt\tidalconversion>jobwalktext.py
>> 2018-04-27 16:44:58,037 INFO sqlalchemy.engine.base.Engine SELECT USER
>> FROM DUAL
>> 2018-04-27 16:44:58,037 INFO sqlalchemy.engine.base.Engine {}
>> 2018-04-27 16:44:58,040 INFO sqlalchemy.engine.base.Engine SELECT
>> CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
>> 2018-04-27 16:44:58,040 INFO sqlalchemy.engine.base.Engine {}
>> 2018-04-27 16:44:58,041 INFO sqlalchemy.engine.base.Engine SELECT
>> CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
>>
>> C:\opt\tidalconversion>jobwalktext.py
>> 2018-04-27 16:58:13,199 INFO sqlalchemy.engine.base.Engine SELECT USER
>> FROM DUAL
>> 2018-04-27 16:58:13,199 INFO sqlalchemy.engine.base.Engine {}
>> 2018-04-27 16:58:13,202 INFO sqlalchemy.engine.base.Engine SELECT
>> CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM
>> DUAL2018-04-27 16:58:13,202 INFO sqlalchemy.engine.base.Engine {}2018-04-27
>> 16:58:13,203 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode
>> returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL2018-04-27 16:58:13,203 INFO
>> sqlalchemy.engine.base.Engine {}2018-04-27 16:58:13,204 INFO
>> sqlalchemy.engine.base.Engine select value from nls_session_parameters
>> where parameter = 'NLS_NUMERIC_CHARACTERS'
>> 2018-04-27 16:58:13,204 INFO sqlalchemy.engine.base.Engine {}
>> Start: 0, Finish 1
>> 2018-04-27 16:58:13,206 INFO sqlalchemy.engine.base.Engine WITH
>> J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS (SELECT
>> JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1 FROM TIDAL.JOBMST
>> WHERE JOBMST_PRNTID IS NULL UNION ALL SELECT J2.JOBMST_ID, J2.JOBMST_NAME,
>> J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1 FROM TIDAL.JOBMST J2 INNER
>> JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID WHERE J2.JOBMST_PRNTID IS NOT
>> NULL ) SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ SELECT * FROM J1
>> WHERE ROWNUM BETWEEN :s AND :f ORDER BY DISP_SEQ2018-04-27 16:58:13,207
>> INFO sqlalchemy.engine.base.Engine {'s': 0, 'f': 1}[('jobmst_id', 57020),
>> ('jobmst_name', '6G_ZAFIN_PROD'), ('jobmst_prntid', None), ('jobmst_type',
>> 1), ('lvl', 1), ('disp_seq', 1)]
>> Start: 2, Finish 3
>> 2018-04-27 16:58:13,510 INFO sqlalchemy.engine.base.Engine WITH
>> J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS (SELECT
>> JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1 FROM TIDAL.JOBMST
>> WHERE JOBMST_PRNTID IS NULL UNION ALL SELECT J2.JOBMST_ID, J2.JOBMST_NAME,
>> J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1 FROM TIDAL.JOBMST J2 

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Mike Bayer]

you don't really need the columns(...) part if you are sending the
text() object to connection.execute().   That's mostly a thing to help
when using the ORM which I don't think you want to be using here.

Also set echo='debug' on create_engine which will log all the rows
coming back.

On Fri, Apr 27, 2018 at 12:12 PM, Jeremy Flowers
 wrote:
> Hah.. Seems Oracle ROWNUM starts from 1, not 0..
> When you select between 3 and 4 - nothing returns. There's an Oracle
> quirk...
>
> On Fri, Apr 27, 2018 at 5:03 PM, Jeremy Flowers 
> wrote:
>>
>> Have tried dropping session.execute and using connection.execute - where
>> the method of parameter binding seems to work...
>>
>> ==
>>
>> #!/usr/bin/env python
>> import sqlalchemy
>> from sqlalchemy.types import Integer
>> from sqlalchemy.sql.expression import bindparam
>> from sqlalchemy import text, create_engine, column
>> stmt = text('WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE,
>> LVL) AS (' +
>> 'SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1
>> ' +
>> 'FROM TIDAL.JOBMST ' +
>> 'WHERE JOBMST_PRNTID IS NULL ' +
>> 'UNION ALL ' +
>> 'SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID,
>> J2.JOBMST_TYPE, ' +
>> 'J1.LVL + 1 ' +
>> 'FROM TIDAL.JOBMST J2 ' +
>> 'INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID '
>> 'WHERE J2.JOBMST_PRNTID IS NOT NULL ' +
>> ') SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ ' +
>> 'SELECT * ' +
>> 'FROM J1 '+
>> 'WHERE ROWNUM BETWEEN :s AND :f ' +
>> 'ORDER BY DISP_SEQ ')
>> stmt.columns(column('JOBMST_ID'), column('JOBMST_NAME'),
>> column('JOBMST_PRNT_ID'), column('LVL'),column('DISP_SEQ'))
>> stmt.bindparams(bindparam('s', type_=Integer), bindparam('f',
>> type_=Integer))
>> engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
>> echo=True)
>> connection = engine.connect()
>> offset = 0
>> limit = 2
>> more_results = True
>> while more_results:
>> start = offset
>> finish = offset + limit - 1
>> print('Start: {0}, Finish {1}'.format(start,finish))
>> partial_results = connection.execute(stmt, s = start, f =
>> finish).fetchall()
>> offset += limit
>> if partial_results == [] or offset == 4:
>> more_results = False
>> for row in partial_results:
>> print(row.items())
>> connection.close()
>>
>> ===
>>
>> C:\opt\tidalconversion>jobwalktext.py
>> 2018-04-27 16:44:58,037 INFO sqlalchemy.engine.base.Engine SELECT USER
>> FROM DUAL
>> 2018-04-27 16:44:58,037 INFO sqlalchemy.engine.base.Engine {}
>> 2018-04-27 16:44:58,040 INFO sqlalchemy.engine.base.Engine SELECT
>> CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
>> 2018-04-27 16:44:58,040 INFO sqlalchemy.engine.base.Engine {}
>> 2018-04-27 16:44:58,041 INFO sqlalchemy.engine.base.Engine SELECT
>> CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
>>
>> C:\opt\tidalconversion>jobwalktext.py
>> 2018-04-27 16:58:13,199 INFO sqlalchemy.engine.base.Engine SELECT USER
>> FROM DUAL
>> 2018-04-27 16:58:13,199 INFO sqlalchemy.engine.base.Engine {}
>> 2018-04-27 16:58:13,202 INFO sqlalchemy.engine.base.Engine SELECT
>> CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL2018-04-27
>> 16:58:13,202 INFO sqlalchemy.engine.base.Engine {}2018-04-27 16:58:13,203
>> INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS
>> NVARCHAR2(60)) AS anon_1 FROM DUAL2018-04-27 16:58:13,203 INFO
>> sqlalchemy.engine.base.Engine {}2018-04-27 16:58:13,204 INFO
>> sqlalchemy.engine.base.Engine select value from nls_session_parameters where
>> parameter = 'NLS_NUMERIC_CHARACTERS'
>> 2018-04-27 16:58:13,204 INFO sqlalchemy.engine.base.Engine {}
>> Start: 0, Finish 1
>> 2018-04-27 16:58:13,206 INFO sqlalchemy.engine.base.Engine WITH
>> J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS (SELECT
>> JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1 FROM TIDAL.JOBMST
>> WHERE JOBMST_PRNTID IS NULL UNION ALL SELECT J2.JOBMST_ID, J2.JOBMST_NAME,
>> J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1 FROM TIDAL.JOBMST J2 INNER JOIN
>> J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID WHERE J2.JOBMST_PRNTID IS NOT NULL )
>> SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ SELECT * FROM J1 WHERE ROWNUM
>> BETWEEN :s AND :f ORDER BY DISP_SEQ2018-04-27 16:58:13,207 INFO
>> sqlalchemy.engine.base.Engine {'s': 0, 'f': 1}[('jobmst_id', 57020),
>> ('jobmst_name', '6G_ZAFIN_PROD'), ('jobmst_prntid', None), ('jobmst_type',
>> 1), ('lvl', 1), ('disp_seq', 1)]
>> Start: 2, Finish 3
>> 2018-04-27 16:58:13,510 INFO sqlalchemy.engine.base.Engine WITH
>> J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS (SELECT
>> JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1 FROM TIDAL.JOBMST
>> WHERE JOBMST_PRNTID IS NULL UNION ALL SELECT J2.JOBMST_ID, 

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Jeremy Flowers]

Hah.. Seems Oracle ROWNUM starts from 1, not 0..
When you select between 3 and 4 - nothing returns. There's an Oracle
quirk...

On Fri, Apr 27, 2018 at 5:03 PM, Jeremy Flowers 
wrote:

> Have tried dropping session.execute and using connection.execute - where
> the method of parameter binding seems to work...
>
> ==
>
> #!/usr/bin/env python
> import sqlalchemy
> from sqlalchemy.types import Integer
> from sqlalchemy.sql.expression import bindparam
> from sqlalchemy import text, create_engine, column
> stmt = text('WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE,
> LVL) AS (' +
> 'SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1
> ' +
> 'FROM TIDAL.JOBMST ' +
> 'WHERE JOBMST_PRNTID IS NULL ' +
> 'UNION ALL ' +
> 'SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID,
> J2.JOBMST_TYPE, ' +
> 'J1.LVL + 1 ' +
> 'FROM TIDAL.JOBMST J2 ' +
> 'INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID '
> 'WHERE J2.JOBMST_PRNTID IS NOT NULL ' +
> ') SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ ' +
> 'SELECT * ' +
> 'FROM J1 '+
> 'WHERE ROWNUM BETWEEN :s AND :f ' +
> 'ORDER BY DISP_SEQ ')
> stmt.columns(column('JOBMST_ID'), column('JOBMST_NAME'),
> column('JOBMST_PRNT_ID'), column('LVL'),column('DISP_SEQ'))
> stmt.bindparams(bindparam('s', type_=Integer), bindparam('f',
> type_=Integer))
> engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
> echo=True)
> connection = engine.connect()
> offset = 0
> limit = 2
> more_results = True
> while more_results:
> start = offset
> finish = offset + limit - 1
> print('Start: {0}, Finish {1}'.format(start,finish))
> partial_results = connection.execute(stmt, s = start, f =
> finish).fetchall()
> offset += limit
> if partial_results == [] or offset == 4:
> more_results = False
> for row in partial_results:
> print(row.items())
> connection.close()
>
> ===
>
> C:\opt\tidalconversion>jobwalktext.py
> 2018-04-27 16:44:58,037 INFO sqlalchemy.engine.base.Engine SELECT USER
> FROM DUAL
> 2018-04-27 16:44:58,037 INFO sqlalchemy.engine.base.Engine {}
> 2018-04-27 16:44:58,040 INFO sqlalchemy.engine.base.Engine SELECT
> CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
> 2018-04-27 16:44:58,040 INFO sqlalchemy.engine.base.Engine {}
> 2018-04-27 16:44:58,041 INFO sqlalchemy.engine.base.Engine SELECT
> CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
>
> C:\opt\tidalconversion>jobwalktext.py
> 2018-04-27 16:58:13,199 INFO sqlalchemy.engine.base.Engine SELECT USER
> FROM DUAL
> 2018-04-27 16:58:13,199 INFO sqlalchemy.engine.base.Engine {}
> 2018-04-27 16:58:13,202 INFO sqlalchemy.engine.base.Engine SELECT
> CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM
> DUAL2018-04-27 16:58:13,202 INFO sqlalchemy.engine.base.Engine {}2018-04-27
> 16:58:13,203 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode
> returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL2018-04-27 16:58:13,203 INFO
> sqlalchemy.engine.base.Engine {}2018-04-27 16:58:13,204 INFO
> sqlalchemy.engine.base.Engine select value from nls_session_parameters
> where parameter = 'NLS_NUMERIC_CHARACTERS'
> 2018-04-27 16:58:13,204 INFO sqlalchemy.engine.base.Engine {}
> Start: 0, Finish 1
> 2018-04-27 16:58:13,206 INFO sqlalchemy.engine.base.Engine WITH
> J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS (SELECT
> JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1 FROM TIDAL.JOBMST
> WHERE JOBMST_PRNTID IS NULL UNION ALL SELECT J2.JOBMST_ID, J2.JOBMST_NAME,
> J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1 FROM TIDAL.JOBMST J2 INNER
> JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID WHERE J2.JOBMST_PRNTID IS NOT
> NULL ) SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ SELECT * FROM J1
> WHERE ROWNUM BETWEEN :s AND :f ORDER BY DISP_SEQ2018-04-27 16:58:13,207
> INFO sqlalchemy.engine.base.Engine {'s': 0, 'f': 1}[('jobmst_id', 57020),
> ('jobmst_name', '6G_ZAFIN_PROD'), ('jobmst_prntid', None), ('jobmst_type',
> 1), ('lvl', 1), ('disp_seq', 1)]
> Start: 2, Finish 3
> 2018-04-27 16:58:13,510 INFO sqlalchemy.engine.base.Engine WITH
> J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS (SELECT
> JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1 FROM TIDAL.JOBMST
> WHERE JOBMST_PRNTID IS NULL UNION ALL SELECT J2.JOBMST_ID, J2.JOBMST_NAME,
> J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1 FROM TIDAL.JOBMST J2 INNER
> JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID WHERE J2.JOBMST_PRNTID IS NOT
> NULL ) SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ SELECT * FROM J1
> WHERE ROWNUM BETWEEN :s AND :f ORDER BY DISP_SEQ
> 2018-04-27 16:58:13,511 INFO sqlalchemy.engine.base.Engine {'s': 2, 'f': 3}
>
> C:\opt\tidalconversion>
>
> ===
>
> For some bizarre reasone, the tuple for the row only prints first record..
> I can 

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Jeremy Flowers]

Have tried dropping session.execute and using connection.execute - where
the method of parameter binding seems to work...

==

#!/usr/bin/env python
import sqlalchemy
from sqlalchemy.types import Integer
from sqlalchemy.sql.expression import bindparam
from sqlalchemy import text, create_engine, column
stmt = text('WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE,
LVL) AS (' +
'SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1 '
+
'FROM TIDAL.JOBMST ' +
'WHERE JOBMST_PRNTID IS NULL ' +
'UNION ALL ' +
'SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID,
J2.JOBMST_TYPE, ' +
'J1.LVL + 1 ' +
'FROM TIDAL.JOBMST J2 ' +
'INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID '
'WHERE J2.JOBMST_PRNTID IS NOT NULL ' +
') SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ ' +
'SELECT * ' +
'FROM J1 '+
'WHERE ROWNUM BETWEEN :s AND :f ' +
'ORDER BY DISP_SEQ ')
stmt.columns(column('JOBMST_ID'), column('JOBMST_NAME'),
column('JOBMST_PRNT_ID'), column('LVL'),column('DISP_SEQ'))
stmt.bindparams(bindparam('s', type_=Integer), bindparam('f',
type_=Integer))
engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
echo=True)
connection = engine.connect()
offset = 0
limit = 2
more_results = True
while more_results:
start = offset
finish = offset + limit - 1
print('Start: {0}, Finish {1}'.format(start,finish))
partial_results = connection.execute(stmt, s = start, f =
finish).fetchall()
offset += limit
if partial_results == [] or offset == 4:
more_results = False
for row in partial_results:
print(row.items())
connection.close()

===

C:\opt\tidalconversion>jobwalktext.py
2018-04-27 16:44:58,037 INFO sqlalchemy.engine.base.Engine SELECT USER FROM
DUAL
2018-04-27 16:44:58,037 INFO sqlalchemy.engine.base.Engine {}
2018-04-27 16:44:58,040 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
2018-04-27 16:44:58,040 INFO sqlalchemy.engine.base.Engine {}
2018-04-27 16:44:58,041 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL

C:\opt\tidalconversion>jobwalktext.py
2018-04-27 16:58:13,199 INFO sqlalchemy.engine.base.Engine SELECT USER FROM
DUAL
2018-04-27 16:58:13,199 INFO sqlalchemy.engine.base.Engine {}
2018-04-27 16:58:13,202 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM
DUAL2018-04-27 16:58:13,202 INFO sqlalchemy.engine.base.Engine {}2018-04-27
16:58:13,203 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode
returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL2018-04-27 16:58:13,203 INFO
sqlalchemy.engine.base.Engine {}2018-04-27 16:58:13,204 INFO
sqlalchemy.engine.base.Engine select value from nls_session_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS'
2018-04-27 16:58:13,204 INFO sqlalchemy.engine.base.Engine {}
Start: 0, Finish 1
2018-04-27 16:58:13,206 INFO sqlalchemy.engine.base.Engine WITH
J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS (SELECT
JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1 FROM TIDAL.JOBMST
WHERE JOBMST_PRNTID IS NULL UNION ALL SELECT J2.JOBMST_ID, J2.JOBMST_NAME,
J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1 FROM TIDAL.JOBMST J2 INNER
JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID WHERE J2.JOBMST_PRNTID IS NOT
NULL ) SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ SELECT * FROM J1
WHERE ROWNUM BETWEEN :s AND :f ORDER BY DISP_SEQ2018-04-27 16:58:13,207
INFO sqlalchemy.engine.base.Engine {'s': 0, 'f': 1}[('jobmst_id', 57020),
('jobmst_name', '6G_ZAFIN_PROD'), ('jobmst_prntid', None), ('jobmst_type',
1), ('lvl', 1), ('disp_seq', 1)]
Start: 2, Finish 3
2018-04-27 16:58:13,510 INFO sqlalchemy.engine.base.Engine WITH
J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS (SELECT
JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1 FROM TIDAL.JOBMST
WHERE JOBMST_PRNTID IS NULL UNION ALL SELECT J2.JOBMST_ID, J2.JOBMST_NAME,
J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1 FROM TIDAL.JOBMST J2 INNER
JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID WHERE J2.JOBMST_PRNTID IS NOT
NULL ) SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ SELECT * FROM J1
WHERE ROWNUM BETWEEN :s AND :f ORDER BY DISP_SEQ
2018-04-27 16:58:13,511 INFO sqlalchemy.engine.base.Engine {'s': 2, 'f': 3}

C:\opt\tidalconversion>

===

For some bizarre reasone, the tuple for the row only prints first record..
I can see the record selection works correctly.. had to move where above
order by since before..
It seems the parameter binding works fine. Don't thing I necessarly needed
have stmt.bindparams - seemed to work equally well without that.
Anyone see anything obvious?
==
Here's same output with , echo=False
==
C:\opt\tidalconversion>jobwalktext.py
Start: 0, Finish 1
[('jobmst_id', 57020), ('jobmst_name', 

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Jeremy Flowers]

I'm thinking along these lines. But can't get it to work.
#!/usr/bin/env python
import sqlalchemy
from sqlalchemy import text, create_engine, column
from sqlalchemy.orm import Session, sessionmaker
stmt = text('''WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE,
LVL) AS  (
  SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID,
JOBMST_TYPE, 1
  FROM TIDAL.JOBMST
  WHERE JOBMST_PRNTID IS NULL
  UNION ALL
  SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID,
J2.JOBMST_TYPE,
  J1.LVL + 1
  FROM TIDAL.JOBMST J2
  INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID
  WHERE J2.JOBMST_PRNTID IS NOT NULL
  ) SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
  SELECT *
  FROM J1
  ORDER BY DISP_SEQ
  WHERE ROWNUM BETWEEN :s AND :f
  ''')
stmt.columns(column('JOBMST_ID'), column('JOBMST_NAME'),
column('JOBMST_PRNT_ID'), column('LVL'),column('DISP_SEQ'))
engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
echo=False)
Session = sessionmaker(bind=engine)
ses = Session()
more_results = True
offset = 0
limit = 2
while more_results:
start = offset
finish = offset + limit - 1
partial_results = ses.execute(stmt, s = start, f = finish)
offset += limit
if partial_results == [] or offset == 4:
more_results = False
for row in partial_results:
print(row.items())

Getting
TypeError: get_bind() got an unexpected keyword argument 's'

Got idea from here...
https://security.openstack.org/guidelines/dg_parameterize-database-queries.html
- it's obviously wrong!

On Fri, Apr 27, 2018 at 3:49 PM, Jeremy Flowers 
wrote:

> Right so some sort of statement bind..
> Just found this didn't work:
>
> #!/usr/bin/env python
> import sqlalchemy
> from sqlalchemy import text, create_engine, column
> from sqlalchemy.orm import Session, sessionmaker
> stmt = text('''WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE,
> LVL) AS  (
>   SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID,
> JOBMST_TYPE, 1
>   FROM TIDAL.JOBMST
>   WHERE JOBMST_PRNTID IS NULL
>   UNION ALL
>   SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID,
> J2.JOBMST_TYPE,
>   J1.LVL + 1
>   FROM TIDAL.JOBMST J2
>   INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID
>   WHERE J2.JOBMST_PRNTID IS NOT NULL
>   ) SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
>   SELECT *
>   FROM J1
>   ORDER BY DISP_SEQ''')
> stmt.columns(column('JOBMST_ID'), column('JOBMST_NAME'),
> column('JOBMST_PRNT_ID'), column('LVL'),column('DISP_SEQ'))
> engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
> echo=False)
> Session = sessionmaker(bind=engine)
> ses = Session()
> more_results = True
> offset = 0
> limit = 2
> while more_results:
> partial_results = ses.execute(stmt).offset(offset).limit(limit)
> offset += limit
> if partial_results == [] or offset == 4:
> more_results = False
> for row in partial_results:
> print(row.items())
>
> On Fri, Apr 27, 2018 at 3:19 PM, Simon King 
> wrote:
>
>> No, you'll need to convert that to the equivalent SQL.
>>
>> On Fri, Apr 27, 2018 at 3:14 PM, Jeremy Flowers
>>  wrote:
>> > But can you still do things like slice(0,5)? Struggling to get that to
>> work
>> > atm...
>> >
>> > On Fri, Apr 27, 2018 at 2:48 PM, Simon King 
>> wrote:
>> >>
>> >> On Fri, Apr 27, 2018 at 12:57 PM, Jeremy Flowers
>> >>  wrote:
>> >> > Right...
>> >> > Been trawling back thru this chain of exchanges..
>> >> > Looking for this:
>> >> >>
>> >> >> At this point I would echo Mike's question: why can't you just use
>> >> >> "text()"?
>> >> >
>> >> > Just spotted another comment from Mike, that I've just fixed too...
>> >> >>
>> >> >>  the literal() has to be against the regular value 1 and not the
>> >> >> string...
>> >> >
>> >> >
>> >> >> as a practical matter, this query is Oracle-specific in any
>> >> >> case, is there a reason you can't just use text() ?   The reason
>> >> >> text() exists is for when one has the exact SQL they want already
>> and
>> >> >> there is no need to work it into the expression language.
>> >> >
>> >> >
>> >> > Can I just put that into some sort of session execute?
>> >> > If there's a relevant example someone can point me to, it would help.
>> >> > I'll
>> >> > have a dig around.
>> >> >
>> >>
>> >> There are a few examples of using text() with the ORM here:
>> >>
>> >>
>> >> http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using
>> -textual-sql
>> >>
>> >> session.execute() behaves more like the 

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Jeremy Flowers]

Right so some sort of statement bind..
Just found this didn't work:

#!/usr/bin/env python
import sqlalchemy
from sqlalchemy import text, create_engine, column
from sqlalchemy.orm import Session, sessionmaker
stmt = text('''WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE,
LVL) AS  (
  SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID,
JOBMST_TYPE, 1
  FROM TIDAL.JOBMST
  WHERE JOBMST_PRNTID IS NULL
  UNION ALL
  SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID,
J2.JOBMST_TYPE,
  J1.LVL + 1
  FROM TIDAL.JOBMST J2
  INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID
  WHERE J2.JOBMST_PRNTID IS NOT NULL
  ) SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
  SELECT *
  FROM J1
  ORDER BY DISP_SEQ''')
stmt.columns(column('JOBMST_ID'), column('JOBMST_NAME'),
column('JOBMST_PRNT_ID'), column('LVL'),column('DISP_SEQ'))
engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
echo=False)
Session = sessionmaker(bind=engine)
ses = Session()
more_results = True
offset = 0
limit = 2
while more_results:
partial_results = ses.execute(stmt).offset(offset).limit(limit)
offset += limit
if partial_results == [] or offset == 4:
more_results = False
for row in partial_results:
print(row.items())

On Fri, Apr 27, 2018 at 3:19 PM, Simon King  wrote:

> No, you'll need to convert that to the equivalent SQL.
>
> On Fri, Apr 27, 2018 at 3:14 PM, Jeremy Flowers
>  wrote:
> > But can you still do things like slice(0,5)? Struggling to get that to
> work
> > atm...
> >
> > On Fri, Apr 27, 2018 at 2:48 PM, Simon King 
> wrote:
> >>
> >> On Fri, Apr 27, 2018 at 12:57 PM, Jeremy Flowers
> >>  wrote:
> >> > Right...
> >> > Been trawling back thru this chain of exchanges..
> >> > Looking for this:
> >> >>
> >> >> At this point I would echo Mike's question: why can't you just use
> >> >> "text()"?
> >> >
> >> > Just spotted another comment from Mike, that I've just fixed too...
> >> >>
> >> >>  the literal() has to be against the regular value 1 and not the
> >> >> string...
> >> >
> >> >
> >> >> as a practical matter, this query is Oracle-specific in any
> >> >> case, is there a reason you can't just use text() ?   The reason
> >> >> text() exists is for when one has the exact SQL they want already and
> >> >> there is no need to work it into the expression language.
> >> >
> >> >
> >> > Can I just put that into some sort of session execute?
> >> > If there's a relevant example someone can point me to, it would help.
> >> > I'll
> >> > have a dig around.
> >> >
> >>
> >> There are a few examples of using text() with the ORM here:
> >>
> >>
> >> http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#
> using-textual-sql
> >>
> >> session.execute() behaves more like the Core-level
> >> connection.execute(), which is described here:
> >>
> >>
> >> http://docs.sqlalchemy.org/en/latest/core/tutorial.html#
> sqlexpression-text
> >>
> >> Simon
> >>
> >> --
> >> SQLAlchemy -
> >> The Python SQL Toolkit and Object Relational Mapper
> >>
> >> http://www.sqlalchemy.org/
> >>
> >> To post example code, please provide an MCVE: Minimal, Complete, and
> >> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full
> >> description.
> >> ---
> >> You received this message because you are subscribed to a topic in the
> >> Google Groups "sqlalchemy" group.
> >> To unsubscribe from this topic, visit
> >> https://groups.google.com/d/topic/sqlalchemy/aWJT1VdY3LU/unsubscribe.
> >> To unsubscribe from this group and all its topics, send an email to
> >> sqlalchemy+unsubscr...@googlegroups.com.
> >> To post to this group, send email to sqlalchemy@googlegroups.com.
> >> Visit this group at https://groups.google.com/group/sqlalchemy.
> >> For more options, visit https://groups.google.com/d/optout.
> >
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> > description.
> > ---
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send an
> > email to sqlalchemy+unsubscr...@googlegroups.com.
> > To post to this group, send email to sqlalchemy@googlegroups.com.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> 

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Simon King]

No, you'll need to convert that to the equivalent SQL.

On Fri, Apr 27, 2018 at 3:14 PM, Jeremy Flowers
 wrote:
> But can you still do things like slice(0,5)? Struggling to get that to work
> atm...
>
> On Fri, Apr 27, 2018 at 2:48 PM, Simon King  wrote:
>>
>> On Fri, Apr 27, 2018 at 12:57 PM, Jeremy Flowers
>>  wrote:
>> > Right...
>> > Been trawling back thru this chain of exchanges..
>> > Looking for this:
>> >>
>> >> At this point I would echo Mike's question: why can't you just use
>> >> "text()"?
>> >
>> > Just spotted another comment from Mike, that I've just fixed too...
>> >>
>> >>  the literal() has to be against the regular value 1 and not the
>> >> string...
>> >
>> >
>> >> as a practical matter, this query is Oracle-specific in any
>> >> case, is there a reason you can't just use text() ?   The reason
>> >> text() exists is for when one has the exact SQL they want already and
>> >> there is no need to work it into the expression language.
>> >
>> >
>> > Can I just put that into some sort of session execute?
>> > If there's a relevant example someone can point me to, it would help.
>> > I'll
>> > have a dig around.
>> >
>>
>> There are a few examples of using text() with the ORM here:
>>
>>
>> http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-textual-sql
>>
>> session.execute() behaves more like the Core-level
>> connection.execute(), which is described here:
>>
>>
>> http://docs.sqlalchemy.org/en/latest/core/tutorial.html#sqlexpression-text
>>
>> Simon
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to a topic in the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/sqlalchemy/aWJT1VdY3LU/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Jeremy Flowers]

Ah right..
http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.ResultProxy
You can do fetchmany - and do a partial_results compare to empty list..
I see there is a next option too.
So, it's a slightly different paradigm

On Fri, Apr 27, 2018 at 3:14 PM, Jeremy Flowers 
wrote:

> But can you still do things like slice(0,5)? Struggling to get that to
> work atm...
>
> On Fri, Apr 27, 2018 at 2:48 PM, Simon King 
> wrote:
>
>> On Fri, Apr 27, 2018 at 12:57 PM, Jeremy Flowers
>>  wrote:
>> > Right...
>> > Been trawling back thru this chain of exchanges..
>> > Looking for this:
>> >>
>> >> At this point I would echo Mike's question: why can't you just use
>> >> "text()"?
>> >
>> > Just spotted another comment from Mike, that I've just fixed too...
>> >>
>> >>  the literal() has to be against the regular value 1 and not the
>> string...
>> >
>> >
>> >> as a practical matter, this query is Oracle-specific in any
>> >> case, is there a reason you can't just use text() ?   The reason
>> >> text() exists is for when one has the exact SQL they want already and
>> >> there is no need to work it into the expression language.
>> >
>> >
>> > Can I just put that into some sort of session execute?
>> > If there's a relevant example someone can point me to, it would help.
>> I'll
>> > have a dig around.
>> >
>>
>> There are a few examples of using text() with the ORM here:
>>
>> http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using
>> -textual-sql
>>
>> session.execute() behaves more like the Core-level
>> connection.execute(), which is described here:
>>
>> http://docs.sqlalchemy.org/en/latest/core/tutorial.html#sqle
>> xpression-text
>>
>> Simon
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to a topic in the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit https://groups.google.com/d/to
>> pic/sqlalchemy/aWJT1VdY3LU/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Jeremy Flowers]

But can you still do things like slice(0,5)? Struggling to get that to work
atm...

On Fri, Apr 27, 2018 at 2:48 PM, Simon King  wrote:

> On Fri, Apr 27, 2018 at 12:57 PM, Jeremy Flowers
>  wrote:
> > Right...
> > Been trawling back thru this chain of exchanges..
> > Looking for this:
> >>
> >> At this point I would echo Mike's question: why can't you just use
> >> "text()"?
> >
> > Just spotted another comment from Mike, that I've just fixed too...
> >>
> >>  the literal() has to be against the regular value 1 and not the
> string...
> >
> >
> >> as a practical matter, this query is Oracle-specific in any
> >> case, is there a reason you can't just use text() ?   The reason
> >> text() exists is for when one has the exact SQL they want already and
> >> there is no need to work it into the expression language.
> >
> >
> > Can I just put that into some sort of session execute?
> > If there's a relevant example someone can point me to, it would help.
> I'll
> > have a dig around.
> >
>
> There are a few examples of using text() with the ORM here:
>
> http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#
> using-textual-sql
>
> session.execute() behaves more like the Core-level
> connection.execute(), which is described here:
>
> http://docs.sqlalchemy.org/en/latest/core/tutorial.html#
> sqlexpression-text
>
> Simon
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/sqlalchemy/aWJT1VdY3LU/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Simon King]

On Fri, Apr 27, 2018 at 12:57 PM, Jeremy Flowers
 wrote:
> Right...
> Been trawling back thru this chain of exchanges..
> Looking for this:
>>
>> At this point I would echo Mike's question: why can't you just use
>> "text()"?
>
> Just spotted another comment from Mike, that I've just fixed too...
>>
>>  the literal() has to be against the regular value 1 and not the string...
>
>
>> as a practical matter, this query is Oracle-specific in any
>> case, is there a reason you can't just use text() ?   The reason
>> text() exists is for when one has the exact SQL they want already and
>> there is no need to work it into the expression language.
>
>
> Can I just put that into some sort of session execute?
> If there's a relevant example someone can point me to, it would help. I'll
> have a dig around.
>

There are a few examples of using text() with the ORM here:

http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-textual-sql

session.execute() behaves more like the Core-level
connection.execute(), which is described here:

http://docs.sqlalchemy.org/en/latest/core/tutorial.html#sqlexpression-text

Simon

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Mike Bayer]

the Oracle dialect has to run complex gymnastics to get the effect of
LIMIT in rewriting the query, so that is likely failing with a query
that has a CTE inside of it as the CTE feature is not widely used with
Oracle and we haven't put development resources into these cases.
For now I'd avoid any use of limit/offset.   first() is documented as
using limit in the tutorial:
http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#returning-lists-and-scalars
  Use all()[0] instead for a query like this.



On Fri, Apr 27, 2018 at 7:57 AM, Jeremy Flowers
 wrote:
> Right...
> Been trawling back thru this chain of exchanges..
> Looking for this:
>>
>> At this point I would echo Mike's question: why can't you just use
>> "text()"?
>
> Just spotted another comment from Mike, that I've just fixed too...
>>
>>  the literal() has to be against the regular value 1 and not the string...
>
>
>> as a practical matter, this query is Oracle-specific in any
>> case, is there a reason you can't just use text() ?   The reason
>> text() exists is for when one has the exact SQL they want already and
>> there is no need to work it into the expression language.
>
>
> Can I just put that into some sort of session execute?
> If there's a relevant example someone can point me to, it would help. I'll
> have a dig around.
>
>> .first() ... Oracle apparently doesn't support the LIMIT
>
>
> Yes. I'd noticed the solution for that when you do slice too. I think the
> example output given in the docs was for Postgresql - which is far more
> elegant. (LIMIT & OFFSET)
> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.slice
> With Oracle you have an or conjunction. I was surprised that wasn't
> implemented with between. That would have been more intuitive in my eyes.
>
> On Fri, Apr 27, 2018 at 10:31 AM, Simon King  wrote:
>>
>> The ".first()" method applies the DB-specific equivalent of "LIMIT 1"
>> to the query. Oracle apparently doesn't support the LIMIT keyword, so
>> SQLAlchemy wraps the entire query in "SELECT ... FROM (subquery) WHERE
>> ROWNUM <= limit":
>>
>>
>> http://docs.sqlalchemy.org/en/latest/dialects/oracle.html#limit-offset-support
>>
>> The code to do this is a bit hairy:
>>
>>
>> https://bitbucket.org/zzzeek/sqlalchemy/src/30b02003a70f37aa83e20de6229afe2a3600b648/lib/sqlalchemy/dialects/oracle/base.py#base.py-813
>>
>> I suspect that it's not handling your CTE properly. I don't even know
>> what the right SQL syntax should be.
>>
>> At this point I would echo Mike's question: why can't you just use
>> "text()"?
>>
>> Simon
>>
>> On Fri, Apr 27, 2018 at 10:07 AM, Jeremy Flowers
>>  wrote:
>> > Well the printed oracle sql dialect now works correctly - when I hack it
>> > an
>> > replace the substitution variables for the literals like so in my SQL
>> > Editor:
>> > ==
>> > WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS
>> > (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS
>> > jobmst_name,
>> > jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS
>> > jobmst_type, 1
>> > AS lvl
>> > FROM jobmst
>> > WHERE jobmst.jobmst_prntid IS NULL
>> > UNION ALL
>> > SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name,
>> > j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type,
>> > j1.lvl + 1
>> > FROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id
>> > WHERE j2.jobmst_prntid IS NOT NULL)
>> >  SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
>> > SELECT j1.jobmst_id, j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type,
>> > j1.lvl
>> > FROM j1 ORDER BY DISP_SEQ
>> >
>> > ===
>> >
>> > But for some reason, the for row in stmt.first() when I run my code
>> > listed
>> > below yields:  "ORA-00907: missing right parenthesis":
>> >
>> > #!/usr/bin/env python
>> > import sqlalchemy
>> > from tidal import Jobmst
>> > from sqlalchemy import create_engine, literal, text
>> > from sqlalchemy.dialects import oracle
>> > from sqlalchemy.orm import Session, aliased, sessionmaker
>> > from sqlalchemy.ext.compiler import compiles
>> > from sqlalchemy.sql.expression import Select
>> > import re
>> >
>> > @compiles(Select, "oracle")
>> > def _add_search_hints(element, compiler, **kw):
>> > text = compiler.visit_select(element, **kw)
>> > for prefix, _ in element._prefixes:
>> > prefix = prefix.text
>> > text = re.sub(r'SELECT %s' % prefix, "%s SELECT" % prefix, text)
>> > return text
>> >
>> > engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
>> > echo=True)
>> > Session = sessionmaker(bind=engine)
>> > ses = Session()
>> > j2 = aliased(Jobmst, name='j2')
>> > j1 = ses.query(
>> >   Jobmst.jobmst_id, Jobmst.jobmst_name,
>> >   Jobmst.jobmst_prntid, Jobmst.jobmst_type, literal('1').label('lvl')
>> > ).filter(Jobmst.jobmst_prntid == None).cte(recursive=True,name='j1')
>> > j1 = j1.union_all(
>> >

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Jeremy Flowers]

Right...
Been trawling back thru this chain of exchanges..
Looking for this:

> At this point I would echo Mike's question: why can't you just use
> "text()"?

Just spotted another comment from Mike, that I've just fixed too...

>  the literal() has to be against the regular value 1 and not the string...


as a practical matter, this query is Oracle-specific in any
> case, is there a reason you can't just use text() ?   The reason
> text() exists is for when one has the exact SQL they want already and
> there is no need to work it into the expression language.


Can I just put that into some sort of session execute?
If there's a relevant example someone can point me to, it would help. I'll
have a dig around.

.first() ... Oracle apparently doesn't support the LIMIT


Yes. I'd noticed the solution for that when you do slice too. I think the
example output given in the docs was for Postgresql - which is far more
elegant. (LIMIT & OFFSET)
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.slice

With Oracle you have an or conjunction. I was surprised that wasn't
implemented with between. That would have been more intuitive in my eyes.

On Fri, Apr 27, 2018 at 10:31 AM, Simon King  wrote:

> The ".first()" method applies the DB-specific equivalent of "LIMIT 1"
> to the query. Oracle apparently doesn't support the LIMIT keyword, so
> SQLAlchemy wraps the entire query in "SELECT ... FROM (subquery) WHERE
> ROWNUM <= limit":
>
> http://docs.sqlalchemy.org/en/latest/dialects/oracle.html#
> limit-offset-support
>
> The code to do this is a bit hairy:
>
> https://bitbucket.org/zzzeek/sqlalchemy/src/30b02003a70f37aa83e20de6229afe
> 2a3600b648/lib/sqlalchemy/dialects/oracle/base.py#base.py-813
>
> I suspect that it's not handling your CTE properly. I don't even know
> what the right SQL syntax should be.
>
> At this point I would echo Mike's question: why can't you just use
> "text()"?
>
> Simon
>
> On Fri, Apr 27, 2018 at 10:07 AM, Jeremy Flowers
>  wrote:
> > Well the printed oracle sql dialect now works correctly - when I hack it
> an
> > replace the substitution variables for the literals like so in my SQL
> > Editor:
> > ==
> > WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS
> > (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name,
> > jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS
> jobmst_type, 1
> > AS lvl
> > FROM jobmst
> > WHERE jobmst.jobmst_prntid IS NULL
> > UNION ALL
> > SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name,
> > j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type,
> > j1.lvl + 1
> > FROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id
> > WHERE j2.jobmst_prntid IS NOT NULL)
> >  SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
> > SELECT j1.jobmst_id, j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type,
> > j1.lvl
> > FROM j1 ORDER BY DISP_SEQ
> >
> > ===
> >
> > But for some reason, the for row in stmt.first() when I run my code
> listed
> > below yields:  "ORA-00907: missing right parenthesis":
> >
> > #!/usr/bin/env python
> > import sqlalchemy
> > from tidal import Jobmst
> > from sqlalchemy import create_engine, literal, text
> > from sqlalchemy.dialects import oracle
> > from sqlalchemy.orm import Session, aliased, sessionmaker
> > from sqlalchemy.ext.compiler import compiles
> > from sqlalchemy.sql.expression import Select
> > import re
> >
> > @compiles(Select, "oracle")
> > def _add_search_hints(element, compiler, **kw):
> > text = compiler.visit_select(element, **kw)
> > for prefix, _ in element._prefixes:
> > prefix = prefix.text
> > text = re.sub(r'SELECT %s' % prefix, "%s SELECT" % prefix, text)
> > return text
> >
> > engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
> > echo=True)
> > Session = sessionmaker(bind=engine)
> > ses = Session()
> > j2 = aliased(Jobmst, name='j2')
> > j1 = ses.query(
> >   Jobmst.jobmst_id, Jobmst.jobmst_name,
> >   Jobmst.jobmst_prntid, Jobmst.jobmst_type, literal('1').label('lvl')
> > ).filter(Jobmst.jobmst_prntid == None).cte(recursive=True,name='j1')
> > j1 = j1.union_all(
> >ses.query(j2.jobmst_id, j2.jobmst_name,
> >  j2.jobmst_prntid, j2.jobmst_type,
> >  j1.c.lvl + 1
> > ).join(j1, j2.jobmst_prntid == j1.c.jobmst_id
> >   ).filter(j2.jobmst_prntid != None)
> > )
> > stmt = ses.query(j1).prefix_with('SEARCH DEPTH FIRST BY JOBMST_NAME SET
> > DISP_SEQ').order_by(text('DISP_SEQ'))
> > oraclesql = stmt.statement.compile(dialect=oracle.dialect())
> > print('oraclesql: ', oraclesql)
> > for row in stmt.first():
> >   print(row)
> >
> > 
> >
> > Here is the output:
> >
> > ===
> > C:\opt\tidalconversion>jobwalk.py
> > oraclesql:  WITH j1(jobmst_id, jobmst_name, 

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Simon King]

The ".first()" method applies the DB-specific equivalent of "LIMIT 1"
to the query. Oracle apparently doesn't support the LIMIT keyword, so
SQLAlchemy wraps the entire query in "SELECT ... FROM (subquery) WHERE
ROWNUM <= limit":

http://docs.sqlalchemy.org/en/latest/dialects/oracle.html#limit-offset-support

The code to do this is a bit hairy:

https://bitbucket.org/zzzeek/sqlalchemy/src/30b02003a70f37aa83e20de6229afe2a3600b648/lib/sqlalchemy/dialects/oracle/base.py#base.py-813

I suspect that it's not handling your CTE properly. I don't even know
what the right SQL syntax should be.

At this point I would echo Mike's question: why can't you just use "text()"?

Simon

On Fri, Apr 27, 2018 at 10:07 AM, Jeremy Flowers
 wrote:
> Well the printed oracle sql dialect now works correctly - when I hack it an
> replace the substitution variables for the literals like so in my SQL
> Editor:
> ==
> WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS
> (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name,
> jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type, 1
> AS lvl
> FROM jobmst
> WHERE jobmst.jobmst_prntid IS NULL
> UNION ALL
> SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name,
> j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type,
> j1.lvl + 1
> FROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id
> WHERE j2.jobmst_prntid IS NOT NULL)
>  SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
> SELECT j1.jobmst_id, j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type,
> j1.lvl
> FROM j1 ORDER BY DISP_SEQ
>
> ===
>
> But for some reason, the for row in stmt.first() when I run my code listed
> below yields:  "ORA-00907: missing right parenthesis":
>
> #!/usr/bin/env python
> import sqlalchemy
> from tidal import Jobmst
> from sqlalchemy import create_engine, literal, text
> from sqlalchemy.dialects import oracle
> from sqlalchemy.orm import Session, aliased, sessionmaker
> from sqlalchemy.ext.compiler import compiles
> from sqlalchemy.sql.expression import Select
> import re
>
> @compiles(Select, "oracle")
> def _add_search_hints(element, compiler, **kw):
> text = compiler.visit_select(element, **kw)
> for prefix, _ in element._prefixes:
> prefix = prefix.text
> text = re.sub(r'SELECT %s' % prefix, "%s SELECT" % prefix, text)
> return text
>
> engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
> echo=True)
> Session = sessionmaker(bind=engine)
> ses = Session()
> j2 = aliased(Jobmst, name='j2')
> j1 = ses.query(
>   Jobmst.jobmst_id, Jobmst.jobmst_name,
>   Jobmst.jobmst_prntid, Jobmst.jobmst_type, literal('1').label('lvl')
> ).filter(Jobmst.jobmst_prntid == None).cte(recursive=True,name='j1')
> j1 = j1.union_all(
>ses.query(j2.jobmst_id, j2.jobmst_name,
>  j2.jobmst_prntid, j2.jobmst_type,
>  j1.c.lvl + 1
> ).join(j1, j2.jobmst_prntid == j1.c.jobmst_id
>   ).filter(j2.jobmst_prntid != None)
> )
> stmt = ses.query(j1).prefix_with('SEARCH DEPTH FIRST BY JOBMST_NAME SET
> DISP_SEQ').order_by(text('DISP_SEQ'))
> oraclesql = stmt.statement.compile(dialect=oracle.dialect())
> print('oraclesql: ', oraclesql)
> for row in stmt.first():
>   print(row)
>
> 
>
> Here is the output:
>
> ===
> C:\opt\tidalconversion>jobwalk.py
> oraclesql:  WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl)
> AS
> (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name,
> jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type,
> :param_1 AS lvl
> FROM jobmst
> WHERE jobmst.jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS
> j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS
> j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS
> anon_1
> FROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id
> WHERE j2.jobmst_prntid IS NOT NULL)
>  SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ SELECT j1.jobmst_id,
> j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type, j1.lvl
> FROM j1 ORDER BY DISP_SEQ
> 2018-04-27 09:53:25,847 INFO sqlalchemy.engine.base.Engine SELECT USER FROM
> DUAL
> 2018-04-27 09:53:25,847 INFO sqlalchemy.engine.base.Engine {}
> 2018-04-27 09:53:25,850 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
> plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
> 2018-04-27 09:53:25,850 INFO sqlalchemy.engine.base.Engine {}
> 2018-04-27 09:53:25,851 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
> unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
> 2018-04-27 09:53:25,851 INFO sqlalchemy.engine.base.Engine {}
> 2018-04-27 09:53:25,852 INFO sqlalchemy.engine.base.Engine select value from
> nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'
> 2018-04-27 09:53:25,852 INFO sqlalchemy.engine.base.Engine {}
> 

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Jeremy Flowers]

*Well the printed oracle sql dialect now works correctly - when I hack it
an replace the substitution variables for the literals like so in my SQL
Editor:*
==
WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS
(SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name,
jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type, 1
AS lvl
FROM jobmst
WHERE jobmst.jobmst_prntid IS NULL
UNION ALL
SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name,
j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type,
j1.lvl + 1
FROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id
WHERE j2.jobmst_prntid IS NOT NULL)
 SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
SELECT j1.jobmst_id, j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type,
j1.lvl
FROM j1 ORDER BY DISP_SEQ

===

*But for some reason, the for row in stmt.first() when I run my code listed
below yields:  "ORA-00907: missing right parenthesis":*

#!/usr/bin/env python
import sqlalchemy
from tidal import Jobmst
from sqlalchemy import create_engine, literal, text
from sqlalchemy.dialects import oracle
from sqlalchemy.orm import Session, aliased, sessionmaker
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Select
import re

@compiles(Select, "oracle")
def _add_search_hints(element, compiler, **kw):
text = compiler.visit_select(element, **kw)
for prefix, _ in element._prefixes:
prefix = prefix.text
text = re.sub(r'SELECT %s' % prefix, "%s SELECT" % prefix, text)
return text

engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
echo=True)
Session = sessionmaker(bind=engine)
ses = Session()
j2 = aliased(Jobmst, name='j2')
j1 = ses.query(
  Jobmst.jobmst_id, Jobmst.jobmst_name,
  Jobmst.jobmst_prntid, Jobmst.jobmst_type, literal('1').label('lvl')
).filter(Jobmst.jobmst_prntid == None).cte(recursive=True,name='j1')
j1 = j1.union_all(
   ses.query(j2.jobmst_id, j2.jobmst_name,
 j2.jobmst_prntid, j2.jobmst_type,
 j1.c.lvl + 1
).join(j1, j2.jobmst_prntid == j1.c.jobmst_id
  ).filter(j2.jobmst_prntid != None)
)
stmt = ses.query(j1).prefix_with('SEARCH DEPTH FIRST BY JOBMST_NAME SET
DISP_SEQ').order_by(text('DISP_SEQ'))
oraclesql = stmt.statement.compile(dialect=oracle.dialect())
print('oraclesql: ', oraclesql)
for row in stmt.first():
  print(row)



*Here is the output:*

===
C:\opt\tidalconversion>jobwalk.py
oraclesql:  WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type,
lvl) AS
(SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name,
jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type,
:param_1 AS lvl
FROM jobmst
WHERE jobmst.jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS
j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS
j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS
anon_1
FROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id
WHERE j2.jobmst_prntid IS NOT NULL)
 SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ SELECT j1.jobmst_id,
j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type, j1.lvl
FROM j1 ORDER BY DISP_SEQ
2018-04-27 09:53:25,847 INFO sqlalchemy.engine.base.Engine SELECT USER FROM
DUAL
2018-04-27 09:53:25,847 INFO sqlalchemy.engine.base.Engine {}
2018-04-27 09:53:25,850 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
2018-04-27 09:53:25,850 INFO sqlalchemy.engine.base.Engine {}
2018-04-27 09:53:25,851 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
2018-04-27 09:53:25,851 INFO sqlalchemy.engine.base.Engine {}
2018-04-27 09:53:25,852 INFO sqlalchemy.engine.base.Engine select value
from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'
2018-04-27 09:53:25,852 INFO sqlalchemy.engine.base.Engine {}
2018-04-27 09:53:25,854 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)2018-04-27 09:53:25,856 INFO sqlalchemy.engine.base.Engine WITH
j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS
(SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name,
jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type,
:param_1 AS lvlFROM jobmstWHERE jobmst.jobmst_prntid IS NULL UNION ALL
SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name,
j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type,
j1.lvl + :lvl_1 AS anon_1
FROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id
WHERE j2.jobmst_prntid IS NOT NULL)
 SELECT j1_jobmst_id, j1_jobmst_name, j1_jobmst_prntid, j1_jobmst_type,
j1_lvl
FROM (SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ SELECT j1.jobmst_id AS
j1_jobmst_id, j1.jobmst_name AS j1_jobmst_name, j1.jobmst_prntid AS
j1_jobmst_prntid, j1.jobmst_type AS 

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Jeremy Flowers]

*NO*: you gave me...
SELECT SEARCH DEPTH FIRST...
There is a SELECT where it shouldn't have been.

It should have been *suffixed* to prior statement... NOT *prefixed*...

The search/set does a tree walk and adds a numeric id to the columns called
DISP_SEQ
You can traverse the hierarchy depth or breadth first and so the sequential
number allocation will

I see you have some new regular expression code for me to get my head
around.
Is that removing the SELECT prior to SEARCH?
After the DISP_SEQ, SELECT should be re-added...
Is the code doing that too?

and I gave you:
>
>   SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
>  SELECT
>  j1.jobmst_id,
>  j1.jobmst_name,
>  j1.jobmst_prntid,
>  j1.jobmst_type,
>  j1.lvl
> FROM j1 ORDER BY DISP_SEQ
>

@compiles(Select, "oracle")
> def _add_search_hints(element, compiler, **kw):
> text = compiler.visit_select(element, **kw)
> for prefix, _ in element._prefixes:
> prefix = prefix.text
> text = re.sub(r'SELECT %s' % prefix, "%s SELECT" % prefix, text)
> return text


On Thu, Apr 26, 2018 at 10:53 PM, Mike Bayer 
wrote:

> Here's that, also the literal() has to be against the regular value 1
> and not the string else it casts as string:
>
> from sqlalchemy import *
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import Session, aliased
> from sqlalchemy.ext.compiler import compiles
> from sqlalchemy.sql.expression import Select
> import re
>
>
> @compiles(Select, "oracle")
> def _add_search_hints(element, compiler, **kw):
> text = compiler.visit_select(element, **kw)
> for prefix, _ in element._prefixes:
> prefix = prefix.text
> text = re.sub(r'SELECT %s' % prefix, "%s SELECT" % prefix, text)
> return text
>
> Base = declarative_base()
>
>
> class JobMst(Base):
> __tablename__ = 'jobmst'
>
> jobmst_id = Column(Integer, primary_key=True)
> jobmst_name = Column(String(50))
> jobmst_prntid = Column(Integer)
> jobmst_type = Column(String(10))
>
> e = create_engine("oracle://scott:tiger@oracle1120", echo=True)
> Base.metadata.create_all(e)
>
> s = Session(e)
>
> j2 = aliased(JobMst, name="j2")
>
> j1 = s.query(
> JobMst.jobmst_id, JobMst.jobmst_name, JobMst.jobmst_prntid,
> JobMst.jobmst_type, literal(1).label("lvl")
> ).filter(JobMst.jobmst_prntid == None).cte(recursive=True, name="j1")
> j1 = j1.union_all(
> s.query(
> j2.jobmst_id, j2.jobmst_name,
> j2.jobmst_prntid, j2.jobmst_type, j1.c.lvl + literal(1)).join(
> j1, j2.jobmst_prntid == j1.c.jobmst_id
> ).filter(j2.jobmst_prntid != None)
> )
>
> stmt = s.query(j1).prefix_with(
> "SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ"
> ).order_by(text("DISP_SEQ"))
>
> stmt.all()
>
>
> please modify the database URL and optionally the column names if they
> are wrong and run this as is.
>
>
>
> On Thu, Apr 26, 2018 at 5:42 PM, Mike Bayer 
> wrote:
> > the issue is that your original query ends with:
> >
> > SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
> > SELECT *
> > FROM J1 ORDER BY DISP_SEQ
> >
> > and I gave you:
> >
> >   SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
> >  SELECT
> >  j1.jobmst_id,
> >  j1.jobmst_name,
> >  j1.jobmst_prntid,
> >  j1.jobmst_type,
> >  j1.lvl
> > FROM j1 ORDER BY DISP_SEQ
> >
> > I apologize for this mistake.
> >
> > Basically it's not appropriate to use select.with_prefix() in this
> > particular case as it applies prefixes subsequent to the SELECT
> > keyword.  The SQLAlchemy Oracle dialect does not currently have
> > support for special strings added in front of the SELECT keyword.I
> > can provide you with a recipe that allows for this to be possible,
> > however, as a practical matter, this query is Oracle-specific in any
> > case, is there a reason you can't just use text() ?   The reason
> > text() exists is for when one has the exact SQL they want already and
> > there is no need to work it into the expression language.
> >
> > If you want to continue using the expression language I can show you a
> > recipe to add those special keywords to the left side of the SELECT
> > keyword.
> >
> >
> >
> >
> >
> >
> >
> > On Thu, Apr 26, 2018 at 5:33 PM, Mike Bayer 
> wrote:
> >> OK, your original SQL works, will find the difference
> >>
> >> On Thu, Apr 26, 2018 at 5:32 PM, Mike Bayer 
> wrote:
> >>> Jeremy -
> >>>
> >>> This whole thread is based on an exact SQL that you have asked for,
> >>> and that was the query I gave you.   Going back to the script I
> >>> posted, the SQL output when I run it via query.all() vs. just
> >>> query.statement.compile() is identical except for the label names
> >>> applied to the final column 

Re: [sqlalchemy] Re: __init__ method

[Julien Cigar]

On Thu, Apr 26, 2018 at 11:30:13PM -0700, Jose Miguel Ibáñez wrote:
> Please, find below an example of what I mean:
> 
> class User(Base):
>  id = Column(Integer, primary_key=True)
>  name = Column(String)
>  fullname = Column(String)
> 
>  def __init__(self, name, fullname):
> 
>  self.name = name
>  self.fullname = fullname
> 
> 
> is the __init__() method recommended ?

The __init__() method is optional when you use Declarative, as the
Declarative extension provides a default constructor which accepts
keyword names that match the mapped columns

(Of course you are free to define any explicit __init__ to override the
default one)

> 
> 
> El jueves, 26 de abril de 2018, 18:48:53 (UTC+2), Jose Miguel Ibáñez 
> escribió:
> >
> > Hi all !
> >
> > when defining a class (derived from Base), when is recommended to define 
> > the __init__() method ?  I know this consideration  https://goo.gl/2umBJv, 
> > but I can't see the diference when creating objects for database 
> > population. It seems __init_() is never required.
> >
> > Thanks !
> > José M.
> >
> >
> 
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
> 
> http://www.sqlalchemy.org/
> 
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.


-- 
Julien Cigar
Belgian Biodiversity Platform (http://www.biodiversity.be)
PGP fingerprint: EEF9 F697 4B68 D275 7B11  6A25 B2BB 3710 A204 23C0
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


signature.asc
Description: PGP signature

[sqlalchemy] Re: __init__ method

[Jose Miguel Ibáñez]

Please, find below an example of what I mean:

class User(Base):
 id = Column(Integer, primary_key=True)
 name = Column(String)
 fullname = Column(String)

 def __init__(self, name, fullname):

 self.name = name
 self.fullname = fullname


is the __init__() method recommended ?


El jueves, 26 de abril de 2018, 18:48:53 (UTC+2), Jose Miguel Ibáñez 
escribió:
>
> Hi all !
>
> when defining a class (derived from Base), when is recommended to define 
> the __init__() method ?  I know this consideration  https://goo.gl/2umBJv, 
> but I can't see the diference when creating objects for database 
> population. It seems __init_() is never required.
>
> Thanks !
> José M.
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.