Search code examples
pythonsqlalchemy

SQL ALCHEMY - How to return query(model).all() as a tuple of values instead of model instance

I'm using SQL Alchemy for my project but I'm having a hard time finding out how to simply query a model and return a list of tuples as a result.

I have a simple model:

class Security(Base):

    __tablename__   = 'Securities'
    id              = Column(Integer, primary_key=True, autoincrement=True)
    ticker          = Column(String, index= True)
    company         = Column(String)
    sector          = Column(String)
    industry        = Column(String)
    country         = Column(String)

When I query this model within a session, I get a model in return:

s = Session()
q = s.query(Security).all()

If I simply print the query, I get a list of the model class.

[<models.Security at 0x7ff6eaf75a90>,
 <models.Security at 0x7ff6eaf759a0>,
 <models.Security at 0x7ff6eaf75940>,
 <models.Security at 0x7ff6eaf758e0>,
 <models.Security at 0x7ff6eaf75040>,
 <models.Security at 0x7ff6eaf750a0>,
 <models.Security at 0x7ff6eaf75100>,
 <models.Security at 0x7ff6eaf75070>,
 <models.Security at 0x7ff6eaf751c0>,..

So I've found a solution for now:

[(i.id, i.ticker, i.company, i. sector, i.industry, i.country) for i in q]

Is there a more pythonic way to do this? When I was querying my database with mysql--connector-python (or pymysql, can't remember), the results would be printed out directly as a list of tuple.

Solution

  • If you want to get tuples rather than model instances you can query the underlying Table object rather than the model class, for example

    # 1.x style
rows = session.query(Security.__table__).all()

# 2.0 style
rows = session.scalars(sa.select(Security.__table__)).all()

    Note that these approaches may not work for relationships.

    If the application is designed to only handle raw tuples then there is little point using SQLAlchemy's ORM layer - use the core layer instead where you can work with Table objects directly.