The ".first()" method applies the DB-specific equivalent of "LIMIT 1" to the query. Oracle apparently doesn't support the LIMIT keyword, so SQLAlchemy wraps the entire query in "SELECT ... FROM (subquery) WHERE ROWNUM <= limit":
http://docs.sqlalchemy.org/en/latest/dialects/oracle.html#limit-offset-support The code to do this is a bit hairy: https://bitbucket.org/zzzeek/sqlalchemy/src/30b02003a70f37aa83e20de6229afe2a3600b648/lib/sqlalchemy/dialects/oracle/base.py#base.py-813 I suspect that it's not handling your CTE properly. I don't even know what the right SQL syntax should be. At this point I would echo Mike's question: why can't you just use "text()"? Simon On Fri, Apr 27, 2018 at 10:07 AM, Jeremy Flowers <[email protected]> wrote: > 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: >> >> 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. > > > > === > 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. -- 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.
