On Mon, Apr 23, 2018 at 9:30 AM, Jeremy Flowers
<jeremy.g.flow...@gmail.com> wrote:
> I am looking at replicating this Oracle SQL code:
>
> 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
>
> I used SQLACODEGEN to generate the classes from an Oracle database schema.
> I had to fix some things, per this post
>
> I have got this far:
> import tidal
> from sqlalchemy import create_engine
> from sqlalchemy.orm import sessionmaker
> from tidal import Jobmst
> engine = create_engine('oracle://TIDAL:Password123@WINTIDAL:1521/ORCL',
> echo=True)
> Session = sessionmaker(bind=engine)
> session = Session(Jobmst.jobmst_id, Jobmst.jobmst_name,
> Jobmst.jobmst_prntid, Jobmst.jobmst_type)
>
> But, not I want to include a column that is for the LVL per my original SQL
> code. How do I add ad-hoc columns to my CTE?
The Session() object does not accept column objects within its
constructor, it looks like you are thinking of using the Query object.
I would strongly recommend going through the ORM tutorial at
http://docs.sqlalchemy.org/en/latest/orm/tutorial.html first to learn
the basics of what's going on with a query, as this will save you lots
of time to have this background.
>From there, the CTE syntax is generated by the CTE() construct,
examples of recursive CTEs using the ORM-level Query are here:
http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=cte#sqlalchemy.orm.query.Query.cte
Here's a proof of concept of the query you describe:
from sqlalchemy import *
from sqlalchemy.dialects import oracle
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, aliased
Base = declarative_base()
class JobMst(Base):
__tablename__ = 'jobmst'
__table_args = {"schema": "tidal"}
jobmst_id = Column(Integer, primary_key=True)
jobmst_name = Column(String(50))
jobmst_prntid = Column(Integer)
jobmst_type = Column(String(10))
s = Session()
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 + 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"))
print(stmt.statement.compile(dialect=oracle.dialect()))
>
>
> On Thursday, 19 April 2018 23:20:10 UTC+1, Jeremy Flowers wrote:
>>
>> I take it you meant here:
>>
>> https://bitbucket.org/zzzeek/sqlalchemy/issues/3220/several-small-issues-with-oracle-recursive
>> I'll look into this more tomorrow. Thanks for your time.
>>
>> On Thursday, 19 April 2018 23:14:23 UTC+1, Jonathan Vanasco wrote:
>>>
>>> almost everything in `func` is just standard sql that is executed in the
>>> database. `unnest` is a sql function that is used to turn arrays into
>>> tabular data.
>>>
>>> a search for CTE should surface 'suffix_with' which can be used to
>>> augment a query
>>>
>>>
>>> http://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.CTE.suffix_with
>>>
>>> there are examples of this being used for depth-first in old tickets on
>>> the issue tracker
>
> --
> 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 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.
