SQLAlchemy使用教程

文章目录

• • • • 一、SQLAlchemy简介
      • • 1.ORM
        • 2.SQLAlchemy的优点
        • 3.SQLAlchemy分为两部分
      • 二、SQLAlchemy的安装和连接
      • • 1.安装SQLAlchemy
        • 2.使用SQLAlchemy连接MySQL数据库
      • 三、SQLAlchemy常用数据类型
      • 四、创建类
      • 五、创建模式
      • • 1.查看表信息
        • 2.创建表
        • 3.创建实例
        • 4.创建会话
      • 六、SQLAlchemy对MySQL数据库的基本操作
      • • 1.添加对象
        • 2.查询对象
        • 3.更新对象
        • 4.删除对象
        • 5.高级查询
      • 七、嵌入使用SQL语句
      • • 1.在查询中嵌入SQL语句
        • 2.通过Engine对象执行SQL语句
        • 3.保存/更新用户信息示例

一、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
1
2

2.使用SQLAlchemy连接MySQL数据库

（1）通过SQLAlchemy中的create_engine()函数连接数据库

from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://user:password@localhost:3306/database",echo=True)
1
2

连接数据库的引擎参数形式如下

engine = create_engine("数据库类型+数据库驱动://数据库用户名:数据库密码@IP地址:端口号/数据库?编码...", 其它参数)
1

其中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（）
1
2

三、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}"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

上面的代码中，User类继承了Base类，__tablename__指明表明，通过Column()指定列属性。Column常用的列选项如下，

选项

说明

primary_key

是否为主键

unique

是否唯一

index

如果为True，为该列创建索引，提高查询效率

nullable

是否允许为空

default

默认值

name

在数据表中的字段映射

autoincrement

是否自动增长

onupdate

更新时执行的函数

comment

字段描述

（2）类返回字符串

上面代码中__repr__函数定义该类返回的字符串内容。

通过上面类的声明，将类映射成能够读写数据库的表和列。

五、创建模式

1.查看表信息

User.__table__
1

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)	# 通过此语句创建表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

3.创建实例

NewUser = User(name="Jason", phone="12345678910", country="China")
1

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()
1
2
3
4
5
6

或者

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()
1
2
3
4
5
6

经过上面的操作，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()方法提交事务。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

其中，也可以通过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
2
3
4
5

（1）添加查询条件

使用query()方法中的filter()方法实现与WHERE等同的效果，示例代码如下：

results = session.query(User).filter(User.name=='python')
for result in results:
    print(f"查询结果为: {result}")
1
2
3

（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()
1
2
3
4

4.删除对象

SQLAlchemy中的删除对象方法比较简单，示例代码如下，

result = session.query(User).filter(User.name=="python").first()
session.delete(result)
session.commit()
1
2
3

5.高级查询

在SQLAlchemy中通过filter()方法中的操作符实现高级查询。

（1）like操作符

query_result = session.query(User).filter(User.name.like("%py%"))
1

该方法与MySQL中的like模糊查询用法基本一致。

（2）and操作符

实现MySQL中的and查询有三种方法

·方法一：使用and_()

query_result = session.query.filter(and_(User.name == 'python', User.id > 1))
1

·方法二：在filter()中设置多个表达式

query_result = session.query.filter(User.name == 'python', User.id > 1)
1

·方法三：使用多个filter()

query_result = session.query.filter(User.name == 'python').filter(User.id > 1)
1

（3）or操作符

query_result = session.query.filter(or_(User.name == 'python', User.id > 1))
1

（4）in操作符

query_result = session.query.filter(User.name.in_(["python", "C"]))
1

（5）not in操作符

在in操作符的基础加上运算符’~’。

query_result = session.query.filter(~User.name.in_(["python", "C"]))
1

七、嵌入使用SQL语句

从SQLAlchemy中导出text方法，可以通过text(SQL语句)嵌入使用SQL语句。

1.在查询中嵌入SQL语句

from sqlalchemy import text
...
...
...
query_result = session.query(User).filter(text("name='python'")).all()
1
2
3
4
5

2.通过Engine对象执行SQL语句

engine = create_engine("mysql+pymysql://root:root@localhost:3306/apptest", echo=True)
        with engine.connect() as conn:
            conn.execute(text(sql))
1
2
3

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}"
1
2
3
4
5
6
7
8
9
10
11

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦