Hi all,
I'd like to learn a trick that would allow me to don't-repeat-myself: in my
current project, I have the noble goal of replacing MySQL with PostgreSQL. As
this is obviously an on-going effort, I need to keep existing code in working
order.
I have several queries built dynamically, using a "global" variable that
specifies the target engine, so my code is something like
PG_ENGINE = 'PostgreSQL'
MS_ENGINE = 'MySQL'
DB_ENGINE = PG_ENGINE
somequery = sa.select([foo.c.id, foo.c.description],
from_obj=foo.join(bar)...).where(...)
if DB_ENGINE is PG_ENGINE:
somequery.append_column(foo.c.pg_specific_field.label('field'))
elif DB_ENGINE is MS_ENGINE:
somequery.append_column(foo.c.ms_specific_field.label('field'))
In a few cases, those specific fields come from a PostgreSQL function that
returns multiple values, so I'd need to add a "LEFT JOIN function(xx,yy)" but
so far I wasn't able to find a way to do that. To keep going, I used
"append_from()" to add the function, but it's not ideal (nor right, probably).
Here is an actual example:
import sqlalchemy as sa
e = sa.create_engine('postgresql://localhost/tests')
md = sa.MetaData(bind=e)
kinds = sa.Table('kinds', md,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('description', sa.String(64)))
products = sa.Table('products', md,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('description', sa.String(64)),
sa.Column('idkind', sa.Integer,
sa.ForeignKey('kinds.id')))
md.create_all()
c = e.connect()
with c.begin():
c.execute("""
CREATE OR REPLACE FUNCTION price_and_discount(
in p_idproduct integer, p_idcustomer integer,
out price numeric(15,4), out discount numeric(7,4)
) AS $$
BEGIN
-- determine price and discount for the given customer
price := 123.456 * p_idproduct;
discount := 12.34;
RETURN;
END;
$$ LANGUAGE plpgsql
""")
c.execute('truncate kinds cascade')
c.execute('truncate products cascade')
insert_kind = kinds.insert()
insert_prod = products.insert()
with c.begin():
c.execute(insert_kind, id=1, description='Kind of foos')
c.execute(insert_kind, id=2, description='Kind of bars')
c.execute(insert_prod, id=1, description='Foo', idkind=1)
c.execute(insert_prod, id=2, description='Bar', idkind=2)
# This is what I'd like to obtain
raw_query = """\
SELECT p.id, p.description, k.description AS kind, pad.price, pad.discount
FROM products AS p
JOIN kinds k ON k.id = p.idkind
LEFT JOIN price_and_discount(p.id, 100) AS pad ON true"""
print("\n\nRaw query:\n%s\n" % raw_query)
res = c.execute(raw_query)
print(res.fetchall())
# This produces the same result, *in this particular case*
p = products.alias('p')
k = kinds.alias('k')
pad = sa.func.price_and_discount(
p.c.id,
sa.bindparam('idcustomer')).alias('pad')
query = sa.select([p.c.id, p.c.description, k.c.description.label('kind')],
from_obj=p.join(k))
query.append_from(pad)
query.append_column(sa.sql.column('pad.price',
is_literal=True).label('price'))
query.append_column(sa.sql.column('pad.discount',
is_literal=True).label('discount'))
print("\n\nCross-table query:\n%s\n" % query)
res = c.execute(query, idcustomer=100)
print(res.fetchall())
# This is wrong, it outputs a cross-table result
query = sa.select([p.c.id, p.c.description, k.c.description.label('kind')],
from_obj=p.join(k))
query.append_from(p.outerjoin(pad, sa.text('true')))
query.append_column(sa.sql.column('pad.price',
is_literal=True).label('price'))
query.append_column(sa.sql.column('pad.discount',
is_literal=True).label('discount'))
print("\n\nJoined query:\n%s\n" % query)
res = c.execute(query, idcustomer=100)
print(res.fetchall())
The last case in particular creates the following query (slightly edited for
readability):
SELECT p.id, p.description, k.description AS kind, pad.price AS price,
pad.discount AS discount
FROM products AS p JOIN kinds AS k ON k.id = p.idkind,
products AS p
LEFT OUTER JOIN price_and_discount(p.id, %(idcustomer)s) AS pad ON true
that clearly raises an 'ambiguous table "p"' psycopg2.ProgrammingError. I
tried playing with .correlate(), but without luck, so I'm assuming this is
plain wrong.
Curiously, with a simpler base query such as
query = sa.select([p], from_obj=p)
query.append_from(p.outerjoin(pad, sa.text('true')))
it instead emits the right SQL, that is:
SELECT p.id, p.description, p.idkind, pad.price AS price, pad.discount AS
discount
FROM products AS p LEFT OUTER JOIN price_and_discount(p.id, %(idcustomer)s)
AS pad ON true
So, is there a "correct way" to dynamically inject an outer join into an
existing base query?
Thanks in advance for any hint,
ciao, lele.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
[email protected] | -- Fortunato Depero, 1929.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
