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 <[email protected]> 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 <[email protected]> wrote:
>> OK, your original SQL works, will find the difference
>>
>> On Thu, Apr 26, 2018 at 5:32 PM, Mike Bayer <[email protected]> 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
>>> <[email protected]> wrote:
>>>> Does this need to be submitted as an error on Git somewhere?
>>>>
>>>> On Thu, Apr 26, 2018 at 10:29 PM, Jeremy Flowers
>>>> <[email protected]> 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
>>>>> <[email protected]> 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
>>>>>> <[email protected]> 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 [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.
