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 <[email protected]>
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 <
> [email protected]> 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 <
>> [email protected]> 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 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', '6G_ZAFIN_PROD'),
>>> ('jobmst_prntid', None), ('jobmst_type', 1), ('lvl', 1), ('disp_seq', 1)]
>>> Start: 2, Finish 3
>>>
>>> C:\opt\tidalconversion>
>>>
>>> On Fri, Apr 27, 2018 at 4:12 PM, Jeremy Flowers <
>>> [email protected]> wrote:
>>>
>>>> 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.opens
>>>> tack.org/guidelines/dg_parameterize-database-queries.html - it's
>>>> obviously wrong!
>>>>
>>>> On Fri, Apr 27, 2018 at 3:49 PM, Jeremy Flowers <
>>>> [email protected]> 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 <[email protected]>
>>>>> wrote:
>>>>>
>>>>>> No, you'll need to convert that to the equivalent SQL.
>>>>>>
>>>>>> On Fri, Apr 27, 2018 at 3:14 PM, Jeremy Flowers
>>>>>> <[email protected]> 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 <[email protected]>
>>>>>> wrote:
>>>>>> >>
>>>>>> >> On Fri, Apr 27, 2018 at 12:57 PM, Jeremy Flowers
>>>>>> >> <[email protected]> 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/topic/sqlalchemy/aWJT1VdY3LU/uns
>>>>>> ubscribe.
>>>>>> >> To unsubscribe from this group and all its topics, send an email to
>>>>>> >> [email protected].
>>>>>> >> To post to this group, send email to [email protected].
>>>>>> >> 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 [email protected].
>>>>>> > To post to this group, send email to [email protected].
>>>>>> > 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 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
>>>>>> [email protected].
>>>>>> To post to this group, send email to [email protected].
>>>>>> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
