SQLAlchemy详解

概述：

           SQLAlchemy也是一种ORM 框架。ORM翻译过来就是 '关系对象映射'。通过简单的规则，自动转化成sql语句。

简单的说就是 类就是表，行就是对象。

ORM分类：

             1 DB first   每个表生成一个类，先有表后有类。

                    手动创建数据库以及表---------->orm---------------->自动生成类

            

              2  code first   手动创建类和数据库。先有类后有表

                     手动创建类和数据库-------------->orm---------------->自动生成表

 

SQLAlchemy 的使用：

　　1 创建表

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

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 增 删

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

# 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 更改

?

1 2 3 4 5 6 7 8 9

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 条件查询

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

#单条查询 # 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 通配符

+ View Code?

1 2 3 4

# #通配符 # 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 分页

?

1 2 3

# ret11=session.query(Users)[1:3] # # print([a.name for a in ret11]) # print(session.query(Users))

　　 7 排序

?

1 2	# ret12=session.query(Users).order_by(Users.name.desc()).all() # ret13=session.query(Users).order_by(Users.name.desc(),Users.id.asc()).all()

　　 8 分组

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

#分组 # 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 连表

?

1 2 3 4 5 6 7 8

#连表 #笛卡尔积 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 子查询

?

1 2 3 4

# 子查询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）

?

1 2 3 4 5 6 7 8 9

#组合 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 利用外键默认关联了所对应的表

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

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  提供了正向和反向操作