代码收藏家 SQLAlchemy使用教程
文章目录
一、SQLAlchemy简介
1.ORM
ORM(Object Relational Mapping，对象关系映射)可以绕过SQL语句，把数据库的table（表）映射为编程语言的class（类），可以直接使用编程语言的对象模型操作数据库，而不使用SQL语句。
ORM把表映射成类，把行作为实例，把字段作为属性，在执行对象操作时最终会把对象的操作专函为数据库原生语句。
2.SQLAlchemy的优点
易用性：减少SQL语句的使用，使代码、模型更加直观、清晰；
性能损耗小；设计灵活；可移植性强；
3.SQLAlchemy分为两部分
ORM对象映射和核心的SQLexpression
二、SQLAlchemy的安装和连接
1.安装SQLAlchemy
（1）在使用SQLAlchemy前要先给Python安装MySQL驱动，由于MySQL不支持和Python3，因此需要使用PyMySQL与SQLAlchemy交互。
pip install pymysql
pip install sqlalchemy
2.使用SQLAlchemy连接MySQL数据库
（1）通过SQLAlchemy中的create_engine()函数连接数据库
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://user:password@localhost:3306/database",echo=True)
连接数据库的引擎参数形式如下
engine = create_engine("数据库类型+数据库驱动://数据库用户名:数据库密码@IP地址:端口号/数据库?编码...", 其它参数)
其中echo用来设置SQLAlchemy日志，设置为True时，可以看见所有数据库的操作记录。
create_engine()返回的是Engine的一个实例，代表了操作数据库的核心接口，处理数据库和数据库的API。
初次调用create_engine()并不会真正连接数据库，只有在真正执行一条命令的时候才会尝试建立连接，目的是节省资源。
（2）映射声明
当使用ORM时，其配置过程主要分成两个部分：一是描述处理的数据库表的信息；二是将Python类映射到这些表上。它们在SQLAlchemy中一起完成，被称为Declarative。
使用Declarative参与的ORM映射的类需要被定义为一个指定基类的子类，这个基类含有ORM映射中相关类和表的信息。这样的基类称为declarative base class。这个基类可以通过declarative_base来创建。
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base（）
三、SQLAlchemy常用数据类型
Interger：整型，映射到数据库中是int类型
Float：浮点类型，float
Double; String; Boolean;
Decimal: 定点类型，专门为解决浮点类型精度丢失的问题而设定。Decimal需要传入两个参数，第一个参数标记该字段能存储多少位数，第二个参数表示小数点后有又多少个小数位。
Enum：枚举类型；
Date：日期类型，年月日；
DateTime： 时间类型，年月日时分毫秒；
Time：时间类型，时分秒；
Text：长字符串，可存储6万多个字符，text；
LongText：长文本类型，longtext.
四、创建类
前面已经介绍了如何创建一个基类及常用的数据类型，可以基于这个基类创建自定义的类。
（1）下面以创建一个用户类为例：
from sqlalchemy import Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base # ORM(对象关系映射)的基类
Base = declarative_base()
# 继承Base基类
class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True)
name = Column(String(20), default=None, nullable=False, comment="用户姓名")
phone = Column(String(20), default=None, nullable=False, comment="电话")
country = Column(Integer, default=0, nullable=False, comment="国家")
def __repr__(self):
Name = self.course_name
Phone = self.teacher_name
Country = self.class_times
return f"User: name: {Name}, phone: {Phone}, country: {Country}"
上面的代码中，User类继承了Base类，__tablename__指明表明，通过Column()指定列属性。Column常用的列选项如下，
| 选项 | 说明 |
|---|---|
| primary_key | 是否为主键 |
| unique | 是否唯一 |
| index | 如果为True，为该列创建索引，提高查询效率 |
| nullable | 是否允许为空 |
| default | 默认值 |
| name | 在数据表中的字段映射 |
| autoincrement | 是否自动增长 |
| onupdate | 更新时执行的函数 |
| comment | 字段描述 |
（2）类返回字符串
上面代码中__repr__函数定义该类返回的字符串内容。
通过上面类的声明，将类映射成能够读写数据库的表和列。
五、创建模式
1.查看表信息
User.__table__
2.创建表
Table对象是一个更大家庭——metadata，metadata是与数据库打交道的一个接口，创建表需要使用metadata发出CREATE TABLE的命令。创建表的完整代码如下，
from sqlalchemy import Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
Base = declarative_base()
engine = create_engine("mysql+pymysql://root:root@localhost:3306/apptest", echo=True)
class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True)
name = Column(String(20), default=None, nullable=False, comment="用户姓名")
phone = Column(String(20), default=None, nullable=False, comment="电话")
country = Column(Integer, default=0, nullable=False, comment="国家")
def __repr__(self):
Name = self.course_name
Phone = self.teacher_name
Country = self.class_times
return f"User: name: {Name}, phone: {Phone}, country: {Country}"
Base.metadata.create_all(engine) # 通过此语句创建表
3.创建实例
NewUser = User(name="Jason", phone="12345678910", country="China")
4.创建会话
要真正应用类对象操作数据表，还需要一个Session对象，ORM对数据库的入口即Session。Session的创建实例如下，
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine("mysql+pymysql://root:root@localhost:3306/apptest", echo=True)
Session = sessionmaker(bind=engine)
session = Session()
或者
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
Session = sessionmaker()
engine = create_engine("mysql+pymysql://root:root@localhost:3306/apptest", echo=True)
Session.configure(bind=engine)
session = Session()
经过上面的操作，Session已经与MySQL数据库的Engine关联了。当一个Session被首次使用时，它会从Engine所维护的连接池中取出一个连接来操作数据库。这个连接在应用有所更改或者关闭Session时会被释放。
SQLAlchemy的Session是用于管理数据库操作的一个像容器一样的工厂对象。Session工厂对象中提供query(), add(), add_all(), commit(), delete(), flush(), rollback(), close()等方法。
六、SQLAlchemy对MySQL数据库的基本操作
1.添加对象
SQLAlchemy可以通过Session提供的add()方法，将数据存入数据库。示例如下，
from sqlalchemy import Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
engine = create_engine("mysql+pymysql://root:root@localhost:3306/apptest", echo=True)
Session = sessionmaker(bind=engine)
session = Session()
class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True)
name = Column(String(20), default=None, nullable=False, comment="用户姓名")
phone = Column(String(20), default=None, nullable=False, comment="电话")
country = Column(Integer, default=0, nullable=False, comment="国家")
def __repr__(self):
Name = self.course_name
Phone = self.teacher_name
Country = self.class_times
return f"User: name: {Name}, phone: {Phone}, country: {Country}"
Base.metadata.create_all(engine)
NewUser = User(name="python", phone="12345678910", country="China")
session.add(NewUser)
session.commit() # 需要调用commit()方法提交事务。
其中，也可以通过add_all()方法添加多个对象，传入参数为一个包含多个对象的列表。
2.查询对象
通过Session的query()方法查询数据，返回对象中的__repr__()函数的返回值。
query_result = session.query(User).all()
for result in query_result:
print(f"查询结果为: {result}")
# [out]查询结果为: User: name: python, phone: 12345678910, country: China
（1）添加查询条件
使用query()方法中的filter()方法实现与WHERE等同的效果，示例代码如下：
results = session.query(User).filter(User.name=='python')
for result in results:
print(f"查询结果为: {result}")
（2）返回对象
可以通过在查询语句后添加first(), all()等返回对应的查询对象。
3.更新对象
在SQLAlchemy中，更改值的方式是，找到目标的类对象，然后通过字段映射到类对象对应的属性字段，对属性字段重新赋值。再执行Session的add(),commit()方法实现更新。例子如下，
result = session.query(User).filter(User.name=="python").first()
result.name = "Cython"
session.add(result)
session.commit()
4.删除对象
SQLAlchemy中的删除对象方法比较简单，示例代码如下，
result = session.query(User).filter(User.name=="python").first()
session.delete(result)
session.commit()
5.高级查询
在SQLAlchemy中通过filter()方法中的操作符实现高级查询。
（1）like操作符
query_result = session.query(User).filter(User.name.like("%py%"))
该方法与MySQL中的like模糊查询用法基本一致。
（2）and操作符
实现MySQL中的and查询有三种方法
·方法一：使用and_()
query_result = session.query.filter(and_(User.name == 'python', User.id > 1))
·方法二：在filter()中设置多个表达式
query_result = session.query.filter(User.name == 'python', User.id > 1)
·方法三：使用多个filter()
query_result = session.query.filter(User.name == 'python').filter(User.id > 1)
（3）or操作符
query_result = session.query.filter(or_(User.name == 'python', User.id > 1))
（4）in操作符
query_result = session.query.filter(User.name.in_(["python", "C"]))
（5）not in操作符
在in操作符的基础加上运算符’~’。
query_result = session.query.filter(~User.name.in_(["python", "C"]))
七、嵌入使用SQL语句
从SQLAlchemy中导出text方法，可以通过text(SQL语句)嵌入使用SQL语句。
1.在查询中嵌入SQL语句
from sqlalchemy import text
...
...
...
query_result = session.query(User).filter(text("name='python'")).all()
2.通过Engine对象执行SQL语句
engine = create_engine("mysql+pymysql://root:root@localhost:3306/apptest", echo=True)
with engine.connect() as conn:
conn.execute(text(sql))
3.保存/更新用户信息示例
在web开发中，通常涉及数据的保存和更新，下面例子通过嵌入使用SQL语句，实现数据（字典类型）的保存，当primary_key或uniqu字段重复时，会自动实现数据的更新。
def SaveData(table, data: dict) -> (bool, str):
sqlSet = ','.join([key+'='+(str(value) if type(value) == int else f"'{value}'")
for key, value in data.__iter__()])
sqlInsert = f"insert into {table} set {sqlSet} on duplicate key update {sqlSet}"
try:
engine = get_engine() # 在这里我将创建Engine的方法封装成get_engine()方法
with engine.connect() as conn:
conn.execute(text(sqlInsert))
return True, "数据保存/更新成功！"
except Exception as e:
return False, f"数据保存/更新失败！err:{e}"
来源：Viva Python
