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 库时的版本要求,有些最新库不支持上述操作。