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