SQLAlchemy 执行原生 SQL 语句

在使用 SQLAlchemy 进行数据库操作时，虽然 ORM 提供了强大的模型映射功能，但有时候直接使用原生 SQL 语句可能更加简单直率，甚至性能更优。下面我们来看一下如何在 SQLAlchemy 中执行原生的 SQL 语句。

执行原生 SQL 语句的步骤

1. 构造 SQL 语句
2. 调用 session 中的 execute 方法执行，得到游标对象
3. 通过游标获取结果

示例：查询学生信息

首先，我们来看一个查询学生信息的示例。

import db


sql = db.text('SELECT * FROM tb_student')
cursor = db.session.execute(sql)
print(cursor)  # 打印游标对象

这段代码构造了一条查询所有学生信息的 SQL 语句，并通过 db.session.execute 方法执行，得到一个游标对象。

获取查询结果

获取一条结果

import db


cursor = db.session.execute(db.text('SELECT * FROM tb_student'))
print(cursor.mappings().fetchone())

使用 mappings().fetchone() 方法可以将结果转换为字典并获取一条记录。

获取指定数量的结果

import db


cursor = db.session.execute(db.text('SELECT * FROM tb_student'))
print(cursor.mappings().fetchmany(2))

使用 mappings().fetchmany(2) 方法可以获取指定数量的记录，这里获取了两条记录。

获取所有结果

import db


cursor = db.session.execute(db.text('SELECT * FROM tb_student'))
print(cursor.mappings().fetchall())

使用 mappings().fetchall() 方法可以获取所有记录。

示例：插入学生数据

接下来，我们来看一个插入学生数据的示例。

import db


sql = db.text(
    'INSERT INTO tb_student(name, class, age, sex, description) VALUES(:name, :class_, :age, :sex, :description)'
)
data = dict(
    name='李小白',
    age=17,
    sex=True,
    class_=1,
    description='大河之剑天上来',
)
cursor = db.session.execute(sql, params=data)
db.session.commit()
print(cursor.lastrowid)  # 打印新插入记录的主键值

这段代码构造了一条插入学生数据的 SQL 语句，并使用命名参数（如 :name, :class_ 等）来避免 SQL 注入问题。然后，通过 db.session.execute 方法执行插入操作，并提交事务。最后，使用 cursor.lastrowid 获取新插入记录的主键值。

复习一下

1. 构造 SQL 语句：使用 db.text 方法构造 SQL 语句。
2. 执行 SQL 语句：使用 db.session.execute 方法执行 SQL 语句，并得到游标对象。
3. 获取结果：通过游标对象的 mappings() 方法，将结果转换为字典格式，并使用 fetchone(), fetchmany(n), 或 fetchall() 方法获取结果。

注意事项

• SQL 注入防护：使用命名参数（如 :name, :class_ 等）来防止 SQL 注入。
• 事务管理：在执行插入、更新或删除操作时，别忘了提交事务（db.session.commit()）。
• 游标对象：通过游标对象可以获取查询结果，也可以获取插入操作生成的主键值（lastrowid）。