更新 SqlAlchemy ORM 中的行 [英] Updating row in SqlAlchemy ORM
问题描述
我正在尝试从数据库中获取一行，修改该行并再次保存.
一切都使用 SqlAlchemy
I am trying to obtain a row from DB, modify that row and save it again.
Everything by using SqlAlchemy
我的代码
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()
不幸的是它不起作用，因为我正在尝试修改 RowProxy 对象.如何以 SqlAlchemy ORM 方式执行我想要的操作(加载、更改和保存(更新)行)?
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?
推荐答案
我假设您的意图是使用 对象关系 API.因此，要更新 db 中的行，您需要通过从表记录加载映射对象并更新对象的属性来执行此操作.
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 还提供了 SQL 表达式 API，允许直接处理表记录，无需创建映射对象.在我的实践中，我们在大多数应用程序中使用 Object-Relation API，有时当我们需要高效地执行低级别数据库操作(例如使用一个查询插入或更新数千条记录)时，我们会使用 SQL Expressions API.
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.
SQLAlchemy 文档的直接链接:
Direct links to SQLAlchemy documentation:
这篇关于更新 SqlAlchemy ORM 中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
