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
<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 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
>> <jeremy.g.flow...@gmail.com> 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
>>> <jeremy.g.flow...@gmail.com> 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 <si...@simonking.org.uk>
>>>> wrote:
>>>>>
>>>>> No, you'll need to convert that to the equivalent SQL.
>>>>>
>>>>> On Fri, Apr 27, 2018 at 3:14 PM, Jeremy Flowers
>>>>> <jeremy.g.flow...@gmail.com> 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 <si...@simonking.org.uk>
>>>>> > wrote:
>>>>> >>
>>>>> >> On Fri, Apr 27, 2018 at 12:57 PM, Jeremy Flowers
>>>>> >> <jeremy.g.flow...@gmail.com> 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 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.
