As far as I can tell, the only difference between your code and mine is you
have:
__table_args = {"schema": "tidal"}
when you defined your table - the generated classes I have didn't include
that.
I also have more columns in the class definition of Jobmst. than your
JobMst - that came out of SQLACODEGEN.
Other than that, mine vs yours the only difference I have is
engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
echo=True)
Session = sessionmaker(bind=engine)
ses = Session()
You have s = Session() - and an import of Session.
Other than that I can't see anything different.
If either of those would make a difference, can you illuminate me?
If there is anything else I'd appreciate a pointer.
On Thu, Apr 26, 2018 at 3:58 PM, Mike Bayer <[email protected]>
wrote:
> 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 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.
