[sqlalchemy] Re: Need help with update + select query

2016-02-25 Thread sector119 


product_t = Product.__table__
product_flavor_t = ProductFlavor.__table__
product_t_a = product_t.alias()

op.add_column(u'product', sa.Column('servings', sa.Integer(), nullable=True))
op.add_column(u'product', sa.Column('flavors_count', sa.Integer(), 
nullable=True))

servings = select([func.coalesce(func.avg(product_flavor_t.c.size), 0)]).\
   select_from(product_t_a.outerjoin(product_flavor_t)).\
   where(product_t.c.id == product_t_a.c.id)

flavors_count = select([func.count(product_flavor_t.c.id)]).\
select_from(product_t_a.outerjoin(product_flavor_t)).\
where(product_t.c.id == product_t_a.c.id)

op.execute(product_t.update().values(servings=servings, 
flavors_count=flavors_count))


op.alter_column(u'product', 'servings', nullable=False)
op.alter_column(u'product', 'flavors_count', nullable=False)

-- 
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] Re: Need help with update + select query

2016-02-25 Thread sector119 
Postgresql doesn't support this yet, so I have to use separate query all 
aggregates (( 

 SET (servings, flavors_count) = (
SELECT coalesce(avg(f.size), 0),
   count(f.id)
FROM product p LEFT OUTER JOIN product_flavor f ON p.id = f.product_id
WHERE product.id = p.id

четверг, 25 февраля 2016 г., 15:19:11 UTC+2 пользователь sector119 написал:
>
> Hello,
>
> Can some one help me with that query? I get AttributeError: servings
> I expect that sqlalchemy use update from select for that query or it's not 
> possible and I must use select(...).as_scalar() for every updated column?
>
> s = 
> select([func.coalesce(func.avg(product_flavor_t.c.size).label('servings'), 
> 0),
> func.count().label('flavors_count')]).\
> where(and_(product_flavor_t.c.product_id == product_t.c.id, 
> product_flavor_t.c.quantity > 0))
>
> op.execute(product_t.update().values(servings=s.c.servings, 
> flavors_count=s.c.flavors_count))
>
> Thanks!
>

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