概述：SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上，使用关系对象映射进行数据库操作，简言之便是：将类和对象转换成SQL，然后使用数据API执行SQL并获取执行结果
1. 快速使用
（1）创建模型类生成数据表
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String
Base = declarative_base()
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64))
extra = Column(String(16))
if __name__ == '__main__':
# 数据库连接相关
engine = create_engine("mysql+pymysql://root:149789@127.0.0.1:3306/s8day127")
# # 创建表
Base.metadata.create_all(engine)
# 删除表
Base.metadata.drop_all(engine)（2）导入模型类，新增记录
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import User
engine = create_engine("mysql+pymysql://root:149789@127.0.0.1:3306/s8day127", max_overflow=5)
Session = sessionmaker(bind=engine)
session = Session()
user = User(name="admin", extra="110")
user2 = User(name="admin2", extra="119")
session.add(user)
session.add(user2)
session.commit()2. 使用sqlalchemy执行原生sql语句，sqlalchemy支持数据库连接池
from sqlalchemy import create_engine
# engine = create_engine("mysql+pymysql://root:149789@localhost:3306/s8day127")
engine = create_engine(
"mysql+pymysql://root:149789@127.0.0.1:3306/s8day127?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间，否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）,-1代表一直使用
)
cursor = engine.execute("select * from users;")
ret = cursor.fetchall()
print(ret)3. SQLAlchemy创建表结构
from datetime import datetime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, DateTime, ForeignKey, UniqueConstraint
Base = declarative_base()
class Classes(Base):
__tablename__ = "classes"
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64), unique=True, nullable=False)
class Student(Base):
__tablename__ = "student"
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(64), nullable=False, unique=True)
password = Column(String(64), nullable=False)
ctime = Column(DateTime, nullable=False, default=datetime.now)
class_id = Column(Integer, ForeignKey("classes.id"))
cls = relationship("Classes", backref="stus")
class Hobby(Base):
__tablename__ = 'hobby'
id = Column(Integer, primary_key=True)
caption = Column(String(50), default='篮球')
class Student2Hobby(Base):
__tablename__ = 'student2hobby'
id = Column(Integer, primary_key=True, autoincrement=True)
student_id = Column(Integer, ForeignKey('student.id'))
hobby_id = Column(Integer, ForeignKey('hobby.id'))
# 联合唯一索引
__table_args__ = (
UniqueConstraint("student_id", "hobby_id", name="uix_student_id_hobby_id"),
)
if __name__ == '__main__':
# 数据库连接相关
engine = create_engine("mysql+pymysql://root:149789@127.0.0.1:3306/s8day127")
# # 创建表
Base.metadata.create_all(engine)
# 删除表
# Base.metadata.drop_all(engine)4. sqlalchemy增删改查、子查询、关联查询、构造sql语句查询等
from sqlalchemy import create_engine, text, or_
from sqlalchemy.orm import sessionmaker
from models import Classes
engine = create_engine("mysql+pymysql://root:149789@127.0.0.1:3306/s8day127?charset=utf8")
Session = sessionmaker(bind=engine)
session = Session()
# 1. （1）单条增加
obj = Classes(name="98班")
session.add(obj)
session.commit()
# 1. （2）多条增加
objs = [
Classes(name="101班"),
Classes(name="102班"),
]
session.add_all(objs)
session.commit()
# 2.
# （1）修改
obj = session.query(Classes).get(3)
obj.name = "201班"
session.commit()
# （2）修改
session.query(Classes).filter(Classes.id > 3).update({"name": "002班"})
# 参数：synchronize_session=False时是字符串拼接，="evaluate"时是数值运算
session.query(Classes).filter(Classes.id > 3).update({Classes.name: "1" + Classes.name}, synchronize_session=False)
session.query(Classes).filter(Classes.id > 3).update({Classes.id: Classes.id - 40}, synchronize_session="evaluate")
session.commit()
# 3. 删除
obj = session.query(Classes).filter(Classes.id == 2).delete()
session.commit()
# 4. 查询
# 1. .label就是给字段取别名
objs = session.query(Classes.id, Classes.name.label("Name")).filter(Classes.id > 3).all()
for obj in objs:
print(obj.id, obj.Name)
# 2. 打印obj虽然看上去是个元组，但是它并不是一个元组，它的类型是个对象：<class 'sqlalchemy.engine.row.Row'>
objs = session.query(Classes.id, Classes).filter(Classes.id > 3).all()
for obj in objs:
print(type(obj))
print(obj.id, obj[1].name)
# 3. filter和filter_by, filter传递的是表达式，而filter_by传递的是关键字参数
# filter_by是依赖于filter的, 如果筛选条件是字典的话，filter_by更方便
obj = session.query(Classes).filter(Classes.id == 3).first()
print(obj.name)
obj2 = session.query(Classes).filter_by(**{"id": 10}).all()
obj2 = session.query(Classes).filter_by(id=10).all()
print(obj2[0].name)
# 4. filter构造复杂的查询条件
objs = session.query(Classes).filter(or_(text("id>:value and name=:name"), text("id=:value2 and name=:name2")))\
.params(value=1, name="11002班", value2=3, name2="201班").all()
print([obj.name for obj in objs])
# 5. from_statement构造sql语句
objs = session.query(Classes).from_statement(text("select * from classes where name=:name")).params(name="201班")
print(objs)
print([obj.name for obj in objs])
# 6. 子查询
objs = session.query(Classes).filter(Classes.id.in_(
session.query(Classes.id).filter(Classes.name == "201班")
))
print([obj.name for obj in objs])5. 带外键的关联查询
from sqlalchemy import create_engine, text, or_
from sqlalchemy.orm import sessionmaker
from models import Classes, Student
engine = create_engine("mysql+pymysql://root:149789@127.0.0.1:3306/s8day127?charset=utf8")
Session = sessionmaker(bind=engine)
session = Session()
# 一：关联查询：不带外键的查询方法
# 答应学生信息，包含班级名称方法一：
objs = session.query(Student).all()
class_ids = {obj.class_id for obj in objs}
classes_objs = session.query(Classes).filter(Classes.id.in_(class_ids))
id_map_name = {obj.id: obj.name for obj in classes_objs}
for obj in objs:
print(obj.id, obj.username, id_map_name[obj.class_id])
# 二：关联查询：带外键的查询方法 join
# 答应学生信息，包含班级名称方法二：
objs = session.query(Student.id, Student.username, Classes.name).join(Classes, isouter=True).all()
for obj in objs:
print(obj.id, obj.username, obj.name)
"""
class Student(Base):
__tablename__ = "student"
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(64), nullable=False, unique=True)
password = Column(String(64), nullable=False)
ctime = Column(DateTime, nullable=False, default=datetime.now)
class_id = Column(Integer, ForeignKey("classes.id"))
cls = relationship("Classes", backref="stus")
"""
# 三 ：关联查询：带外键的查询方法 通过relationship
# 由多表查一表：答应学生信息，包含班级名称方法三：
objs = session.query(Student).all()
for obj in objs:
print(obj.id, obj.username, obj.cls.name)
# 四：由一查多,找到201班所有的学生
obj = session.query(Classes).filter_by(**{"name": "201班"}).first()
for stu_obj in obj.stus:
print(obj.id, obj.name, stu_obj.username)6. 其它查询
from sqlalchemy import create_engine, text, or_
from sqlalchemy.orm import sessionmaker
from models import Classes, Student
engine = create_engine("mysql+pymysql://root:149789@127.0.0.1:3306/s8day127?charset=utf8")
Session = sessionmaker(bind=engine)
session = Session()
# between 使用
# # objs = session.query(Student).filter(Student.id.between(1, 3)).all()
# print([obj.username for obj in objs])
# 查询条件取反的用法
objs = session.query(Student).filter(~Student.id.in_([1, 4])).all()
print([obj.username for obj in objs])
# 通配符
ret = session.query(Student).filter(Student.name.like('e%')).all()
ret2 = session.query(Student).filter(~Student.name.like('e%')).all()
# 限制
ret3 = session.query(Student)[1:2]
# 排序
ret4 = session.query(Student).order_by(Student.name.desc()).all()
ret5 = session.query(Student).order_by(Student.name.desc(), Student.id.asc()).all()
# 分组
from sqlalchemy.sql import func
ret6 = session.query(Student).group_by(Student.extra).all()
ret7 = session.query(
func.max(Student.id),
func.sum(Student.id),
func.min(Student.id)).group_by(Student.name).all()
ret8 = session.query(
func.max(Student.id),
func.sum(Student.id),
func.min(Student.id)).group_by(Student.name).having(func.min(Student.id) >2).all()
# 连表
ret9 = session.query(Student, Classes).filter(Student.id == Classes.nid).all()
ret10 = session.query(Student).join(Classes).all() # 默认inner join
ret11 = session.query(Student).join(Classes, isouter=True).all() # isouter=True 代表left join
# 没有外键的时候需要写上连表的条件 Student.class_id == Classes.id
rets = session.query(Student, Classes).join(Classes, Student.class_id == Classes.id, isouter=True).all()
for a, b in rets:
print(a.username, b.name)
# 组合
q1 = session.query(Student.name).filter(Student.id > 2)
q2 = session.query(Classes.caption).filter(Classes.nid < 2)
ret12 = q1.union(q2).all()
q1 = session.query(Student.name).filter(Student.id > 2)
q2 = session.query(Classes.caption).filter(Classes.nid < 2)
ret13 = q1.union_all(q2).all()7. 基于relationship实现数据添加
person = Person(name='张九', hobby=Hobby(caption='姑娘'))
session.add(person)
hb = Hobby(caption='人妖')
hb.pers = [Person(name='文飞'), Person(name='博雅')]
session.add(hb)
session.commit()7. 基于relationship实现正向、反向查询
# 使用relationship正向查询
"""
v = session.query(Person).first()
print(v.name)
print(v.hobby.caption)
"""
# 使用relationship反向查询
"""
v = session.query(Hobby).first()
print(v.caption)
print(v.pers) # 此处查出来是多个，是一个列表
"""多对多模型类：
8. 多对多添加数据
gp = Group(name='C组')
gp.servers = [Server(hostname='c3.com'),Server(hostname='c4.com')]
session.add(gp)
session.commit()
ser = Server(hostname='c6.com')
ser.groups = [Group(name='F组'),Group(name='G组')]
session.add(ser)
session.commit()9. 多对多正向查询、反向查询
# 使用relationship正向查询
"""
v = session.query(Group).first()
print(v.name)
print(v.servers)
"""
# 使用relationship反向查询
"""
v = session.query(Server).first()
print(v.hostname)
print(v.groups)
"""relationship可以帮助我们快速的添加数据，正向方向查询数据。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
