you want to have the "good SQL " that definitely runs correctly, then you look at the "bad SQL", and figure out exactly what is different. "I think in my original, DISP_SEQ would still be in the resultset" what does that mean in terms of the query? the query I gave you should have had every element that you asked for.
On Thu, Apr 26, 2018 at 10:30 AM, Jeremy Flowers <[email protected]> wrote: > 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 NULL > UNION ALL > SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID, J2.JOBMST_TYPE, > J1.LVL + 1 > FROM TIDAL.JOBMST J2 > INNER 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 * > FROM J1 > ORDER 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. -- 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.
