Sqlalchemy 简明教程

SQLAlchemy Core - Executing Expression

在上一章中，我们了解了 SQL 表达式。本章中，我们将会了解如何执行这些表达式。

In the previous chapter, we have learnt SQL Expressions. In this chapter, we shall look into the execution of these expressions.

要执行所得的 SQL 表达式，必须首先 obtain a connection object representing an actively checked out DBAPI connection resource 并 feed the expression object ，如下面的代码所示。

In order to execute the resulting SQL expressions, we have to obtain a connection object representing an actively checked out DBAPI connection resource and then feed the expression object as shown in the code below.

conn = engine.connect()

以下 insert() 对象可用于 execute() 方法：

The following insert() object can be used for execute() method −

ins = students.insert().values(name = 'Ravi', lastname = 'Kapoor')
result = conn.execute(ins)

控制台将显示 SQL 表达式执行结果，如下面所示：

The console shows the result of execution of SQL expression as below −

INSERT INTO students (name, lastname) VALUES (?, ?)
('Ravi', 'Kapoor')
COMMIT

以下是显示使用 SQLAlchemy 核心技术执行 INSERT 查询的完整代码段：

Following is the entire snippet that shows the execution of INSERT query using SQLAlchemy’s core technique −

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),
)

ins = students.insert()
ins = students.insert().values(name = 'Ravi', lastname = 'Kapoor')
conn = engine.connect()
result = conn.execute(ins)

可以使用 SQLite Studio 打开数据库并查看结果，如下图所示：

The result can be verified by opening the database using SQLite Studio as shown in the below screenshot −

sqlite studio

结果变量被称为 ResultProxy object 。这类似于 DBAPI 游标对象。我们可以使用 ResultProxy.inserted_primary_key 获取语句中生成的主键值的信息，如下所示：

The result variable is known as a ResultProxy object. It is analogous to the DBAPI cursor object. We can acquire information about the primary key values which were generated from our statement using ResultProxy.inserted_primary_key as shown below −

result.inserted_primary_key
[1]

要使用 DBAPI 的 execute many() 方法发出多个插入操作，可以发送一个列表，其中每个列表包含一组要插入的不同参数。

To issue many inserts using DBAPI’s execute many() method, we can send in a list of dictionaries each containing a distinct set of parameters to be inserted.

conn.execute(students.insert(), [
   {'name':'Rajiv', 'lastname' : 'Khanna'},
   {'name':'Komal','lastname' : 'Bhandari'},
   {'name':'Abdul','lastname' : 'Sattar'},
   {'name':'Priya','lastname' : 'Rajhans'},
])

此操作反映在表的数据视图中，如图所示：

This is reflected in the data view of the table as shown in the following figure −

table data view