0

I am newbie in python/sqlachemy world. I am trying to fetch data from PostgreSQL using sqlachemy in flask, where I need to fetch only selected column instead of streaming all the column from database through network. One of the approach is using session (like below) which is working fine,

session.query(User.user_id, User.name).all()

But for some reason I would like to stick with Model.query method instead of using sessions. So I ended up to use something like below,

User.query.options(load_only(User.user_id, User.name)).all()

Above code snippet doesn't filters the selected column, instead it gives back all the column. It looks like sqlachemy doesn't respects the load_only arguments. Why is that behaviour and any solution to achieve my use case in Model.query instead of using sessions?

My user model looks like below,

class User(db.Model):
    __tablename__ = 'user_info'

    user_id = Column(String(250), primary_key=True)
    name = Column(String(250))
    email = Column(String(250))
    address = Column(String(512))

Version Info

  • Python - 3.7
  • sqlachemy - 1.3.11

Edit 1: Though I added load_only attributes, It is generating the following query,

SELECT user.user_id AS user_user_id, user.name AS user_name, user.email AS user_email, user.address AS user_address FROM user_info
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Jaya Ananthram
  • 3,433
  • 1
  • 22
  • 37
  • How are you sure it's generating the query you're seeing? (You can check what your query compiles to by doing `User.query.options(load_only(User.user_id, User.name)).statement.compile(engine)`.) – univerio Jan 18 '20 at 03:12
  • Does this answer your question? [Flask SQLAlchemy query, specify column names](https://stackoverflow.com/questions/11530196/flask-sqlalchemy-query-specify-column-names) – daveruinseverything Jan 20 '20 at 00:49
  • This has been answered before: https://stackoverflow.com/questions/11530196/flask-sqlalchemy-query-specify-column-names - tl;dr use `User.query.with_entities(User.user_id, User.name).all()`. You can confirm this generates the correct query by printing `User.query.with_entities(User.id, User.name).statement.compile().string` – daveruinseverything Jan 20 '20 at 00:52

0 Answers0