Efficiently updating database using SQLAlchemy ORM

Question

I m starting a new application and looking at using an ORM -- in particular  SQLAlchemy   Say I ve got a column  foo  in my database and I want to increment it   In straight sqlite  this is easy   db   sqlite3 connect  mydata sqlitedb   cur   db cursor   cur execute  update table stuff set foo   foo   1     I figured out the SQLAlchemy SQL-builder equivalent   engine   sqlalchemy create engine  sqlite    mydata sqlitedb   md   sqlalchemy MetaData engine  table   sqlalchemy Table  stuff   md  autoload True  upd   table update values  table c foo table c foo 1   engine execute upd    This is slightly slower  but there s not much in it   Here s my best guess for a SQLAlchemy ORM approach     snip definition of Stuff class made using declarative base   snip creation of session object for c in session query Stuff       c foo   c foo   1 session flush   session commit     This does the right thing  but it takes just under fifty times as long as the other two approaches   I presume that s because it has to bring all the data into memory before it can work with it   Is there any way to generate the efficient SQL using SQLAlchemy s ORM   Or using any other python ORM   Or should I just go back to writing the SQL by hand

User · Answer

There are several ways to UPDATE using sqlalchemy  1  for c in session query Stuff  all           c foo    1    session commit    2  session query            update   foo    Stuff foo   1       session commit    3  conn   engine connect      stmt   Stuff update            values Stuff foo    Stuff foo   1      conn execute stmt

User · Answer

SQLAlchemy s ORM is meant to be used together with the SQL layer  not hide it  But you do have to keep one or two things in mind when using the ORM and plain SQL in the same transaction  Basically  from one side  ORM data modifications will only hit the database when you flush the changes from your session  From the other side  SQL data manipulation statements don t affect the objects that are in your session   So if you say  for c in session query Stuff  all        c foo   c foo 1 session commit     it will do what it says  go fetch all the objects from the database  modify all the objects and then when it s time to flush the changes to the database  update the rows one by one   Instead you should do this   session execute update stuff table  values  stuff table c foo  stuff table c foo   1    session commit     This will execute as one query as you would expect  and because at least the default session configuration expires all data in the session on commit you don t have any stale data issues   In the almost-released 0 5 series you could also use this method for updating   session query Stuff  update  Stuff foo  Stuff foo   1   session commit     That will basically run the same SQL statement as the previous snippet  but also select the changed rows and expire any stale data in the session  If you know you aren t using any session data after the update you could also add synchronize session False to the update statement and get rid of that select

User · Answer

Here s an example of how to solve the same problem without having to map the fields manually   from sqlalchemy import Column  ForeignKey  Integer  String  Date  DateTime  text  create engine from sqlalchemy exc import IntegrityError from sqlalchemy ext declarative import declarative base from sqlalchemy orm import sessionmaker from sqlalchemy orm attributes import InstrumentedAttribute  engine   create engine  postgres   postgres localhost 5432 database   session   sessionmaker   session configure bind engine   Base   declarative base     class Media Base       tablename      media    id   Column Integer  primary key True    title   Column String  nullable False    slug   Column String  nullable False    type   Column String  nullable False     def update self       s   session       mapped values          for item in Media   dict   iteritems          field name   item 0        field type   item 1        is column   isinstance field type  InstrumentedAttribute        if is column          mapped values field name    getattr self  field name       s query Media  filter Media id    self id  update mapped values      s commit     So to update a Media instance  you can do something like this   media   Media id 123  title  Titular Line   slug  titular-line   type  movie   media update

User · Answer

Withough testing  I d try   for c in session query Stuff  all         c foo   c foo 1 session commit      IIRC  commit   works without flush      I ve found that at times doing a large query and then iterating in python can be up to 2 orders of magnitude faster than lots of queries   I assume that iterating over the query object is less efficient than iterating over a list generated by the all   method of the query object    Please note comment below - this did not speed things up at all

User · Answer

If it is because of the overhead in terms of creating objects  then it probably can t be sped up at all with SA   If it is because it is loading up related objects  then you might be able to do something with lazy loading   Are there lots of objects being created due to references    IE  getting a Company object also gets all of the related People objects

User · Answer

There are several ways to UPDATE using sqlalchemy  1  for c in session query Stuff  all           c foo    1    session commit    2  session query            update   foo    Stuff foo   1       session commit    3  conn   engine connect      stmt   Stuff update            values Stuff foo    Stuff foo   1      conn execute stmt

User · Answer

session query Clients  filter Clients id    client id list  update   status   status   session commit     Try this

User · Answer

Withough testing  I d try   for c in session query Stuff  all         c foo   c foo 1 session commit      IIRC  commit   works without flush      I ve found that at times doing a large query and then iterating in python can be up to 2 orders of magnitude faster than lots of queries   I assume that iterating over the query object is less efficient than iterating over a list generated by the all   method of the query object    Please note comment below - this did not speed things up at all

User · Answer

Withough testing  I d try   for c in session query Stuff  all         c foo   c foo 1 session commit      IIRC  commit   works without flush      I ve found that at times doing a large query and then iterating in python can be up to 2 orders of magnitude faster than lots of queries   I assume that iterating over the query object is less efficient than iterating over a list generated by the all   method of the query object    Please note comment below - this did not speed things up at all

User · Answer

SQLAlchemy s ORM is meant to be used together with the SQL layer  not hide it  But you do have to keep one or two things in mind when using the ORM and plain SQL in the same transaction  Basically  from one side  ORM data modifications will only hit the database when you flush the changes from your session  From the other side  SQL data manipulation statements don t affect the objects that are in your session   So if you say  for c in session query Stuff  all        c foo   c foo 1 session commit     it will do what it says  go fetch all the objects from the database  modify all the objects and then when it s time to flush the changes to the database  update the rows one by one   Instead you should do this   session execute update stuff table  values  stuff table c foo  stuff table c foo   1    session commit     This will execute as one query as you would expect  and because at least the default session configuration expires all data in the session on commit you don t have any stale data issues   In the almost-released 0 5 series you could also use this method for updating   session query Stuff  update  Stuff foo  Stuff foo   1   session commit     That will basically run the same SQL statement as the previous snippet  but also select the changed rows and expire any stale data in the session  If you know you aren t using any session data after the update you could also add synchronize session False to the update statement and get rid of that select

User · Answer

Here s an example of how to solve the same problem without having to map the fields manually   from sqlalchemy import Column  ForeignKey  Integer  String  Date  DateTime  text  create engine from sqlalchemy exc import IntegrityError from sqlalchemy ext declarative import declarative base from sqlalchemy orm import sessionmaker from sqlalchemy orm attributes import InstrumentedAttribute  engine   create engine  postgres   postgres localhost 5432 database   session   sessionmaker   session configure bind engine   Base   declarative base     class Media Base       tablename      media    id   Column Integer  primary key True    title   Column String  nullable False    slug   Column String  nullable False    type   Column String  nullable False     def update self       s   session       mapped values          for item in Media   dict   iteritems          field name   item 0        field type   item 1        is column   isinstance field type  InstrumentedAttribute        if is column          mapped values field name    getattr self  field name       s query Media  filter Media id    self id  update mapped values      s commit     So to update a Media instance  you can do something like this   media   Media id 123  title  Titular Line   slug  titular-line   type  movie   media update

User · Answer

Withough testing  I d try   for c in session query Stuff  all         c foo   c foo 1 session commit      IIRC  commit   works without flush      I ve found that at times doing a large query and then iterating in python can be up to 2 orders of magnitude faster than lots of queries   I assume that iterating over the query object is less efficient than iterating over a list generated by the all   method of the query object    Please note comment below - this did not speed things up at all

User · Answer

SQLAlchemy s ORM is meant to be used together with the SQL layer  not hide it  But you do have to keep one or two things in mind when using the ORM and plain SQL in the same transaction  Basically  from one side  ORM data modifications will only hit the database when you flush the changes from your session  From the other side  SQL data manipulation statements don t affect the objects that are in your session   So if you say  for c in session query Stuff  all        c foo   c foo 1 session commit     it will do what it says  go fetch all the objects from the database  modify all the objects and then when it s time to flush the changes to the database  update the rows one by one   Instead you should do this   session execute update stuff table  values  stuff table c foo  stuff table c foo   1    session commit     This will execute as one query as you would expect  and because at least the default session configuration expires all data in the session on commit you don t have any stale data issues   In the almost-released 0 5 series you could also use this method for updating   session query Stuff  update  Stuff foo  Stuff foo   1   session commit     That will basically run the same SQL statement as the previous snippet  but also select the changed rows and expire any stale data in the session  If you know you aren t using any session data after the update you could also add synchronize session False to the update statement and get rid of that select

User · Answer

If it is because of the overhead in terms of creating objects  then it probably can t be sped up at all with SA   If it is because it is loading up related objects  then you might be able to do something with lazy loading   Are there lots of objects being created due to references    IE  getting a Company object also gets all of the related People objects

User · Answer

session query Clients  filter Clients id    client id list  update   status   status   session commit     Try this

User · Answer

SQLAlchemy s ORM is meant to be used together with the SQL layer  not hide it  But you do have to keep one or two things in mind when using the ORM and plain SQL in the same transaction  Basically  from one side  ORM data modifications will only hit the database when you flush the changes from your session  From the other side  SQL data manipulation statements don t affect the objects that are in your session   So if you say  for c in session query Stuff  all        c foo   c foo 1 session commit     it will do what it says  go fetch all the objects from the database  modify all the objects and then when it s time to flush the changes to the database  update the rows one by one   Instead you should do this   session execute update stuff table  values  stuff table c foo  stuff table c foo   1    session commit     This will execute as one query as you would expect  and because at least the default session configuration expires all data in the session on commit you don t have any stale data issues   In the almost-released 0 5 series you could also use this method for updating   session query Stuff  update  Stuff foo  Stuff foo   1   session commit     That will basically run the same SQL statement as the previous snippet  but also select the changed rows and expire any stale data in the session  If you know you aren t using any session data after the update you could also add synchronize session False to the update statement and get rid of that select

User · Answer

If it is because of the overhead in terms of creating objects  then it probably can t be sped up at all with SA   If it is because it is loading up related objects  then you might be able to do something with lazy loading   Are there lots of objects being created due to references    IE  getting a Company object also gets all of the related People objects

User · Answer

If it is because of the overhead in terms of creating objects  then it probably can t be sped up at all with SA   If it is because it is loading up related objects  then you might be able to do something with lazy loading   Are there lots of objects being created due to references    IE  getting a Company object also gets all of the related People objects

[python] Efficiently updating database using SQLAlchemy ORM

Examples related to python

Examples related to orm

Examples related to sqlalchemy