10

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?

mrok
  • 2,680
  • 3
  • 27
  • 46
  • 1
    quick glance note: you don't add objects to a session for modifying. you add when creating a new row. Normally, you simply modify the proxy object then commit on the session object. Also, if you really want to use the ORM you don't normally construct a query in SQL and use the execute method. Use the query generator. – Keith Jan 13 '13 at 19:57
  • he said he got an AttributeError when modifying the RowProxy object. Why would you expect that to work? – Terrence Brannon Apr 27 '15 at 17:13

1 Answers1

14

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:

vvladymyrov
  • 5,715
  • 2
  • 32
  • 50