I was missing the correlate().
Thanks a lot Mike!
Best regards,
Kevin
On 16/08/2561 00:26, Mike Bayer wrote:
On Tue, Aug 14, 2018 at 12:40 AM, <kevin.thierry.cit...@gmail.com> wrote:
Dear SQLAlchemy experts,
I'm having difficulty generating an SQL query with SQLAlchemy.
I'm trying to query a list of messages with their originator and recipients.
Each message is sent by one host to one or more hosts. Hosts are all stored
in one table (host). There are two types of messages stored in two different
tables (dep, arr) which have their own association table for the
many-to-many relationship (dst_dep, arr_dep).
I attached a working code sample to this email (just modify the
DB_CONNECTION variable to your needs and create a DB if you want to run it).
I am running SQLAlchemy version 1.2.7 and PostgreSQL 10.4.
the code below generates your query exactly, see what might be
different about your own code:
subq1 = select([
func.string_agg(
Host.address,
aggregate_order_by(literal_column(","), Host.address)
)
]).where(
and_(
DEP.id == dst_dep.c.msg,
Host.id == dst_dep.c.host
)
)
subq2 = select([
func.string_agg(
Host.address,
aggregate_order_by(literal_column(","), Host.address)
)
]).where(
and_(
ARR.id == dst_arr.c.msg,
Host.id == dst_arr.c.host
)
)
stmt1 = select([
Host.address.label('src'),
subq1.correlate(DEP).label('dst'),
DEP.msg,
]).where(DEP.src == Host.id)
stmt2 = select([
Host.address.label('src'),
subq2.correlate(ARR).label('dst'),
ARR.msg,
]).where(ARR.src == Host.id)
union = union_all(stmt1, stmt2)
print(union.compile(dialect=postgresql.dialect()))
Here is the SQL request I would like to have:
SELECT
host.address AS src,
(SELECT string_agg(host.address, ', ' ORDER BY host.address)
FROM dst_dep, host
WHERE dep.id = dst_dep.msg AND host.id = dst_dep.host) AS dst,
dep.msg AS msg
FROM host, dep
WHERE dep.src = host.id
UNION ALL
SELECT
host.address AS src,
(SELECT string_agg(host.address, ', ' ORDER BY host.address)
FROM dst_arr, host
WHERE arr.id = dst_arr.msg AND host.id = dst_arr.host) AS dst,
arr.msg AS msg
FROM host, arr
WHERE arr.src = host.id;
This is the SQL query I am currently generating with SQLAlchemy:
SELECT
host.address AS src,
(SELECT string_agg(host.address, ', ' ORDER BY host.address) AS
string_agg_1
FROM dst_dep
WHERE dep.id = dst_dep.msg AND host.id = dst_dep.host) AS dst,
dep.msg AS msg
FROM host, dep
WHERE dep.src = host.id
UNION ALL
SELECT
host.address AS src,
(SELECT string_agg(host.address, ', ' ORDER BY host.address) AS
string_agg_2
FROM dst_arr
WHERE arr.id = dst_arr.msg AND host.id = dst_arr.host) AS dst,
arr.msg AS msg
FROM host, arr
WHERE arr.src = host.id;
This query generates the following SQL error:
ERROR: column "host.address" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 2: host.address AS src,
The only difference between the working SQL query and the non working one is
the missing table "host" in the FROM clause of the "string_agg" SELECT.
Code leading to this query:
def get_dst_subq(t, t_dst):
subq = select([
func.string_agg(Host.address, aggregate_order_by(literal_column("',
'"), Host.address).label('dst'))
]).where(
(t.id == t_dst.c.msg)
& (Host.id == t_dst.c.host)
).as_scalar()
return subq
def get_bs_subq(t, t_dst):
subq = select([
Host.address.label('src'),
get_dst_subq(t, t_dst).label('dst'),
t.msg.label('msg'),
]).where(t.src == Host.id)
return subq
tables = ((DEP, dst_dep), (ARR, dst_arr))
q = union_all(*[get_bs_subq(t[0], t[1]) for t in tables]).alias('res')
How can I generate the proper, working SQL query with SQLAlchemy? Thank you.
Best regards,
Kevin
--
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.
