Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-19 Thread su-sa
Hi Simon, you are absolutely correct, I just thought may be its something so obvious that some experienced people immediately see. I am sorry about not being in a position to put enough data. But luckily, I found out that the problem was only this that I was writing a parameter in the wrong c

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-19 Thread Simon King
Trying to diagnose a problem with a query when we don't have any way to run it for ourselves or sample data to run it against, is very difficult. If you want more help, I think you're going to need to produce a self-contained script with your table definitions and some sample data, that runs agains

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread su-sa
@Mike, Jonathan and Simon - Thank you so much for all your efforts. I hope I can fix the problem soon in the dialect :-) P.s. I have finally been able to generate the correct query, but now currently the problem is that I get no result, although the query is absolutely correct and should give b

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread Mike Bayer
Dialects can completely affect the generation of SQL strings and the odd pattern of FILTER (WHERE ... ) shown in your output suggests this is the case as SQLAlchemy does not generate any such content ; there is no "FILTER" keyword in SQLAlchemy. On Wed, Apr 18, 2018, 11:20 AM su-sa wrote: > > Bu

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread su-sa
Hi, Thanks for your time, probably you are all right. Greetings, Sugandha -- 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/h

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread su-sa
Hi, Thanks a lot for your time, I have been work on and with that dialect for sometime, but probably you are all correct. Greetings, Sugandha -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Mini

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread Jonathan Vanasco
On Wednesday, April 18, 2018 at 11:20:39 AM UTC-4, su-sa wrote: > > > But if I am not mistaken, the from clause of the query is generated by > SQLAlchemy and the database driver or the dialect has no influence on this > from clause generation of SQLAlchemy. > As an aside from everything Simon

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread su-sa
q2 = session.query(Supplier.s_acctbal, Supplier.s_name, Nation.n_name, Part.p_partkey, Part.p_mfgr, Supplier.s_address, Supplier.s_phone, Supplier.s_comment).filter(Part.p_partkey == Partsupp.ps_partkey, Supplier.s_suppkey== Partsupp.ps_suppkey, Part.p_size

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread su-sa
I am not sure what you mean exactly, am I applying the .correlate at the wrong place ? Thats the query I am trying to reproduce. select top 100 s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment from sf10.c_part, sf10.c_supplier, sf10.

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread Simon King
Ah, I didn't notice this part of your query: .filter(..., Partsupp.ps_supplycost == session.query(func.min(Partsupp.ps_supplycost))) I'm not sure exactly what SQL you are aiming for there, but I think you need to add .correlate (or maybe .correlate_except) to that inner query. Simon On Wed, Ap

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread su-sa
Hi Simon, I am executing q2 on its own, may be I shouldnt have mentioned correlate, but if I dont mention it I get another error: sqlalchemy.exc.InvalidRequestError: Select statement 'SELECT min(partsupp.ps_supplycost) AS min_1 FROM partsupp, part, supplier, nation, region WHERE part.p_partkey

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread Simon King
Are you executing q2 on it's own, or nested in a larger query? If so, can you show the code for the larger query. I ask because you are calling ".correlate(Partsupp, Supplier, Nation, Region)", which I think has the effect of removing those tables from the FROM clause, in the assumption that they

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread su-sa
But if I am not mistaken, the from clause of the query is generated by SQLAlchemy and the database driver or the dialect has no influence on this from clause generation of SQLAlchemy. And as you can see, the wrong from clause in generated in the subquery: AS min_1 \nFROM part \nWHERE part.p_

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread Mike Bayer
unfortunately this issue is related to that database /drivers so you'd need to get support from those folks. On Wed, Apr 18, 2018 at 10:37 AM, su-sa wrote: > Hi Mike, > > 'hdbcli' is the official python driver for SAP HANA and you are right that > it is not open-source. I also tried with pyhdb: >

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread su-sa
Hi Mike, 'hdbcli' is the official python driver for SAP HANA and you are right that it is not open-source. I also tried with pyhdb: q2 = session.query(Supplier.s_acctbal, Supplier.s_name, Nation.n_name, Part.p_partkey, Part.p_mfgr, Supplier.s_address, Supplier.s_phone, Suppl

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread su-sa
Hi Mike, 'hdbcli' is the official python driver and you are right that it is not open-source. I also tried with pyhdb: q2 = session.query(Supplier.s_acctbal, Supplier.s_name, Nation.n_name, Part.p_partkey, Part.p_mfgr, Supplier.s_address, Supplier.s_phone, Supplier.s_comme

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread su-sa
On Wednesday, 18 April 2018 15:50:10 UTC+2, Mike Bayer wrote: > > what the heck is FILTER?I am googling for "hdbcli" and not getting > a clear signal back, it's not even on pypi, seems to be related to SAP > but I can find no source links or anything (there seems to be a > competing driver

Re: [sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread Mike Bayer
what the heck is FILTER?I am googling for "hdbcli" and not getting a clear signal back, it's not even on pypi, seems to be related to SAP but I can find no source links or anything (there seems to be a competing driver PyHDB that is more open source friendly).This would be an issue with tha

[sqlalchemy] Re: unable to do nested select while using query object

2018-04-18 Thread su-sa
On Wednesday, 18 April 2018 12:18:51 UTC+2, su-sa wrote: > > > > Hi everyone, > > I've been trying quite a while to reproduce the following query:q2 = text( > "select top 100 s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, > s_phone, s_comment from system.part, " > "system.suppl