*NO*: you gave me...
SELECT SEARCH DEPTH FIRST...
There is a SELECT where it shouldn't have been.
It should have been *suffixed* to prior statement... NOT *prefixed*...
The search/set does a tree walk and adds a numeric id to the columns called
DISP_SEQ
You can traverse the hierarchy depth or breadth first and so the sequential
number allocation will
I see you have some new regular expression code for me to get my head
around.
Is that removing the SELECT prior to SEARCH?
After the DISP_SEQ, SELECT should be re-added...
Is the code doing that too?
and I gave you:
>
> 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
>
@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
On Thu, Apr 26, 2018 at 10:53 PM, Mike Bayer <mike...@zzzcomputing.com>
wrote:
> Here's that, also the literal() has to be against the regular value 1
> and not the string else it casts as string:
>
> from sqlalchemy import *
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import Session, aliased
> 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
>
> Base = declarative_base()
>
>
> class JobMst(Base):
> __tablename__ = 'jobmst'
>
> jobmst_id = Column(Integer, primary_key=True)
> jobmst_name = Column(String(50))
> jobmst_prntid = Column(Integer)
> jobmst_type = Column(String(10))
>
> e = create_engine("oracle://scott:tiger@oracle1120", echo=True)
> Base.metadata.create_all(e)
>
> s = Session(e)
>
> j2 = aliased(JobMst, name="j2")
>
> j1 = s.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(
> s.query(
> j2.jobmst_id, j2.jobmst_name,
> j2.jobmst_prntid, j2.jobmst_type, j1.c.lvl + literal(1)).join(
> j1, j2.jobmst_prntid == j1.c.jobmst_id
> ).filter(j2.jobmst_prntid != None)
> )
>
> stmt = s.query(j1).prefix_with(
> "SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ"
> ).order_by(text("DISP_SEQ"))
>
> stmt.all()
>
>
> please modify the database URL and optionally the column names if they
> are wrong and run this as is.
>
>
>
> On Thu, Apr 26, 2018 at 5:42 PM, Mike Bayer <mike...@zzzcomputing.com>
> wrote:
> > the issue is that your original query ends with:
> >
> > SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
> > SELECT *
> > FROM J1 ORDER BY DISP_SEQ
> >
> > and I gave you:
> >
> > 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
> >
> > I apologize for this mistake.
> >
> > Basically it's not appropriate to use select.with_prefix() in this
> > particular case as it applies prefixes subsequent to the SELECT
> > keyword. The SQLAlchemy Oracle dialect does not currently have
> > support for special strings added in front of the SELECT keyword. I
> > can provide you with a recipe that allows for this to be possible,
> > however, 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.
> >
> > If you want to continue using the expression language I can show you a
> > recipe to add those special keywords to the left side of the SELECT
> > keyword.
> >
> >
> >
> >
> >
> >
> >
> > On Thu, Apr 26, 2018 at 5:33 PM, Mike Bayer <mike...@zzzcomputing.com>
> wrote:
> >> OK, your original SQL works, will find the difference
> >>
> >> On Thu, Apr 26, 2018 at 5:32 PM, Mike Bayer <mike...@zzzcomputing.com>
> wrote:
> >>> Jeremy -
> >>>
> >>> This whole thread is based on an exact SQL that you have asked for,
> >>> and that was the query I gave you. Going back to the script I
> >>> posted, the SQL output when I run it via query.all() vs. just
> >>> query.statement.compile() is identical except for the label names
> >>> applied to the final column names, which is not the cause of this
> >>> error.
> >>>
> >>> please give me **the exact SQL statement that you know runs
> >>> correctly** and I will show you how to render it.
> >>>
> >>>
> >>> On Thu, Apr 26, 2018 at 5:30 PM, Jeremy Flowers
> >>> <jeremy.g.flow...@gmail.com> wrote:
> >>>> Does this need to be submitted as an error on Git somewhere?
> >>>>
> >>>> On Thu, Apr 26, 2018 at 10:29 PM, Jeremy Flowers
> >>>> <jeremy.g.flow...@gmail.com> wrote:
> >>>>>
> >>>>> I can confirm. I copied and pasted Mike's Python verbatim, and got
> exactly
> >>>>> same issues with generated SQL
> >>>>>
> >>>>> On Thu, Apr 26, 2018 at 10:21 PM, Jeremy Flowers
> >>>>> <jeremy.g.flow...@gmail.com> wrote:
> >>>>>>
> >>>>>> I hacked the parameterised query:
> >>>>>> This works..
> >>>>>> 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 As lvl
> >>>>>> 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
> >>>>>>
> >>>>>> So, the AS names are ok it seems... But, the SELECT should come
> after
> >>>>>> DISP_SEQ too
> >>>>>>
> >>>>>> On Thu, Apr 26, 2018 at 10:17 PM, Jeremy Flowers
> >>>>>> <jeremy.g.flow...@gmail.com> wrote:
> >>>>>>>
> >>>>>>> Mike I know what the problem is.
> >>>>>>> The SQL emitted by SQLAlchemy is wrong!
> >>>>>>> This is why I get: ORA-00923: FROM keyword not found where
> expected. You
> >>>>>>> only get error code when you try to execute the code - with
> first(), all(),
> >>>>>>> slice()
> >>>>>>> Look for the <----- in the generated code.... (x4)
> >>>>>>>
> >>>>>>> ====
> >>>>>>> This is my Original SQL:
> >>>>>>>
> >>>>>>> WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL)
> AS (
> >>>>>>> SELECT * FROM (
> >>>>>>> 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
> >>>>>>> ===
> >>>>>>>
> >>>>>>> This is the code output by printing the Oracle dialect:
> >>>>>>>
> >>>>>>> WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl)
> AS .
> >>>>>>> <----- Look no opening parenthesis
> >>>>>>> (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
> >>>>>>>
> >>>>>>> <!----- LOOK no closing parenthesis
> >>>>>>> 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 . <!----- the AS names should match
> what's
> >>>>>>> inside WITH()
> >>>>>>> FROM jobmst j2
> >>>>>>> JOIN j1 ON j2.jobmst_prntid = j1.jobmst_id
> >>>>>>> WHERE 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 . <-----
> There
> >>>>>>> should be no SELECT before SEARCH (my why prefix - should be
> suffix)
> >>>>>>> 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 sqlalchemy+unsubscr...@googlegroups.com.
> >>>> To post to this group, send email to sqlalchemy@googlegroups.com.
> >>>> 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
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
