Efficiently Updating Database Using SQLalchemy Orm

Efficiently updating database using SQLAlchemy ORM

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.

Updating a row using SQLAlchemy ORM

I believe you are looking for something like this for your update query:

session.query(FoobarModel).filter(FoobarModel.id == foobar_id).update({'name': 'New Foobar Name!'})

Since update() belongs to Query, and filter() does return a Query object, this will work, contrary to trying to call update() on your FoobarModel object (which does not have such a function) returned by Query.get(), see also here.

As for looping over your properties and assigning them by name, you could do this with setattr and a dict, like this:

foobar = session.query(FoobarModel).get(foobar_id)

props = {'name': 'my new name'}

for key, value in props.items():
    setattr(foobar, key, value)

session.commit()
session.flush()

This is obviously a little pointless with just one property, but maybe it will come in handy at some point.

Updating an entity with sqlalchemy ORM

Table objects are not part of SQLAlchemy ORM, they are part of SQLAlchemy Core. In order to use ORM you'll want to do something like this:

from sqlalchemy import create_engine
from sqlalchemy import select
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session

engine = create_engine("sqlite://", echo=True)

# create test environment
with engine.begin() as conn:
    conn.exec_driver_sql("CREATE TABLE my_thing (id int primary key, sell_price int)")
    conn.exec_driver_sql("INSERT INTO my_thing (id, sell_price) VALUES (1, 123)")

Base = automap_base()

class MyThing(Base):
    __tablename__ = "my_thing"

Base.prepare(autoload_with=engine)

# test
with Session(engine) as sess:
    thing_1 = sess.scalar(select(MyThing).where(MyThing.id == 1))
    thing_1.sell_price = 456
    sess.commit()
""" SQL emitted:
UPDATE my_thing SET sell_price=? WHERE my_thing.id = ?
[generated in 0.00032s] (456, 1)
"""

Updating row in SqlAlchemy ORM

I assume that your intention is to use Object-Relational API.
So to update row in db you'll need to do this by loading mapped object from the table record and updating object's property.

Please see code example below.
Please note I've added example code for creating new mapped object and creating first record in table also there is commented out code at the end for deleting the record.

from sqlalchemy import Column, DateTime, Integer, String, Table, MetaData
from sqlalchemy.orm import mapper
from sqlalchemy import create_engine, orm

metadata = MetaData()

product = Table('product', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(1024), nullable=False, unique=True),

)

class Product(object):
    def __init__(self, id, name):
        self.id = id
        self.name = name
    def __repr__(self):
        return "%s(%r,%r)" % (self.__class__.name,self.id,self.name)

mapper(Product, product)

db = create_engine('sqlite:////temp/test123.db')
metadata.create_all(db)

sm = orm.sessionmaker(bind=db, autoflush=True, autocommit=True, expire_on_commit=True)
session = orm.scoped_session(sm)

#create new Product record:
if session.query(Product).filter(Product.id==1).count()==0:

    new_prod = Product("1","Product1")
    print "Creating new product: %r" % new_prod
    session.add(new_prod)
    session.flush()
else:
    print "product with id 1 already exists: %r" % session.query(Product).filter(Product.id==1).one()

print "loading Product with id=1"
prod = session.query(Product).filter(Product.id==1).one()
print "current name: %s" % prod.name
prod.name = "new name"

print prod

prod.name = 'test'

session.add(prod)
session.flush()

print prod

#session.delete(prod)
#session.flush()

PS SQLAlchemy also provides SQL Expression API that allows to work with table records directly without creating mapped objects. In my practice we are using Object-Relation API in most of the applications, sometimes we use SQL Expressions API when we need to perform low level db operations efficiently such as inserting or updating thousands of records with one query.

Direct links to SQLAlchemy documentation:

• Object Relational Tutorial
• SQL Expression Language Tutorial

---

Related Topics