一SQLAlchemy简介
SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上，使用关系对象映射进行数据库操作，简言之便是：将类和对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
1.与Django中models的区别
很多小伙伴说SQLAlchemy不如Django的models好用，这里我们需要知道。
Models其实只是配置和使用比较简单，毕竟是Django自带的ORM框架，但是兼容性远不如SQLAchemy，真正算得上全面的ORM框架必然是SQLAlchemy。
无论使用什么ORM框架，其实都是为了方便不熟练数据库使用的同学，最推荐的还是使用原生的SQL语句，也建议大家攻克SQL难关。
组成部分：
-
Engine，框架的引擎
-
Connection Pooling ，数据库连接池
-
Dialect，选择连接数据库的DB API种类
-
Schema/Types，架构和类型
-
SQL Exprression Language，SQL表达式语言
SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：
MySQL-Python mysql+mysqldb://: @ [: ]/ pymysql mysql+pymysql:// : @ / [? ] MySQL-Connector mysql+mysqlconnector:// : @ [: ]/ cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] 更多：http://docs.sqlalchemy.org/en/latest/dialects/index.html
二、SQLAlchemy使用
1.执行原生sql语句
通过SQLAlchemy执行源生的sql语句
方式一：
from sqlalchemy import create_engineengine = create_engine( "mysql+pymysql://root:123@127.0.0.1:3306/sqlalchemy01?charset=utf8", max_overflow=0, # 超过连接池大小外最多创建的连接 pool_size=5, # 连接池大小 pool_timeout=30, # 池中没有线程最多等待的时间，否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）)def task(): conn = engine.raw_connection() cursor = conn.cursor() cursor.execute( "select * from t1" ) result = cursor.fetchall() print(">>>",result) cursor.close() conn.close()task() 方式二：
from sqlalchemy import create_engineengine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/sqlalchemy01", max_overflow=0, pool_size=5)def task(): conn = engine.connect() with conn: cur = conn.execute( "select * from t1" ) result = cur.fetchall() print(result)task() 方式三
from sqlalchemy import create_engineengine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/sqlalchemy01", max_overflow=0, pool_size=5)def task(): cur = engine.execute("select * from t1") result = cur.fetchall() cur.close() print(result)task() 2.数据表的操作
通过sqlalchemy来创建表和删除表
import datetimefrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index# 建立基础类 R关系 M映射 类Base = declarative_base()class Users(Base): __tablename__ = 'users' # 指定创建的表名 # 写字段 id = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=False) email = Column(String(32), unique=True) # ctime = Column(DateTime, default=datetime.datetime.now) # extra = Column(Text, nullable=True) __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), # 设置位移约束 Index('ix_id_name', 'name', 'email'), # 设置索引 )# 创建数据库的引擎engine = create_engine( "mysql+pymysql://root:123@127.0.0.1:3306/sqlalchemy01?charset=utf8", max_overflow=0, # 超过连接池大小外最多创建的连接 pool_size=5, # 连接池大小 pool_timeout=30, # 池中没有线程最多等待的时间，否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）)# 检索所有继承Base的Object并在 engine 指向的数据库中创建所有的表Base.metadata.create_all(engine)# 删除所有的数据库表Base.metadata.drop_all(engine) 1.单表创建示例
import datetimefrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index# 建立基础类 R关系 M映射 类Base = declarative_base()class Users(Base): __tablename__ = 'users' # 指定创建的表名 # 写字段 id = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=False) email = Column(String(32), unique=True) # ctime = Column(DateTime, default=datetime.datetime.now) # extra = Column(Text, nullable=True) __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), # 设置位移约束 Index('ix_id_name', 'name', 'email'), # 设置索引 )# 创建数据库的引擎engine = create_engine( "mysql+pymysql://root:123@127.0.0.1:3306/sqlalchemy01?charset=utf8", max_overflow=0, # 超过连接池大小外最多创建的连接 pool_size=5, # 连接池大小 pool_timeout=30, # 池中没有线程最多等待的时间，否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）)# 检索所有继承Base的Object并在 engine 指向的数据库中创建所有的表Base.metadata.create_all(engine)# 删除所有的数据库表Base.metadata.drop_all(engine) 2.一对多示例
# ########## 一对多示例 ##########class School(Base): __tablename__ = "school" id = Column(Integer,primary_key=True) name = Column(String(32),nullable=False)class Student(Base): __tablename__ = "student" id = Column(Integer,primary_key=True) name = Column(String(32),nullable=False) school_id = Column(Integer,ForeignKey("school.id")) # 多对一关系存储列 # 与生成表结构无关，仅用于查询方便 school = relationship("School", backref='student') engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/sqlalchemy01?charset=utf8")# 检索所有继承 Model 的Object 并在 engine 指向的数据库中创建 所有的表Model.metadata.create_all(engine) 3.多对多表结构创建
from sqlalchemy import Column,Integer,String,ForeignKeyfrom sqlalchemy.orm import relationshipclass Girls(Model): __tablename__ = "girl" id = Column(Integer,primary_key=True) name = Column(String(32),nullable=False) # relationship g2b = relationship("Boys",backref="b2g",secondary="hotel")class Boys(Model): __tablename__ = "boy" id = Column(Integer,primary_key=True) name = Column(String(32),nullable=False)class Hotels(Model): __tablename__ = "hotel" id = Column(Integer,primary_key=True) boy_id = Column(Integer,ForeignKey("boy.id")) girl_id = Column(Integer,ForeignKey("girl.id")) engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/sqlalchemy01?charset=utf8")# 检索所有继承 Model 的Object 并在 engine 指向的数据库中创建 所有的表Model.metadata.create_all(engine) 4.定义函数来创建和删除表
def init_db(): """ 根据类创建数据库表 :return: """ engine = create_engine( "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, # 超过连接池大小外最多创建的连接 pool_size=5, # 连接池大小 pool_timeout=30, # 池中没有线程最多等待的时间，否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置） ) Base.metadata.create_all(engine)def drop_db(): """ 根据类删除数据库表 :return: """ engine = create_engine( "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, # 超过连接池大小外最多创建的连接 pool_size=5, # 连接池大小 pool_timeout=30, # 池中没有线程最多等待的时间，否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置） ) Base.metadata.drop_all(engine)if __name__ == '__main__': drop_db() init_db()
3.记录的增删改查
数据库记录操作的两种方式
from sqlalchemy.orm import sessionmakerfrom sqlalchemy import create_enginefrom models import Users engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5)############方式一#############Session = sessionmaker(bind=engine) # 每次执行数据库操作时，都需要创建一个sessionsession = Session() # ############# 执行ORM操作 #############obj1 = Users(name="alex1")session.add(obj1) # 提交事务session.commit()# 关闭sessionsession.close()###########方式二############ 方式二：支持线程安全，为每个线程创建一个session # - threading.Local # - 唯一标识 # ScopedSession对象 # self.registry(), 加括号 创建session # self.registry(), 加括号 创建session # self.registry(), 加括号 创建session from greenlet import getcurrent as get_identSession = sessionmaker(bind=engine) session = scoped_session(Session,get_ident) # session.add # 操作 session.remove() 1.单表的增删改查
from day101_sqlAlchemy.SQLAlchemy02_create_table_single import engine,Usersfrom sqlalchemy.orm import sessionmakerSession = sessionmaker(engine) # 新建数据库的查询窗口db_session = Session() # 打开查询窗口# 增加单条数据# u = Users(name="ryxiong") # 新建insert语句 insert into# db_session.add(u) # 将insert语句移动到 db_session 查询窗口# db_session.commit() # 执行查询窗口中的所有语句# db_session.close() # 关闭查询窗口# 增加多条数据# u_list = [Users(name="egon"),Users(name="alex")]# db_session.add_all(u_list) # 添加多条数据# db_session.commit()# db_session.close()# 查询数据# res = db_session.query(Users).all() # 查询所有数据# for user in res:# print(user.id,user.name)# res = db_session.query(Users).first() # 查询符合条件的第一条数据# print(res.id,res.name) # 3 alex# 并列条件查询# res = db_session.query(Users).filter(Users.id<3,Users.name=="ryxiong").all()# for user in res:# print(user.id,user.name) # 1 ryxiong# res = db_session.query(Users).filter(Users.id<3,Users.name=="ryxiong").first()# print(res.id,res.name) # 1 ryxiong# 修改数据# db_session.query(Users).filter(Users.id==2).update({"name":"Egon"})# db_session.commit()# 删除数据db_session.query(Users).filter(Users.id==3).delete()db_session.commit() 2.一对多的增删改查
from day101_sqlAlchemy.SQLAlchemy03_create_table_foreignKey import engine,Student,Schoolfrom sqlalchemy.orm import sessionmakerSession = sessionmaker(engine) # 新建数据库的查询窗口db_session = Session() # 打开查询窗口# 增加一条数据# school = School(name="新东方")# db_session.add(school)# db_session.commit()# 在添加学生# school_fir = db_session.query(School).filter(School.name=="新东方").first()## student = Student(name="ryxiong",school_id=school_fir.id)# db_session.add(student)# db_session.commit()# 1.添加数据 relationship 正向添加数据# stu = Student(name="alex",school=School(name="蓝翔"))# db_session.add(stu)# db_session.commit()# 2.添加数据relationship 反向添加数据# sch = School(name="蓝翔")# sch.student = [# Student(name="egon"),# Student(name="wusir")# ]# db_session.add(sch)# db_session.commit()# 查询# 1.relationship正向查询res = db_session.query(Student).all()for stu in res: print(stu.id,stu.name,stu.school.name)# 2.relationship反向查询res = db_session.query(School).all()for sch in res: for stu in sch.student: print(sch.name,stu.id,stu.name)
3.多对多查询
from day101_sqlAlchemy.SQLAlchemy04_create_table_M2M import engine,Boys,Girlsfrom sqlalchemy.orm import sessionmakerSession = sessionmaker(engine) # 新建数据库的查询窗口db_session = Session() # 打开查询窗口# 添加数据# 1.relationship正向添加# girl = Girls(name="Nancy",boy=[Boys(name="ryxiong"),Boys(name="alex")])# db_session.add(girl)# db_session.commit()# 2.relationship反向添加# boy = Boys(name="egon")# boy.girl = [# Girls(name="罗玉凤"),# Girls(name="朱利安"),# ]## db_session.add(boy)# db_session.commit()# 查询数据# 1.relationship 正向查询res = db_session.query(Girls).all()for girl in res: for boy in girl.boy: print(girl.name,boy.name)# 2.relationship 反向查询res = db_session.query(Boys).all()for boy in res: for girl in boy.girl: print(boy.name,girl.name)
from day101_sqlAlchemy.SQLAlchemy02_create_table_single import engine,Usersfrom sqlalchemy.sql import textfrom sqlalchemy.orm import sessionmakerfrom sqlalchemy import and_,or_Session = sessionmaker(engine) # 新建数据库的查询窗口db_session = Session() # 打开查询窗口# 逻辑条件查询 and/or# ret1 = db_session.query(Users).filter(and_(Users.id<3,Users.name=="ryxiong")).all()# print(ret1)# ret2 = db_session.query(Users).filter(or_(Users.id<2,Users.name=="egon")).all()# print(ret2)## ret3 = db_session.query(Users).filter(# or_(# and_(Users.id==1,Users.name=="ryxiong"),# and_(Users.id==2,Users.name=="egon")# )# ).all()# print(ret3)# 查询所有数据排序# ret = db_session.query(Users).order_by(Users.id.asc()).all() # 按照id升序排列## print(ret)# 查询数据，指定查询数据列，加入别名# ret = db_session.query(Users.name.label("username"),Users.id).first()## print(ret) # ('alex', 3)# print(ret.id,ret.username) # 3 alex# 表达式筛选条件# user_list = db_session.query(Users).filter(Users.name=="ryxiong").all()# user_list1 = db_session.query(Users).filter_by(name="ryxiong").all()# for user in user_list:# print(user.name)# 复杂查询# user_list2 = db_session.query(Users).filter(text("id<:value and name=:name")).params(value=3,name="ryxiong")# print(user_list2)# 查询语句# user_list3 = db_session.query(Users).filter(text("select * from user id<:value and name=:name")).params(value=3,name="ryxiong")# print(user_list3)# 其他查询条件# ret = db_session.query(Users).filter(Users.id.between(1,3)).all() # 查询id值在1-3之间，不包含3的# print(ret)## ret1 = db_session.query(Users).filter(Users.id.in_([1,2])).all() # 查询id在列表[1,2]中的用户# print(ret1)## ret2 = db_session.query(Users).filter(~Users.id.in_([1,2])).all() # 查询用户id不在列表[1,2]中的。# print(ret2)# 子查询# ret3 = db_session.query(Users).filter(Users.id.in_(db_session.query(Users.id).filter_by(name="ryxiong"))).all()# print(ret3)# 通配符# ret4 = db_session.query(Users).filter(Users.name.like("%ong")).all()# print(ret4)# ret5 = db_session.query(Users).filter(~Users.name.like("%ong")).all()# print(ret5)# 切片# ret6 = db_session.query(Users)[1:2]# print(ret6)# 分组 group_byfrom sqlalchemy.sql import func# ret7 = db_session.query(Users).group_by(Users.name).all()# print(ret7)# 聚合函数ret8 = db_session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id),).group_by(Users.name).all()print(ret8) # [(3, Decimal('3'), 3), (2, Decimal('2'), 2), (1, Decimal('1'), 1)]ret9 = db_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()print(ret9) # [(3, Decimal('3'), 3)]