Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

Finally with some fluff removed... #!/usr/bin/env python import sqlalchemy from sqlalchemy import text, create_engine stmt = text('WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS (' + 'SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1 ' +

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

Thanks. On Fri, Apr 27, 2018 at 6:08 PM, Mike Bayer wrote: > you don't really need the columns(...) part if you are sending the > text() object to connection.execute(). That's mostly a thing to help > when using the ORM which I don't think you want to be using here.

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

So.. Here it is working at last: #!/usr/bin/env python import sqlalchemy from sqlalchemy.types import Integer from sqlalchemy.sql.expression import bindparam from sqlalchemy import text, create_engine, column stmt = text('WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS (' +

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

SELECT * FROM ( SELECT A.*, rownum rn FROM ( SELECT * FROM J1 ORDER BY DISP_SEQ) A WHERE rownum <= 4) WHERE rn >= 3 This is the crazy type of syntax required... On Fri, Apr 27, 2018 at 5:12 PM, Jeremy Flowers wrote: >

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

you don't really need the columns(...) part if you are sending the text() object to connection.execute(). That's mostly a thing to help when using the ORM which I don't think you want to be using here. Also set echo='debug' on create_engine which will log all the rows coming back. On Fri, Apr

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

Hah.. Seems Oracle ROWNUM starts from 1, not 0.. When you select between 3 and 4 - nothing returns. There's an Oracle quirk... On Fri, Apr 27, 2018 at 5:03 PM, Jeremy Flowers wrote: > Have tried dropping session.execute and using connection.execute - where > the

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

Have tried dropping session.execute and using connection.execute - where the method of parameter binding seems to work... == #!/usr/bin/env python import sqlalchemy from sqlalchemy.types import Integer from sqlalchemy.sql.expression import bindparam from sqlalchemy import text, create_engine,

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

I'm thinking along these lines. But can't get it to work. #!/usr/bin/env python import sqlalchemy from sqlalchemy import text, create_engine, column from sqlalchemy.orm import Session, sessionmaker stmt = text('''WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS (

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

Right so some sort of statement bind.. Just found this didn't work: #!/usr/bin/env python import sqlalchemy from sqlalchemy import text, create_engine, column from sqlalchemy.orm import Session, sessionmaker stmt = text('''WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS (

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

No, you'll need to convert that to the equivalent SQL. On Fri, Apr 27, 2018 at 3:14 PM, Jeremy Flowers wrote: > But can you still do things like slice(0,5)? Struggling to get that to work > atm... > > On Fri, Apr 27, 2018 at 2:48 PM, Simon King

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

Ah right.. http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.ResultProxy You can do fetchmany - and do a partial_results compare to empty list.. I see there is a next option too. So, it's a slightly different paradigm On Fri, Apr 27, 2018 at 3:14 PM, Jeremy Flowers

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

But can you still do things like slice(0,5)? Struggling to get that to work atm... On Fri, Apr 27, 2018 at 2:48 PM, Simon King wrote: > On Fri, Apr 27, 2018 at 12:57 PM, Jeremy Flowers > wrote: > > Right... > > Been trawling back thru this

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

On Fri, Apr 27, 2018 at 12:57 PM, Jeremy Flowers wrote: > Right... > Been trawling back thru this chain of exchanges.. > Looking for this: >> >> At this point I would echo Mike's question: why can't you just use >> "text()"? > > Just spotted another comment from Mike,

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

the Oracle dialect has to run complex gymnastics to get the effect of LIMIT in rewriting the query, so that is likely failing with a query that has a CTE inside of it as the CTE feature is not widely used with Oracle and we haven't put development resources into these cases. For now I'd avoid any

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

Right... Been trawling back thru this chain of exchanges.. Looking for this: > At this point I would echo Mike's question: why can't you just use > "text()"? Just spotted another comment from Mike, that I've just fixed too... > the literal() has to be against the regular value 1 and not the

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

The ".first()" method applies the DB-specific equivalent of "LIMIT 1" to the query. Oracle apparently doesn't support the LIMIT keyword, so SQLAlchemy wraps the entire query in "SELECT ... FROM (subquery) WHERE ROWNUM <= limit":

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

*Well the printed oracle sql dialect now works correctly - when I hack it an replace the substitution variables for the literals like so in my SQL Editor:* == WITH j1(jobmst_id, jobmst_name, jobmst_prntid, jobmst_type, lvl) AS (SELECT jobmst.jobmst_id AS jobmst_id, jobmst.jobmst_name AS

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

*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

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

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

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

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,

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

OK, your original SQL works, will find the difference On Thu, Apr 26, 2018 at 5:32 PM, Mike Bayer 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

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

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

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

Does this need to be submitted as an error on Git somewhere? On Thu, Apr 26, 2018 at 10:29 PM, Jeremy Flowers 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,

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

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 wrote: > I hacked the parameterised query: > This works.. > WITH j1(jobmst_id, jobmst_name, jobmst_prntid,

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

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

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

*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

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

On Thu, Apr 26, 2018 at 3:16 PM, Jeremy Flowers wrote: > Since the SQL injects parameters into the string, this doesn't seem to work. > eg: > A value is required for bind parameter 'param_1' What Jonathan said was, "If you want to see what it compiles to,", meaning,

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

What's needed is a method on the class that compiles the statement, to implicitly bind params. Here is the type: sqlalchemy.dialects.oracle.cx_oracle.OracleCompiler_cx_oracle/ I want to add slice to the end too, to paginate results. More bound params! -- SQLAlchemy - The Python SQL Toolkit

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

http://sqlalche.me/e/cd3x On Thu, Apr 26, 2018 at 8:16 PM, Jeremy Flowers wrote: > Since the SQL injects parameters into the string, this doesn't seem to > work. > eg: > A value is required for bind parameter 'param_1' > > WITH j1(jobmst_id, jobmst_name,

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

Since the SQL injects parameters into the string, this doesn't seem to work. eg: A value is required for bind parameter 'param_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

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

On Thursday, April 26, 2018 at 1:59:02 PM UTC-4, Jeremy Flowers wrote: > > >> But I now have a stmt variable at the end.. > How do I iterate over that in the form: > for row in results: >print(row) > results = query.all() for row in results: print row

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

> > I think it must have been down to the imports.. > By changing code for imports to this, it seemed to fix it. import sqlalchemy from tidal import Jobmst from sqlalchemy import create_engine, literal, text from sqlalchemy.dialects import oracle from sqlalchemy.orm import Session, aliased,

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

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

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

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

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

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

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

Mike. Thanks for the Datacamp link you emailed me. If I understand your point earlier the references to the full table class as well as the selected columns were causing the

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

It's just I've already pre-generated most of the classes - and Jobmst contains far more columns than I'm interested in using. So I wanted to select only some of the columns. I guess part of where I get confused is in the use of aliased. Whether it has to be for whole table. Also the use of the 'c'

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

On Mon, Apr 23, 2018 at 4:35 PM, Jeremy Flowers wrote: > I've had a go at trying to adapt your code: > > import tidal > from sqlalchemy import create_engine, Column > from sqlalchemy.dialects import oracle > from sqlalchemy.orm import aliased > from

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

That seems wrong what version of SQLAlchemy is that ? Make sure you are on the latest. On Mon, Apr 23, 2018, 4:36 PM Jeremy Flowers wrote: > I've had a go at trying to adapt your code: > > import tidal > from sqlalchemy import create_engine, Column > from

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

I've had a go at trying to adapt your code: 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

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

> > I'm also mysfified why connect() doesn't get shown as an method for engine > in Visual Studio. > Looked here

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

(I've literally got 18 tabs open looking at different parts of the docs ATM!) On Mon, Apr 23, 2018 at 5:29 PM, Jeremy Flowers wrote: > > > On Monday, 23 April 2018 16:46:52 UTC+1, Jeremy Flowers wrote: >> >> Is alias or mapping the correct construct if I want to

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

I am beginning to thing this may be the way... http://docs.sqlalchemy.org/en/latest/orm/tutorial.html >>> stmt = text("SELECT name, id, fullname, password "... "FROM >>> users where name=:name")>>> stmt = stmt.columns(User.name, User.id, >>> User.fullname, User.password)SQL >>>

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

On Monday, 23 April 2018 16:46:52 UTC+1, Jeremy Flowers wrote: > > Is alias or mapping the correct construct if I want to select a subset of >> the fields that SQLACODEGEN created? > > I sent you a Skype invite. I'd be interested in getting some training if > you'd like to continue the chat

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

> > Is alias or mapping the correct construct if I want to select a subset of > the fields that SQLACODEGEN created? I sent you a Skype invite. I'd be interested in getting some training if you'd like to continue the chat via Skype. -- SQLAlchemy - The Python SQL Toolkit and Object

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

> > The Session() object does not accept column objects within its > constructor, it looks like you are thinking of using the Query object. You are right, I was looking at the example here and I

[sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

I'm assuimg you do something like define a Column . But how do you assign a literal or expression value to it? (1 or LVL+1 later) Is there a bind processor example I need? from sqlalchemy import

Re: [sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

On Mon, Apr 23, 2018 at 9:30 AM, Jeremy Flowers 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

[sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

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,

[sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

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

[sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

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

[sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

Also in the materialised path example , what is What is unnest doing? Is it something like converting 1 row into many? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

[sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

Thanks for the feedback. CTE handles the WITH side of things. But how about depth/breadth first? On Thursday, 19 April 2018 22:28:00 UTC+1, Jonathan Vanasco wrote: > > In the example you pointed to, `c` are the columns of an object created by > `alias()` > > `secondary` was created as an

[sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

In the example you pointed to, `c` are the columns of an object created by `alias()` `secondary` was created as an `alias()`, and the query has 2 columns that were queried and named via `label()` "id" and "ancestor_id" sqlalchemy has a lot of relevant docs if you search via the keyword "CTE".

[sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

I'm thinking 'c' must be some sort of column shorthand -- 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

[sqlalchemy] Re: Recursive Queries in SQLAlchemy (WITH clause/SEARCH BY depth/breadth)

I suspect this may be trying to do something similar perhaps.. http://docs.sqlalchemy.org/en/latest/_modules/examples/materialized_paths/materialized_paths.html But I don't understand this part. Specifically where 'c' comes from: ancestors = relationship("Node", viewonly=True,