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 case. So it was actually nothing so serious. But 
now I can atleast be sure that there is no problem with the dialect.

Thanks a lot for your help :-)

Have a nice day! 
Best Regards,
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/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.

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 against a sqlite database.

Good luck!

Simon

On Wed, Apr 18, 2018 at 10:21 PM, su-sa  wrote:
> @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 back results.
> Thats how I built the query now:
>
> subquery = select([func.min(Partsupp.ps_supplycost)]).where(Part.p_partkey
> == Partsupp.ps_partkey).where(
>Supplier.s_suppkey ==
> Partsupp.ps_suppkey).where(Supplier.s_nationkey ==
> Nation.n_nationkey).where(
>Nation.n_regionkey ==
> Region.r_regionkey).where(Region.r_name == 'EUROPE').correlate(Part)
> 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 ==
> 15, Part.p_type.like('%BRASS'),
>Supplier.s_nationkey == Nation.n_nationkey,
> Nation.n_regionkey == Region.r_regionkey,
>Region.r_name == 'Europe', Partsupp.ps_supplycost ==
> subquery).order_by(Supplier.s_acctbal.desc(),
>Nation.n_name, Supplier.s_name,
> Part.p_partkey).limit(100)
>
>
>
>
> --
> 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.

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 back results.
Thats how I built the query now:

subquery = select([func.min(Partsupp.ps_supplycost)]).where(Part.p_partkey == 
Partsupp.ps_partkey).where(
   Supplier.s_suppkey == 
Partsupp.ps_suppkey).where(Supplier.s_nationkey == Nation.n_nationkey).where(
   Nation.n_regionkey == 
Region.r_regionkey).where(Region.r_name == 'EUROPE').correlate(Part)
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 == 15, 
Part.p_type.like('%BRASS'),
   Supplier.s_nationkey == Nation.n_nationkey, 
Nation.n_regionkey == Region.r_regionkey,
   Region.r_name == 'Europe', Partsupp.ps_supplycost == 
subquery).order_by(Supplier.s_acctbal.desc(),
   Nation.n_name, Supplier.s_name, Part.p_partkey).limit(100)

 


-- 
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.

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:

>
> 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_partkey = partsupp.ps_partkey AND 
> supplier.s_suppkey = partsupp.ps_suppkey AND supplier.s_nationkey = 
> nation.n_nationkey
>
>
> What should actually be generated is:
> FROM supplier, nation, part, partsupp, region
>
> --
> 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.

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/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.

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: 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.

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 is saying:

SqlAlchemy generates the textual SQL provided to the DBAPI, but it is 
influenced-by or overridden-in the dialect.

It sort of looks like this:

   [your code] -> [complied by sqlalchemy WITH AND FOR dialect] -> [dbapi] 
-> [database] -> [dbapi] -> [sqlalchemy turns into objects] -> [your code]

I assume you are probably using this 
dialect:  https://github.com/SAP/sqlalchemy-hana

When there is an issue with a dialect in a certain situation

* very often: the dialects are capable of generating the SQL for a certain 
situation, and they must fix it.
* very rare: sqlalchemy doesn't offer the dialect appropriate hooks to 
accomplish something; the dialect maintainers propose a way to for 
SqlAlchemy to offer the needed functionality for their dialect to generate 
a specific query

-- 
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.

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 == 15, 
Part.p_type.like('%BRASS'),
   Supplier.s_nationkey == Nation.n_nationkey, 
Nation.n_regionkey == Region.r_regionkey,
   Region.r_name == 'Europe', Partsupp.ps_supplycost == 
(session.query(func.min(Partsupp.ps_supplycost)
).filter(Part.p_partkey == Partsupp.ps_partkey,
   Supplier.s_suppkey == Partsupp.ps_suppkey, 
Supplier.s_nationkey == Nation.n_nationkey,
   Nation.n_regionkey == Region.r_regionkey, Region.r_name == 
'EUROPE')).correlate(Partsupp, Supplier, Nation, 
Region)).order_by(Supplier.s_acctbal.desc(),
   Nation.n_name, Supplier.s_name, Part.p_partkey).limit(100)

If thats what you meant, I tried it, but it gives me the same error :(

-- 
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.

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.c_partsupp, sf10.c_nation, 
sf10.c_region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 15
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
and ps_supplycost = (
select
min(ps_supplycost)
from
sf10.c_partsupp, sf10.c_supplier, sf10.c_nation, sf10.c_region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey;

Thanks a lot for your time
 

-- 
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.

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, Apr 18, 2018 at 5:24 PM, su-sa  wrote:
>
> 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 = partsupp.ps_partkey AND supplier.s_suppkey =
> partsupp.ps_suppkey AND supplier.s_nationkey = nation.n_nationkey AND
> nation.n_regionkey = region.r_regionkey AND region.r_name = ?' returned no
> FROM clauses due to auto-correlation; specify correlate() to control
> correlation manually.
>
> I am not sure how I should go about it.
>
> Thanks for your help,
>
> Greetings,
> Sugandha
>
> On Wednesday, 18 April 2018 17:27:36 UTC+2, Simon King wrote:
>>
>> 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 are part of the outer
>> query.
>>
>> Simon
>>
>>
> --
> 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.

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 = partsupp.ps_partkey AND supplier.s_suppkey = 
partsupp.ps_suppkey AND supplier.s_nationkey = nation.n_nationkey AND 
nation.n_regionkey = region.r_regionkey AND region.r_name = ?' returned no 
FROM clauses due to auto-correlation; specify correlate() to 
control correlation manually.

I am not sure how I should go about it.

Thanks for your help,

Greetings,
Sugandha

On Wednesday, 18 April 2018 17:27:36 UTC+2, Simon King wrote:
>
> 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 are part of the outer 
> query. 
>
> Simon 
>
>
>

-- 
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.

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 are part of the outer
query.

Simon

On Wed, Apr 18, 2018 at 4:20 PM, 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.
>
> And as you can see, the wrong from clause in generated in the subquery:
>
> AS min_1 \nFROM part \nWHERE part.p_partkey = partsupp.ps_partkey AND
> supplier.s_suppkey = partsupp.ps_suppkey AND supplier.s_nationkey =
> nation.n_nationkey
>
>
> What should actually be generated is:
> FROM supplier, nation, part, partsupp, region
>
> --
> 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.

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_partkey = partsupp.ps_partkey AND 
supplier.s_suppkey = partsupp.ps_suppkey AND supplier.s_nationkey = 
nation.n_nationkey 

 
What should actually be generated is:
FROM supplier, nation, part, partsupp, region 

-- 
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.

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:
>
> 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 ==
> 15, Part.p_type.like('%BRASS'),
>Supplier.s_nationkey == Nation.n_nationkey,
> Nation.n_regionkey == Region.r_regionkey,
>Region.r_name == 'Europe', Partsupp.ps_supplycost ==
> session.query(func.min(Partsupp.ps_supplycost)
> ).filter(Part.p_partkey == Partsupp.ps_partkey,
>Supplier.s_suppkey == Partsupp.ps_suppkey,
> Supplier.s_nationkey == Nation.n_nationkey,
>Nation.n_regionkey == Region.r_regionkey, Region.r_name
> == 'EUROPE').correlate(Partsupp, Supplier, Nation,
> Region)).order_by(Supplier.s_acctbal.desc(),
>Nation.n_name, Supplier.s_name,
> Part.p_partkey).limit(100)
>
> I still get an error similar to my 2nd update:
>
> sqlalchemy.exc.DatabaseError: (pyhdb.exceptions.DatabaseError) invalid
> column name: The column 'SUPPLIER.S_ACCTBAL' is
> invalid in the ORDER BY clause because the GROUP BY clause or an aggregation
> function does not contain it: line 5 col 211 (at pos 887)
> [SQL: u'SELECT supplier.s_acctbal AS supplier_s_acctbal, supplier.s_name AS
> supplier_s_name, nation.n_name AS nation_n_name, part.p_partkey
> AS part_p_partkey, part.p_mfgr AS part_p_mfgr, supplier.s_address AS
> supplier_s_address, supplier.s_phone AS supplier_s_phone,
> supplier.s_comment AS supplier_s_comment \nFROM supplier, nation, part,
> partsupp, region \nWHERE part.p_partkey = partsupp.ps_partkey AND
> supplier.s_suppkey = partsupp.ps_suppkey AND part.p_size = ? AND part.p_type
> LIKE ? AND supplier.s_nationkey = nation.n_nationkey
> AND nation.n_regionkey = region.r_regionkey AND region.r_name = ? AND
> partsupp.ps_supplycost = (SELECT min(partsupp.ps_supplycost)
> AS min_1 \nFROM part \nWHERE part.p_partkey = partsupp.ps_partkey AND
> supplier.s_suppkey = partsupp.ps_suppkey AND supplier.s_nationkey =
> nation.n_nationkey
> AND nation.n_regionkey = region.r_regionkey AND region.r_name = ?) ORDER BY
> supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey\n
> LIMIT ?']
> [parameters: (15, '%BRASS', 'Europe', 'EUROPE', 100)] (Background on this
> error at: http://sqlalche.me/e/4xp6)
>
> Do you may be know what the reason could be? Can there be some error in my
> query building using SQLAlchemy?
>
> Thanks and Best Regards,
> 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/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.

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, 
Supplier.s_comment).filter(Part.p_partkey == Partsupp.ps_partkey,
   Supplier.s_suppkey== Partsupp.ps_suppkey, Part.p_size == 15, 
Part.p_type.like('%BRASS'),
   Supplier.s_nationkey == Nation.n_nationkey, 
Nation.n_regionkey == Region.r_regionkey,
   Region.r_name == 'Europe', Partsupp.ps_supplycost == 
session.query(func.min(Partsupp.ps_supplycost)
).filter(Part.p_partkey == Partsupp.ps_partkey,
   Supplier.s_suppkey == Partsupp.ps_suppkey, 
Supplier.s_nationkey == Nation.n_nationkey,
   Nation.n_regionkey == Region.r_regionkey, Region.r_name == 
'EUROPE').correlate(Partsupp, Supplier, Nation, 
Region)).order_by(Supplier.s_acctbal.desc(),
   Nation.n_name, Supplier.s_name, Part.p_partkey).limit(100)

I still get an error similar to my 2nd update:

sqlalchemy.exc.DatabaseError: (pyhdb.exceptions.DatabaseError) invalid column 
name: The column 'SUPPLIER.S_ACCTBAL' is 
invalid in the ORDER BY clause because the GROUP BY clause or an aggregation 
function does not contain it: line 5 col 211 (at pos 887) 
[SQL: u'SELECT supplier.s_acctbal AS supplier_s_acctbal, supplier.s_name AS 
supplier_s_name, nation.n_name AS nation_n_name, part.p_partkey 
AS part_p_partkey, part.p_mfgr AS part_p_mfgr, supplier.s_address AS 
supplier_s_address, supplier.s_phone AS supplier_s_phone, 
supplier.s_comment AS supplier_s_comment \nFROM supplier, nation, part, 
partsupp, region \nWHERE part.p_partkey = partsupp.ps_partkey AND 
supplier.s_suppkey = partsupp.ps_suppkey AND part.p_size = ? AND part.p_type 
LIKE ? AND supplier.s_nationkey = nation.n_nationkey 
AND nation.n_regionkey = region.r_regionkey AND region.r_name = ? AND 
partsupp.ps_supplycost = (SELECT min(partsupp.ps_supplycost) 
AS min_1 \nFROM part \nWHERE part.p_partkey = partsupp.ps_partkey AND 
supplier.s_suppkey = partsupp.ps_suppkey AND supplier.s_nationkey = 
nation.n_nationkey 
AND nation.n_regionkey = region.r_regionkey AND region.r_name = ?) ORDER BY 
supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey\n LIMIT 
?']
[parameters: (15, '%BRASS', 'Europe', 'EUROPE', 100)] (Background on this error 
at: http://sqlalche.me/e/4xp6)

Do you may be know what the reason could be? Can there be some error in my 
query building using SQLAlchemy?

Thanks and Best Regards,
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/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.

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_comment).filter(Part.p_partkey == Partsupp.ps_partkey,
   Supplier.s_suppkey== Partsupp.ps_suppkey, Part.p_size == 15, 
Part.p_type.like('%BRASS'),
   Supplier.s_nationkey == Nation.n_nationkey, 
Nation.n_regionkey == Region.r_regionkey,
   Region.r_name == 'Europe', Partsupp.ps_supplycost == 
session.query(func.min(Partsupp.ps_supplycost)
).filter(Part.p_partkey == Partsupp.ps_partkey,
   Supplier.s_suppkey == Partsupp.ps_suppkey, 
Supplier.s_nationkey == Nation.n_nationkey,
   Nation.n_regionkey == Region.r_regionkey, Region.r_name == 
'EUROPE').correlate(Partsupp, Supplier, Nation, 
Region)).order_by(Supplier.s_acctbal.desc(),
   Nation.n_name, Supplier.s_name, Part.p_partkey).limit(100)

I still get an error similar to my 2nd update:

sqlalchemy.exc.DatabaseError: (pyhdb.exceptions.DatabaseError) invalid column 
name: The column 'SUPPLIER.S_ACCTBAL' is 
invalid in the ORDER BY clause because the GROUP BY clause or an aggregation 
function does not contain it: line 5 col 211 (at pos 887) 
[SQL: u'SELECT supplier.s_acctbal AS supplier_s_acctbal, supplier.s_name AS 
supplier_s_name, nation.n_name AS nation_n_name, part.p_partkey 
AS part_p_partkey, part.p_mfgr AS part_p_mfgr, supplier.s_address AS 
supplier_s_address, supplier.s_phone AS supplier_s_phone, 
supplier.s_comment AS supplier_s_comment \nFROM supplier, nation, part, 
partsupp, region \nWHERE part.p_partkey = partsupp.ps_partkey AND 
supplier.s_suppkey = partsupp.ps_suppkey AND part.p_size = ? AND part.p_type 
LIKE ? AND supplier.s_nationkey = nation.n_nationkey 
AND nation.n_regionkey = region.r_regionkey AND region.r_name = ? AND 
partsupp.ps_supplycost = (SELECT min(partsupp.ps_supplycost) 
AS min_1 \nFROM part \nWHERE part.p_partkey = partsupp.ps_partkey AND 
supplier.s_suppkey = partsupp.ps_suppkey AND supplier.s_nationkey = 
nation.n_nationkey 
AND nation.n_regionkey = region.r_regionkey AND region.r_name = ?) ORDER BY 
supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey\n LIMIT 
?']
[parameters: (15, '%BRASS', 'Europe', 'EUROPE', 100)] (Background on this error 
at: http://sqlalche.me/e/4xp6)

Do you may be know what the reason could be? Can there be some error in my 
query building using SQLAlchemy?

Thanks and Best Regards,
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/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.

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 PyHDB that is more open source friendly).This 
> would be an issue with that SQLAlchemy dialect that I am not even able 
> to find on the web, you'd need to ask them. 
>
>  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_comment).filter(Part.p_partkey == Partsupp.ps_partkey,
   Supplier.s_suppkey== Partsupp.ps_suppkey, Part.p_size == 15, 
Part.p_type.like('%BRASS'),
   Supplier.s_nationkey == Nation.n_nationkey, 
Nation.n_regionkey == Region.r_regionkey,
   Region.r_name == 'Europe', Partsupp.ps_supplycost == 
session.query(func.min(Partsupp.ps_supplycost)
).filter(Part.p_partkey == Partsupp.ps_partkey,
   Supplier.s_suppkey == Partsupp.ps_suppkey, 
Supplier.s_nationkey == Nation.n_nationkey,
   Nation.n_regionkey == Region.r_regionkey, Region.r_name == 
'EUROPE').correlate(Partsupp, Supplier, Nation, 
Region)).order_by(Supplier.s_acctbal.desc(),
   Nation.n_name, Supplier.s_name, Part.p_partkey).limit(100)

I still get an error similar to my 2nd update:

sqlalchemy.exc.DatabaseError: (pyhdb.exceptions.DatabaseError) invalid column 
name: The column 'SUPPLIER.S_ACCTBAL' is 
invalid in the ORDER BY clause because the GROUP BY clause or an aggregation 
function does not contain it: line 5 col 211 (at pos 887) 
[SQL: u'SELECT supplier.s_acctbal AS supplier_s_acctbal, supplier.s_name AS 
supplier_s_name, nation.n_name AS nation_n_name, part.p_partkey 
AS part_p_partkey, part.p_mfgr AS part_p_mfgr, supplier.s_address AS 
supplier_s_address, supplier.s_phone AS supplier_s_phone, 
supplier.s_comment AS supplier_s_comment \nFROM supplier, nation, part, 
partsupp, region \nWHERE part.p_partkey = partsupp.ps_partkey AND 
supplier.s_suppkey = partsupp.ps_suppkey AND part.p_size = ? AND part.p_type 
LIKE ? AND supplier.s_nationkey = nation.n_nationkey 
AND nation.n_regionkey = region.r_regionkey AND region.r_name = ? AND 
partsupp.ps_supplycost = (SELECT min(partsupp.ps_supplycost) 
AS min_1 \nFROM part \nWHERE part.p_partkey = partsupp.ps_partkey AND 
supplier.s_suppkey = partsupp.ps_suppkey AND supplier.s_nationkey = 
nation.n_nationkey 
AND nation.n_regionkey = region.r_regionkey AND region.r_name = ?) ORDER BY 
supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey\n LIMIT 
?']
[parameters: (15, '%BRASS', 'Europe', 'EUROPE', 100)] (Background on this error 
at: http://sqlalche.me/e/4xp6)

Do you may be know what the reason could be? Can there be some error in my 
query building using SQLAlchemy?

Thanks and Best Regards,
Sugandha



On Wed, Apr 18, 2018 at 6:34 AM, su-sa  
> wrote: 
> > 
> > 
> > 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.supplier, system.partsupp, system.nation, system.region 
> where 
> >> p_partkey = ps_partkey " 
> >> "and s_suppkey = ps_suppkey and p_size = 15 and p_type like 
> '%BRASS' 
> >> and s_nationkey = n_nationkey " 
> >> "and n_regionkey = r_regionkey and r_name = 'EUROPE' and 
> ps_supplycost 
> >> = (select min(ps_supplycost) " 
> >> "from system.partsupp, system.supplier, system.nation, 
> system.region 
> >> where p_partkey = ps_partkey " 
> >> "and s_suppkey = ps_suppkey and s_nationkey = n_nationkey and 
> >> n_regionkey = r_regionkey and r_name = 'EUROPE')" 
> >> " order by s_acctbal desc, n_name, s_name, p_partkey;") 
> >> 
> >> 
> >> Thats what I have currently: 
> >> 
> >> 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 
> >> == 15, Part.p_type.like('%BRASS'), 
> >>Supplier.s_nationkey == Nation.n_nationkey, 
> >> Nation.n_regionkey == Region.r_regionkey, 
> >>Region.r_name == 'Europe', Partsupp.ps_supplycost == 
> >> func.min(Partsupp.ps_supplycost).filter(Part.p_partkey == 
> >> Partsupp.ps_partkey, 
> >>Supplier.s_suppkey == Partsupp.ps_suppkey,

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 that SQLAlchemy dialect that I am not even able
to find on the web, you'd need to ask them.

On Wed, Apr 18, 2018 at 6:34 AM, su-sa  wrote:
>
>
> 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.supplier, system.partsupp, system.nation, system.region where
>> p_partkey = ps_partkey "
>> "and s_suppkey = ps_suppkey and p_size = 15 and p_type like '%BRASS'
>> and s_nationkey = n_nationkey "
>> "and n_regionkey = r_regionkey and r_name = 'EUROPE' and ps_supplycost
>> = (select min(ps_supplycost) "
>> "from system.partsupp, system.supplier, system.nation, system.region
>> where p_partkey = ps_partkey "
>> "and s_suppkey = ps_suppkey and s_nationkey = n_nationkey and
>> n_regionkey = r_regionkey and r_name = 'EUROPE')"
>> " order by s_acctbal desc, n_name, s_name, p_partkey;")
>>
>>
>> Thats what I have currently:
>>
>> 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
>> == 15, Part.p_type.like('%BRASS'),
>>Supplier.s_nationkey == Nation.n_nationkey,
>> Nation.n_regionkey == Region.r_regionkey,
>>Region.r_name == 'Europe', Partsupp.ps_supplycost ==
>> func.min(Partsupp.ps_supplycost).filter(Part.p_partkey ==
>> Partsupp.ps_partkey,
>>Supplier.s_suppkey == Partsupp.ps_suppkey,
>> Supplier.s_nationkey == Nation.n_nationkey,
>>Nation.n_regionkey == Region.r_regionkey, Region.r_name
>> == 'EUROPE')).order_by(Supplier.s_acctbal.desc(),
>>Nation.n_name, Part.p_partkey).limit(100)
>>
>> But I am getting the error:
>>
>> sqlalchemy.exc.DBAPIError: (hdbcli.dbapi.Error) (257, 'sql syntax error:
>> incorrect syntax near "FILTER": line 3 col 301 (at pos 649)')
>> [SQL: u'SELECT supplier.s_acctbal AS supplier_s_acctbal, supplier.s_name
>> AS supplier_s_name, nation.n_name AS nation_n_name, part.p_partkey AS
>> part_p_partkey, part.p_mfgr AS part_p_mfgr,
>> supplier.s_address AS supplier_s_address, supplier.s_phone AS
>> supplier_s_phone, supplier.s_comment AS supplier_s_comment \nFROM supplier,
>> nation, part, partsupp, region \nWHERE part.p_partkey = partsupp.ps_partkey
>> AND supplier.s_suppkey = partsupp.ps_suppkey AND part.p_size = ? AND
>> part.p_type LIKE ? AND supplier.s_nationkey = nation.n_nationkey AND
>> nation.n_regionkey = region.r_regionkey AND region.r_name = ? AND
>> partsupp.ps_supplycost = min(partsupp.ps_supplycost) FILTER (WHERE
>> part.p_partkey = partsupp.ps_partkey AND supplier.s_suppkey =
>> partsupp.ps_suppkey AND supplier.s_nationkey = nation.n_nationkey AND
>> nation.n_regionkey = region.r_regionkey AND region.r_name = ?) ORDER BY
>> supplier.s_acctbal DESC, nation.n_name, part.p_partkey\n LIMIT ?']
>> [parameters: (15, '%BRASS', 'Europe', 'EUROPE', 100)]
>> (Background on this error at: http://sqlalche.me/e/dbapi)
>>
>>
>> Which makes sense because inside the generated SQL Query, also the word
>> filter is present which is not recognised and because the nested select
>> statement is not generated.
>>
>> Could anyone of you tell me what I doing wrong?
>>
>>
>> Sorry if that seems to be a stupid question.
>>
>> Greetings,
>> Sugandha
>
>
>
> I also did the following:
>
> 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 ==
> 15, Part.p_type.like('%BRASS'),
>Supplier.s_nationkey == Nation.n_nationkey,
> Nation.n_regionkey == Region.r_regionkey,
>Region.r_name == 'Europe', Partsupp.ps_supplycost ==
> session.query(func.min(Partsupp.ps_supplycost)).filter(Part.p_partkey ==
> Partsupp.ps_partkey,
>Supplier.s_suppkey == Partsupp.ps_suppkey,
> Supplier.s_nationkey == Nation.n_nationkey,
>Nation.n_regionkey == Region.r_regionkey, Region.r_name
> == 'EUROPE').correlate(Partsupp, Supplier, Nation,
> Region)).order_by(Supplier.s_acctbal.desc(),
>Nation.n_name, Part.p_partkey).limit(100)
>
> But this gives me a new error: