SQLAlchemy 是 Python 中最流行的 ORM(对象关系映射)框架之一,它提供了高效且灵活的数据库操作方式。在实际项目中,掌握 SQLAlchemy ORM 能显著提升数据层的开发效率。
安装
pip install sqlalchemy
如果连接特定数据库,还需安装相应的驱动程序:
# PostgreSQL
pip install psycopg2-binary
# MySQL
pip install mysql-connector-python
# SQLite (Python 标准库已包含,无需额外安装)
核心概念
理解这几个核心组件有助于快速上手:
- Engine:数据库连接的引擎,负责与数据库通信
- Session:数据库会话,管理所有持久化操作
- Model:数据模型类,对应数据库中的表
- Query:查询对象,用于构建和执行数据库查询
连接数据库
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
# 创建数据库连接引擎
# SQLite 示例
engine = create_engine('sqlite:///example.db', echo=True)
# PostgreSQL 示例
# engine = create_engine('postgresql://username:password@localhost:5432/mydatabase')
# MySQL 示例
# engine = create_engine('mysql+mysqlconnector://username:password@localhost:3306/mydatabase')
# 创建会话工厂
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
# 创建会话实例
session = SessionLocal()
定义数据模型
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, declarative_base
# 创建基类
Base = declarative_base()
class User(Base):
__tablename__ =
= Column(Integer, primary_key=, index=)
name = Column(String(), nullable=)
email = Column(String(), unique=, index=)
posts = relationship(, back_populates=)
():
__tablename__ =
= Column(Integer, primary_key=, index=)
title = Column(String(), nullable=)
content = Column(String())
author_id = Column(Integer, ForeignKey())
author = relationship(, back_populates=)
tags = relationship(, secondary=, back_populates=)
():
__tablename__ =
= Column(Integer, primary_key=, index=)
name = Column(String(), unique=, nullable=)
posts = relationship(, secondary=, back_populates=)
():
__tablename__ =
post_id = Column(Integer, ForeignKey(), primary_key=)
tag_id = Column(Integer, ForeignKey(), primary_key=)

