SQLAlchemy详解
概述:
SQLAlchemy也是一种ORM 框架。ORM翻译过来就是 '关系对象映射'。通过简单的规则,自动转化成sql语句。
简单的说就是 类就是表,行就是对象。
ORM分类:
1 DB first 每个表生成一个类,先有表后有类。
手动创建数据库以及表---------->orm---------------->自动生成类
2 code first 手动创建类和数据库。先有类后有表
手动创建类和数据库-------------->orm---------------->自动生成表
SQLAlchemy 的使用:
1 创建表
from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index,VARCHAR,CHAR # 创建表 #declarative_base() 创建了一个 BaseModel 类,这个类的子类可以自动与一个表关联。 Base=declarative_base() class UserType(Base): # __tablename__=表名 固定写法 __tablename__='usertype' id=Column(Integer,primary_key=True,autoincrement=True) title=Column(VARCHAR(32)) class Users(Base): __tablename__='users' id=Column(Integer,primary_key=True,autoincrement=True) name=Column(VARCHAR(32),nullable=True) email=Column(String(32),unique=True) user_type_id=Column(Integer,ForeignKey('usertype.id')) __table_args__=( UniqueConstraint('id','name',name='mix_id_name'), Index('ix_n_ex','name','email',) ) #链接数据库 其实SQLAlchemy 使用pymysql链接数据库 max_overflow是最大连接数#相当于线程池,create_engine 返回反一个数据库引擎 #engine=create_engine('mysql+pymysql://root:degnity0304@127.0.0.1:3306/db7?charset=utf8',max_overflow=5) #最后,Base.metadata.create_all(engine) 会找到 BaseModel 的所有子类,并在数据库中建立这些表 # Base.metadata.create_all(engine) # Base.metadata.drop_all(engine)
2 增 删
# engine=create_engine('mysql+pymysql://root:degnity0304@127.0.0.1:3306/db7?charset=utf8',max_overflow=5) #sessionmaker() 会生成一个数据库会话类,类的实例就是一个数据库链接 # Session=sessionmaker(bind=engine) # session=Session() #插入一条 # obj1=UserType(title='普通用户') # session.add(obj1) #插入多条 # obj2=[ # UserType(title='黑铁用户'), # UserType(title='白银用户'), # UserType(title='黄金用户'), # UserType(title='黑金用户') # ] # session.add_all(obj2) # session.commit() # session.close() #查 类就代表表 engine=create_engine('mysql+pymysql://root:degnity0304@127.0.0.1:3306/db7?charset=utf8',max_overflow=5) #拿到链接 Session=sessionmaker(bind=engine) session=Session() #打印结果可知 直接生成了sql语句 # print(session.query(UserType)) #查询所有 # user_type_list=session.query(UserType).all() # # 通过打印print(user_type_list) 可以返回的列表内全部是UserType的对象 # print(user_type_list) # print(type(user_type_list[0])) # for row in user_type_list: # print(row.id,row.title) # 筛选 query --select ** filter相当与where # user_type_list=session.query(UserType.id,UserType.title).filter(UserType.id>3) # for row in user_type_list: # print(row.id,row.title) # sqlalchemy 想要删除操作 必须要先查 # session.query(UserType).filter(UserType.id>3).delete() # session.commit()
3 更改
session.query(UserType).filter(UserType.id>2).update({"title":"山东大李逵"}) session.commit() #在原有基础上进行更改 # 列值为字符串类型 session.query(UserType).filter(UserType.id > 2).update({UserType.title: UserType.title + "099"}, synchronize_session=False) #列值为数字类型 session.query(UserType).filter(UserType.id > 2).update({"num": UserType.num + 1}, synchronize_session="evaluate")
4 条件查询
#单条查询 # ret=session.query(Users).filter_by(name='武松').all() # #范围查询 # ret1=session.query(Users).filter(Users.id>2,Users.name=='武松').all() # ret2=session.query(Users).filter(Users.id.between(1,4),Users.name=='松江').all() # ret3=session.query(Users).filter(Users.id.in_([1,3,4])).all() # ret4=session.query(Users).filter(-Users.id.in_([1,2])).all() # ret5=session.query(Users).filter(Users.id.in_(session.query(Users.id).filter(Users.name=='武松')).all()) # # #and_ or_ # from sqlalchemy import and_,or_ # ret6=session.query(Users).filter(and_(Users.id>3,Users.name=='武松')).all() # ret7=session.query(Users).filter(or_(Users.id<4,Users.name=='松江')).all() # ret8=session.query(Users).filter( # or_(Users.id<2, # and_(Users.name=='华容',Users.id>3), # Users.email !="") # ).all()
5 通配符
# #通配符 # ret9=session.query(Users).filter(Users.name.like('李%')).all() # ret91=session.query(Users).filter(Users.name.like('李_')).all() # ret10=session.query(Users).filter(-Users.name.like('李%')).all()
6 分页
# ret11=session.query(Users)[1:3] # # print([a.name for a in ret11]) # print(session.query(Users))
7 排序
# ret12=session.query(Users).order_by(Users.name.desc()).all() # ret13=session.query(Users).order_by(Users.name.desc(),Users.id.asc()).all()
8 分组
#分组 # from sqlalchemy.sql import func # # ret=session.query(Users).group_by(Users.name).all() # ret1=session.query( # func.max(Users.id), # func.sum(Users.id), # func.min(Users.id) # ).group_by(Users.name).all() # # ret2=session.query( # func.max(Users.id), # func.sum(Users.id), # func.min(Users.id) # ).group_by(Users.name).having(func.min(Users.id)>2).all()
9 连表
#连表 #笛卡尔积 ret=session.query(Users,UserType).filter(UserType.id==Users.user_type_id).all() print(ret) # #join 默认使用外键关联 # ret1=session.query(UserType).join(Users).all() # #左链接 # ret2=session.query(UserType).join(UserType,isouter=True).all()
10 子查询
# 子查询subquery() # ret=session.query(UserType.id,session.query(Users)).all() # ret1=session.query(UserType.id,session.query(Users).subquery()).all() # print(ret1)
11 组合 (sql中的union,union all)
#组合 union union_all # q1 = session.query(Users.name).filter(Users.id > 2) # q2 = session.query(UserType.title).filter(UserType.id < 2) # ret = q1.union(q2).all() # # q1 = session.query(Users.name).filter(Users.id > 2) # q2 = session.query(UserType.title).filter(UserType.id < 2) # ret2= q1.union_all(q2).all() # print(ret2)
12 relationship 使用relationship 实际上relationship 利用外键默认关联了所对应的表
from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index,VARCHAR,CHAR Base=declarative_base() class UserType(Base): __tablename__='usertype' id=Column(Integer,primary_key=True,autoincrement=True) title=Column(VARCHAR(32)) class Users(Base): __tablename__='users' id=Column(Integer,primary_key=True,autoincrement=True) name=Column(VARCHAR(32),nullable=True) email=Column(String(32),unique=True) user_type_id=Column(Integer,ForeignKey('usertype.id')) user_type_info=relationship('UserType',backref='xxxx') __table_args__=( UniqueConstraint('id','name',name='mix_id_name'), Index('ix_n_ex','name','email',) ) engine=create_engine('mysql+pymysql://root:degnity0304@127.0.0.1:3306/db7?charset=utf8',max_overflow=5) Session=sessionmaker(bind=engine) session=Session() # 获取用户信息,以及与其关联的用户类型名称 #没有使用relationship #此时ret1没有加all 实际上是个生成器 # ret1=session.query(Users,UserType).join(UserType,isouter=True) # # 在这里 ret 内实际上是列表内嵌套的元组,元组内元素分别是Users和UserType的对象 # print(ret1) # for a in ret1: # print(a[0].id,a[0].name,a[0].user_type_id,a[1].title) # 使用relationship 实际上relationship 利用外键默认关联了所对应的表 # user_lista=session.query(Users) # for row in user_lista: # print(row.id,row.name,row.user_type_info.title) #问题2 获取用户类型,以及所有用户 # user_type_info=relationship('UserType',backref='xxxx') #relationship 中的backref 实际上是传给关联表的操作句柄,以便反向查询 type_list=session.query(UserType).filter(UserType.id<5) for row in type_list: print(row.id,row.title,row.xxxx[0].name,[(a.name,a.id) for a in row.xxxx]) #总之 relationship 提供了正向和反向操作