sqlalchemy 使用要点
基本应用
from sqlalchemy import create_engine
from sqlalchemy import Column, String, Integer, Float, Text, ForeignKey
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
engine = create_engine('sqlite:///./test2.db')
# Base 对象必须在声明表结构之前创建
Base = declarative_base()
class Author(Base):
__tablename__ = 'author'
id = Column(Integer, primary_key=True)
name = Column(String(64), unique=True)
phone = Column(String(64))
# 在“一对多”的“一”端定义关系，不会生成字段写入数据库，relationship 的参数是 类名
books = relationship('Book')
# 定义 print(对象实例) 的行为
def __repr__(self):
return f'{self.__class__.__name__}, id:{self.id} name:{self.name} phone:{self.phone}\n'
class Book(Base):
__tablename__ = "books" # __tablename__ 声明表名
# primary_key为主键,autoincrement为自增, doc 为注释但是不会在数据库中生成
id = Column(Integer, primary_key=True, autoincrement=True, doc="主键")
title = Column(String(64), index=True, doc="书名") # unique=True 为唯一约束会在数据库中生成索引
body = Column(Text)
# 在“一对多”的“多”端建立外键，是真实存在的字段，ForeignKey的参数是 表名.字段名
author_id = Column(Integer, ForeignKey('author.id'))
# 以类引用的形式定义外键，更直观，但要求 Author 类必须在此前定义，否则会导致引用异常
author_id = Column(Integer, ForeignKey(Author.id))
def __repr__(self):
return "Book(id=%s,title=%s,body=%s, author_id=%s)\n" % (self.id, self.title, self.body, self.author_id)
# 创建表结构
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()
# 插入数据，创建实例化对象
a1 = Author(name='李白', phone='12345')
a2 = Author(name='金庸', phone='67890')
book1 = Book(title='射雕英雄传', body='郭靖和黄蓉的故事')
book2 = Book(title='神雕侠侣', body='杨过和小龙女的故事')
book3 = Book(title='倚天屠龙记', body='张无忌和赵敏的故事')
# session.add 会自动将对象实例插入到相应表中
session.add(a1)
session.add(a2)
session.add(book1)
session.add(book2)
session.add(book3)
session.commit()
# 查询数据
book = session.get(Book, 2) # 根据表名和主键取得指定记录，如未找到返回 None
books = session.query(Book) # query 中指定类名，返回所有列
books = session.query(Book.id, Book.Title) # 返回指定列
book_list = books.all() # 返回包含所有记录的列表
book = books.firse() # 返回第一条记录，如果未找到则返回 None
book = books.one() # 如果只有一条记录则返回该记录，如果条数大于或小于1，则抛出异常
iCount = books.count() # 返回记录条数
# 过滤数据
special_books = session.query(Book).filter(Book.title=='碧血剑')
special_books = session.query(Book).filter(Book.title.like('%foo%'))
special_books = session.query(Book).filter(Book.id>5)
# IN
special_books = session.query(Book).filter(Book.id.in_([1,2,3])) # 注意是 in_
# NOT IN
special_books = session.query(Book).filter(~Book.id.in_([1,2,3])) # 在类名前加 ~
# 多条件过滤
from sqlalchemy import and_, or_
special_books = session.query(Book).filter(and_(Book.id>1, Book.title.like('%am%')))
special_books = session.query(Book).filter(or_(Book.id>1, Book.title.like('%am%')))
# 文本化过滤
from sqlalchemy import text
# 在 filter 中使用 text 函数传入条件文本
users = session.query(User).filter(text("id<3"))
# 带参数的条件文本
users = session.query(User).filter(text("id<:value and name=:name")).params(value=5, name='fred').order_by(User.id).one()
# 通过 from_statement() 方法传入 SQL 语句查询
u = session.query(User).from_statement(text("SELECT * FROM users where name='ed' ")).one()
# 排序
books = session.query(Book).order_by(Book.id) # 升序
books = session.query(Book).order_by(-Book.id) # 降序
# 统计函数及分组
from sqlalchemy import func
# func('字段名').label('显示名') 相当于对象的一个列
results = sessin.query( Person.school, func.count('*').label('c') ).filter(Person.gender=='male').group_by(Person.age)
# 等价 SQL 为 SELECT school, COUNT(*) AS c FROM persons WHERE gender="male" GROUP BY age
# SELECT school, COUNT(*) AS c FROM persons WHERE gender="male" GROUP BY age HAVING c > 10
nums = func.count('*').label('c')
results = sessin.query(Person.school, nums).filter(Person.gender=='male').group_by(Person.age).having(nums > 10)
# 更新数据
book = session.get(Book, 2)
book.Title = '神雕侠侣'
session.commit()
# 删除数据
book = session.get(Book, 2)
session.delete(book)
session.commit()
多表关联
单向关系
- 定义关系
class Author(Base):
# 在“一对多”的“一”端定义关系，不会生成字段写入数据库，建立单向关系时 relationship 的参数是 类名
books = relationship('Book')
class Book(Base):
# 在“一对多”的“多”端建立外键，是真实存在的字段，ForeignKey的参数是 表名.字段名
author_id = Column(Integer, ForeignKey('author.id'))
- 建立关系
# 直接赋值
book1.author_id = 2
book1.author_id = None
# 操作关系
author1.books.append(book2)
author1.books.remove(book2)
双向关系
class Writer(Base):
__tablename__ = 'writer'
# 关联的双方表中都建立关系，双向关系中 relationship 需要两个参数：对方类名，back_populates 指向对方表中的关系名称
# 为外键列提供主键的是“一”的一侧，其 relationship 返回的是集合属性，命名时使用复数形式
books = relationship('Book', back_populates='writer')
# 如果希望对通过关系查得的 books 排序 可以在关系定义中加入 order_by
books = relationship('Book', back_populates='writer', order_by='Book.name, Book.publishdate')
# 建立一对一关系时，需要在主键提供方（未定义外键的一方）的关系定义中，使用 uselist=False强制其返回单一量而不是集合，此时关系应命名为单数形式
book = relationship('Book', back_populates='writer', uselist=False)
class Book(Base):
__tablename__ = 'book'
# 在“多”的一侧表中定义外键
writer_id = Column(Integer, ForeignKey('writer.id'))
# 关联的双方表中都建立关系，双向关系中 relationship 需要两个参数：对方类名，back_populates 指向对方表中的关系名称
# 定义外键的模型是“多”的一侧，其 relationship 返回的是单一量，命名时不用复数形式
writer = relationship('Writer', back_populates='books')
# 通过关系查询关联信息
aBook.writer
aWriter.books
多对多关联中的双向关系
# 中间表，必须在两个基本表之前定义
mid_table = Table(
# 中间联结表物理表名，将保存到数据库中
'association',
Base.metadata,
# 在中间表中定义外键列，多对多关系中，外键只存在于中间表，双方表中都不建立外键
Column('student_id', Integer, ForeignKey('student.id')),
Column('teacher_id', Integer, ForeignKey('teacher.id')))
class Student(Base):
__tablename__ = 'student'
# 定义关系，relationship 的参数分别是：对方类名，中间表模型名（非物理表名），对方关系名
teachers = relationship('Teacher', secondary=mid_table, back_populates='students')
class Teacher(Base):
__tablename__ = 'teacher'
# 定义关系，relationship 的参数分别是：对方类名，中间模型名（非物理表名），对方关系名
students = relationship('Student', secondary=midtea_table, back_populates='teachers')
内联查询及列别名
class Writer(Base):
name = Column(String())
class Book(Base):
name = Column(String())
writer_id = Column(Integer, ForeignKey('writer.id'))
# 两表中需要返回的列都写在 query 中，可以使用 label 函数为其设置别名；如果已经设定了外键，join 后面的 Writer.id==Book.writer_id 可以省略
ret = session.query(Book.name.label('BookName'), Writer.name.label('WriterName')).join(Writer, Writer.id==Book.writer_id).all()
# 通过别名引用返回的列
print(ret[0].BookName)
print(ret[0].WriterName
子查询
将子查询按照传统的方式写好查询代码，然后在query对象后面执行subquery方法，将这个查询变成一个子查询。
在子查询中，将以后需要用到的字段通过label方法，取个别名。
在父查询中，要使用子查询的字段，通过子查询的返回值上的c属性拿到（c=Column）。
stmt = session.query(User.city.label('city'),User.age.label('age')).filter(User.uname == '一哥').subquery()
result = session.query(User).filter(User.city == stmt.c.city, User.age == stmt.c.age).all()
表别名
当多表关联查询的时候，有时候同一个表要用到多次，这时候用别名就可以方便的解决命名冲突的问题了
from sqlalchemy.orm import aliased
a1 = aliased(User)
a2 = aliased(User)
for uname, age1, age2 in session.query(User.uname,a1.age, a2.age).join(a1, User.id==a1.id).join(a2, a1.id==a2.id).all():
print(uname, age1, age2)
计算列和自定义方法
计算列与自定义方法是作用于数据的每一行，相当于表的一列，不同之处在于自定义方法可以接收输入的参数。
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.ext.hybrid import hybrid_method
class Author(Base):
__tablename__ = 'author'
id = Column(Integer, primary_key=True)
@hybrid_property
def newid(self) -> int:
return self.id * 3
@hybrid_method
def testid(self, value: int) -> bool:
# 如果传入的 value 在 id 和 3*id 之间则返回 True
return (self.id <= value) & (value <= self.id * 3)
aa = session.query(Author).all()
print(aa[0].newid) # 使用计算列
print(aa[0].testid(5)) # 调用自定义方法
自定义统计属性
自定义统计属性作用于全部数据行，类似于 Count、Min、Max 等聚合函数。只能对本模型中包含的可枚举对象（List）进行统计。
class Teacher(Base):
__tablename__ = 'teacher'
id = Column(Integer, primary_key=True)
# students 是本模型下的可枚举对象，可对其进行枚举统计
students = relationship('Student', back_populates='teacher')
@hybrid_property
def countZb(self) -> int:
return len([_child for _child in self.students
if _child.stuStatus <= 10 ]) # 根据 stuStatus 的值统计符合要求的人数
tea = session.query(Teacher).all()
print(tea[0].countZb)
浙公网安备 33010602011771号