SQLAlchemy备忘录

Python, 数据库

前言

SQLALchemy是Python社区中著名的ORM库，由于本职工作是前端，Python相关技术栈不常用到，经常性的会造成遗忘，所以记下这篇文章，已备忘速查

以下代码演示环境为:

  • 数据库: MySQL5.7
  • 操作系统: Windows10
  • 开发工具: PyCharm2019.3.2
  • Python3.8

安装

1
2
3
4
5
# 安装sqlalchemy
pip install sqlalchemy

# 安装对应的数据库驱动
pip install mysql-connector-python

引入对应的模块

1
2
3
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import Column, String, Integer, Text, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base

连接数据库并创建session

1
2
3
4
5
6
# 创建连接
engine = create_engine('mysql+mysqlconnector://<username>:<password>@<host>:<port>/<db>')
# 创建会话类
Session = sessionmaker(bind=engine)
# 创建会话
session = Session()

创建表的class定义

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Base = declarative_base()

class Vod(Base):
    # 表名
    __tablename__ = 'vod'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    
    title = Column(String(200))
    
    cover = Column(Text)
    
    intro = Column(Text)

class Actor(Base):
    # 表名
    __tablename__ = 'actor'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    
    name = Column(String(40))
    
    avatar = Column(Text)

创建表(如果表已经存在，不会重新创建覆盖)

1
Base.metadata.create_all(engine)

删除表

1
Base.metadata.drop_all(engine)

基本CRUD

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
def get_vod(vid):
    return session.query(Vod).filter_by(id=vid).first()


def insert_vod(vod):
    # add方法仅仅是新建，如果实例的主键值已经存在于表中，则会报错，不会去更新
    session.add(vod)
    session.commit()
    return vod


def delete_vod(vod):
    # 以一个实例对象为参数，通过主键字段来判断表中是否存在这条记录
    session.delete(vod)
    session.commit()


def update_vod(vod):
    # merge方法会通过主键值来判断当前实例是否存在于表中，如果存在，则更新; 否则新建
    session.merge(vod)
    session.commit()
    # 或者先通过查询获取到需要更新的对象，然后更新这个对象并提交
    my_vod = session.query(Vod).filter_by(id=vod.id).first()
    if my_vod:
        my_vod.name = vod.name
        session.commit()

创建多对多关系

创建多对多关系，需要通过中间表实现

1
2
3
4
5
6
7
8
9
class Vod2Actor(Base):
    # 表名
    __tablename__ = 'vod2actor'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    
    vod_id = Column(Integer, ForeignKey('vod.id'))
    
    actor_id = Column(Integer, ForeignKey('actor.id'))

使用ForeignKey创建外键, 外键所在的表为子表，所以Vod2Actor为子表，Vod和Actor为父表
ForeignKey接受一个字符串作为参数，参数为’<__tablename__>.‘

创建relationship

relationship是sqlalchemy.orm提供的对关系之间的一种便利调用方式。

创建relationship不是必须的。假设现在已知一个Vod.title的值，想要知道这个Vod.actors的值，如果不通过relationship，则需要这样操作

1
2
3
4
5
6
7
def get_actors_by_vod_title(title):
    vod = session.query(Vod).filter_by(title=title).first()
    if vod:
        vod2Actors = session.query(Vod2Actor).filter_by(vod_id=vod.id).all()
        actors = [session.query(Actor).filter_by(id=item.actor_id).first() for item in vod2Actors]
        return actors
    return None

如果在Vod中加上relationship, 则操作就会变得非常简单

1
2
3
4
5
6
7
8
9
10
11
class Vod(Base):
    # ......
    
    # 第一个参数为类名，第二个secondary为中间表的表名
    actors = relationship('Actor', secondary='vod2actor')

def get_actors_by_vod_title_with_relationship(title):
    vod = session.query(Vod).filter_by(title=title).first()
    if vod:
        return vod.actors
    return None

relationship还有一个backref参数，它提供了反向操作的能力, 当设置后，
actor也可以直接查询与其对应的vods的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
class Vod(Base):
    # ......
    
    # backref为一个字符串，设置后，actor便会多一个以这个字符串为名的属性
    # 除了backref，还有一个back_populates, 两者功能一样，backref是back_populates的高级版，使用更简单方便
    # backref只需单向声明，而back_populates则需要双向声明
    # 以vod和actor为例,backref只需要在Vod或Actor其中一个的relationship中声明，而back_populates则需要在Vod和Actor的relationship中都声明
    actors = relationship('Actor', secondary='vod2actor', backref='vods')

def get_vods_by_actor_name_with_backref(name):
    actor = session.query(Actor).filter_by(name=name).first()
    if actor:
        return actor.vods
    return None

创建一对多关系

创建一对多关系，只需要在子表中设置外键, 外键的值为父表的主键值， 不需要额外建立中间表；多对多关系本质上就是通过两张表与中间表建立一对多关系实现的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
class Vod(Base):
    # ...
    downloads = relationship('Download', backref="vod");

class Download(Base):
    # 表名
    __tablename__ = 'download'

    id = Column(Integer, primary_key=True, autoincrement=True)

    url = Column(Text)

    # 迅雷/磁力/百度云
    type = Column(Text(20))
    
    vod_id = Column(Integer, ForeignKey('vod.id'))

创建一对一关系

创建一对一关系，和创建一对多关系方法一致，只是在通过relationship进行快捷操作时，需要设置uselist=False, 使play_bilibili不是一个列表

1
2
3
4
class Vod(Base):
    # ...
    # 默认uselist=True, 通过play_bilibili获取到的值是一个列表
    play_bilibili = relationship('Play', uselist=False, backref='vod');

Demo源码

https://github.com/demo-box/sqlalchemy-demo.git