Python ORM框架：SQLAlchemy的使用

前言：简介ORM

什么是ORM？它能带给我们什么优势？

ORM：Object对象，Relations关系、Mapping映射。简称：对象关系映射

对象关系映射是通过面向对象的方式来操作数据库，这就需要对应的关系映射，数据中可以分为库，表，字段信息，一条条数据，而需要用面向对象的关系去对应。于是就有了下面对应关系。

数据库 ======= 面向对象模型
表 ======= 类
字段 ======= 类属性
记录 ======= 每个实例

ORM：目的就是为了能够让不熟悉SQL语句的人通过面向对象也能够轻松自如的操作数据库。

一、SQLAlchemy

---

SQLAlchemy是一个Python的ORM框架，使用它可以通过面向对象的形式来操作数据库。

首先通过pip安装SQLAlchemy：

pip install sqlalchemy

2.1 创建表

此时我们就可以使用SQLAlchemy来创建表了

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import relationship

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey

# 创建对象的基类
Base = declarative_base()

# create_engine就是去建立连接，相当于我们pymsql建立连接的时候 conn= pymysql.connect(...)
conn = create_engine(
    "mysql+pymysql://用户名:密码@127.0.0.1:3306/连接的数据库名称?charset=utf8mb4",
    max_overflow=0,  # 超过连接池大小外最多创建的连接数
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 连接池中没有线程最多等待时间，否则报错
    pool_recycle=-1,  # 多久之后对连接池中的连接进行回收（重置）-1不回收
)


class User(Base):

    # 创建表时的名称
    __tablename__ = 'users'

    # Column类创建字段，第一个参数：字段类型。 其它参数
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    age = Column(Integer)

    def __str__(self):
        return self.name

# 一对多关系
class Hobby(Base):
    __tablename__ = 'hobby'
    id = Column(Integer, primary_key=True)
    caption = Column(String(50), default='篮球')

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    # hobby指的是tablename而不是类名，uselist=False
    hobby_id = Column(Integer, ForeignKey("hobby.id"))

    # 跟数据库无关，不会新增字段，只用于快速链表操作
    # 类名，backref用于反向查询
    hobby = relationship('Hobby', backref='pers')
    

class Boy2Girl(Base):
    __tablename__ = 'boy2girl'
    id = Column(Integer, primary_key=True, autoincrement=True)
    girl_id = Column(Integer, ForeignKey('girl.id'))
    boy_id = Column(Integer, ForeignKey('boy.id'))


class Girl(Base):
    __tablename__ = 'girl'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)


class Boy(Base):
    __tablename__ = 'boy'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), unique=True, nullable=False)

    # 与生成表结构无关，仅用于查询方便,放在哪个单表中都可以
    girls = relationship('Girl', secondary='boy2girl', backref='boys')

def create_db():
    # 创建所有继承了Base的表，参数：指定连接的数据库
    Base.metadata.create_all(conn)


def drop_db():
    # 删除所有使用create_all创建的表
    Base.metadata.drop_all(conn)

create_db()

调用create_db()就可以创建我们继承Base类的所有表了

2.2 操作数据库

此时我们已经创建完表之后就可以对数据库进行增、删、改、查等操作了。

详细代码如下：

from sqlalchemy.orm import sessionmaker

from sqlalchemy import create_engine

from models import User, Hobby, Person, Boy2Girl, Girl, Boy

# create_engine就是去建立连接，相当于我们pymsql建立连接的时候 conn= pymysql.connect(...)
conn = create_engine(
    "mysql+pymysql://用户名:密码@127.0.0.1:3306/连接的数据库名称?charset=utf8mb4",
    max_overflow=0,  # 超过连接池大小外最多创建的连接数
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 连接池中没有线程最多等待时间，否则报错
    pool_recycle=-1,  # 多久之后对连接池中的连接进行回收（重置）-1不回收
)

Session = sessionmaker(bind=conn)

session = Session()  # 调用数据库需要实例化Session

# 新增数据
user = User(name='jack') # 先创建一个对象
session.add(user) # 添加进入会话内

session.add_all([ # 添加多个对象
    User(name='jack',age=18),
    User(name='tom',age=19)
])

session.commit() # 将添加到会话的内容提交到数据库


# 查询
res = session.query(User).filter(User.id == 2).first() # 获取第一个元素

# filter_by中使用关键字
res = session.query(User).filter_by(name='tom').all()  # 获取User表内所有name字段值为tom的内容
# filter_by内关键字匹配内容只能使用=，不能使用其它特殊符号


# 更新
# session.query(User).filter(User.id == 1).update({'name':'jack000'}) # 修改name字段值
session.commit() # 涉及到操作表内的数据，需要commit提交事务

# 删除
session.query(User).filter_by(name='jack000').delete()
session.commit()

# 在原来的基础上进行更新
session.query(User).filter_by(name='tom').update({'name':User.name + "101010"})
session.commit()

# 也可以使用对象属性作为key（只要能和数据库字段对应上即可）
session.query(User).filter_by(name='tom101010').update({User.age: User.age + 10})
session.commit()


# 一对多关系操作

# 新增
hobby = Hobby(caption='篮球')
person = Person(name='jack',hobby_id=1)
person2 = Person(name='tom',hobby_id=1)
session.add_all([hobby,person,person2])
session.commit()

# 查询（基于对象的跨表查询，需要借助于relationship）

person = session.query(Person).filter_by(nid=1).first()
print(person.hobby_id)
print(person.hobby.caption)

# 反向查询（查询选择的篮球作为兴趣的所有人）
hobby = session.query(Hobby).filter_by(caption='篮球').first()
print(hobby.pers) # 根据Person内的backref值反向查询到person表

# 多对多关系操作

girl = Girl(name='rouse')
boy = Boy(name='Jack')
boy2 = Boy(name='James')
session.add_all([girl,boy,boy2])
session.commit()

# 新增关联数据
session.add_all([
    Boy2Girl(boy_id=1, girl_id=1),
    Boy2Girl(boy_id=2, girl_id=1),
])
session.commit()

# 通过girls字段查询到关联的girl
jack = session.query(Boy).filter_by(name='jack').first()
print(jack.girls[0].name)

# 通过relationship的backrf值反向查询到boy
rouse = session.query(Girl).filter_by(name='rouse').first()
print(rouse.boys[0].name)

2.3 常用api查询

SQLAlchemy提供给了我们可以多种条件查询的api

# 常用api，查询
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

from models import User, Hobby, Person, Boy2Girl, Girl, Boy

# 1、创建engine对象
conn = create_engine(
    "mysql+pymysql://用户名:密码@127.0.0.1:3306/连接的数据库名称?charset=utf8mb4",
    max_overflow=0,  # 超过连接池大小外最多创建的连接数
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 连接池中没有线程最多等待时间，否则报错
    pool_recycle=-1,  # 多久之后对连接池中的连接进行回收（重置）-1不回收
)

# 2 生成一个session对象
Session = sessionmaker(bind=engine)
session = Session()  # 就是一个连接对象，就是一个conn

# 　条件
ret = session.query(User).filter_by(name='jack').all()

# 表达式，and条件连接
ret = session.query(User).filter(User.id > 1, User.name == 'jack').all()

ret = session.query(User).filter(User.id.between(7, 9), User.name == 'jack').all()
# #注意下划线
ret = session.query(User).filter(User.id.in_([1, 3, 4, 6])).all()

# #~非，除。。外
ret = session.query(User).filter(~User.id.in_([1, 3, 4])).all()

# #二次筛选
ret = session.query(User.id, User.name).filter_by(name='jack').all()

ret = session.query(User).filter(User.id.in_(session.query(User.id).filter_by(name='jack')))

from sqlalchemy import and_, or_

# or_包裹的都是or条件，and_包裹的都是and条件
ret = session.query(User).filter(and_(User.id > 3, User.name == 'jack')).all()
ret = session.query(User).filter(or_(User.id > 15, User.name == 'jack')).all()
ret = session.query(User).filter(
    or_(
        User.id < 2,
        and_(User.name == 'jack', User.id > 3),
        User.extra != ""
    ))

# 通配符，以e开头，不以e开头
ret = session.query(User).filter(User.name.like('a%'))
ret = session.query(User).filter(~User.name.like('l%')).all()
#
# 限制，用于分页，区间
ret = session.query(User)[1:2]

# 排序，根据name降序排列（从大到小）
ret = session.query(User).order_by(User.name.desc()).all()
# 第一个条件重复后，再按第二个条件升序排
ret = session.query(User).order_by(User.name.desc(), User.id.asc()).all()
#
# 分组
from sqlalchemy.sql import func


ret = session.query(User.name).group_by(User.name).all()

# 分组之后取最大id，id之和，最小id
ret = session.query(
    User.name,
    func.max(User.id),
    func.sum(User.id),
    func.min(User.id)).group_by(User.name).all()

# select name,max(id),sum(id),min(id) from User group by name;
# haviing筛选

ret = session.query(
    func.max(User.id),
    func.sum(User.id),
    func.min(User.id)).filter(User.id > 1).group_by(User.name).having(func.min(User.id) > 2).all()

# 使用SQL语句查询：
# select name,max(id),sum(id),min(id) as min_id from User where id >1 group by name having min_id>2;


# 连表（默认用forinkey关联）

ret = session.query(Hobby.id, Hobby.caption, Person.name).filter(Hobby.id == Person.hobby_id).all()

ret = session.query(Hobby, Person).filter(Hobby.id == Person.hobby_id).all()

# select * from hobby,person where hobby.id=person.hobby_id;

# join表，默认是inner join
ret = session.query(Person).join(Hobby)

# select  * from Person inner join Hobby on Person.hobby_id=Hobby.id;


# isouter=True 外连，表示Person left join Favor，没有右连接，反过来即可
ret = session.query(Person).join(Hobby, isouter=True)
# 如果不指定isouter表示inner join，默认使用外键字段关联
# 如果isouter=True，就是left join
# 如果想实现right join 反过来写ret = session.query(Hobby).join(Person, isouter=True)


# 自己指定on条件（连表条件）,第二个参数，支持on多个条件，用and_,同上
ret = session.query(Person).join(Hobby, Person.nid == Hobby.id, isouter=True)

print(ret)

如果本文对您有帮助，别忘一键3连，您的支持就是笔者最大的鼓励，感谢阅读！

技术小白记录学习过程，有错误或不解的地方请指出，如果这篇文章对你有所帮助请点赞 收藏+关注 子夜期待您的关注，谢谢支持！