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 NULLUNION ALLSELECT J2.JOBMST_ID, J2.JOBMST_NAME,
J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1FROM TIDAL.JOBMST J2INNER JOIN J1
ON J2.JOBMST_PRNTID = J1.JOBMST_IDWHERE J2.JOBMST_PRNTID IS NOT NULL)
SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQSELECT *FROM J1ORDER BY DISP_SEQ
I used SQLACODEGEN
<https://simpletutorials.com/c/2220/SQLAlchemy+Code+Generation> to generate
the classes from an Oracle database schema.
I had to fix some things, per this post
<https://stackoverflow.com/questions/48666026/sqlalchemy-could-not-determine-join-condition-between-parent-child-tables-on-re>
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?
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 [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.
