On 10/24/15 4:19 PM, Lele Gaifax wrote:
> 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?
i think the form here follows the documentation for a function that we
can select from; you'd take your func.foo() and put it inside of a
select() object as described at
http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html#functions,
where you get a subquery that you select from. Try that for now.
With Postgresql, we are more and more getting requests for PG's more
sophisticated functional syntaxes, which can be achieved now with some
trickery such as that we see in
https://bitbucket.org/zzzeek/sqlalchemy/issues/3555/extract-a-records-field#comment-22608043
or similar, so eventually we're going to have to stop ignoring those
syntaxes and add some more first class constructs that map to all of
PG's extensions to SQL syntax. You can try the recipe in that
bitbucket comment as well.
>
> Thanks in advance for any hint,
> ciao, lele.
>
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
