Python 更新 SqlAlchemy ORM 中的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14307238/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating row in SqlAlchemy ORM
提问by mrok
I am trying to obtain a row from DB, modify that row and save it again.
Everything by using SqlAlchemy
我试图从数据库中获取一行，修改该行并再次保存。
一切都使用 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?
不幸的是它不起作用，因为我正在尝试修改 RowProxy 对象。如何以 SqlAlchemy ORM 方式执行我想要的操作（加载、更改和保存（更新）行）？
采纳答案by vvladymyrov
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.
我假设您的意图是使用Object-Relational API。因此，要更新 db 中的行，您需要通过从表记录加载映射对象并更新对象的属性来执行此操作。
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 APIthat 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.
PS SQLAlchemy 还提供SQL 表达式 API，允许直接处理表记录，而无需创建映射对象。在我的实践中，我们在大多数应用程序中使用 Object-Relation API，有时当我们需要高效地执行低级别数据库操作（例如使用一个查询插入或更新数千条记录）时，我们会使用 SQL Expressions API。
Direct links to SQLAlchemy documentation:
SQLAlchemy 文档的直接链接：
