Updating row in SqlAlchemy ORM

Question

I am trying to obtain a row from DB, modify that row and save it again.
Everything by using SqlAlchemy

My code

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

mapper(Product, product)


db = create_engine('sqlite:////' + db_path)
sm = orm.sessionmaker(bind=db, autoflush=True, autocommit=True, expire_on_commit=True)
session = orm.scoped_session(sm)

result = session.execute("select * from product where id = :id", {'id': 1}, mapper=Product)
prod = result.fetchone() #there are many products in db so query is ok

prod.name = 'test' #<- here I got AttributeError: 'RowProxy' object has no attribute 'name'

session .add(prod)
session .flush()

Unfortunately it does not work, because I am trying to modify RowProxy object. How can I do what I want (load, change and save(update) row) in SqlAlchemy ORM way?

Answer 1

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