python SQLAlchemy操作

0 背景

在Python中，SQLAlchemy是一个强大的关系型数据库工具，它提供了高级的SQL表达和对象关系映射（ORM）功能，可以更加方便地与数据库进行交互。可以不使用SQL去操作数据库, 在爬虫 & Flask & Django中使用很多。以下介绍一些使用SQLAlchemy操作数据库的基础操作。

1 安装和导入

使用pip命令来安装它：

pip install sqlalchemy

以下例子会用到以下包, 可以提前导入:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker

2 连接数据库

使用create_engine 函数连接到不同类型的数据库, 以下是连接到mysql & sqlite 的栗子:

mysql

username = 'root'
password = 'root'
host = '172.20.118.242'
port = 3307
database_name = 'users'

engine = create_engine(f'mysql+pymysql://{username}:{password}@{host}:{port}/{database_name}')

sqlite

# 创建一个SQLite数据库引擎，数据库文件为example.db
engine = create_engine('sqlite:///example.db')

3 创建表

mysql

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(length=255))  # 指定长度为255
    email = Column(String(length=255))  # 指定长度为255
    
    def __repr__(self):
        return f"<User(id={self.id}, username={self.username}, email={self.email})>"

    def __str__(self):
        return f"<User(id={self.id}, username={self.username}, email={self.email})>"

Base.metadata.create_all(engine)

sqlite


# 创建一个基类
Base = declarative_base()

# 定义User模型类
class User(Base):
    # 数据库表的名称
    __tablename__ = 'users'  

    id = Column(Integer, primary_key=True)
    username = Column(String, nullable=False)
    email = Column(String, nullable=False)

    def __repr__(self):
        return f"<User(id={self.id}, username={self.username}, email={self.email})>"

    def __str__(self):
        return f"<User(id={self.id}, username={self.username}, email={self.email})>"

# 在数据库中创建User表
Base.metadata.create_all(engine)

Base.metadata.create_all(engine) 是SQLAlchemy中一个用于创建所有定义在 Base 中的数据表格（表格模式）的方法。

当使用 declarative_base() 创建一个 Base 类时，这个类会继承 SQLAlchemy 的 Base 类，它允许你自定义数据表的结构，并且它知道如何将这些定义翻译成 SQL 语句。但是，这些定义只存在于 Python 的对象中，并没有实际被映射到数据库中的数据表。

当你调用 Base.metadata.create_all(engine) 时，SQLAlchemy 将会检查在 Base 类中定义的所有数据表，并生成相应的 SQL 语句来在数据库中创建这些表格。

这个方法通常用于在程序启动时，或者在数据库结构有所变化时，比如我修改了表中的一个字段或者新增了一个字段, 它可以确保数据库中的表格与我的定义保持同步。

4 增删改查操作

创建`sessionmaker`对象

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

查询数据

查询所有用户

# 查询所有用户
users = session.query(User).all()

条件查询单个用户

# 条件查询
user = session.query(User).filter_by(name='John Doe').first()

查询前n个用户

# 查询前n个用户
users = session.query(User).limit(3).all()

模糊查询

# 查询开头是cookie_00的所有用户
users = session.query(User).filter(User.username.startswith('cookie_00')).all()

# 查询包含"cookie_"的所有用户
users = session.query(User).filter(User.username.like('%cookie_00%')).all()

增加数据

新增单个数据

# 新增单个数据
new_user = User(username='orange', email='of@orange.com')
session.add(new_user)
session.commit()

新增多个数据

# 新增多条数据
new_users = [User(username=f'cookie_{i:0>3}', email=f'cookie_{i:0>3}@orange.com') for i in range(100)]
session.add_all(new_users)
session.commit()

删除数据

删除单个数据

# 删除数据
user = session.query(User).filter_by(username='orange').first()
session.delete(user)
session.commit()

删除多条数据

# 删除多条数据
session.query(User).filter(User.username.like('%cookie_%')).delete(synchronize_session=False)
session.commit()

synchronize_session=False 参数告诉SQLAlchemy不要同步Session，这意味着不会自动刷新Session中的对象状态，而是直接在数据库中执行更新操作。

更新数据

更新单个数据

# 更新数据
user = session.query(User).filter_by(username='cookie_004').first()
user.email = 'hahaha@qq.com'
session.commit()

更新多个数据

# 更新多条数据
session.query(User).filter(User.username.startswith('cookie_01')).update({'email': '123456@qq.com'}, synchronize_session=False)
session.commit()