It's just I've already pre-generated most of the classes - and Jobmst contains far more columns than I'm interested in using. So I wanted to select only some of the columns. I guess part of where I get confused is in the use of aliased. Whether it has to be for whole table. Also the use of the 'c' for columns. It seems to be somewhat inconsistent as to when you do and don't use that form of access to get to the columns. And why text had to be used for the order by. And.. prefix_with... I'd have thought that would have been suffix_with.
On Mon, Apr 23, 2018 at 11:56 PM, Mike Bayer <[email protected]> wrote: > On Mon, Apr 23, 2018 at 4:35 PM, Jeremy Flowers > <[email protected]> wrote: > > I've had a go at trying to adapt your code: > > > > 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 > > engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL', > > echo=True) > > Session = sessionmaker(bind=engine) > > sel = select([Jobmst.jobmst_id, Jobmst.jobmst_name, Jobmst.jobmst_prntid, > > Jobmst.jobmst_type]) > > j2 = aliased(sel, name='j2') > > ses = Session() > > j1 = ses.query( > > sel, literal('1').label('lvl') > > ).filter(Jobmst.jobmst_prntid == None).cte(recursive=True,name='j1') > > j1 = j1.union_all( > > ses.query(j2, j1.c.lvl + 1 > > ).join(j1, j2.c.jobmst_prntid == j1.c.jobmst_id > > ).filter(Jobmst.jobmst_prntid != None) > > ) > > stmt = ses.query(j1).prefix_with( > > 'SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ' > > ).order_by(text('DISP_SEQ')) > > print(stmt.statement.compile(dialect=oracle.dialect())) > > print(stmt.first()) > > It looks like you are trying to embed a second SELECT and within your > CTE you're referrring to both the subquery as well as the Jobmst > entity, and you're getting "FROM jobmst, (SELECT ... " which is likely > the part that's confusing Oracle. The query I gave you generates the > exact SQL you asked for. Can you describe how you'd like it changed? > CTEs are pretty advanced structures to be starting with. > > > > > > When I run it. I get this > > > > c:\opt\tidalconversion>cd c:\opt\tidalconversion && cmd /C "set > > "PYTHONIOENCODING=UTF-8" && set "PYTHONUNBUFFERED=1" && python > > C:\Users\administrator\.vscode\extensions\ms-python. > python-2018.3.1\pythonFiles\PythonTools\visualstudio_py_ > launcher_nodebug.py > > c:\opt\tidalconversion 58494 34806ad9-833a-4524-8cd6-18ca4aa74f14 > > RedirectOutput,RedirectOutput c:\opt\tidalconversion\jobwalk.py " > > WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS > > (SELECT jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, :param_1 AS > lvl > > FROM (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 > > FROM jobmst), 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, (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 > > FROM jobmst) j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id > > WHERE jobmst.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_SEQ2018-04-23 21:32:26,345 INFO > > sqlalchemy.engine.base.Engine SELECT USER FROM DUAL2018-04-23 > 21:32:26,346 > > INFO sqlalchemy.engine.base.Engine {}2018-04-23 21:32:26,349 INFO > > sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS > VARCHAR(60 > > CHAR)) AS anon_1 FROM DUAL2018-04-23 21:32:26,350 INFO > > sqlalchemy.engine.base.Engine {}2018-04-23 21:32:26,350 INFO > > sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS > > NVARCHAR2(60)) AS anon_1 FROM DUAL2018-04-23 21:32:26,351 INFO > > sqlalchemy.engine.base.Engine {}2018-04-23 21:32:26,352 INFO > > sqlalchemy.engine.base.Engine select value from nls_session_parameters > where > > parameter = 'NLS_NUMERIC_CHARACTERS'2018-04-23 21:32:26,352 INFO > > sqlalchemy.engine.base.Engine {}2018-04-23 21:32:26,353 INFO > > sqlalchemy.engine.base.Engine BEGIN (implicit) > > 2018-04-23 21:32:26,355 INFO sqlalchemy.engine.base.Engine WITH > > j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS > > (SELECT jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, :param_1 AS > lvl > > FROM (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 > > FROM jobmst), 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, (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 > > FROM jobmst) j2 JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id > > WHERE jobmst.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-23 21:32:26,356 INFO sqlalchemy.engine.base.Engine {'param_1': > '1', > > 'lvl_1': 1, 'param_2': 1} > > Traceback (most recent call last): File > > "C:\Users\administrator\.vscode\extensions\ms-python. > python-2018.3.1\pythonFiles\PythonTools\visualstudio_py_ > launcher_nodebug.py", > > line 74, in run > > _vspu.exec_file(file, globals_obj) > > File > > "C:\Users\administrator\.vscode\extensions\ms-python. > python-2018.3.1\pythonFiles\PythonTools\visualstudio_py_util.py", > > line 119, in exec_file > > exec_code(code, file, global_variables) > > File > > "C:\Users\administrator\.vscode\extensions\ms-python. > python-2018.3.1\pythonFiles\PythonTools\visualstudio_py_util.py", > > line 95, in exec_code > > exec(code_obj, global_variables) > > File "c:\opt\tidalconversion\jobwalk.py", line 27, 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_id, jobmst_name, > > jobmst_prntid, jobmst_type, :param_1 AS lvl \nFROM (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 \nFROM jobmst), 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, (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 \nFROM jobmst) j2 JOIN j1 ON > > j2.jobmst_prntid = j1.jobmst_id \nWHERE jobmst.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) > > > > Any ideas on what I need to do to fix this? > > > > -- > > 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.
