the Oracle dialect has to run complex gymnastics to get the effect of LIMIT in rewriting the query, so that is likely failing with a query that has a CTE inside of it as the CTE feature is not widely used with Oracle and we haven't put development resources into these cases. For now I'd avoid any use of limit/offset. first() is documented as using limit in the tutorial: http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#returning-lists-and-scalars Use all()[0] instead for a query like this.
On Fri, Apr 27, 2018 at 7:57 AM, Jeremy Flowers <[email protected]> wrote: > 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/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 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. -- 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.
