Thanks. On Fri, Apr 27, 2018 at 6:08 PM, Mike Bayer <[email protected]> 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 > <[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.openstack.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# > 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 > >>>>> >> [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/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/ > 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.
