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: [phone], 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}"