Sqlalchemy 简明教程

SQLAlchemy ORM - Updating Objects

在本章中，我们将了解如何使用所需的值修改或更新表。

In this chapter, we will see how to modify or update the table with desired values.

要修改任何对象的某个属性的数据，我们必须向其分配一个新值并提交更改以使更改保持持久性。

To modify data of a certain attribute of any object, we have to assign new value to it and commit the changes to make the change persistent.

让我们从主键标识符为 2 的 Customers 表中获取一个对象。我们可以按如下方式使用会话的 get() 方法 −

Let us fetch an object from the table whose primary key identifier, in our Customers table with ID=2. We can use get() method of session as follows −

x = session.query(Customers).get(2)

我们可以通过下面给出的代码来显示所选对象的内容 -

We can display contents of the selected object with the below given code −

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

从我们的客户表中，应当显示以下输出 -

From our customers table, following output should be displayed −

Name: Komal Pande Address: Koti, Hyderabad Email: komal@gmail.com

现在我们需要通过分配新的值（如下所示）来更新地址字段 -

Now we need to update the Address field by assigning new value as given below −

x.address = 'Banjara Hills Secunderabad'
session.commit()

更改将持久地反映在数据库中。现在，我们通过如下使用 first() method 来获取表中第一行的相应对象：

The change will be persistently reflected in the database. Now we fetch object corresponding to first row in the table by using first() method as follows −

x = session.query(Customers).first()

这会执行以下 SQL 表达式：

This will execute following SQL expression −

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
LIMIT ? OFFSET ?

绑定参数分别是 LIMIT = 1 和 OFFSET = 0，这意味着将选择第一行。

The bound parameters will be LIMIT = 1 and OFFSET = 0 respectively which means first row will be selected.

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

现在，显示第一行的上述代码的输出如下：

Now, the output for the above code displaying the first row is as follows −

Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com

现在，更改名称属性，并使用下面的代码显示内容：

Now change name attribute and display the contents using the below code −

x.name = 'Ravi Shrivastava'
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

上述代码的输出为：

The output of the above code is −

Name: Ravi Shrivastava Address: Station Road Nanded Email: ravi@gmail.com

尽管显示了更改，但未提交。你可以通过在下面的代码中使用 rollback() method 来保留早期的持久位置。

Even though the change is displayed, it is not committed. You can retain the earlier persistent position by using rollback() method with the code below.

session.rollback()

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

将显示第一条记录的原始内容。

Original contents of first record will be displayed.

对于批量更新，我们要使用查询对象的 update() 方法。让我们尝试并给每一行（ID = 2 除外）的名称加上前缀“Mr.”。相应的 update() 语句如下：

For bulk updates, we shall use update() method of the Query object. Let us try and give a prefix, ‘Mr.’ to name in each row (except ID = 2). The corresponding update() statement is as follows −

session.query(Customers).filter(Customers.id! = 2).
update({Customers.name:"Mr."+Customers.name}, synchronize_session = False)

The update() method requires two parameters as follows −

The update() method requires two parameters as follows −

  1. A dictionary of key-values with key being the attribute to be updated, and value being the new contents of attribute.

  2. synchronize_session attribute mentioning the strategy to update attributes in the session. Valid values are false: for not synchronizing the session, fetch: performs a select query before the update to find objects that are matched by the update query; and evaluate: evaluate criteria on objects in the session.

表中四行中的三行名前会加上前缀“Mr.”。但是，不会提交更改，因此也不会反映在 SQLiteStudio 的表视图中。只有在提交会话时才会刷新。

Three out of 4 rows in the table will have name prefixed with ‘Mr.’ However, the changes are not committed and hence will not be reflected in the table view of SQLiteStudio. It will be refreshed only when we commit the session.