【杂记】SQLAlchemy使用方法记录

写在前面

仅作个人学习与记录用。主要整理SQLAlchemy数据库访问与操作的方法。

1. 什么是SQLAlchemy

SQLAlchemy是Python SQL工具包和ORM框架，它为应用程序开发人员提供了SQL的全部功能。

它提供了一套众所周知的企业级持久性模式，这些模式是为高效和高性能的数据库访问而设计的，并被改编成一种简单的python域语言。

本文主要聚焦于SQLAlchemy在以下场景中的初级使用方法：

• 数据库访问和操作:SQLAlchemy提供了高层抽象来操作数据库,可以避免写原生SQL语句。支持多种数据库后端。
• ORM映射:建立Python类与数据库表的映射关系,简化数据模型的操作,支持声明式操作。

2. 安装SQLAlchemy

终端直接pip下载安装即可：

pip install SQLAlchemy

3. 使用方法

3.1 初始化数据库连接

要对数据库进行访问和操作，首先要连接目标数据库，代码如下：

from sqlalchemy import create_engine
db = create_engine('mysql+pymysql://', 
connect_args={'user': '用户名',
'password': '密码',
'host': '数据库地址',
'port': 3306,
'database': '数据库名',
'charset': 'utf8'})                                                                                                                                                                      

其中，connect_args是连接信息：user和password分别是数据库所在的MySQL的账号和密码，host和port是MySQL服务器的地址和端口号，database是要连接的数据库名称，charset是编码方式。

或者使用URL的方式填写连接数据库所需的信息。如下代码所示：

db = create_engine('mysql+pymysql://[用户名]:[密码]@127.0.0.1:3306/[数据库名]?charset=utf8'）

3.2 创建表

3.2.1 基础创建表操作

需要注意的是，对数据库进行任何操作都需要先建立映射关系。创建数据表的代码如下：

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, VARCHAR, DateTime,.....
from sqlalchemy import create_engine
import datetime
# 获取数据库实例
db = create_engine('mysql+pymysql://[用户名]:[密码]@127.0.0.1:3306/[数据库名]?charset=utf8'）

# 建立映射关系
Base = declarative_base()
class User(Base):
    __tablename__ = 'users'  # 设置表名
    id = Column(Integer, primary_key=True) # id列，类型是Integer，主键
    name = Column(String(32), index=True, nullable=False) # name列varchar32，索引，不可为空
    age = Column(Integer) # age列
    ctime = Column(DateTime, default=datetime.datetime.now) # ctime列，类型是时间，datetime.datetime.now是录入的时间不能加括号，加了括号，以后永远是当前时间
    extra = Column(Text, nullable=True, unique=True) # extra列，文本类型，允许空值，重复出现的记录仅保留一条

# 创建表
Base.metadata.create_all(db)

运行上述的代码后会在数据库中创建一个名为users的表。如果需要创建多个表，只需要创建多个类即可。

3.2.2 常用表字段属性代码

SQLAlchemy 创建表时，如3.2.1代码所示，需要使用许多属性来规定表的字段的属性和行为，常用的属性如下：

3.2.3 建立数据库表关系

（1）一对多

一对多的关系是一种特殊的多对多的关系，例如一个用户可以有多个电话号码，即表users中的一条记录能够对应另一张表phone_number中的多条记录，而表phone_number中的一条记录只能对应表users中的一条记录。

代码如下：

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class User(Base):
    __tablename__ = 'users' 
	...
    # 增加一个字段关系，User类通过Phone_number字段找到Phone_number类
    # 如果使用 back_populates 参数来代替 backref 参数，则Phone_number类需要被注释掉的那一行
    phone_number = db.relationship('Phone_number', backref='users')

class Phone_number(Base):
    __tablename__ = 'phone_numbers'
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))
    # user = relationship("User", back_populates="phone_numbers")

relationship函数的目的是让 phone_number类链接到User类，back_populates参数表示引用的互补属性名，也就是本身的表名。back_ref参数表示父表的表名。

ForeignKey即是外键，定义了两表的两列之间依赖关系，表示关联了表users的id和表phone_numbers。

需要注意：

• 一对多关系中，多的一方使用Foreignkey，一的一方使用relationship。

• 如果是一对一的对应关系，则relationship函数增加参数uselist=False即可。

（2）多对多

多对多关系可以举如下例子：建立一个用户与群组的表，一个用户对应多个群组，一个群组中也有多个用户。

需要引入第三张表作为中间表。代码如下：

# 中间表
class UserToGroup(Base):
    __tablename__ = "user_to_group"
    id = db.Column(db.Integer,primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"))
    group_id = db.Column(db.Integer, db.ForeignKey("groups.id"))
 
# 表users
class User(Base):
    __tablename__ = 'users'  # 设置表名
    id = Column(Integer, primary_key=True) # id列，类型是Integer，主键
	...
    group = db.relationship("Group", secondary="user_to_group", back_populates="user")
 
# 表groups
class Group(Base):
    __tablename__ = "groups"
    id = db.Column(db.Integer, primary_key=True)
    ...
    user = db.relationship("User", secondary="user_to_group", back_populates="group")

3.3 查询数据

3.3.1 通用的查询数据方法

以上面创建的数据表users（class User）为例。代码如下：

Session = sessionmaker(db)
session = Session()

User.query.<过滤方法（非必要）>.<返回方法>

# 提交数据，将数据保存到数据库中
session.commit()
# 关闭会话
session.close()

常用的过滤方法如下：

常用的返回方法如下：

3.3.2 过滤规则

有like过滤、is过滤、正则过滤等规则。

详细的过滤规则可以参考：《Python SQLalchemy 基础操作之数据库增删改查》或者《Python ORM之SQLAlchemy全面指南》的第十一节

3.4 向数据表中添加/删除/更改数据

以上面创建的数据表users（class User）为例。使用url网络请求数据。

3.4.1 添加数据

# 插入多条记录
obj = [User(name='张三', ...),
       User(name='李四', ...),
       User(name='王五', ...)]
# 创建会话
obj_session = sessionmaker(db)
# 打开会话
db_session = obj_session()
# 向表中添加数据,此时数据保存在内存中
db_session.add_all(obj)
# 提交数据，将数据保存到数据库中
db_session.commit()
# 关闭会话
db_session.close()

3.4.2 删除数据

Session = sessionmaker(db)
session = Session()
users = User.query.all()

# 删除满足给定条件'id=1'的第一条记录
user = session.query(User).filter_by(id=1).first()
session.delete(user)
    
# 删除满足给定条件'id=1'的所有记录
session.query(User).filter_by(id=1).delete()
    
# 删除所有记录
session.query(User).delete()
	
# 提交数据，将数据保存到数据库中
session.commit()
# 关闭会话
session.close()

3.4.3 更改数据

Session = sessionmaker(db)
session = Session()
users = User.query.all()

# 更新满足给定条件'id=1'的第一条记录
user = session.query(User).filter_by(id=1).first()
user.name = 'new name'

# 更新所有记录
session.query(User).update({'name': 'new name'})
 
# 提交数据，将数据保存到数据库中
session.commit()
# 关闭会话
session.close()

参考

[1] SQLAlchemy官网
[2] 《SQLAlchemy查询语句（新手入坑）》
[3] 《python—sqlalchemy简单使用方法，一对多，多对多，双向关系教程》
[4] 《Python SQLalchemy 基础操作之数据库增删改查》
[5] 《Python ORM之SQLAlchemy全面指南》