（三）SQLAlchemy: Python 中常用的 SQL 工具包

---

1. SQLAlchemy 介绍

最近用到了 SQLAlchemy 来操作数据库，自我感觉比较便捷，在此介绍一下其基本内容。在 Python 中，几个关键库的安装：

pip install Flask==1.1.4 -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install connexion -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install Flask-SQLAlchemy -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install Flask-Script -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install Flask-Migrate==2.7.0 -i https://pypi.tuna.tsinghua.edu.cn/simple

---

2. 基本使用

2.1 数据库迁移工具 migration

migration 避免了手动创建数据表的过程，根据定义类一键生成所需表。首先新建一个 db.py 用于存放数据库表相关内容，如下：

import connexion
from flask_sqlalchemy import SQLAlchemy

app = connexion.App(__name__)
flask_app = app.app
# 配置数据库的连接参数
mysql = {
    "host": "localhost",
    "port": 3306,
    "username": "root",
    "passwd": "admin",
    "database": "csdn_database"
}
DIAL = flask_app.config['DIALECT'] = 'mysql' 
DRIV = flask_app.config['DRIVER'] = 'mysqlconnector'
HOST = flask_app.config['HOST'] = mysql['host']
PORT = flask_app.config['PORT'] = mysql['port']
USER = flask_app.config['USERNAME'] = mysql['username']
PASS = flask_app.config['PASSWORD'] = mysql['passwd']
DATA = flask_app.config['DATABASE'] = mysql['database']
flask_app.config['SQLALCHEMY_DATABASE_URI'] = \
    "{}+{}://{}:{}@{}:{}/{}?charset=utf8".format(
        DIAL, DRIV, USER, PASS, HOST, PORT, DATA)
flask_app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True 
# 初始化 SQLAlchemy 对象
db = SQLAlchemy(flask_app)

# 建数据库表，继承自 db.Model
class CSDNModel(db.Model):
	__tablename__ = 'csdn_model'
	# 主键，自增
	id = db.Column(db.Integer, primary_key=True, autoincrement=True)
	# 其他项
	name = db.Column(db.String(128))
	age = db.Column(db.Integer)
	address = db.Column(db.String(128), nullable=True)

if __name__ == '__main__':
	from flask_migrate import Migrate, MigrateCommand
	from flask_script import Manager
	# 初始化对象并运行
	migrate = Migrate(flask_app, db, compare_type=True)
	manager = Manager(flask_app)
	manager.add_command('db', MigrateCommand)
	manager.run()

进入 mysql 并使用 create database csdn_database 命令创建数据库。

mysql> create database csdn_database;
Query OK, 1 row affected (0.01 sec)

然后依次执行以下命令使用 migration 创建数据库表。

#!/bin/bash
python db.py db init
python db.py db migrate
python db.py db upgrade

查看建的表：

mysql> show tables;
+-------------------------+
| Tables_in_csdn_database |
+-------------------------+
| alembic_version         |
| csdn_model              |
+-------------------------+
2 rows in set (0.01 sec)

2.2 基于 SQLAlchemy 的增删查改

新建一个 main.py 使用基于 SQLAlchemy 的增删查改的功能。

from db import db, CSDNModel

# 1. 插入一条数据
add_model = CSDNModel(name='zhangsan', age=10, address='beijing')
db.session.add(add_model)
db.session.commit()
"""
按照上述语句插入三条记录：
mysql> select * from csdn_model;
+----+----------+------+-----------+
| id | name     | age  | address   |
+----+----------+------+-----------+
|  1 | zhangsan |   10 | beijing   |
|  2 | lisi     |   12 | shanghai  |
|  3 | wangwu   |   14 | guangzhou |
+----+----------+------+-----------+
3 rows in set (0.00 sec)
"""

# 2.1 根据主键 id 查询一条数据
query_model = CSDNModel.query.get(1)
print(query_model.name, query_model.age, query_model.address)
"""
zhangsan 10 beijing
"""

## 2.2 根据条件查询, first 返回满足条件的第一条记录、all 返回所有记录
query_model = CSDNModel.query.filter_by(name='lisi').first()
print(query_model.name, query_model.age, query_model.address)
"""
lisi 12 shanghai
"""

# 3. 删除，先查询再删除，也可根据查询中 all 实现批量删除
delete_model = CSDNModel.query.get(3)
db.session.delete(delete_model)
db.session.commit()
"""
mysql> select * from csdn_model;
+----+----------+------+----------+
| id | name     | age  | address  |
+----+----------+------+----------+
|  1 | zhangsan |   10 | beijing  |
|  2 | lisi     |   12 | shanghai |
+----+----------+------+----------+
2 rows in set (0.00 sec)
"""

# 4. 修改
update_model = CSDNModel.query.filter_by(name='lisi').first()
update_model.name = 'lilaosi'
db.session.merge(update_model)
db.session.commit()
"""
mysql> select * from csdn_model;
+----+----------+------+----------+
| id | name     | age  | address  |
+----+----------+------+----------+
|  1 | zhangsan |   10 | beijing  |
|  2 | lilaosi  |   12 | shanghai |
+----+----------+------+-
"""

---

3. 总结

本文介绍了 SQLAlchemy 的基本使用，注意在安装 Python 库时的版本要求，有些最新库不支持上述操作。

---