Sqlalchemy 简明教程

Using UPDATE Expression

目标表对象上的 update() 方法构建了等效的 UPDATE SQL 表达式。

The update() method on target table object constructs equivalent UPDATE SQL expression.

table.update().where(conditions).values(SET expressions)

结果更新对象上的 values() 方法用于指定 UPDATE 的 SET 条件。如果保留为 None，则 SET 条件将根据在语句执行和/或编译期间传递给该语句的参数确定。

The values() method on the resultant update object is used to specify the SET conditions of the UPDATE. If left as None, the SET conditions are determined from those parameters passed to the statement during the execution and/or compilation of the statement.

where 子句是一个可选表达式，它描述了 UPDATE 语句的 WHERE 条件。

The where clause is an Optional expression describing the WHERE condition of the UPDATE statement.

以下代码片段将 students 表中的“lastname”列的值从“Khanna”更改为“Kapoor” -

Following code snippet changes value of ‘lastname’ column from ‘Khanna’ to ‘Kapoor’ in students table −

stmt = students.update().where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')

stmt 对象是一个更新对象，该对象转换为 -

The stmt object is an update object that translates to −

'UPDATE students SET lastname = :lastname WHERE students.lastname = :lastname_1'

当调用 execute() 方法时，边界参数 lastname_1 将被替换。完整的更新代码如下 -

The bound parameter lastname_1 will be substituted when execute() method is invoked. The complete update code is given below −

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students',
   meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

conn = engine.connect()
stmt=students.update().where(students.c.lastname=='Khanna').values(lastname='Kapoor')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()

上述代码显示了以下输出，其中第二行显示更新操作的效果，如给出的屏幕截图所示 -

The above code displays following output with second row showing effect of update operation as in the screenshot given −

[
   (1, 'Ravi', 'Kapoor'),
   (2, 'Rajiv', 'Kapoor'),
   (3, 'Komal', 'Bhandari'),
   (4, 'Abdul', 'Sattar'),
   (5, 'Priya', 'Rajhans')
]
update operation

请注意，还可以使用 sqlalchemy.sql.expression 模块中的 update() 函数来实现类似的功能，如下所示 -

Note that similar functionality can also be achieved by using update() function in sqlalchemy.sql.expression module as shown below −

from sqlalchemy.sql.expression import update
stmt = update(students).where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')