Mike. Thanks for the Datacamp link
<https://www.datacamp.com/courses/introduction-to-relational-databases-in-python?tap_a=5644-dce66f&tap_s=128604-f35052>
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>
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) \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>
--
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.
