That seems wrong what version of SQLAlchemy is that ? Make sure you are on
the latest.
On Mon, Apr 23, 2018, 4:36 PM Jeremy Flowers <jeremy.g.flow...@gmail.com>
wrote:
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
