On Feb 26, 2010, at 6:28 AM, Gunnlaugur Briem wrote:
> This produces the equivalent SELECT ... UNION ALL ... UNION ALL ...
> using select, literal_column and union_all (and leaving aside the
> details of representing the literal values inside the VALUES clause)
> --- works just fine and PostgreSQL produces the same query plan for
> it:
>
> num_columns = len(weights_and_valueids[0]) # assuming all are the same
> length
> weights_clause = union_all(*(
> select([literal_column('%s' % weight_and_valueid[i]).label('column
> %d' % (i+1))
> for i in xrange(0, num_columns)
> ])
> for weight_and_valueid in weights_and_valueids
> )).alias('weights')
>
> Is this more portable, and thus should be used in preference to VALUES
> as a literal table clause?
ah the literal values clause, just had to use that recently. Probably
should start cataloging recipes for these things:
from sqlalchemy import *
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import FromClause
from sqlalchemy.sql import table, column
class values(FromClause):
def __init__(self, *args):
self.list = args
def _populate_column_collection(self):
self._columns.update(
[("column%d" % i, column("column%d" % i))
for i in xrange(1, len(self.list[0]) + 1)]
)
@compiles(values)
def compile_values(element, compiler, asfrom=False, **kw):
v = "VALUES %s" % ", ".join(
"(%s)" % ", ".join(repr(elem) for elem in tup)
for tup in element.list
)
if asfrom:
v = "(%s)" % v
return v
if __name__ == '__main__':
t1 = table('t1', column('a'), column('b'))
t2 = values((1, 0.5), (2, -0.5)).alias('weights')
print select([t1, t2]).select_from(t1.join(t2, t1.c.a==t2.c.column2))
I will log a ticket to add a nicely subclassable/documented "FromElement" since
that part above is non-obvious.
>
> Regards,
>
> - Gulli
>
>
>
> On Feb 26, 10:28 am, Gunnlaugur Briem <gunnlau...@gmail.com> wrote:
>> Hi,
>>
>> I want to perform a weighted sum over one column, specifying the
>> weights in a literal table (a VALUES clause):
>>
>> select a, sum(b*weights.column1)
>> FROM (select a, b from whatever) foo
>> JOIN (values (1, 0.5), (2, -0.5) ) weights ON weights.column2=a
>> GROUP BY 1;
>>
>> This is on PostgreSQL 8.4. To represent this in
>> sqlalchemy.sql.expression terms:
>>
>> select([subquery.c.a, func.sum(subquery.c.b *
>> weights_clause.c['column2']),
>> weights_clause.c['column1']==subquery.c.a
>> )
>>
>> but how do I get the weights_clause? I want it to compile to VALUES
>> (1, 0.5), (2, -0.5) (and then wrap it with .alias('weights')) but it
>> seems like there is no expression for this in SQLAlchemy so far (just
>> the VALUES support in INSERT and UPDATE, which does not work for this
>> use).
>>
>> Does this require a new subclass of FromClause, or is there an
>> existing way that I'm not seeing?
>>
>> The column names 'column1', 'column2' are as specified
>> herehttp://www.postgresql.org/docs/8.4/static/queries-values.html— the
>> names may be specific to PostgreSQL, not sure, but I believe (haven't
>> checked) that SQL generally allows VALUES to be used wherever SELECT
>> can be used.
>>
>> Thanks,
>>
>> - Gulli
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
