*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():
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-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:*
> *I*f, 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.
--
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.
