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