How to convert SQLAlchemy row object to a Python dict?

Question

Is there a simple way to iterate over column name and value pairs?

My version of SQLAlchemy is 0.5.6

Here is the sample code where I tried using dict(row):

import sqlalchemy
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

print "sqlalchemy version:",sqlalchemy.__version__ 

engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()
users_table = Table('users', metadata,
     Column('id', Integer, primary_key=True),
     Column('name', String),
)
metadata.create_all(engine) 

class User(declarative_base()):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    
    def __init__(self, name):
        self.name = name

Session = sessionmaker(bind=engine)
session = Session()

user1 = User("anurag")
session.add(user1)
session.commit()

# uncommenting next line throws exception 'TypeError: 'User' object is not iterable'
#print dict(user1)
# this one also throws 'TypeError: 'User' object is not iterable'
for u in session.query(User).all():
    print dict(u)

Running this code on my system outputs:

Traceback (most recent call last):
  File "untitled-1.py", line 37, in <module>
    print dict(u)
TypeError: 'User' object is not iterable

Cowpuncher

The title of the question does not match the question itself. According to docs Result rows returned by Query that contain multiple ORM entities and/or column expressions make use of this class to return rows. where this class is sqlalchemy.util.KeyedTuple which is row object from the question's title. However query in the question uses model (mapped) class thus the type of row object is the model class instead of sqlalchemy.util.KeyedTuple. – Overrule

@PiotrDobrogost Question is from 2009 and mentions sqlalchemy version 0.5.6 – Cowpuncher

Answer 1

I couldn't get a good answer so I use this:

def row2dict(row):
    d = {}
    for column in row.__table__.columns:
        d[column.name] = str(getattr(row, column.name))

    return d

Edit: if above function is too long and not suited for some tastes here is a one liner (python 2.7+)

row2dict = lambda r: {c.name: str(getattr(r, c.name)) for c in r.__table__.columns}

Cowpuncher

More succinctly, return dict((col, getattr(row, col)) for col in row.__table__.columns.keys()). – Ketch

@Ketch yeah you may even do row2dict = lambda row: dict((col, getattr(row, col)) for col in row.__table__.columns.keys()) to make it a real one liner, but I prefer my code to be readable, horizontally short, vertically long – Cowpuncher

What if my Column isn't assigned to an attribute of the same name? IE, x = Column('y', Integer, primary_key=True) ? None of these solutions work in this case. – Phenica

Warning: __table__.columns.keys() won't work, because columns dictionary keys are not always strings (as getattr requires), but possibly all sorts of objects like sqlalchemy.sql.expression._truncated_label. Using c.name instead of c works for me. – Buonaparte

drdaeman is right, here is the correct snippet: return {c.name: getattr(self, c.name) for c in self.__table__.columns} – Aurum

row2dict() will leave out columns defined with a @hybrid_property. If you are using @hybrid_property to define columns you can use for column in dir(row) and exclude names starting with _ or __. – Doorframe

This answer makes an invalid assumption: column names don't necessarily match attribute names. – Priming

This worked perfectly for me better than the other solution I did remove the cast to sting though it was throwing errors. – Contrariwise

@RazerM's answer is much more correct because it returns the values as objects in the correct type (integer, decimals, datetimes, strings, null, etc.) whereas this answer returns the values as strings. – Nonperformance

this is cool I change to use this form def row2dict(row): d = {} for column in row.__table__.columns: d[str(column.name).replace(str(row.__table__), "").lower()] = \ getattr(row, str(column.name).replace(str(row.__table__) + ".", "").lower()) return d – Lunde

{c.expression.name: getattr(obj, c.key) for c in inspect(obj).mapper.column_attrs} seems betters, because it make the dict key as database column name. For case when the model key is not equils the database column name. – Glomerulonephritis

newer sqlalchemy allows to just do row.columns – Fashionable

Answer 2

You may access the internal __dict__ of a SQLAlchemy object, like the following:

for u in session.query(User).all():
    print u.__dict__

Thanet

Best answer in this thread, don't know why everyone else is proposing much more complicated solutions. – Krein

This gives an extra '_sa_instance_state' field, at least in version 0.7.9. – Shira

@LucianU what are the downsides of the extra _sa_instance_state field? – Reposeful

jberger, it's not a column name, so you have to remove it separately afterwards. – Shira

so this would be better: dictret = dict(row.__dict__); dictret.pop('_sa_instance_state', None) – Tetraploid

this seems not ideal since as people have pointed out __dict__ includes an _sa_instance_state entry which must then be removed. if you upgrade to a future version and other attributes are added you may have to go back and manually deal with them. if you want just column data (for example, to take a list of instances from a query and drop them in a pandas dataframe) then {col.name: getattr(self, col.name) for col in self.__table__.columns} as answered by Anurag Uniyal (with important corrections from comments to that answer) seems both more succinct and error-proof. – Molluscoid

just another suggestion like lyfings' one: one can also use {k: v for k, v in item.__dict__.iteritems() if not str(k).startswith("_")} or even more specific as lyfing commented, not only with starting underscores. ( >= Python2.7, for Python3 use .items()) – Vulgarism

I'm guessing this is no longer supported as I get: AttributeError: Could not locate column in row for column '__dict__' – Rhinarium

@ZuluDeltaNiner The 2018-03-25 update is unclear - what does it "interprets it as a reference to a column/field" mean? and can you specify which version the behavior was changed? I'm getting "object is not iterable" for the current version of SQLAlchemy 1.2.6 – Ancon

@AdamTerrey in SQLAlchemy 1.2.5, the following works: sel = self.table.select(); logs = sel.execute(); self.rows = [dict(r) for r in logs] (semicolons are line breaks). The fields of rows r can be accessed via several methods (see the sample code block here: docs.sqlalchemy.org/en/latest/core/…). By using u.__dict__, SQLAlchemy looks for a column called __dict__, if I'm reading the docs correctly. – Liquidation

yes, but session.query(User).all() doesn't return a ResultProxy of RowProxies in the same way as session.execute(..)- but just a simple list of instances of the User class. With regards to __dict__ this is a magic python value representing all the attributes of any class instance. E.g. it is possible to do class A(object): pass; a = A(); a.__dict__['blah'] = 123; assert a.blah == 123 – Ancon

This answer is wrong. The question even has dict(u) and correctly states that it throws a TypeError. – Priming

I've rolled back @ZuluDeltaNiner's edit since, as others noted here, it's wrong. The question (despite its misleading title) is asking how to get the values from an SQLAlchemy model instance as a dict, not the values from a RowProxy. Using .__dict__ works for that, and dict(...) raises an error, even as of v1.2.14 (the latest stable release). The edit was wrong for the case being asked about, was kind of incoherent (I'm not sure what either of the "it"s in there were meant to refer to), and wrongly implied that some relevant behaviour has changed between 2012 and now. – Vyatka

There is certainly still room for improving the state of this question, though - the title and the use of the word "row" in the question lends itself to precisely @ZuluDeltaNiner's misinterpretation of the question, and could do with fixing. Once that's done, the previous 5 comments in this thread will be obsolete and able to be safely flagged for deletion (as will this one). – Vyatka

I just created a query which only consists of func.<something>(...).label("something") type columns. This returns a collections.result type object, which has no __dict__ method (full list, surrounded by __: add, class, contains, delattr, doc, eq, format, ge, getattribute, getitem, getnewargs, getslice, gt, hash, init, iter, le, len, lt, module, mul, ne, new, reduce, reduce_ex, repr, rmul, setattr, sizeof, slots, str, subclasshook and _asdict, _fields, _real_fields, count). – Pastorship

@Gocht I think that this answer only applies to a query (with filter, etc.) from a session object, while .execute() from create_engine is different. – Whimsicality

All of this is still insane. Why can't SQLAlchemy results just be serialized with jsonify? – Mata

You shouldn't try to access __dict__ directly. – Interjacent

How would you implement this solution with flask.json.jsonify()? – Popper

I have been searching for a way to make a dict out of a model for days. This is the best answer, full stop. Still applicable in Jan 2021 with SQLAlchemy 1.3.x – Head

Note that even though it gives you a list of dicts, SqlAlchemy does a lot of extra work here: it constructs a lot of ORM objects that you don't really need. This is going to be really slow when objects are many. – Stubble

In my case, SQLalchemy instance u.__dict__ will NOT return content of any field unless you access the field explicitly in advance. – Humorous

Not working with collections.result object :/ – Unmeant

When I try this, I get TypeError: Object of type InstanceState is not JSON serializable. What am I doing wrong? – Wally

Answer 3

As per @zzzeek in comments:

note that this is the correct answer for modern versions of SQLAlchemy, assuming "row" is a core row object, not an ORM-mapped instance.

for row in resultproxy:
    row_as_dict = row._mapping  # SQLAlchemy 1.4 and greater
    # row_as_dict = dict(row)  # SQLAlchemy 1.3 and earlier

background on row._mapping, new as of SQLAlchemy 1.4: https://docs.sqlalchemy.org/en/stable/core/connections.html#sqlalchemy.engine.Row._mapping

Yasmeen

It says 'XXX object is not iterable', I am using 0.5.6, i get by session.query(Klass).filter().all() – Cowpuncher

@Anurag Uniyal: Please update your question with (a) the smallest piece of code that shows the problem and (b) the real error traceback. It's very hard to do this through comments and summaries of the code. – Khajeh

Please see the updated code, I am using same example given in the links you have mentioned, I also see no use of dict there? – Cowpuncher

note that this is the correct answer for modern versions of SQLAlchemy, assuming "row" is a core row object, not an ORM-mapped instance. – Wsan

Also note that zzzeek is the creator of sqlalchemy. – Trenna

Does anyone know why this works? RowProxy does not inherit from collections.Mapping, dict, collections.OrderedDict() or collections.UserDict(). And none of the descriptions I found for the dict constructor seem to fit. – Yawn

Can anyone elaborate on this one? I'm a noob an don't get this. – Racine

@Yawn you are right that it doesn't inherit from those types, but it does implement __getitem__ and keys amongst other methods, so it quacks like a mapping. github.com/sqlalchemy/sqlalchemy/blob/… – Donovan

Yes, that's correct. docs.python.org/3.7/glossary.html#term-mapping – Yawn

What is the difference between a core row object versus an ORM-mapped instance? This doesn't work for me on the rows from of query(MyModel).all(): MyModel object is not iterable. – Dialogue

I have tired to do this, but when you have many fields with the same name you will get strange results. for example when joining two tables in a raw query like "Select a.*, b.* from A a, B b" – Prow

This answer is unhelpful as you're not outlining how or what is "resultproxy"? – Counterchange

resultproxy is just a stand-in variable name (a proxy) for whatever your results variable is. Replace it with your appropriate query results variable. – Unthrone

Note that row._mapping returns a RowMapping not a dict. You need to cast it to dict if you want to add keys or do other dict operations. – Palladin

this should be marked as the correct answer given that we are in 2022, SQLAlchemy 1.4 is mature and soon we will have 2.0 – Dygall

@Wsan Python convention is not to rely on "_anything" as its hands off. Why is this the correct thing to do in SQLAlchemy ? – Ketone

The answer is good, but also for version 1.4 you need to use the suggested 1.3 approach, as per documentation is returns "RowMapping for this Row". – Spense

Others were asking how to get the "resultproxy". I used this kind of code: resultproxy = session.get_bind().execute(text("select * from table")).all() which returns a list of RowMapping object, which if you make them dict() are proper Python dictionaries. So, code like ``` lang-js all_dict_records = [] for record in resultproxy: record_dict = dict(record._mapping) all_dict_records.append(record_dict)``` will get you a list of dictionaries from full result set. Then you can use json or process it.. – Spense

Answer 4

In SQLAlchemy v0.8 and newer, use the inspection system.

from sqlalchemy import inspect

def object_as_dict(obj):
    return {
        c.key: getattr(obj, c.key)
        for c in inspect(obj).mapper.column_attrs
    }

user = session.query(User).first()

d = object_as_dict(user)

Note that .key is the attribute name, which can be different from the column name, e.g. in the following case:

class_ = Column('class', Text)

This method also works for column_property.

Priming

@DukeDougal I think this works from v0.8 (when the inspection system was added). – Priming

This doesn't take into account deferred columns – Armin

@Armin It's not clear to me that they should be excluded by default. Nevertheless, you can check that the keys aren't in sqlalchemy.inspect(obj).unloaded – Priming

While I won't use this for the results of query, inspect was very useful when using insert_many and I wanted to return the inserted entities (including any generated cols such as ids) – Circassia

Good, but doesn't include attributes that are defined as relationships to other tables – Kaluga

Answer 5

rows have an _asdict() function which gives a dict

In [8]: r1 = db.session.query(Topic.name).first()

In [9]: r1
Out[9]: (u'blah')

In [10]: r1.name
Out[10]: u'blah'

In [11]: r1._asdict()
Out[11]: {'name': u'blah'}

Rodenhouse

It is supposed to be private and not could possibly be removed/changed in future versions. – Rodenhouse

@Rodenhouse It is quite well documented and as such not quite private. Though a leading underscore has that meaning in Python in general, here it is probably used in order to not clash with possible tuple keys. – Guinness

This only works with KeyedTuple s, which are only returned when querying specific fields of a row. ie .query(Topic.name) returns a KeyedTuple, while .query(Topic) returns a Topic, which does not have ._asdict() - Derp. just saw STBs answer below. – Peepul

KeyedTuple has been replaced with engine.Row in 1.4 – Teahouse

_asdict() solved my problem – Mich

Answer 6

Assuming the following functions will be added to the class User the following will return all key-value pairs of all columns:

def columns_to_dict(self):
    dict_ = {}
    for key in self.__mapper__.c.keys():
        dict_[key] = getattr(self, key)
    return dict_

unlike the other answers all but only those attributes of the object are returned which are Column attributes at class level of the object. Therefore no _sa_instance_state or any other attribute SQLalchemy or you add to the object are included. Reference

EDIT: Forget to say, that this also works on inherited Columns.

hybrid_property extention

If you also want to include hybrid_property attributes the following will work:

from sqlalchemy import inspect
from sqlalchemy.ext.hybrid import hybrid_property

def publics_to_dict(self) -> {}:
    dict_ = {}
    for key in self.__mapper__.c.keys():
        if not key.startswith('_'):
            dict_[key] = getattr(self, key)

    for key, prop in inspect(self.__class__).all_orm_descriptors.items():
        if isinstance(prop, hybrid_property):
            dict_[key] = getattr(self, key)
    return dict_

I assume here that you mark Columns with an beginning _ to indicate that you want to hide them, either because you access the attribute by an hybrid_property or you simply do not want to show them. Reference

Tipp all_orm_descriptors also returns hybrid_method and AssociationProxy if you also want to include them.

Remarks to other answers

Every answer (like 1, 2 ) which based on the __dict__ attribute simply returns all attributes of the object. This could be much more attributes then you want. Like I sad this includes _sa_instance_state or any other attribute you define on this object.

Every answer (like 1, 2 ) which is based on the dict() function only works on SQLalchemy row objects returned by session.execute() not on the classes you define to work with, like the class User from the question.

The solving answer which is based on row.__table__.columns will definitely not work. row.__table__.columns contains the column names of the SQL Database. These can only be equal to the attributes name of the python object. If not you get an AttributeError. For answers (like 1, 2 ) based on class_mapper(obj.__class__).mapped_table.c it is the same.

Choking

Perfect for adding a simple method to make models easily JSON serializable – Parterre

Answer 7

as @balki mentioned:

The _asdict() method can be used if you're querying a specific field because it is returned as a KeyedTuple.

In [1]: foo = db.session.query(Topic.name).first()
In [2]: foo._asdict()
Out[2]: {'name': u'blah'}

Whereas, if you do not specify a column you can use one of the other proposed methods - such as the one provided by @charlax. Note that this method is only valid for 2.7+.

In [1]: foo = db.session.query(Topic).first()
In [2]: {x.name: getattr(foo, x.name) for x in foo.__table__.columns}
Out[2]: {'name': u'blah'}

Countess

If the python ORM class attributes have different names from the database columns, try this solution: #27947794 – Cathycathyleen

actually, a better solution for all cases is provided by the sqlalchemy author at https://mcmap.net/q/81911/-when-the-sqlalchemy-orm-class-attributes-are-different-from-database-columns-how-to-get-a-list-of-the-orm-class-attributes – Cathycathyleen

When I try this I get ResultProxy object has no attribute '_asdict' – A

@slashdottir, are you executing your query object (calling the .first() method)? – Countess

This answer makes an invalid assumption: column names don't necessarily match attribute names – see RazerM's answer. – Overrule

@Piotr Dobrogost – Countess

True! However I've found this to be more useful in my experiences. – Countess

Answer 8

Old question, but since this the first result for "sqlalchemy row to dict" in Google it deserves a better answer.

The RowProxy object that SqlAlchemy returns has the items() method: http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.RowProxy.items

It simply returns a list of (key, value) tuples. So one can convert a row to dict using the following:

In Python <= 2.6:

rows = conn.execute(query)
list_of_dicts = [dict((key, value) for key, value in row.items()) for row in rows]

In Python >= 2.7:

rows = conn.execute(query)
list_of_dicts = [{key: value for (key, value) in row.items()} for row in rows]

Tripedal

You can just do list_of_dicts = [dict(row.items()) for row in rows] – Tarsometatarsus

One snag is that the column names that SQLAlchemy uses in a result set are table_name_column_name, if you want different names (eg. just column_name), use the .label method. session.query( MyTable.column_name.label('column_name'), ... ) – Lunular

Hi I am getting this issue pls help me * datetime.datetime(2018, 11, 24, 18, 52, 50) is not JSON serializable * – Majesty

It seems that Row.items() disappeared in SQLAlchemy 1.4. If you were using it in SQLAlchemy 1.3, you will need to change to dict(row).items() – Colombo

Answer 9

A very simple solution is row._asdict():

data = session.query(Table).all()
[row._asdict() for row in data]

References:

• sqlalchemy.engine.Row._asdict() (v1.4)
• sqlalchemy.util.KeyedTuple._asdict() (v1.3)

Deeprooted

At this moment it is not in the docs. Maybe it is deprecated. – Acklin

I've added links to the 1.4 and 1.3 docs. – Deeprooted

Confirm this works in 1.4. This should be the accepted answer IMHO – Lumper

Important: the Table mentioned here is not a Model (a class), but rather a Table instance (that can be accessed as Model.__table__. The model (the most common way of declaring tables) does not have this attribute. – Factual

Thanks for this note @Factual so in the example above the query would then be: data = session.query(Model.__table__).all() and then each instance will have the _asdict attribute – Linea

Answer 10

with sqlalchemy 1.4

session.execute(select(User.id, User.username)).mappings().all()
>> [{'id': 1, 'username': 'Bob'}, {'id': 2, 'username': 'Alice'}]

Berberidaceous

fyi for the unwary: you need to use select 2.0 query rather than session.query (at least for my use case), or this still not work. – Fanatic

this works great if you're selecting specific columns. if you run something like session.execute(select(User)).mappings().all() to select the whole object you get this result: [{'User': User(id=1, username='Bob')}, {'User': User(id=2, username='Alice')}] – Enneastyle

however you can do session.execute(select('*').select_from(User)).mappings().all() and it will give you [{'id': 1, 'username': 'Bob'}, {'id': 2, 'username': 'Alice'}]. or if you filter the results, for example: session.execute(select('*').filter(User.id.in_([1, 2]))).mappings().all(), should get you the same result. just need a way to specify which table you're selecting '*' from. – Enneastyle

This only works if you select specific columns. – Cuspidor

Answer 11

Following @balki answer, since SQLAlchemy 0.8 you can use _asdict(), available for KeyedTuple objects. This renders a pretty straightforward answer to the original question. Just, change in your example the last two lines (the for loop) for this one:

for u in session.query(User).all():
   print u._asdict()

This works because in the above code u is an object of type class KeyedTuple, since .all() returns a list of KeyedTuple. Therefore it has the method _asdict(), which nicely returns u as a dictionary.

WRT the answer by @STB: AFAIK, anything that .all() returns is a list of KeypedTuple. Therefore, the above works either if you specify a column or not, as long as you are dealing with the result of .all() as applied to a Query object.

Barmecidal

This may have been true in the past, but on SQLAlchemy v1.0 .all() returns a list of User instances, so this doesn't work. – Priming

@RazerM, sorry, but I don't understand what you mean. The for loop should precisely loop through the list of User instances, converting them (u) to dictionaries, and then printing them... – Barmecidal

User instances don't have an _asdict method. See gist.github.com/RazerM/2eff51571b3c70e8aeecd303c2a2bc8d – Priming

Now I got it. Thanks. Instead of KeyedTuple, now .all() returns User objects. So the problem for v1.0 (and up, I assume) is how to get a dictionary out of a User object. Thanks for the clarification. – Barmecidal

Answer 12

from sqlalchemy.orm import class_mapper

def asdict(obj):
    return dict((col.name, getattr(obj, col.name))
                for col in class_mapper(obj.__class__).mapped_table.c)

Furrow

Be aware of the difference between local_table and mapped_table. For example, if you apply some sort of table inheritance in your db (tbl_employees > tbl_managers, tbl_employees > tbl_staff), your mapped classes will need to reflect this (Manager(Employee), Staff(Employee)). mapped_table.c will give you the column names of both the base table and the inheriting table. local_table only gives you the name of your (inheriting) table. – Parlour

This avoids giving the '_sa_instance_state' field, at least in version 0.8+. – Compiler

This answer makes an invalid assumption: column names don't necessarily match attribute names. – Priming

Answer 13

Refer to Alex Brasetvik's Answer, you can use one line of code to solve the problem

row_as_dict = [dict(row) for row in resultproxy]

Under the comment section of Alex Brasetvik's Answer, zzzeek the creator of SQLAlchemy stated this is the "Correct Method" for the problem.

Miracle

@Greenonline Sure, the approval comment is under the Alex Brasetvik's answer. Edited to added link to his answer – Miracle

What is the resultproxy ? – Racine

Answer 14

With python 3.8+, we can do this with dataclass, and the asdict method that comes with it:

from dataclasses import dataclass, asdict

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, String, Integer, create_engine

Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=False)


@dataclass
class User(Base):
    __tablename__ = 'users'

    id: int = Column(Integer, primary_key=True)
    name: str = Column(String)
    email = Column(String)

    def __init__(self, name):
        self.name = name
        self.email = '[email protected]'


Base.metadata.create_all(engine)

SessionMaker = sessionmaker(bind=engine)
session = SessionMaker()

user1 = User("anurag")
session.add(user1)
session.commit()

query_result = session.query(User).one()  # type: User
print(f'{query_result.id=:}, {query_result.name=:}, {query_result.email=:}')
# query_result.id=1, query_result.name=anurag, [email protected]

query_result_dict = asdict(query_result)
print(query_result_dict)
# {'id': 1, 'name': 'anurag'}

The key is to use the @dataclass decorator, and annotate each column with its type (the : str part of the name: str = Column(String) line).

Also note that since the email is not annotated, it is not included in query_result_dict.

Applause

On Python3.7 I get "NameError: name 'asdict' is not defined" – Counterchange

My bad! It's a function added in python 3.8. Fixed my answer. – Applause

So pythonic. 3.8 is awesome. But you don't really need the init method do you? declarative and dataclass both provide generic init methods. – Miseno

@JeffLaughlin It's not needed, but I was just being loyal to OP's code, and also wanted to provide a way to add default value to email field. – Applause

I don't think this is the correct way to use dataclasses with sqlalchemy. See: docs.sqlalchemy.org/en/20/orm/… But using dataclasses.asdict is indeed the best way to do it. – Maleficence

Answer 15

I've found this post because I was looking for a way to convert a SQLAlchemy row into a dict. I'm using SqlSoup... but the answer was built by myself, so, if it could helps someone here's my two cents:

a = db.execute('select * from acquisizioni_motes')
b = a.fetchall()
c = b[0]

# and now, finally...
dict(zip(c.keys(), c.values()))

Logotype

or, if you prefer..: [ dict(zip(i.keys(), i.values())) for i in b ] – Logotype

This is the only syntax I've found that actually works! I've been trying stuff for over an hour. – A

For core selects, the RowProxy (c in this answer) adheres to the mapping protocol, so you can just call dict(c). – Priming

Answer 16

You could try to do it in this way.

for u in session.query(User).all():
    print(u._asdict())

It use a built-in method in the query object that return a dictonary object of the query object.

references: https://docs.sqlalchemy.org/en/latest/orm/query.html

Fayefayette

Add some more explaining maybe? – Lurcher

Nothing really more to explain. It's a built-in method on the result object. So whether you do this for all results, or a single row, there is a built-in _asdict() method that essentially zips the field names with field values and returns the result as a dictionary. – Saturnalia

Very concise and I wish it worked but u in my case is a string, and I get error ``Model' object has no attribute '_asdict'` @Thanet below worked for me – Latini

Answer 17

The expression you are iterating through evaluates to list of model objects, not rows. So the following is correct usage of them:

for u in session.query(User).all():
    print u.id, u.name

Do you realy need to convert them to dicts? Sure, there is a lot of ways, but then you don't need ORM part of SQLAlchemy:

result = session.execute(User.__table__.select())
for row in result:
    print dict(row)

Update: Take a look at sqlalchemy.orm.attributes module. It has a set of functions to work with object state, that might be useful for you, especially instance_dict().

Salomo

I want to convert them to dict to, because some other code needs data as dict, and i want a generic way because I will not know what columns a model object have – Cowpuncher

and when I get handle to them I have access to model objects only so i can't use session.execute etc – Cowpuncher

Answer 18

I've just been dealing with this issue for a few minutes. The answer marked as correct doesn't respect the type of the fields. Solution comes from dictalchemy adding some interesting fetures. https://pythonhosted.org/dictalchemy/ I've just tested it and works fine.

Base = declarative_base(cls=DictableModel)

session.query(User).asdict()
{'id': 1, 'username': 'Gerald'}

session.query(User).asdict(exclude=['id'])
{'username': 'Gerald'}

Sokoto

This should be the new best sollution. How lucky that I checked every answer found this one! No more '_sa_instance_state' to be delt with. – Tenorio

Answer 19

class User(object):
    def to_dict(self):
        return dict([(k, getattr(self, k)) for k in self.__dict__.keys() if not k.startswith("_")])

That should work.

Turnstile

what happens if column name starts with "_" ? – Cowpuncher

I would imagine that you really shouldn't name your columns with a leading underscore. If you do, it won't work. If it's just the odd one, that you know about, you could modify it to add those columns. – Turnstile

Answer 20

You can convert sqlalchemy object to dictionary like this and return it as json/dictionary.

Helper functions:

import json
from collections import OrderedDict


def asdict(self):
    result = OrderedDict()
    for key in self.__mapper__.c.keys():
        if getattr(self, key) is not None:
            result[key] = str(getattr(self, key))
        else:
            result[key] = getattr(self, key)
    return result


def to_array(all_vendors):
    v = [ ven.asdict() for ven in all_vendors ]
    return json.dumps(v) 

Driver Function:

def all_products():
    all_products = Products.query.all()
    return to_array(all_products)

Dewees

Answer 21

Two ways:

1.

for row in session.execute(session.query(User).statement):
    print(dict(row))

2.

selected_columns = User.__table__.columns
rows = session.query(User).with_entities(*selected_columns).all()
for row in rows :
    print(row._asdict())

Voice

Answer 22

Here is how Elixir does it. The value of this solution is that it allows recursively including the dictionary representation of relations.

def to_dict(self, deep={}, exclude=[]):
    """Generate a JSON-style nested dict/list structure from an object."""
    col_prop_names = [p.key for p in self.mapper.iterate_properties \
                                  if isinstance(p, ColumnProperty)]
    data = dict([(name, getattr(self, name))
                 for name in col_prop_names if name not in exclude])
    for rname, rdeep in deep.iteritems():
        dbdata = getattr(self, rname)
        #FIXME: use attribute names (ie coltoprop) instead of column names
        fks = self.mapper.get_property(rname).remote_side
        exclude = [c.name for c in fks]
        if dbdata is None:
            data[rname] = None
        elif isinstance(dbdata, list):
            data[rname] = [o.to_dict(rdeep, exclude) for o in dbdata]
        else:
            data[rname] = dbdata.to_dict(rdeep, exclude)
    return data

Ketch

Link is dead. Next time please copy the relevant code here as well. – Antihelix

Will do next time. If I remember correctly, the function was quite long. – Ketch

Answer 23

With this code you can also to add to your query "filter" or "join" and this work!

query = session.query(User)
def query_to_dict(query):
        def _create_dict(r):
            return {c.get('name'): getattr(r, c.get('name')) for c in query.column_descriptions}

    return [_create_dict(r) for r in query]

Codel

Answer 24

For the sake of everyone and myself, here is how I use it:

def run_sql(conn_String):
  output_connection = engine.create_engine(conn_string, poolclass=NullPool).connect()
  rows = output_connection.execute('select * from db1.t1').fetchall()  
  return [dict(row) for row in rows]

Subastral

Answer 25

To complete @Anurag Uniyal 's answer, here is a method that will recursively follow relationships:

from sqlalchemy.inspection import inspect

def to_dict(obj, with_relationships=True):
    d = {}
    for column in obj.__table__.columns:
        if with_relationships and len(column.foreign_keys) > 0:
             # Skip foreign keys
            continue
        d[column.name] = getattr(obj, column.name)

    if with_relationships:
        for relationship in inspect(type(obj)).relationships:
            val = getattr(obj, relationship.key)
            d[relationship.key] = to_dict(val) if val else None
    return d

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    first_name = Column(TEXT)
    address_id = Column(Integer, ForeignKey('addresses.id')
    address = relationship('Address')

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    city = Column(TEXT)


user = User(first_name='Nathan', address=Address(city='Lyon'))
# Add and commit user to session to create ids

to_dict(user)
# {'id': 1, 'first_name': 'Nathan', 'address': {'city': 'Lyon'}}
to_dict(user, with_relationship=False)
# {'id': 1, 'first_name': 'Nathan', 'address_id': 1}

Pome

in case the default for 'with_relationships' is changed to false, better pass this value through to the recursive call. ie: d[relationship.key] = to_dict(val,with_relationships) if val else None – Lowspirited

how can I achieve the result, if I want to join the user and address table based upon address_id column and fetch all the column from user table and only id column from address table. – Theocrasy

Answer 26

from copy import copy

def to_record(row):
    record = copy(row.__dict__)
    del record["_sa_instance_state"]
    return record

If not using copy, you might run into errors.

Deb

Answer 27

As OP stated, calling the dict initializer raises an exception with the message "User" object is not iterable. So the real question is how to make a SQLAlchemy Model iterable?

We'll have to implement the special methods __iter__ and __next__, but if we inherit directly from the declarative_base model, we would still run into the undesirable "_sa_instance_state" key. What's worse, is we would have to loop through __dict__.keys() for every call to __next__ because the keys() method returns a View -- an iterable that is not indexed. This would increase the time complexity by a factor of N, where N is the number of keys in __dict__. Generating the dict would cost O(N^2). We can do better.

We can implement our own Base class that implements the required special methods and stores a list of of the column names that can be accessed by index, reducing the time complexity of generating the dict to O(N). This has the added benefit that we can define the logic once and inherit from our Base class anytime we want our model class to be iterable.

class IterableBase(declarative_base()):
    __abstract__ = True

    def _init_keys(self):
        self._keys = [c.name for c in self.__table__.columns]
        self._dict = {c.name: getattr(self, c.name) for c in self.__table__.columns}

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self._init_keys()

    def __setattr__(self, name, value):
        super().__setattr__(name, value)
        if name not in ('_dict', '_keys', '_n') and '_dict' in self.__dict__:
            self._dict[name] = value

    def __iter__(self):
        self._n = 0
        return self

    def __next__(self):
        if self._n >= len(self._keys):
            raise StopIteration
        self._n += 1
        key = self._keys[self._n-1]
        return (key, self._dict[key])

Now the User class can inherit directly from our IterableBase class.

class User(IterableBase):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

You can confirm that calling the dict function with a User instance as an argument returns the desired dictionary, sans "_sa_instance_state". You may have noticed the __setattr__ method that was declared in the IterableBase class. This ensures the _dict is updated when attributes are mutated or set after initialization.

def main():
    user1 = User('Bob')
    print(dict(user1))
    # outputs {'id': None, 'name': 'Bob'}
    user1.id = 42
    print(dict(user1))
    # outputs {'id': 42, 'name': 'Bob'}

if __name__ == '__main__':
    main()

Freehold

This is the working version of April 2022. Using dictalchemy was my preferred method but it's unmaintained since 2015. – Binny

Answer 28

I have a variation on Marco Mariani's answer, expressed as a decorator. The main difference is that it'll handle lists of entities, as well as safely ignoring some other types of return values (which is very useful when writing tests using mocks):

@decorator
def to_dict(f, *args, **kwargs):
  result = f(*args, **kwargs)
  if is_iterable(result) and not is_dict(result):
    return map(asdict, result)

  return asdict(result)

def asdict(obj):
  return dict((col.name, getattr(obj, col.name))
              for col in class_mapper(obj.__class__).mapped_table.c)

def is_dict(obj):
  return isinstance(obj, dict)

def is_iterable(obj):
  return True if getattr(obj, '__iter__', False) else False

Winters

Answer 29

Return the contents of this :class:.KeyedTuple as a dictionary

In [46]: result = aggregate_events[0]

In [47]: type(result)
Out[47]: sqlalchemy.util._collections.result

In [48]: def to_dict(query_result=None):
    ...:     cover_dict = {key: getattr(query_result, key) for key in query_result.keys()}
    ...:     return cover_dict
    ...: 
    ...:     

In [49]: to_dict(result)
Out[49]: 
{'calculate_avg': None,
 'calculate_max': None,
 'calculate_min': None,
 'calculate_sum': None,
 'dataPointIntID': 6,
 'data_avg': 10.0,
 'data_max': 10.0,
 'data_min': 10.0,
 'data_sum': 60.0,
 'deviceID': u'asas',
 'productID': u'U7qUDa',
 'tenantID': u'CvdQcYzUM'}

Dyslalia

Answer 30

We can get a list of object in dict:

def queryset_to_dict(query_result):
   query_columns = query_result[0].keys()
   res = [list(ele) for ele in query_result]
   dict_list = [dict(zip(query_columns, l)) for l in res]
   return dict_list

query_result = db.session.query(LanguageMaster).all()
dictvalue=queryset_to_dict(query_result)

Maharajah

Answer 31

An improved version of Anurag Uniyal's version, which takes into account types:

def sa_vars(row):
    return {
        column.name: column.type.python_type(getattr(row, column.name))
        for column in row.__table__.columns
    }

Jaddan

Answer 32

I am a newly minted Python programmer and ran into problems getting to JSON with Joined tables. Using information from the answers here I built a function to return reasonable results to JSON where the table names are included avoiding having to alias, or have fields collide.

Simply pass the result of a session query:

test = Session().query(VMInfo, Customer).join(Customer).order_by(VMInfo.vm_name).limit(50).offset(10)

json = sqlAl2json(test)

def sqlAl2json(self, result):
    arr = []
    for rs in result.all():
        proc = []
        try:
            iterator = iter(rs)
        except TypeError:
            proc.append(rs)
        else:
            for t in rs:
                proc.append(t)

        dict = {}
        for p in proc:
            tname = type(p).__name__
            for d in dir(p):
                if d.startswith('_') | d.startswith('metadata'):
                    pass
                else:
                    key = '%s_%s' %(tname, d)
                    dict[key] = getattr(p, d)
        arr.append(dict)
    return json.dumps(arr)

January

Answer 33

if your models table column is not equie mysql column.

such as :

class People:
    id: int = Column(name='id', type_=Integer, primary_key=True)
    createdTime: datetime = Column(name='create_time', type_=TIMESTAMP,
                               nullable=False,
                               server_default=text("CURRENT_TIMESTAMP"),
                               default=func.now())
    modifiedTime: datetime = Column(name='modify_time', type_=TIMESTAMP,
                                server_default=text("CURRENT_TIMESTAMP"),
                                default=func.now())

Need to use:

 from sqlalchemy.orm import class_mapper 
 def asDict(self):
        return {x.key: getattr(self, x.key, None) for x in
            class_mapper(Application).iterate_properties}

if you use this way you can get modify_time and create_time both are None

{'id': 1, 'create_time': None, 'modify_time': None}


    def to_dict(self):
        return {c.name: getattr(self, c.name, None)
         for c in self.__table__.columns}

Because Class Attributes name not equal with column store in mysql

Jenisejenkel

Answer 34

def to_dict(row):
    return {column.name: getattr(row, row.__mapper__.get_property_by_column(column).key) for column in row.__table__.columns}


for u in session.query(User).all():
    print(to_dict(u))

This function might help. I can't find better solution to solve problem when attribute name is different then column names.

Jodi

Answer 35

You'll need it everywhere in your project, I apriciate @anurag answered it works fine. till this point I was using it, but it'll mess all your code and also wont work with entity change.

Rather try this, inherit your base query class in SQLAlchemy

from flask_sqlalchemy import SQLAlchemy, BaseQuery


class Query(BaseQuery):
    def as_dict(self):
        context = self._compile_context()
        context.statement.use_labels = False
        columns = [column.name for column in context.statement.columns]

        return list(map(lambda row: dict(zip(columns, row)), self.all()))


db = SQLAlchemy(query_class=Query)

after that wherever you'll define your object "as_dict" method will be there.

Photosynthesis

Answer 36

use dict Comprehensions

for u in session.query(User).all():
    print ({column.name: str(getattr(row, column.name)) for column in row.__table__.columns})

Clowers

Please refrain from code-only answers - Take the time to post a little explanation as to how your code works. – Factional

Answer 37

After querying the database using following SQLAlchemy code:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


SQLALCHEMY_DATABASE_URL = 'sqlite:///./examples/sql_app.db'
engine = create_engine(SQLALCHEMY_DATABASE_URL, echo=True)
query = sqlalchemy.select(TABLE)
result = engine.execute(query).fetchall()

You can use this one-liner:

query_dict = [record._mapping for record in results]

Leal

This answer had been posted 13 years before yours. – Diesel

@Diesel woke up and chose violence lol – Monteux

Answer 38

sqlalchemy-utils has get_columns to help with this.

You could write:

{column: getattr(row, column) for column in get_columns(row)}

Disastrous

Answer 39

SQlAlchemy 2.x supports dataclasses. Based on the example of the original documentation:

from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import MappedAsDataclass


class Base(MappedAsDataclass, DeclarativeBase):
    """subclasses will be converted to dataclasses"""


class User(Base):
    __tablename__ = "user_account"

    id: Mapped[int] = mapped_column(init=False, primary_key=True)
    name: Mapped[str]

you can apply dataclasses.asdict to instances of User:

import dataclasses

print(dataclasses.asdict(User(id=1,name='foo')))
# {'id': 1,'name': 'foo'}

Bill

Answer 40

In latest versions of SQL Alchemy 2.0 if you are using declarative mapping you can use __dict__ directly on the query results.

        with Session() as session:
          statement = select(User).filter_by({"id": 1})
          result = session.scalars(statement).one()

          dict_result = result.__dict__

In latest versions of SQL Alchemy 2.0 with SQLModel library I think you can do like that:

        with Session() as session:
          statement = select(User).filter_by({"id": 1})
          result = session.scalars(statement).one()

          dict_result = result.model_dump()

Cranial

I think model_dump is a pydantic feature, not SQLAlchemy. – Barilla

Oh you're right! I was using an additional library on my example. I updated my post accordingly – Cranial

Answer 41

Here is a super simple way of doing it

row2dict = lambda r: dict(r.items())

Vivisection

Answer 42

In most scenarios, column name is fit for them. But maybe you write the code like follows:

class UserModel(BaseModel):
    user_id = Column("user_id", INT, primary_key=True)
    email = Column("user_email", STRING)

the column.name "user_email" while the field name is "email", the column.name could not work well as before.

sqlalchemy_base_model.py

also i write the answer here

Latty

Answer 43

A solution that works with inherited classes too:

from itertools import chain
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()


class Mixin(object):
    def as_dict(self):
        tables = [base.__table__ for base in self.__class__.__bases__ if base not in [Base, Mixin]]
        tables.append(self.__table__)
        return {c.name: getattr(self, c.name) for c in chain.from_iterable([x.columns for x in tables])}

Furlani

My answer using the inspection system already works for table inheritance without having to go through all that trouble – Priming

Yes but it actually works and answer the question, so why a downvote ? – Furlani

You're also using the column name which can be different from the attribute name. – Priming

There could be other, non declarative bases without __table__ attribute and this code does not account for such. That's what inspect module is for. – Overrule

Answer 44

I don't have much experience with this, but the following seems to work for what I'm doing:

dict(row)

This seems too simple (compared to the other answers here). What am I missing?

Leralerch

If you're claming that your simple answer works, you should provide evidence. I think you're using a core select; this question is about ORM queries. – Priming

Yes. I had not noticed this question is about ORM. I am using core. – Leralerch

I was trying to figure out how to work with a RowProxy, and was frustrated by the SQLAlchemy documentation that says 'Mostly follows “ordered dictionary” behavior'. I couldn't find what 'Mostly' means, but I found I couldn't set values, like row['ultimate answer'] = 42 Just using dict() doesn't preserve the order of the columns, but the following seems to: d = OrderedDict() # Add each item in order. for k, v in row.items(): d.update({k: v}) – Leralerch

Answer 45

Python 3.6.8+

The builtin str() method automatically converts datetime.datetime objects to iso-8806-1.

print(json.dumps([dict(row.items()) for row in rows], default=str, indent="  "))

NOTE: The default func will only be applied to a value if there's an error so int and float values won't be converted... unless there's an error :).

Bipinnate

Answer 46

My take utilizing (too many?) dictionaries:

def serialize(_query):
#d = dictionary written to per row
#D = dictionary d is written to each time, then reset
#Master = dictionary of dictionaries; the id Key (int, unique from database) from D is used as the Key for the dictionary D entry in Master
Master = {}
D = {}
x = 0
for u in _query:
    d = u.__dict__
    D = {}
    for n in d.keys():
        if n != '_sa_instance_state':
            D[n] = d[n]
    x = d['id']
    Master[x] = D
return Master

Running with flask (including jsonify) and flask_sqlalchemy to print outputs as JSON.

Call the function with jsonify(serialize()).

Works with all SQLAlchemy queries I've tried so far (running SQLite3)

Mistral

There's no need to rely on the class dictionary, attempting to filter out non-columns manually when the SQLAlchemy inspection system exists. – Priming