Sqlalchemy

SQLAlchemy 从入门到精通教程（以 MySQL 数据库为基础）

本教程将详细介绍如何使用 SQLAlchemy 操作 MySQL 数据库，从基础的连接配置到复杂的 ORM（对象关系映射）操作，再到数据库迁移与项目实战。通过本教程，您将掌握如何高效地在 Python 中通过 SQLAlchemy 操作 MySQL 数据库。

目录

1. SQLAlchemy 简介
2. 环境搭建与安装（MySQL）
3. SQLAlchemy 核心组件
4. 基础使用：ORM 模式
5. MySQL 数据库操作详解
6. 表设计与管理
7. 高级查询和操作
8. 数据库迁移与管理（Alembic）
9. 实战项目：博客系统

---

1. SQLAlchemy 简介

SQLAlchemy 是一个 Python 中强大的 SQL 工具包和 ORM 库，支持多种数据库（如 MySQL、PostgreSQL、SQLite 等）。它的核心是一个数据库连接引擎（Engine），以及通过对象关系映射（ORM）让开发者以面向对象的方式操作数据库。

---

2. 环境搭建与安装（MySQL）

2.1 安装 MySQL 数据库

首先需要确保安装了 MySQL 数据库。以 CentOS 为例：

sudo yum install mysql-server
sudo systemctl start mysqld
sudo systemctl enable mysqld

2.2 安装 SQLAlchemy 和 MySQL 驱动

在 Python 中通过 pymysql 驱动连接 MySQL，因此除了 SQLAlchemy，还需要安装 MySQL 驱动程序 pymysql。

pip install sqlalchemy pymysql

2.3 配置 MySQL 数据库连接

首先登录 MySQL，创建一个数据库和用户：

CREATE DATABASE testdb;
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';
FLUSH PRIVILEGES;

---

3. SQLAlchemy 核心组件

SQLAlchemy 核心组件包括：

• Engine：用于数据库连接，负责执行 SQL 语句。
• Session：提供一个“工作区”，用于处理所有的 ORM 操作。
• Base：所有 ORM 模型类都继承自 Base。
• ORM：提供了对象关系映射功能，帮助将类映射到数据库表中。

---

4. 基础使用：ORM 模式

4.1 创建数据库连接

创建一个 database.py 文件来管理数据库引擎和会话：

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# MySQL 数据库 URL
SQLALCHEMY_DATABASE_URL = "mysql+pymysql://testuser:password@localhost/testdb"

# 创建引擎
engine = create_engine(SQLALCHEMY_DATABASE_URL)

# 创建会话工厂
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 基类，用于模型的创建
Base = declarative_base()

4.2 定义数据库模型

在 models.py 文件中定义与数据库表对应的模型类。例如创建 User 模型来表示用户表：

from sqlalchemy import Column, Integer, String
from .database import Base

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, index=True)
    email = Column(String(100), unique=True)
    password = Column(String(100))

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

4.3 创建数据库表

通过 Base.metadata.create_all() 创建数据库中的表：

from .database import engine
from .models import Base

Base.metadata.create_all(bind=engine)

运行这段代码后，会在 MySQL 的 testdb 数据库中创建 users 表。

4.4 基本的 CRUD 操作

插入数据

from .database import SessionLocal
from .models import User

# 创建会话
db = SessionLocal()

# 插入新用户
new_user = User(username="johndoe", email="john@example.com", password="password123")
db.add(new_user)
db.commit()  # 提交事务
db.refresh(new_user)  # 获取用户 ID
print(new_user.id)

查询数据

user = db.query(User).filter(User.username == "johndoe").first()
print(user.email)

更新数据

user = db.query(User).filter(User.username == "johndoe").first()
user.email = "newemail@example.com"
db.commit()  # 提交修改

删除数据

user = db.query(User).filter(User.username == "johndoe").first()
db.delete(user)
db.commit()  # 提交删除

---

5. MySQL 数据库操作详解

SQLAlchemy 提供了对 MySQL 数据库的完备支持。下面列出一些常用的 MySQL 数据库操作。

5.1 MySQL 数据类型

SQLAlchemy 支持常见的 MySQL 数据类型，以下是常用的数据类型：

• Integer：整数类型。
• String(size)：字符串类型，需指定最大长度。
• Text：大文本字段。
• DateTime：日期时间类型。
• Boolean：布尔类型。
• Float：浮点类型。

from sqlalchemy import Column, Integer, String, Float, Boolean

class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    price = Column(Float)
    available = Column(Boolean, default=True)

5.2 外键与关系

通过 ForeignKey 和 relationship() 定义表之间的关系。例如，用户和帖子（User 和 Post）是一对多关系。

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship

class Post(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    title = Column(String(100), nullable=False)
    content = Column(String(500))
    owner_id = Column(Integer, ForeignKey('users.id'))

    owner = relationship("User", back_populates="posts")

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True)
    posts = relationship("Post", back_populates="owner")

---

6. 表设计与管理

SQLAlchemy 允许非常灵活的表设计。以下是一些常见的设计模式。

6.1 一对多关系

一个用户有多篇帖子，一个帖子属于一个用户，这是一对多的关系。在 SQLAlchemy 中通过 ForeignKey 和 relationship() 来实现。

6.2 多对多关系

多对多关系可以通过一个中间表实现，例如用户和角色之间的关系。

from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship

# 中间表
user_roles = Table('user_roles', Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('role_id', Integer, ForeignKey('roles.id'))
)

class Role(Base):
    __tablename__ = 'roles'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True)

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True)

    roles = relationship("Role", secondary=user_roles, back_populates="users")

Role.users = relationship("User", secondary=user_roles, back_populates="roles")

---

7. 高级查询和操作

7.1 联合查询

使用 join() 来进行表之间的联合查询：

posts = db.query(Post).join(User).filter(User.username == "johndoe").all()
for post in posts:
    print(post.title, post.content)

7.2 分组与聚合查询

使用 func 进行聚合操作，例如统计帖子数量：

from sqlalchemy import func

post_count = db.query(func.count(Post.id)).scalar()
print(post_count)

7.3 分页查询

使用 offset() 和 limit() 实现分页查询：

posts = db.query(Post).offset(0).limit(10).all()  # 查询前 10 条记录

---

8. 数据库迁移与管理（Alembic）

SQLAlchemy 通常结合 Alembic 来进行数据库迁移和版本管理。

8.1 安装 Alembic

pip install alembic

8.2 初始化 Alembic

alembic init alembic

8.3 配置数据库连接

修改 alembic.ini 文件，将数据库连接配置为 MySQL：

sqlalchemy.url = mysql+pymysql://testuser:password@localhost/testdb

8.4 生成迁移脚本

alembic revision --autogenerate -m "Initial migration"

8.5 应用迁移

alembic upgrade head

---

9. 实战项目：博客系统

通过以上知识，可以搭建一个简单的博客系统。此项目包含用户、帖子、评论等模块，并通过 SQLAlchemy 进行 MySQL 数据库操作和管理。

---

通过此详细的 SQLAlchemy 教程，您将学会如何使用 SQLAlchemy 操作 MySQL 数据库，并能够在实际开发中灵活应用这些技术。