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/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. >>>> >>>> -- >>>> 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.
