SQLAlchemy – how to get data as a list of dictionaries

How to get data in python of models SQLAlchemy as a list or a list of dictionaries.

Basic query methods in SQLAlchemy.

We form the list list from the column

Get all user IDs in the List

user_ids = [[x.id for x in db.sessionquery(User.id)distinct()]

If instead of User.id request User, it will also work, but the processing speed will decrease significantly. When measuring from just two lines of data, the speed dropped on average 15 times. Conclusion: whenever possible, always get only the data you need right away.

The same, but not in a sheet and separately, for example, for processing at once:

for id in db.sessionquery(User.id)distinct():

Returning a list of dictionaries (dicts in list)

r._asdict ()

If we indicate specific columns that we want to pick up PriceMinute.time… This is convenient when there are few of them.

data = [[r._asdict() for r in db.sessionquery(PriceMinute.price, PriceMinute.time)filter_by(ticker=ticker)order_by(

row .__ dict__ for row in

When to pick up all columns method _asdict () will no longer work. Another option for getting all data from the table at once in the form of a list with nested dictionaries is suitable here:

data = [[row.__dict__ for row in db.sessionquery(PriceMinute)filter_by(ticker=ticker)order_by(

Through the @property decorator – with processing

Another way to return data in a list with combined dictionaries.

To do this, in the model class, you need to make an additional method with a decorator @property… Within this method, return data as dictionaries.

This method is also convenient in that you can process data and return it in the format in which you would like to see it. By processing them on return, not after. While keeping the code clean in the methods.

class Portfolio(db.Model):
__tablename__ = ‘portfolio’
id = db.Column(db.Integer, primary_key=True, nullable=False)
user_id = db.Column(db.Integer, ForeignKey(‘users.id’))
ticker = db.Column(db.String, nullable=False)

def __repr__(self):
return format(selfuser_id, selfticker, selfname)

def serialize(self):
return {
‘user_id’: selfuser_id,
‘ticker’: selfticker,
‘name’: selfname,

The request will be like this:

shares = db.sessionquery(Portfolio)filter_by(user_id=1)
shares = [[x.serialize for x in shares.all()]


[[{‘user_id’: 1, ‘ticker’: ‘ADS’, ‘name’: ‘Alliance Data Systems’},
{‘user_id’: 1, ‘ticker’: ‘WB’, ‘name’: ‘Weibo Corporation’}]

Through the @property decorator – no processing

When you don’t need to process the data, you can use this method (add to the model as in the example above):

def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__ table__.columns}

Data request:

shares = db.sessionquery(Portfolio)filter_by(user_id=1)
shares = [[x.as_dict for x in shares.all()]

The output will be a list with dictionaries from all data in the specified table.

Processing data in the form of dictionaries after

You can learn how to process data presented in the standard SQLAlchemy model class in the article on vars () or __dict__.

Similar Posts

Leave a Reply