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.
