Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

[Jeremy Flowers]

I'm working on a system that has an underlying Oracle database...

I was looking at this article here:

https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2

Can you do the equivalent of the Oracle WITH inside SQLAlchemy (I think this is also referred to as CTE in MS SQL Server parlance)

Can you also add the search by depth/breadth in the mix?

If so can someone point me to an equivalent way to accomplish this type of thing.

Many thanks.

[Jeremy Flowers]

I suspect this may be trying to do something similar perhaps..

http://docs.sqlalchemy.org/en/latest/_modules/examples/materialized_paths/materialized_paths.html

But I don't understand this part.  Specifically where 'c' comes from:

   ancestors = relationship("Node", viewonly=True, secondary=secondary,
                             primaryjoin=id == secondary.c.id,
                             secondaryjoin=secondary.c.ancestor_id == id,
                             order_by=path)

[Jeremy Flowers]

I'm thinking 'c'  must be some sort of column shorthand

[Jonathan Vanasco]

In the example you pointed to, `c` are the columns of an object created by `alias()` 

`secondary` was created as an `alias()`, and the query has 2 columns that were queried and named via `label()` "id" and "ancestor_id"

sqlalchemy has a lot of relevant docs if you search via the keyword "CTE".

there are a handful of posts in the archive , and possibly stackoverflow, on doing this in Postgresql.    

[Jeremy Flowers]

Thanks for the feedback. CTE handles the WITH side of things. But how about depth/breadth first?

[Jeremy Flowers]

Also in the materialised path example, what is What is unnest doing?  Is it something like converting 1 row into many?

[Jonathan Vanasco]

almost everything in `func` is just standard sql that is executed in the database.  `unnest` is a sql function that is used to turn arrays into tabular data.

a search for CTE should surface 'suffix_with' which can be used to augment a query

http://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.CTE.suffix_with

there are examples of this being used for depth-first in old tickets on the issue tracker 

[Jeremy Flowers]

I take it you meant here:

https://bitbucket.org/zzzeek/sqlalchemy/issues/3220/several-small-issues-with-oracle-recursive

I'll look into this more tomorrow. Thanks for your time.

[Jeremy Flowers]

I am looking at replicating this Oracle SQL code:

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

I used SQLACODEGEN to generate the classes from an Oracle database schema. 

I had to fix some things, per this post

I have got this far:

import tidal

from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

from tidal import Jobmst

engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL', echo=True)

Session = sessionmaker(bind=engine)

session = Session(Jobmst.jobmst_id, Jobmst.jobmst_name, Jobmst.jobmst_prntid, Jobmst.jobmst_type)

But, not I want to include a column that is for the LVL per my original SQL code. How do I add ad-hoc columns to my CTE?

[Mike Bayer]

The Session() object does not accept column objects within its
constructor, it looks like you are thinking of using the Query object.
I would strongly recommend going through the ORM tutorial at
http://docs.sqlalchemy.org/en/latest/orm/tutorial.html first to learn
the basics of what's going on with a query, as this will save you lots
of time to have this background.

From there, the CTE syntax is generated by the CTE() construct,
examples of recursive CTEs using the ORM-level Query are here:

http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=cte#sqlalchemy.orm.query.Query.cte

Here's a proof of concept of the query you describe:

from sqlalchemy import *
from sqlalchemy.dialects import oracle
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, aliased

Base = declarative_base()


class JobMst(Base):
__tablename__ = 'jobmst'
__table_args = {"schema": "tidal"}

jobmst_id = Column(Integer, primary_key=True)
jobmst_name = Column(String(50))
jobmst_prntid = Column(Integer)
jobmst_type = Column(String(10))

s = Session()

j2 = aliased(JobMst, name="j2")

j1 = s.query(
JobMst.jobmst_id, JobMst.jobmst_name, JobMst.jobmst_prntid,
JobMst.jobmst_type, literal("1").label("lvl")
).filter(JobMst.jobmst_prntid == None).cte(recursive=True, name="j1")
j1 = j1.union_all(
s.query(
j2.jobmst_id, j2.jobmst_name,
j2.jobmst_prntid, j2.jobmst_type, j1.c.lvl + 1).join(
j1, j2.jobmst_prntid == j1.c.jobmst_id
).filter(j2.jobmst_prntid != None)
)

stmt = s.query(j1).prefix_with(

"SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ"

).order_by(text("DISP_SEQ"))

print(stmt.statement.compile(dialect=oracle.dialect()))

>
>
> On Thursday, 19 April 2018 23:20:10 UTC+1, Jeremy Flowers wrote:
>>
>> I take it you meant here:
>>
>> https://bitbucket.org/zzzeek/sqlalchemy/issues/3220/several-small-issues-with-oracle-recursive
>> I'll look into this more tomorrow. Thanks for your time.
>>
>> On Thursday, 19 April 2018 23:14:23 UTC+1, Jonathan Vanasco wrote:
>>>
>>> almost everything in `func` is just standard sql that is executed in the
>>> database. `unnest` is a sql function that is used to turn arrays into
>>> tabular data.
>>>
>>> a search for CTE should surface 'suffix_with' which can be used to
>>> augment a query
>>>
>>>
>>> http://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.CTE.suffix_with
>>>
>>> there are examples of this being used for depth-first in old tickets on
>>> the issue tracker
>

> --
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jeremy Flowers]

I'm assuimg you do something like define a Column.

But how do you assign a literal or expression value to it? (1 or LVL+1 later)

Is there a bind processor example I need?

from sqlalchemy import create_engine, Column

lvlOne = Column('lvl', Integer) 

I guess where I am also a little confused is with the *args **kwargs

Being from a Java background, I'm used to seeing a more complete API, so knowing what possible *args/**kwargs are helps.. I'm struggling a bit with that.

[Jeremy Flowers]

The Session() object does not accept column objects within its
constructor, it looks like you are thinking of using the Query object. 

You are right, I was looking at the example here and I forgot to append .query() ...

[Jeremy Flowers]

Is alias or mapping the correct construct if I want to select a subset of the fields that SQLACODEGEN created?

I sent you a Skype invite. I'd be interested in getting some training if you'd like to continue the chat via Skype.

[Jeremy Flowers]

On Monday, 23 April 2018 16:46:52 UTC+1, Jeremy Flowers wrote:

Is alias or mapping the correct construct if I want to select a subset of the fields that SQLACODEGEN created?

I sent you a Skype invite. I'd be interested in getting some training if you'd like to continue the chat via Skype.

I've also seen here you can use load_only. Can a query defined that way be assigned to j2 - per the alias in your example?

 

[Jeremy Flowers]

I am beginning to thing this may be the way...

http://docs.sqlalchemy.org/en/latest/orm/tutorial.html

>>> stmt = text("SELECT name, id, fullname, password "
...             "FROM users where name=:name")
>>> stmt = stmt.columns(User.name, User.id, User.fullname, User.password)
SQL>>> session.query(User).from_statement(stmt).params(name='ed').all()
[<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>]>>> stmt = text("SELECT name, id FROM users where name=:name")

>>> stmt = stmt.columns(User.name, User.id)
SQL>>> session.query(User.id, User.name).\
...          from_statement(stmt).params(name='ed').all()
[(1, u'ed')]

Because the code gives no namespace to things like text, your tutorial makes the product arduous to learn.. Have to go searching for things.

It's like you lookup 'a', then find you need 'b' then 'c' .... etc..

[Jeremy Flowers]

(I've literally got 18 tabs open looking at different parts of the docs ATM!)

--

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+unsubscribe@googlegroups.com.

[Jeremy Flowers]

I'm also mysfified why connect() doesn't get shown as an method for engine in Visual Studio.

Looked here 

[Jeremy Flowers]

I've had a go at trying to adapt your code:

import tidal
from sqlalchemy import create_engine, Column
from sqlalchemy.dialects import oracle
from sqlalchemy.orm import aliased
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm.query import Query
from sqlalchemy.sql import select
from sqlalchemy.sql.expression import literal, text

from tidal import Jobmst
engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL', echo=True)
Session = sessionmaker(bind=engine)

sel = select([Jobmst.jobmst_id, Jobmst.jobmst_name, Jobmst.jobmst_prntid, Jobmst.jobmst_type])
j2 = aliased(sel, name='j2')
ses = Session()
j1 = ses.query(
    sel,  literal('1').label('lvl')
).filter(Jobmst.jobmst_prntid == None).cte(recursive=True,name='j1')
j1 = j1.union_all(
    ses.query(j2, j1.c.lvl + 1
             ).join(j1, j2.c.jobmst_prntid == j1.c.jobmst_id
             ).filter(Jobmst.jobmst_prntid != None)
)
stmt = ses.query(j1).prefix_with(
    'SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ'
).order_by(text('DISP_SEQ'))
print(stmt.statement.compile(dialect=oracle.dialect()))
print(stmt.first())

When I run it. I get this

c:\opt\tidalconversion>cd c:\opt\tidalconversion && cmd /C "set "PYTHONIOENCODING=UTF-8" && set "PYTHONUNBUFFERED=1" && python C:\Users\administrator\.vscode\extensions\ms-python.python-2018.3.1\pythonFiles\PythonTools\visualstudio_py_launcher_nodebug.py c:\opt\tidalconversion 58494 34806ad9-833a-4524-8cd6-18ca4aa74f14 RedirectOutput,RedirectOutput c:\opt\tidalconversion\jobwalk.py "
WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS
(SELECT jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, :param_1 AS lvl
FROM (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type
FROM jobmst), jobmst
WHERE jobmst.jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1
FROM jobmst, (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type
FROM jobmst) j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id
WHERE jobmst.jobmst_prntid IS NOT NULL)
 SELECT SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ j1.jobmst_id, j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type, j1.lvl
FROM j1 ORDER BY DISP_SEQ2018-04-23 21:32:26,345 INFO sqlalchemy.engine.base.Engine SELECT USER FROM DUAL2018-04-23 21:32:26,346 INFO sqlalchemy.engine.base.Engine {}2018-04-23 21:32:26,349 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL2018-04-23 21:32:26,350 INFO sqlalchemy.engine.base.Engine {}2018-04-23 21:32:26,350 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL2018-04-23 21:32:26,351 INFO sqlalchemy.engine.base.Engine {}2018-04-23 21:32:26,352 INFO sqlalchemy.engine.base.Engine select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'2018-04-23 21:32:26,352 INFO sqlalchemy.engine.base.Engine {}2018-04-23 21:32:26,353 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-04-23 21:32:26,355 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, :param_1 AS lvl
FROM (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type
FROM jobmst), jobmst
WHERE jobmst.jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1
FROM jobmst, (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type
FROM jobmst) j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id
WHERE jobmst.jobmst_prntid IS NOT NULL)
 SELECT j1_jobmst_id, j1_jobmst_name, j1_jobmst_prntid, j1_jobmst_type, j1_lvl
FROM (SELECT SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ j1.jobmst_id AS j1_jobmst_id, j1.jobmst_name AS j1_jobmst_name, j1.jobmst_prntid AS j1_jobmst_prntid, j1.jobmst_type AS j1_jobmst_type, j1.lvl AS j1_lvl
FROM j1 ORDER BY DISP_SEQ)
WHERE ROWNUM <= :param_2
2018-04-23 21:32:26,356 INFO sqlalchemy.engine.base.Engine {'param_1': '1', 'lvl_1': 1, 'param_2': 1}
Traceback (most recent call last):  File "C:\Users\administrator\.vscode\extensions\ms-python.python-2018.3.1\pythonFiles\PythonTools\visualstudio_py_launcher_nodebug.py", line 74, in run
    _vspu.exec_file(file, globals_obj)
  File "C:\Users\administrator\.vscode\extensions\ms-python.python-2018.3.1\pythonFiles\PythonTools\visualstudio_py_util.py", line 119, in exec_file
    exec_code(code, file, global_variables)
  File "C:\Users\administrator\.vscode\extensions\ms-python.python-2018.3.1\pythonFiles\PythonTools\visualstudio_py_util.py", line 95, in exec_code
    exec(code_obj, global_variables)
  File "c:\opt\tidalconversion\jobwalk.py", line 27, in <module>
    print(stmt.first())
  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\orm\query.py", line 2789, in first
    ret = list(self[0:1])
  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\orm\query.py", line 2581, in __getitem__
    return list(res)
  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\orm\query.py", line 2889, in __iter__
    return self._execute_and_instances(context)
  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\orm\query.py", line 2912, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
    context)
  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00923: FROM keyword not found where expected [SQL: 'WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS \n(SELECT jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, :param_1 AS lvl \nFROM (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type \nFROM jobmst), jobmst \nWHERE jobmst.jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1 \nFROM jobmst, (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type \nFROM jobmst) j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id \nWHERE jobmst.jobmst_prntid IS NOT NULL)\n SELECT j1_jobmst_id, j1_jobmst_name, j1_jobmst_prntid, j1_jobmst_type, j1_lvl \nFROM (SELECT SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ j1.jobmst_id AS j1_jobmst_id, j1.jobmst_name AS j1_jobmst_name, j1.jobmst_prntid AS j1_jobmst_prntid, j1.jobmst_type AS j1_jobmst_type, j1.lvl AS j1_lvl \nFROM j1 ORDER BY DISP_SEQ) \nWHERE ROWNUM <= :param_2'] [parameters: {'param_1': '1', 'lvl_1': 1, 'param_2': 1}] (Background on this error at: http://sqlalche.me/e/4xp6)

Any ideas on what I need to do to fix this?

[Mike Bayer]

That seems wrong what version of SQLAlchemy is that ?  Make sure you are on the latest.

--

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+...@googlegroups.com.

[Mike Bayer]

It looks like you are trying to embed a second SELECT and within your
CTE you're referrring to both the subquery as well as the Jobmst
entity, and you're getting "FROM jobmst, (SELECT ... " which is likely
the part that's confusing Oracle. The query I gave you generates the
exact SQL you asked for. Can you describe how you'd like it changed?
CTEs are pretty advanced structures to be starting with.

> --
> 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+...@googlegroups.com.

[Jeremy Flowers]

I'm on 1.2.6

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe@googlegroups.com.

To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

[Jeremy Flowers]

It's just I've already pre-generated most of the classes - and Jobmst contains far more columns than I'm interested in using.

So I wanted to select only some of the columns.

I guess part of where I get confused is in the use of aliased. Whether it has to be for whole table.

Also the use of the 'c' for columns. It seems to be somewhat inconsistent as to when you do and don't use that form of access to get to the columns.

And why text had to be used for the order by.

And.. prefix_with... I'd have thought that would have been suffix_with.

> email to sqlalchemy+unsubscribe@googlegroups.com.

> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

[Jeremy Flowers]

Mike. Thanks for the Datacamp link you emailed me.

If I understand your point earlier the references to the full table class as well as the selected columns were causing the issues.

So I took all references to Jobmst out of the main query - only using it to define sel..

But I still end up with the same problem. (ORA-00923: FROM keyword not found where expected)

import tidal
from sqlalchemy import create_engine, Column
from sqlalchemy.dialects import oracle
from sqlalchemy.orm import aliased
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm.query import Query
from sqlalchemy.sql import select
from sqlalchemy.sql.expression import literal, text
from tidal import Jobmst

import sqlalchemy
print(sqlalchemy.__version__)

engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL', echo=True)
Session = sessionmaker(bind=engine)
sel = select([Jobmst.jobmst_id, Jobmst.jobmst_name, Jobmst.jobmst_prntid, Jobmst.jobmst_type])
j2 = aliased(sel, name='j2')
ses = Session()
j1 = ses.query(
    sel,  literal('1').label('lvl')

).filter(sel.c.jobmst_prntid == None).cte(recursive=True,name='j1')

j1 = j1.union_all(
    ses.query(j2, j1.c.lvl + 1
             ).join(j1, j2.c.jobmst_prntid == j1.c.

jobmst_id
             ).filter(sel.c.jobmst_prntid != None)

)
stmt = ses.query(j1).prefix_with(
    'SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ'
).order_by(text('DISP_SEQ'))

print('=== 1: ===')
print(stmt.statement.compile(dialect=oracle.dialect()))
print('=== 2: ===')
print(stmt.first())

That yields this output:

C:\opt\tidalconversion>cd c:\opt\tidalconversion && cmd /C "set "PYTHONIOENCODING=UTF-8" && set "PYTHONUNBUFFERED=1" && C:\opt\python\3.6.5-x64\python.exe C:\Users\administrator\.vscode\extensions\ms-python.python-2018.3.1\pythonFiles\PythonTools\visualstudio_py_launcher.py c:\opt\tidalconversion 60756 34806ad9-833a-4524-8cd6-18ca4aa74f14 RedirectOutput,RedirectOutput c:\opt\tidalconversion\jobwalk.py "

1.2.6

=== 1: ===

WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS

(SELECT jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, :param_1 AS lvl

FROM (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type

FROM jobmst)

WHERE jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1

FROM (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type

FROM jobmst), (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type

FROM jobmst) j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id

WHERE jobmst_prntid IS NOT NULL)

 SELECT SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ j1.jobmst_id, j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type, j1.lvl

FROM j1 ORDER BY DISP_SEQ

=== 2: ===

2018-04-25 17:00:12,183 INFO sqlalchemy.engine.base.Engine SELECT USER FROM DUAL

2018-04-25 17:00:12,186 INFO sqlalchemy.engine.base.Engine {}

2018-04-25 17:00:12,197 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL

2018-04-25 17:00:12,197 INFO sqlalchemy.engine.base.Engine {}

2018-04-25 17:00:12,223 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL

2018-04-25 17:00:12,225 INFO sqlalchemy.engine.base.Engine {}

2018-04-25 17:00:12,230 INFO sqlalchemy.engine.base.Engine select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'

2018-04-25 17:00:12,238 INFO sqlalchemy.engine.base.Engine {}

2018-04-25 17:00:12,247 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2018-04-25 17:00:12,274 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, :param_1 AS lvl

FROM (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type

FROM jobmst)

WHERE jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1

FROM (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type

FROM jobmst), (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type

FROM jobmst) j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id

WHERE jobmst_prntid IS NOT NULL)

 SELECT j1_jobmst_id, j1_jobmst_name, j1_jobmst_prntid, j1_jobmst_type, j1_lvl

FROM (SELECT SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ j1.jobmst_id AS j1_jobmst_id, j1.jobmst_name AS j1_jobmst_name, j1.jobmst_prntid AS j1_jobmst_prntid, j1.jobmst_type AS j1_jobmst_type, j1.lvl AS j1_lvl

FROM j1 ORDER BY DISP_SEQ)

WHERE ROWNUM <= :param_2

2018-04-25 17:00:12,285 INFO sqlalchemy.engine.base.Engine {'param_1': '1', 'lvl_1': 1, 'param_2': 1}

Traceback (most recent call last):

  File "c:\opt\tidalconversion\jobwalk.py", line 31, in <module>

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00923: FROM keyword not found where expected [SQL: 'WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS \n(SELECT jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, :param_1 AS lvl \nFROM (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type \nFROM jobmst) \nWHERE jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1 \nFROM (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type \nFROM jobmst), (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type \nFROM jobmst) j2 JOIN j1 ON j2.jobmst_prntid =

j1.jobmst_id \nWHERE jobmst_prntid IS NOT NULL)\n SELECT j1_jobmst_id, j1_jobmst_name, j1_jobmst_prntid, j1_jobmst_type, j1_lvl \nFROM (SELECT

SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ j1.jobmst_id AS j1_jobmst_id, j1.jobmst_name AS j1_jobmst_name, j1.jobmst_prntid AS j1_jobmst_prntid, j1.jobmst_type AS j1_jobmst_type, j1.lvl AS j1_lvl \nFROM j1 ORDER BY DISP_SEQ) \nWHERE ROWNUM <= :param_2'] [parameters: {'param_1': '1', 'lvl_1': 1, 'param_2': 1}] (Background on this error at: http://sqlalche.me/e/4xp6)

C:\opt\tidalconversion>

[Jeremy Flowers]

Having gone thru the Datacamp videos I now see where my select to get columns was unnecessary.

So, I now have code that is practically identical to Mike's post. But it still errors out:

import tidal
from sqlalchemy import create_engine, Column
from sqlalchemy.dialects import oracle
from sqlalchemy.orm import aliased
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm.query import Query
from sqlalchemy.sql import select
from sqlalchemy.sql.expression import literal, text
from tidal import Jobmst
import sqlalchemy
print(sqlalchemy.__version__)


engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL', echo=True)
Session = sessionmaker(bind=engine)

ses = Session()
j2 = aliased(Jobmst, name='j2')
j1 = ses.query(
  Jobmst.jobmst_id, Jobmst.jobmst_name,
  Jobmst.jobmst_prntid, Jobmst.jobmst_type, literal('1').label('lvl')
).filter(Jobmst.jobmst_prntid == None).cte(recursive=True,name='j1')
j1 = j1.union_all(
                   ses.query(j2.jobmst_id, j2.jobmst_name,
                             j2.jobmst_prntid, j2.jobmst_type,
                             j1.c.lvl + 1
                            ).join(j1, j2.jobmst_prntid == j1.c.jobmst_id
                                  ).filter(j2.jobmst_prntid != None)

                )
stmt = ses.query(j1).prefix_with(
    'SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ'
).order_by(text('DISP_SEQ'))
print('=== 1: ===')
print(stmt.statement.compile(dialect=oracle.dialect()))
print('=== 2: ===')
print(stmt.first())

Here is the output:

===

C:\opt\tidalconversion>cd c:\opt\tidalconversion && cmd /C "set "PYTHONIOENCODING=UTF-8" && set "PYTHONUNBUFFERED=1" && C:\opt\python\3.6.5-x64\python.exe C:\Users\administrator\.vscode\extensions\ms-python.python-2018.3.1\pythonFiles\PythonTools\visualstudio_py_launcher.py c:\opt\tidalconversion 65251 34806ad9-833a-4524-8cd6-18ca4aa74f14 RedirectOutput,RedirectOutput c:\opt\tidalconversion\jobwalk.py "

1.2.6

=== 1: ===

WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS

(SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type, :param_1 AS lvlFROM jobmstWHERE jobmst.jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1FROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_idWHERE j2.jobmst_prntid IS NOT NULL) SELECT SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ j1.jobmst_id, j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type, j1.lvl

FROM j1 ORDER BY DISP_SEQ

=== 2: ===

2018-04-26 15:03:48,768 INFO sqlalchemy.engine.base.Engine SELECT USER FROM DUAL

2018-04-26 15:03:48,771 INFO sqlalchemy.engine.base.Engine {}

2018-04-26 15:03:48,780 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL

2018-04-26 15:03:48,780 INFO sqlalchemy.engine.base.Engine {}

2018-04-26 15:03:48,780 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL

2018-04-26 15:03:48,780 INFO sqlalchemy.engine.base.Engine {}

2018-04-26 15:03:48,780 INFO sqlalchemy.engine.base.Engine select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'

2018-04-26 15:03:48,795 INFO sqlalchemy.engine.base.Engine {}

2018-04-26 15:03:48,795 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2018-04-26 15:03:48,812 INFO sqlalchemy.engine.base.Engine WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS

(SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type, :param_1 AS lvl

FROM jobmst

WHERE jobmst.jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1

FROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id

WHERE j2.jobmst_prntid IS NOT NULL)

 SELECT j1_jobmst_id, j1_jobmst_name, j1_jobmst_prntid, j1_jobmst_type, j1_lvl

FROM (SELECT SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ j1.jobmst_id AS j1_jobmst_id, j1.jobmst_name AS j1_jobmst_name, j1.jobmst_prntid AS j1_jobmst_prntid, j1.jobmst_type AS j1_jobmst_type, j1.lvl AS j1_lvl

FROM j1 ORDER BY DISP_SEQ)

WHERE ROWNUM <= :param_2

2018-04-26 15:03:48,822 INFO sqlalchemy.engine.base.Engine {'param_1': '1', 'lvl_1': 1, 'param_2': 1}

Traceback (most recent call last):

  File "c:\opt\tidalconversion\jobwalk.py", line 34, in <module>

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00923: FROM keyword not found where expected [SQL: 'WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS \n(SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type, :param_1 AS lvl \nFROM jobmst \nWHERE jobmst.jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1 \nFROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id \nWHERE j2.jobmst_prntid IS NOT NULL)\n SELECT j1_jobmst_id, j1_jobmst_name, j1_jobmst_prntid, j1_jobmst_type, j1_lvl \nFROM (SELECT SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ j1.jobmst_id AS j1_jobmst_id, j1.jobmst_name AS j1_jobmst_name, j1.jobmst_prntid AS j1_jobmst_prntid, j1.jobmst_type AS j1_jobmst_type, j1.lvl AS j1_lvl \nFROM j1 ORDER BY DISP_SEQ) \nWHERE ROWNUM <= :param_2'] [parameters: {'param_1': '1', 'lvl_1': 1, 'param_2': 1}] (Background on this error at: http://sqlalche.me/e/4xp6)

C:\opt\tidalconversion>

===

I have to say the output SQL is remarkably similar to my original 

(I've cut/pasted and formatted it) - INNER JOIN is same as JOIN. In set theory it's the intersection - so A join B is same as B join A - difference from my SQL.

I notice the SEARCH DEPTH FIRST PART is now nested and slightly different to my original.

If, I'm reading this correctly, I think in my original, DISP_SEQ would still be in the resultset. But with the code output from the SQLAlchemy, that is now hidden. I definitely want to keep that in my output.

ORA-00923: FROM keyword not found where expected - still not sure why I'm getting this.

Can someone help me diagnose why this message is occurring?

SELECT jobmst.jobmst_id        AS jobmst_id, 

               jobmst.jobmst_name AS jobmst_name, 

               jobmst.jobmst_prntid AS jobmst_prntid, 

              jobmst.jobmst_type    AS jobmst_type, 

             :param_1                    AS lvl

FROM jobmst

WHERE

            jobmst.jobmst_prntid IS NULL 

UNION ALL 

SELECT j2.jobmst_id        AS j2_jobmst_id, 

               j2.jobmst_name  AS j2_jobmst_name, 

              j2.jobmst_prntid   AS j2_jobmst_prntid,

             j2.jobmst_type      AS j2_jobmst_type, 

             j1.lvl + :lvl_1         AS anon_1

FROM jobmst j2 

JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id

WHERE j2.jobmst_prntid IS NOT NULL)

SELECT

              j1_jobmst_id,

              j1_jobmst_name, 

              j1_jobmst_prntid, 

             j1_jobmst_type,

             j1_lvl

FROM (SELECT SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ 

             j1.jobmst_id AS j1_jobmst_id, 

            j1.jobmst_name AS j1_jobmst_name, 

            j1.jobmst_prntid AS j1_jobmst_prntid, 

           j1.jobmst_type AS j1_jobmst_type, 

           j1.lvl AS j1_lvl

          FROM j1 ORDER BY DISP_SEQ)

WHERE ROWNUM <= :param_2

2018-04-26 15:03:48,822 INFO sqlalchemy.engine.base.Engine

 {'param_1': '1', 'lvl_1': 1, 'param_2': 1}

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

[Mike Bayer]

you want to have the "good SQL " that definitely runs correctly, then
you look at the "bad SQL", and figure out exactly what is different.

"I think in my original, DISP_SEQ would still be in the resultset"

what does that mean in terms of the query? the query I gave you
should have had every element that you asked for.

> --
> 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+...@googlegroups.com.

[Jeremy Flowers]

As far as I can tell, the only difference between your code and mine is you have:

    __table_args = {"schema": "tidal"}

when you defined your table - the generated classes I have didn't include that.

I also have more columns in the class definition of Jobmst. than your JobMst - that came out of SQLACODEGEN.

Other than that, mine vs yours the only difference I have is 

engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL', echo=True)

Session = sessionmaker(bind=engine)

ses = Session()

You have s = Session() - and an import of Session.

Other than that I can't see anything different.

If either of those would make a difference, can you illuminate me?

If there is anything else I'd appreciate a pointer.

> email to sqlalchemy+unsubscribe@googlegroups.com.

> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

[Jeremy Flowers]

I think it must have been down to the imports..

By changing code for imports to this, it seemed to fix it.

import sqlalchemy

from tidal import Jobmst

from sqlalchemy import create_engine, literal, text

from sqlalchemy.dialects import oracle 

from sqlalchemy.orm import Session, aliased, sessionmaker 

But I now have a stmt variable at the end..

How do I iterate over that in the form:

for row in results:

   print(row)

  

Despite my best efforts I can't seem to convert stmt (type : sqlalchemy.orm.query.Query).

Maybe it's something to do with the way I've defined the session...

A lot of the examples I've seen are using connection.

I can't seem to execute() it, since I think execute takes a string perhaps?

[Jonathan Vanasco]

On Thursday, April 26, 2018 at 1:59:02 PM UTC-4, Jeremy Flowers wrote:

But I now have a stmt variable at the end..

How do I iterate over that in the form:

for row in results:

   print(row)

results = query.all()

for row in results:

    print row

http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.all

If you want to see what it compiles to,

this will give you a general idea, but the SQL won't be compiled to the active database...

print stmt

 

you can compile the query's statement with a specific dialect for your database like this:

from sqlalchemy.dialects import oracle
print str(stmt.statement.compile(dialect=oracle.dialect()))

[Jeremy Flowers]

Since the SQL injects parameters into the string, this doesn't seem to work.

eg:

A value is required for bind parameter 'param_1'

WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS

(SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type, :param_1 AS lvl

FROM jobmst

WHERE jobmst.jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1

FROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id

WHERE j2.jobmst_prntid IS NOT NULL)

 SELECT jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl

FROM (SELECT jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl, ROWNUM AS ora_rn

FROM (SELECT SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ j1.jobmst_id AS jobmst_id, j1.jobmst_name AS jobmst_name, j1.jobmst_prntid AS jobmst_prntid, j1.jobmst_type

AS jobmst_type, j1.lvl AS lvl

FROM j1 ORDER BY DISP_SEQ)

[Jeremy Flowers]

 http://sqlalche.me/e/cd3x

[Jeremy Flowers]

What's needed is a method on the class that compiles the statement, to implicitly bind params. Here is the type: sqlalchemy.dialects.oracle.cx_oracle.OracleCompiler_cx_oracle/

I want to add slice to the end too, to paginate results. More bound params!

 

[Mike Bayer]

On Thu, Apr 26, 2018 at 3:16 PM, Jeremy Flowers
<jeremy.g...@gmail.com> wrote:
> Since the SQL injects parameters into the string, this doesn't seem to work.
> eg:
> A value is required for bind parameter 'param_1'

What Jonathan said was, "If you want to see what it compiles to,",
meaning, using compile() is a way for you to quickly see for yourself
what SQL query would be generated for a particular statement. To
actually run it, you normally want to stick with the existing methods,
e.g. query.all(), session.execute(), etc. That's where the parameter
values are correctly bound before passing along to the database.

>> sqlalchemy+...@googlegroups.com.

>> To post to this group, send email to sqlal...@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+...@googlegroups.com.

[Jeremy Flowers]

Mike I know what the problem is.

The SQL emitted by SQLAlchemy is wrong!

This is why I get: ORA-00923: FROM keyword not found where expected. You only get error code when you try to execute the code - with first(), all(), slice()

Look for the <----- in the generated code.... (x4)

====

This is my Original SQL:

WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS  (

  SELECT * FROM (

    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

​

===

This is the code output by printing the Oracle dialect:

WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS .  <----- Look no opening parenthesis

   (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type, :param_1 AS lvl

    FROM jobmst

    WHERE jobmst.jobmst_prntid IS NULL 

                                                                           <!----- LOOK no closing parenthesis

UNION ALL 

SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1 .   <!----- the AS names should match what's inside WITH()

FROM jobmst j2 

JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id

WHERE j2.jobmst_prntid IS NOT NULL

)

SELECT SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ j1.jobmst_id, j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type, j1.lvl .  <----- There should be no SELECT before SEARCH (my why prefix - should be suffix)

FROM j1 ORDER BY DISP_SEQ

===

[Jeremy Flowers]

I hacked the parameterised query:

This works..

WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS (

   (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type, 1 AS lvl

    FROM jobmst

    WHERE jobmst.jobmst_prntid IS NULL 

)

UNION ALL 

SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + 1 As lvl

FROM jobmst j2 

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 j1.jobmst_id, j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type, j1.lvl

FROM j1 ORDER BY DISP_SEQ

So, the AS names are ok it seems... But, the SELECT should come after DISP_SEQ too

[Jeremy Flowers]

I can confirm. I copied and pasted Mike's Python verbatim, and got exactly same issues with generated SQL

[Jeremy Flowers]

Does this need to be submitted as an error on Git somewhere?

[Mike Bayer]

Jeremy -

This whole thread is based on an exact SQL that you have asked for,
and that was the query I gave you. Going back to the script I
posted, the SQL output when I run it via query.all() vs. just
query.statement.compile() is identical except for the label names
applied to the final column names, which is not the cause of this
error.

please give me **the exact SQL statement that you know runs
correctly** and I will show you how to render it.


On Thu, Apr 26, 2018 at 5:30 PM, Jeremy Flowers

[Mike Bayer]

OK, your original SQL works, will find the difference

[Mike Bayer]

the issue is that your original query ends with:

SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
SELECT *
FROM J1 ORDER BY DISP_SEQ

and I gave you:

SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
SELECT
j1.jobmst_id,
j1.jobmst_name,
j1.jobmst_prntid,
j1.jobmst_type,
j1.lvl
FROM j1 ORDER BY DISP_SEQ

I apologize for this mistake.

Basically it's not appropriate to use select.with_prefix() in this
particular case as it applies prefixes subsequent to the SELECT
keyword. The SQLAlchemy Oracle dialect does not currently have
support for special strings added in front of the SELECT keyword. I
can provide you with a recipe that allows for this to be possible,
however, 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.

If you want to continue using the expression language I can show you a
recipe to add those special keywords to the left side of the SELECT
keyword.

[Mike Bayer]

Here's that, also the literal() has to be against the regular value 1
and not the string else it casts as string:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, aliased
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Select
import re


@compiles(Select, "oracle")
def _add_search_hints(element, compiler, **kw):
text = compiler.visit_select(element, **kw)
for prefix, _ in element._prefixes:
prefix = prefix.text
text = re.sub(r'SELECT %s' % prefix, "%s SELECT" % prefix, text)
return text

Base = declarative_base()


class JobMst(Base):
__tablename__ = 'jobmst'

jobmst_id = Column(Integer, primary_key=True)
jobmst_name = Column(String(50))
jobmst_prntid = Column(Integer)
jobmst_type = Column(String(10))

e = create_engine("oracle://scott:tiger@oracle1120", echo=True)
Base.metadata.create_all(e)

s = Session(e)

j2 = aliased(JobMst, name="j2")

j1 = s.query(
JobMst.jobmst_id, JobMst.jobmst_name, JobMst.jobmst_prntid,
JobMst.jobmst_type, literal(1).label("lvl")
).filter(JobMst.jobmst_prntid == None).cte(recursive=True, name="j1")
j1 = j1.union_all(
s.query(
j2.jobmst_id, j2.jobmst_name,
j2.jobmst_prntid, j2.jobmst_type, j1.c.lvl + literal(1)).join(

j1, j2.jobmst_prntid == j1.c.jobmst_id
).filter(j2.jobmst_prntid != None)
)

stmt = s.query(j1).prefix_with(

"SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ"

).order_by(text("DISP_SEQ"))

stmt.all()


please modify the database URL and optionally the column names if they
are wrong and run this as is.

[Jeremy Flowers]

NO: you gave me...

SELECT SEARCH DEPTH FIRST...

There is a SELECT where it shouldn't have been.

It should have been suffixed to prior statement... NOT prefixed...

The search/set does a tree walk and adds a numeric id to the columns called DISP_SEQ

You can traverse the hierarchy depth or breadth first and so the sequential number allocation will

I see you have some new regular expression code for me to get my head around.

Is that removing the SELECT prior to SEARCH?

After the DISP_SEQ, SELECT should be re-added...

Is the code doing that too?

and I gave you:

          SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
         SELECT
             j1.jobmst_id,
             j1.jobmst_name,
             j1.jobmst_prntid,
             j1.jobmst_type,
             j1.lvl
            FROM j1 ORDER BY DISP_SEQ

@compiles(Select, "oracle")
def _add_search_hints(element, compiler, **kw):
    text = compiler.visit_select(element, **kw)
    for prefix, _ in element._prefixes:
        prefix = prefix.text
        text = re.sub(r'SELECT %s' % prefix, "%s SELECT" % prefix, text)
    return text

>>>> email to sqlalchemy+unsubscribe@googlegroups.com.

>>>> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

[Jeremy Flowers]

Well the printed oracle sql dialect now works correctly - when I hack it an replace the substitution variables for the literals like so in my SQL Editor:

==

WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS

(SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type, 1 AS lvl

FROM jobmst

WHERE jobmst.jobmst_prntid IS NULL 

UNION ALL 

SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + 1

FROM jobmst j2 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 j1.jobmst_id, j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type, j1.lvl

FROM j1 ORDER BY DISP_SEQ

===

But for some reason, the for row in stmt.first() when I run my code listed below yields:  "ORA-00907: missing right parenthesis":

#!/usr/bin/env python

import sqlalchemy

from tidal import Jobmst

from sqlalchemy import create_engine, literal, text

from sqlalchemy.dialects import oracle 

from sqlalchemy.orm import Session, aliased, sessionmaker

from sqlalchemy.ext.compiler import compiles

from sqlalchemy.sql.expression import Select

import re

@compiles(Select, "oracle")

def _add_search_hints(element, compiler, **kw):

    text = compiler.visit_select(element, **kw)

    for prefix, _ in element._prefixes:

        prefix = prefix.text

        text = re.sub(r'SELECT %s' % prefix, "%s SELECT" % prefix, text)

    return text

engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL', echo=True)

Session = sessionmaker(bind=engine)

ses = Session()

j2 = aliased(Jobmst, name='j2')

j1 = ses.query(

  Jobmst.jobmst_id, Jobmst.jobmst_name, 

  Jobmst.jobmst_prntid, Jobmst.jobmst_type, literal('1').label('lvl')

).filter(Jobmst.jobmst_prntid == None).cte(recursive=True,name='j1')

j1 = j1.union_all(

                   ses.query(j2.jobmst_id, j2.jobmst_name, 

                             j2.jobmst_prntid, j2.jobmst_type, 

                             j1.c.lvl + 1

                            ).join(j1, j2.jobmst_prntid == j1.c.jobmst_id

                                  ).filter(j2.jobmst_prntid != None)

                )

stmt = ses.query(j1).prefix_with('SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ').order_by(text('DISP_SEQ'))

oraclesql = stmt.statement.compile(dialect=oracle.dialect())

print('oraclesql: ', oraclesql)

for row in stmt.first():

  print(row)

====

Here is the output:

===

C:\opt\tidalconversion>jobwalk.py

oraclesql:  WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS

(SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type, :param_1 AS lvl

FROM jobmst

WHERE jobmst.jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1

FROM jobmst j2 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 j1.jobmst_id, j1.jobmst_name, j1.jobmst_prntid, j1.jobmst_type, j1.lvl

FROM j1 ORDER BY DISP_SEQ

2018-04-27 09:53:25,847 INFO sqlalchemy.engine.base.Engine SELECT USER FROM DUAL

2018-04-27 09:53:25,847 INFO sqlalchemy.engine.base.Engine {}

2018-04-27 09:53:25,850 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL

2018-04-27 09:53:25,850 INFO sqlalchemy.engine.base.Engine {}

2018-04-27 09:53:25,851 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL

2018-04-27 09:53:25,851 INFO sqlalchemy.engine.base.Engine {}

2018-04-27 09:53:25,852 INFO sqlalchemy.engine.base.Engine select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'

2018-04-27 09:53:25,852 INFO sqlalchemy.engine.base.Engine {}

2018-04-27 09:53:25,854 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)2018-04-27 09:53:25,856 INFO sqlalchemy.engine.base.Engine WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS

(SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type, :param_1 AS lvlFROM jobmstWHERE jobmst.jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1

FROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id

WHERE j2.jobmst_prntid IS NOT NULL)

 SELECT j1_jobmst_id, j1_jobmst_name, j1_jobmst_prntid, j1_jobmst_type, j1_lvl

FROM (SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ SELECT j1.jobmst_id AS j1_jobmst_id, j1.jobmst_name AS j1_jobmst_name, j1.jobmst_prntid AS j1_jobmst_prntid, j1.jobmst_type AS j1_jobmst_type, j1.lvl AS j1_lvl

FROM j1 ORDER BY DISP_SEQ)

WHERE ROWNUM <= :param_2

2018-04-27 09:53:25,857 INFO sqlalchemy.engine.base.Engine {'param_1': '1', 'lvl_1': 1, 'param_2': 1}

Traceback (most recent call last):

  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context

    context)

  File "C:\opt\python\3.6.5-x64\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute

    cursor.execute(statement, parameters)

cx_Oracle.DatabaseError: ORA-00907: missing right parenthesis

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "C:\opt\tidalconversion\jobwalk.py", line 37, in <module>

    for row in stmt.first():

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00907: missing right parenthesis [SQL: 'WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS

\n(SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type, :param_1 AS lvl \nFROM jobmst \nWHERE jobmst.jobmst_prntid IS NULL UNION ALL SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid,

j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1 \nFROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id \nWHERE j2.jobmst_prntid IS NOT NULL)\n SELECT j1_jobmst_id, j1_jobmst_name, j1_jobmst_prntid, j1_jobmst_type, j1_lvl \nFROM (SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ SELECT j1.jobmst_id AS j1_jobmst_id, j1.jobmst_name AS j1_jobmst_name, j1.jobmst_prntid AS j1_jobmst_prntid, j1.jobmst_type AS j1_jobmst_type, j1.lvl AS j1_lvl \nFROM j1 ORDER BY DISP_SEQ) \nWHERE ROWNUM <= :param_2'] [parameters: {'param_1': '1', 'lvl_1': 1, 'param_2': 1}] (Background on this error at: http://sqlalche.me/e/4xp6)

C:\opt\tidalconversion>

===

Reformatting the output we have:

WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS

  (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS jobmst_name, jobmst.jobmst_prntid AS jobmst_prntid, jobmst.jobmst_type AS jobmst_type, :param_1 AS lvl

   FROM jobmst

   WHERE jobmst.jobmst_prntid IS NULL UNION ALL 

   SELECT j2.jobmst_id AS j2_jobmst_id, j2.jobmst_name AS j2_jobmst_name, j2.jobmst_prntid AS j2_jobmst_prntid, j2.jobmst_type AS j2_jobmst_type, j1.lvl + :lvl_1 AS anon_1

   FROM jobmst j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id

   WHERE j2.jobmst_prntid IS NOT NULL)

   SELECT j1_jobmst_id, j1_jobmst_name, j1_jobmst_prntid, j1_jobmst_type, j1_lvl

   FROM (SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ 

               SELECT j1.jobmst_id AS j1_jobmst_id, j1.jobmst_name AS j1_jobmst_name, j1.jobmst_prntid AS j1_jobmst_prntid, j1.jobmst_type AS j1_jobmst_type, j1.lvl AS j1_lvl

               FROM j1 ORDER BY DISP_SEQ) \nWHERE ROWNUM <= :param_2'] 

[parameters: {'param_1': '1', 'lvl_1': 1, 'param_2': 1}] 

(Background on this error at: http://sqlalche.me/e/4xp6)

===

So here we are again, with me re-iterating the comment:

If, I'm reading this correctly, I think in my original, DISP_SEQ would still be in the resultset. 

But with the code output from the SQLAlchemy, that is now hidden. I definitely want to keep that in my output.

===

Something in the stmt.first() generated output is different to the compiled output...

Please investigate and let me know if this is resolvable.

[Simon King]

The ".first()" method applies the DB-specific equivalent of "LIMIT 1"
to the query. Oracle apparently doesn't support the LIMIT keyword, so
SQLAlchemy wraps the entire query in "SELECT ... FROM (subquery) WHERE
ROWNUM <= limit":

http://docs.sqlalchemy.org/en/latest/dialects/oracle.html#limit-offset-support

The code to do this is a bit hairy:

https://bitbucket.org/zzzeek/sqlalchemy/src/30b02003a70f37aa83e20de6229afe2a3600b648/lib/sqlalchemy/dialects/oracle/base.py#base.py-813

I suspect that it's not handling your CTE properly. I don't even know
what the right SQL syntax should be.

At this point I would echo Mike's question: why can't you just use "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 the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an

> email to sqlalchemy+...@googlegroups.com.

[Jeremy Flowers]

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.

.first() ... Oracle apparently doesn't support the LIMIT

 

Yes. I'd noticed the solution for that when you do slice too. I think the example output given in the docs was for Postgresql - which is far more elegant. (LIMIT & OFFSET)

http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.slice 

With Oracle you have an or conjunction. I was surprised that wasn't implemented with between. That would have been more intuitive in my eyes.

> email to sqlalchemy+unsubscribe@googlegroups.com.

> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

[Mike Bayer]

the Oracle dialect has to run complex gymnastics to get the effect of
LIMIT in rewriting the query, so that is likely failing with a query
that has a CTE inside of it as the CTE feature is not widely used with
Oracle and we haven't put development resources into these cases.
For now I'd avoid any use of limit/offset. first() is documented as
using limit in the tutorial:
http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#returning-lists-and-scalars
Use all()[0] instead for a query like this.



On Fri, Apr 27, 2018 at 7:57 AM, Jeremy Flowers

>> > email to sqlalchemy+...@googlegroups.com.

>> > To post to this group, send email to sqlal...@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+...@googlegroups.com.

>> To post to this group, send email to sqlal...@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+...@googlegroups.com.

[Simon King]

On Fri, Apr 27, 2018 at 12:57 PM, Jeremy Flowers
<jeremy.g...@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

[Jeremy Flowers]

But can you still do things like slice(0,5)? Struggling to get that to work atm...

[Jeremy Flowers]

Ah right.. http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.ResultProxy

You can do fetchmany - and do a partial_results compare to empty list..

I see there is a next option too.

So, it's a slightly different paradigm

[Simon King]

No, you'll need to convert that to the equivalent SQL.

On Fri, Apr 27, 2018 at 3:14 PM, Jeremy Flowers

>> sqlalchemy+...@googlegroups.com.

>> To post to this group, send email to sqlal...@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+...@googlegroups.com.

[Jeremy Flowers]

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())

>> sqlalchemy+unsubscribe@googlegroups.com.

>> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

[Jeremy Flowers]

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!

[Jeremy Flowers]

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>

[Jeremy Flowers]

Hah.. Seems Oracle ROWNUM starts from 1, not 0..

When you select between 3 and 4 - nothing returns. There's an Oracle quirk...

[Mike Bayer]

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

>>>>> >> sqlalchemy+...@googlegroups.com.

>>>>> >> To post to this group, send email to sqlal...@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+...@googlegroups.com.

>>>>> > To post to this group, send email to sqlal...@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+...@googlegroups.com.

>>>>> To post to this group, send email to sqlal...@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+...@googlegroups.com.

[Jeremy Flowers]

SELECT * 

FROM ( SELECT A.*, rownum  rn

       FROM ( SELECT *

                FROM J1

                ORDER BY DISP_SEQ) A

        WHERE rownum <= 4)

WHERE rn >= 3

This is the crazy type of syntax required...

[Jeremy Flowers]

So.. Here it is working at last:

#!/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 ( SELECT A.*, rownum rn ' +

            'FROM ( SELECT * '

            'FROM J1 ' +

            'ORDER BY DISP_SEQ) A ' +

            'WHERE rownum <= :f) ' +

            'WHERE rn >= :s')

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=False)

connection = engine.connect()

offset = 42735 #42735 |||| #42736 records.. ROWCOUNT starts from 1, not 0 in ORACLE database.

limit = 2

more_results = True

while more_results:

    start = offset + 1

    finish = offset + limit

    print('Start: {0}, Finish {1}'.format(start,finish))

    partial_results = connection.execute(stmt, s = start, f = finish).fetchall()

    offset += limit

    #print('partial_results: {0}, type:{1}, empty:{2} '.format(partial_results,type(partial_results), partial_results == []))

    if partial_results == []:

        more_results = False

    else:

        for row in partial_results:

            print(row.items())

        if  offset == 4:

            more_results = False

connection.close()

[Jeremy Flowers]

Thanks. 

>>>>> >> sqlalchemy+unsubscribe@googlegroups.com.

>>>>> >> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

>>>>> > To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

>>>>> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

[Jeremy Flowers]

Finally with some fluff removed...

#!/usr/bin/env python

import sqlalchemy

from sqlalchemy import text, create_engine

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 ( SELECT A.*, rownum rn ' +

            'FROM ( SELECT * '

            'FROM J1 ' +

            'ORDER BY DISP_SEQ) A ' +

            'WHERE rownum <= :f) ' +

            'WHERE rn >= :s')

engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL', echo=False) #echo='debug'

connection = engine.connect()

offset = 0 #42735 |||| #42736 records.. ROWCOUNT starts from 1, not 0 in ORACLE database.

limit = 2

more_results = True

while more_results:

    start = offset + 1

    finish = offset + limit

    print('Start: {0}, Finish {1}'.format(start,finish))

    partial_results = connection.execute(stmt, s = start, f = finish).fetchall()

    offset += limit

    if partial_results == []:

        more_results = False

    else:

        for row in partial_results:

            print(row.items())

        if  offset == 4:

            more_results = False

connection.close()