CODEWITHSUNDEEP
pythonsqlsqlalchemy
wax
wax

Reputation: 23

Sqlalchemy keyed tuple error with select distinct

Keyed tuple objects returned by the same select distinct query, but created differently, are completely different, even though the result is the same.

Below is an example demonstrating this error:

import sqlalchemy as sa
from sqlalchemy.orm import (sessionmaker, scoped_session,)
from sqlalchemy.ext.declarative import (declarative_base,)

Base = declarative_base()
db_uri = 'sqlite:///test.db'
engine = sa.create_engine(db_uri, echo=True)
class Test(Base):

    __tablename__ = 'test'
    id = sa.Column(sa.Integer, primary_key=True)
    field2 = sa.Column(sa.Integer)

Base.metadata.create_all(bind=engine)


def main():
    Session = scoped_session(sessionmaker(bind=engine))
    session = Session()
    session.add_all([Test(field2=i) for i in xrange(10)])
    session.add_all([Test(field2=i) for i in xrange(10)])
    session.commit()

    q1 = session.query(Test.field2).distinct().all()
    q2 = session.query(sa.distinct(Test.field2)).all()

    # print the results
    print 'Q1 results :' , ' -- ', [i for i in q1]
    print 'Q2 results :' , ' -- ', [i for i in q2]

    # print the keyed tuple dictionary 
    print 'Q1 as_dict :', [i._asdict() for i in q1]        
    print 'Q2 as_dict :', [i._asdict() for i in q2]

These 2 queries return exactly the same results as shown by the first pair of prints

However, The obj._as_dict() values are different; populated for the 1st query and empty for the second.

Is this normal? I'm I missing something ?

regards,

Upvotes: 2

Views: 849

Answers (1)

van
van

Reputation: 76962

Short answer is that your q2 looses the name of the field, whereas _asdict() returns values only for named columns. Adding label will solve it:

q2 = session.query(sa.distinct(Test.field2).label('field2')).all()

Upvotes: 1

Related Questions