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>
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.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 NULLUNION ALLSELECT J2.JOBMST_ID, J2.JOBMST_NAME,
J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1FROM TIDAL.JOBMST J2INNER JOIN J1
ON J2.JOBMST_PRNTID = J1.JOBMST_IDWHERE J2.JOBMST_PRNTID IS NOT NULL)
SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQSELECT *FROM J1ORDER BY DISP_SEQ
--
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.
