As usual, I forgot to add the attachment...
Ladislav Lenart
On 21.3.2013 13:57, Ladislav Lenart wrote:
> Hello.
>
> Perhaps something like this is what you are looking for?
>
> def q_union():
> s = session
> q1 = s.query(
> Movement.customer_id.label('customer_id'),
> Movement.document_id.label('document_id'),
> Movement.debt.label('debt_moved'),
> literal(0.00).label('debt_total')
> )
> q2 = s.query(
> Debt.customer_id.label('customer_id'),
> Debt.document_id.label('document_id'),
> literal(0.00).label('debt_moved'),
> Debt.debt.label('debt_total'),
> )
> q3 = q1.union(q2).subquery()
> q4 = session.query(
> q3.c.customer_id,
> q3.c.document_id,
> func.sum(q3.c.debt_moved),
> func.sum(q3.c.debt_total),
> ).group_by(q3.c.customer_id, q3.c.document_id)
> return q4
>
> Call to `print q_union()` prints the following:
>
> SELECT
> anon_1.customer_id AS anon_1_customer_id,
> anon_1.document_id AS anon_1_document_id,
> sum(anon_1.debt_moved) AS sum_1,
> sum(anon_1.debt_total) AS sum_2
> FROM (
> SELECT
> anon_2.customer_id AS customer_id,
> anon_2.document_id AS document_id,
> anon_2.debt_moved AS debt_moved,
> anon_2.debt_total AS debt_total
> FROM (
> SELECT
> movement.customer_id AS customer_id,
> movement.document_id AS document_id,
> movement.debt AS debt_moved,
> %(param_1)s AS debt_total
> FROM movement
> UNION
> SELECT
> debt.customer_id AS customer_id,
> debt.document_id AS document_id,
> %(param_2)s AS debt_moved,
> debt.debt AS debt_total
> FROM debt
> ) AS anon_2
> ) AS anon_1
> GROUP BY anon_1.customer_id, anon_1.document_id
>
> I have attached a complete python script that you can run to verify if it's
> correct.
>
>
> HTH,
>
> Ladislav Lenart
>
>
> On 21.3.2013 09:07, Alexey Vihorev wrote:
>> Hi!
>>
>>
>>
>> I have this query:
>>
>>
>>
>> q1 =(s.query()
>>
>> .add_columns(
>>
>> Movement.customer_id,
>>
>> Movement.document_id,
>>
>> func.sum(Movement.debt).label('debt_moved'),
>>
>> literal(0.00).label('debt_total')
>>
>> )
>>
>> .group_by(Movement.customer_id, Movement.document_id)
>>
>> )
>>
>>
>>
>> q2 =(s.query().
>>
>> add_columns(
>>
>> Debt.customer_id,
>>
>> Debt.document_id,
>>
>> literal(0.00).label('debt_moved'),
>>
>> Debt.debt.label('debt_total'),
>>
>> )
>>
>> )
>>
>>
>>
>> q3 =q1.union(q2)
>>
>>
>>
>> The problem is that I need to SUM() the resulting query (columns
>> 'debt_moved',
>> 'debt_moved' should be summed, grouped by customer_id, document_id), but so
>> far all my attempts to use add_columns() and group_by() on q3 have failed.
>> How
>> should I handle this this?
>>
>> --
>> 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?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
# coding=utf-8
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer
from sqlalchemy.engine import create_engine
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.sql.expression import func, literal
Base = declarative_base()
class Movement(Base):
__tablename__ = 'movement'
id = Column(Integer(), primary_key=True)
customer_id = Column(Integer())
document_id = Column(Integer())
debt = Column(Integer())
class Debt(Base):
__tablename__ = 'debt'
id = Column(Integer(), primary_key=True)
customer_id = Column(Integer())
document_id = Column(Integer())
debt = Column(Integer())
session = None
def configure():
global session
conn_string = 'postgresql+psycopg2://lada:heslo@localhost:5433/sandbox'
engine = create_engine(conn_string, echo=True)
session = scoped_session(sessionmaker(bind=engine, autoflush=False))
Base.metadata.bind = engine
Base.metadata.create_all()
def fill():
session.add_all([
Movement(customer_id=1, document_id=1, debt=1),
Movement(customer_id=1, document_id=1, debt=2),
Movement(customer_id=1, document_id=1, debt=1),
Movement(customer_id=2, document_id=1, debt=1),
Movement(customer_id=2, document_id=1, debt=2),
])
session.add_all([
Debt(customer_id=1, document_id=1, debt=1),
Debt(customer_id=1, document_id=1, debt=2),
Debt(customer_id=1, document_id=1, debt=1),
Debt(customer_id=2, document_id=1, debt=1),
Debt(customer_id=2, document_id=1, debt=2),
])
session.flush()
def q_union():
s = session
q1 = s.query(
Movement.customer_id.label('customer_id'),
Movement.document_id.label('document_id'),
Movement.debt.label('debt_moved'),
literal(0.00).label('debt_total')
)
q2 = s.query(
Debt.customer_id.label('customer_id'),
Debt.document_id.label('document_id'),
literal(0.00).label('debt_moved'),
Debt.debt.label('debt_total'),
)
q3 = q1.union(q2).subquery()
q4 = session.query(
q3.c.customer_id,
q3.c.document_id,
func.sum(q3.c.debt_moved),
func.sum(q3.c.debt_total),
).group_by(q3.c.customer_id, q3.c.document_id)
return q4
def show(q):
print "Query:"
print q
print
print "Results:"
print q.all()
def main():
configure()
fill()
q = q_union()
show(q)
if __name__ == '__main__':
main()
