Convert sqlalchemy row object to 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   but it throws exception   TypeError   User  object is not iterable  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   sqlalchemy version  0 5 6 Traceback  most recent call last     File  untitled-1 py   line 37  in  lt module gt      print dict u  TypeError   User  object is not iterable

User · Answer

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

User · Answer

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

User · Answer

You may access the internal   dict   of a SQLAlchemy object  like the following    for u in session query User  all        print u   dict

User · Answer

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

User · Answer

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

User · Answer

Here is a super simple way of doing it  row2dict   lambda r  dict r items

User · Answer

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

User · Answer

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

User · Answer

The 1 3 docs offer a very simple solution  KeyedTuple  asdict   def to array rows       return  r  asdict   for r in rows   def query        data   session query Table  all       return to array data

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 propery 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  - gt          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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt   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

User · Answer

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   dict row

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt  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

User · Answer

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    hello example com    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  query result email hello example com  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[python] Convert sqlalchemy row object to python dict

Examples related to python

Examples related to sqlalchemy