Right... Been trawling back thru this chain of exchanges.. Looking for this:
> At this point I would echo Mike's question: why can't you just use > "text()"? Just spotted another comment from Mike, that I've just fixed too... > the literal() has to be against the regular value 1 and not the string... as a practical matter, this query is Oracle-specific in any > case, is there a reason you can't just use text() ? The reason > text() exists is for when one has the exact SQL they want already and > there is no need to work it into the expression language. Can I just put that into some sort of session execute? If there's a relevant example someone can point me to, it would help. I'll have a dig around. .first() ... Oracle apparently doesn't support the LIMIT Yes. I'd noticed the solution for that when you do slice too. I think the example output given in the docs was for Postgresql - which is far more elegant. (LIMIT & OFFSET) http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.slice With Oracle you have an or conjunction. I was surprised that wasn't implemented with between. That would have been more intuitive in my eyes. On Fri, Apr 27, 2018 at 10:31 AM, Simon King <[email protected]> wrote: > 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/30b02003a70f37aa83e20de6229afe > 2a3600b648/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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/sqlalchemy/aWJT1VdY3LU/unsubscribe. > To unsubscribe from this group and all its topics, 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.
