SQLAlchemy实战指南：从基础查询到原生SQL操作

一、模型定义与数据关系

1.1 学生模型定义

class Student(db.Model):
    """学生信息表模型"""
    __tablename__ = "tb_student"
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    sex = db.Column(db.Boolean, default=True)
    age = db.Column(db.SmallInteger)
    classes = db.Column("class", db.SMALLINT)
    description = db.Column(db.Text)
    status = db.Column(db.Boolean, default=1)
    addtime = db.Column(db.DateTime, default=datetime.now)
    orders = db.Column(db.SMALLINT, default=1)

    def __repr__(self):
        return f"<{self.__class__.__name__} {self.name}>"

    def todict(self):
        """模型转字典方法"""
        return {
            "id": self.id,
            "name": self.name,
            "classes": self.classes,
            "age": self.age,
        }

二、查询操作全解析

2.1 基础查询方法

# 获取全部数据
students = db.session.query(Student).all()

# 获取第一条记录
student = db.session.query(Student).first()

2.2 过滤条件查询

精确查询（filter_by）

# 单条件查询
students = db.session.query(Student).filter_by(name="小明1号").all()

# 多条件AND查询
students = db.session.query(Student).filter_by(sex=1, age=18).all()

灵活查询（filter）

# 比较运算符
students = db.session.query(Student).filter(Student.age > 17).all()

# IN查询
students = db.session.query(Student).filter(Student.id.in_([1, 3, 4])).all()

# 组合查询（AND/OR）
from sqlalchemy import or_, and_

# OR条件查询
students = db.session.query(Student).filter(
    or_(Student.classes==303, Student.classes==302)
).all()

# 复杂组合查询
students = db.session.query(Student).filter(
    and_(
        Student.classes == 305,
        or_(
            and_(Student.age == 18, Student.sex == 1),
            and_(Student.age == 17, Student.sex == 2)
        )
    )
).all()

2.3 结果集控制

# 限制结果数量
student_list = db.session.query(Student).limit(3).all()

# 结果排序
student_list = db.session.query(Student).order_by(
    Student.classes.asc(), 
    Student.age.desc()
).all()

三、数据更新与删除

3.1 更新数据

student = db.session.query(Student).get(6)
student.age = 16
student.classes = 301
db.session.commit()

3.2 删除数据

student = db.session.query(Student).get(6)
db.session.delete(student)
db.session.commit()

四、原生SQL操作实践

4.1 查询操作

# 执行原生查询
cursor = db.session.execute("SELECT * FROM tb_student")

# 获取结果的不同方式
student = cursor.mappings().fetchone()          # 单条记录
student_list = cursor.mappings().fetchmany(2)   # 指定数量
all_students = cursor.mappings().fetchall()     # 全部结果

4.2 写入操作

sql = """INSERT INTO tb_student 
        (name, class, age, sex, description) 
        VALUES (:name, :class, :age, :sex, :description)"""

data = {
    "class": 305,
    "age": 19,
    "name": "xiaohong",
    "sex": 0,
    "description": "优秀学生",
}

cursor = db.session.execute(sql, params=data)
db.session.commit()
print(f"新增记录ID：{cursor.lastrowid}")

五、最佳实践与注意事项

1. 事务管理

try:
    db.session.begin()
    # 数据库操作...
    db.session.commit()
except:
    db.session.rollback()
    raise

2. 性能优化
   • 批量操作使用bulk_save_objects()
   • 频繁查询考虑缓存机制
   • 复杂查询优先使用原生SQL

3. 连接池配置建议

engine = create_engine(
    url="mysql+pymysql://user:pass@host/db",
    pool_size=10,
    max_overflow=20,
    pool_recycle=3600
)

通过掌握这些核心操作，开发者可以：
• 快速构建企业级应用的数据库层
• 实现复杂的业务查询需求
• 在ORM和原生SQL之间灵活切换
• 保障数据库操作的安全性和性能