SQLALchemy
SQLALchemy
SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上，使用关系对象映射进行数据库操作 ，也就是将类和对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
安装：pip3 install sqlalchemy
组成部分：
- Engine，框架的引擎
- Connection Pooling ，数据库连接池
- Dialect，选择连接数据库的DB API种类
- Schema/Types，架构和类型
- SQL Exprression Language，SQL表达式语言
SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] 更多：http://docs.sqlalchemy.org/en/latest/dialects/index.html |
问题：SQLALchemy，ORM框架
1、什么是orm框架
orm：对象关系映射
类 --- 表
对象 --- 一条记录
当有了对应关系之后，不需要再编写sql语句，直接操作，类，对象
2、sql vs orm
sql查询速度快，开发效率低
orm开发效率高，查询速度较低
3、概念理解
-db first 根据数据库的表生成类
-code first 根据类生成数据库的表
4、orm是怎么实现的
根据对象和类通过字符串格式化转化成sql语句
DDD中：unit of work
使用原生sql语句
import time import threading import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.engine.base import Engine engine = create_engine( "mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8", max_overflow=0, # 超过连接池大小外最多创建的连接 pool_size=5, # 连接池大小 pool_timeout=30, # 池中没有线程最多等待的时间，否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置） ) def task(arg): conn = engine.raw_connection() cursor = conn.cursor() cursor.execute( "select * from t1" ) result = cursor.fetchall() cursor.close() conn.close() for i in range(20): t = threading.Thread(target=task, args=(i,)) t.start()
#!/usr/bin/env python # -*- coding:utf-8 -*- import time import threading import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.engine.base import Engine engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=0, pool_size=5) def task(arg): conn = engine.contextual_connect() with conn: cur = conn.execute( "select * from t1" ) result = cur.fetchall() print(result) for i in range(20): t = threading.Thread(target=task, args=(i,)) t.start()
#!/usr/bin/env python # -*- coding:utf-8 -*- import time import threading import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.engine.base import Engine from sqlalchemy.engine.result import ResultProxy engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=0, pool_size=5) def task(arg): cur = engine.execute("select * from t1") result = cur.fetchall() cur.close() print(result) for i in range(20): t = threading.Thread(target=task, args=(i,)) t.start()
使用orm语句
创建数据库表
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, DateTime,ForeignKey,UniqueConstraint from sqlalchemy import create_engine from sqlalchemy.orm import relationship import datetime Base = declarative_base() # 创建单表 class Classes(Base): __tablename__ = 'classes' id = Column(Integer, primary_key=True,autoincrement=True) name = Column(String(32)) class Student(Base): __tablename__="student" id =Column(Integer,primary_key=True,autoincrement=True) user=Column(String(32)) pwd=Column(String(32)) ctime=Column(DateTime,default=datetime.datetime.now) #不要加() class_id=Column(Integer,ForeignKey("classes.id")) #不会生成字段，用于链表查询，backref用于反向链表查表 cls = relationship("Classes", backref='stus') class Hobby(Base): __tablename__="hobby" id=Column(Integer,primary_key=True,autoincrement=True) caption=Column(String(32),default="大球") # 多对多关系 需要自检创建第三张表 class StudentToHobby(Base): __tablename__="studenttohobby" 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"), ) def init_db(): # 数据库连接相关 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/s8sql?charset=utf8") # 创建表 Base.metadata.create_all(engine) def drop_db(): engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/s8sql?charset=utf8") # 删除表 Base.metadata.drop_all(engine) if __name__ == '__main__': init_db() # drop_db()
增
import models from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine # 连接数据库 engine=create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/s8sql?charset=utf8") Session=sessionmaker(bind=engine) # 每次执行数据库操作时，都需要创建一个session session=Session() # 添加单条数据 # obj=models.Classes(name="全栈8期") # session.add(obj) # 添加多条数据 # obj_all=[ # models.Classes(name="全栈9期"), # models.Classes(name="全栈10期"), # models.Classes(name="全栈11期") # ] # session.add_all(obj_all) # 添加数据（含有外键） obj=models.Student(user="小花",pwd="123",class_id=2) session.add(obj) # 提交事务 session.commit() # 关闭session session.close()
查
import models from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine,text # 连接数据库 engine=create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/s8sql?charset=utf8") Session=sessionmaker(bind=engine) # 每次执行数据库操作时，都需要创建一个session session=Session() # 查全部数据 # class_list=session.query(models.Classes).all() # for classes in class_list: # print(classes.id,classes.name) #别名查询 # class_list=session.query(models.Classes.id,models.Classes.name.label("xx")).all() # for classes in class_list: # print(classes.id,classes.xx) #条件查询 filter(条件) filter_by() # obj=session.query(models.Classes).filter(models.Classes.name=="全栈9期").all() # for i in obj: # print(i.name) # obj1=session.query(models.Classes).filter(models.Classes.name=="全栈9期").first() # print(obj1.name) # obj2=session.query(models.Classes).filter_by(name="全栈10期").first() # print(obj2.name) #子查询 # result = session.query(models.Classes).from_statement(text("SELECT * FROM classes where name=:name")).params(name='全栈11期').all() # print(result) # ret = session.query(models.Classes).filter(models.Classes.id.in_(session.query(models.Classes.id).filter_by(name='全栈8期'))).all() # print(ret) # 显示所有学生信息（含班级） # 1、多次查询 # obj=session.query(models.Student).all() # for obj in objs: # cls_obj = session.query(models.Classes).filter(models.Classes.id==obj.class_id).first() # print(obj.id,obj.username,obj.class_id,cls_obj.name) # 2、连表查询 isouter=True mysql中leftjoin查询 # objs = session.query(models.Student.id,models.Student.user,models.Classes.name).join(models.Classes,isouter=True).all() # print(objs) # 3、使用relationship查询 # objs = session.query(models.Student).all() # for item in objs: # print(item.id,item.user,item.class_id,item.cls.name) #4、使用relationship反向查询 全栈9期所有的学生 # obj = session.query(models.Classes).filter(models.Classes.name=='全栈9期').first() # student_list = obj.stus # for item in student_list: # print(item.id,item.user) # 提交事务 session.commit() # 关闭session session.close()
改
import models from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine # 连接数据库 engine=create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/s8sql?charset=utf8") Session=sessionmaker(bind=engine) # 每次执行数据库操作时，都需要创建一个session session=Session() # 改 # session.query(models.Classes).filter(models.Classes.id > 1).update({"name" : "099"}) # synchronize_session=False 字符串拼接 # session.query(models.Classes).filter(models.Classes.id > 0).update({models.Classes.name: models.Classes.name + "099"}, synchronize_session=False) # synchronize_session="evaluate" 数字计算 session.query(models.Classes).filter(models.Classes.id > 0).update({"age": models.Classes.age + 1}, synchronize_session="evaluate") # 提交事务 session.commit() # 关闭session session.close()
删
import models from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine # 连接数据库 engine=create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/s8sql?charset=utf8") Session=sessionmaker(bind=engine) # 每次执行数据库操作时，都需要创建一个session session=Session() # 删 session.query(models.Classes).filter(models.Classes.id > 2).delete() # 提交事务 session.commit() # 关闭session session.close()
补充操作
# 条件 ret = session.query(Users).filter_by(name='alex').all() ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all() #并且关系 ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all() #between区间 ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() #id=1or3or ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() from sqlalchemy import and_, or_ ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all() ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all() ret = session.query(Users).filter( or_( Users.id < 2, and_(Users.name == 'eric', Users.id > 3), Users.extra != "" )).all() # 通配符 ret = session.query(Users).filter(Users.name.like('e%')).all() ret = session.query(Users).filter(~Users.name.like('e%')).all() # 限制 （mysql中的limit） ret = session.query(Users)[1:2] # 排序 ret = session.query(Users).order_by(Users.name.desc()).all() ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() # 分组 from sqlalchemy.sql import func ret = session.query(Users).group_by(Users.extra).all() ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).all() ret = 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() # 连表 ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all() ret = session.query(Person).join(Favor).all() #inner join 一张表没有的，都不显示 ret = session.query(Person).join(Favor, isouter=True).all() #left join 以左表为基准 # 组合 q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union(q2).all() #数据重复，只留一条 q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union_all(q2).all() #显示数据重复
relationship
- relationship作用？增加数据和查询 一对多： class UserType(Base): __tablename__ = 'usertype' id = Column(Integer, primary_key=True) caption = Column(String(50), default='管理员') class Person(Base): __tablename__ = 'person' nid = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=True) hobby_id = Column(Integer, ForeignKey("usertype.id")) # 与生成表结构无关，仅用于查询方便 hobby = relationship("UserType", backref='pers') 数据添加： session.add(UserType(caption='超级管理员')) session.commit() session.add(Person(name='翔龙',hobby_id=1)) session.commit() user表添加数据，同时也给UserType表添加数据 session.add(Person(name='小韩',hobby=UserType(cation='VVIP'))) session.commit() 数据查询： 正向操作： obj = session.query(Person).filter(Person.nid==2).first() obj.hobby.caption 反向操作： obj = session.query(UserType).filter(UserType.id==1).first() obj.pers 多对多： class User2Hobby(Base): __tablename__ = 'user2hobby' id = Column(Integer, primary_key=True, autoincrement=True) hobby_id = Column(Integer, ForeignKey('hobby.id')) user_id = Column(Integer, ForeignKey('user.id')) class Hobby(Base): __tablename__ = 'hobby' id = Column(Integer, primary_key=True) title = Column(String(64), unique=True, nullable=False) # 与生成表结构无关，仅用于查询方便 users = relationship('User', secondary='user2hobby', backref='hbs') class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(64), unique=True, nullable=False) 添加数据： 添加单条： session.add(Hobby(title='篮球')) session.commit() session.add(User(name='梅凯')) session.commit() session.add(User2Hobby(hobby_id=1,user_id=1)) session.commit() 添加多条 正向： obj = Hobby(title='篮球') obj.servers = [User(name='王岩'),User(name='晓梅')] session.add(obj) session.commit() 反向： obj = User(title='俊杰') obj.hbs = [Hobby(title='翔龙'),Hobby(title='兴隆')] session.add(obj) session.commit() 查询： 反向： obj = session.query(User).filter(User.id==2).first() obj.hbs 正向: obj = session.query(Hobby).filter(Hobby.id==2).first() obj.users
创建session的两种方式
#!/usr/bin/env python # -*- coding:utf-8 -*- import time import threading from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine from db 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) def task(arg): session = Session() obj1 = Users(name="alex1") session.add(obj1) session.commit() for i in range(10): t = threading.Thread(target=task, args=(i,)) t.start()
#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine from sqlalchemy.orm import scoped_session from 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) """ # 线程安全，基于本地线程实现每个线程用同一个session # 特殊的：scoped_session中有原来方法的Session中的一下方法： public_methods = ( '__contains__', '__iter__', 'add', 'add_all', 'begin', 'begin_nested', 'close', 'commit', 'connection', 'delete', 'execute', 'expire', 'expire_all', 'expunge', 'expunge_all', 'flush', 'get_bind', 'is_modified', 'bulk_save_objects', 'bulk_insert_mappings', 'bulk_update_mappings', 'merge', 'query', 'refresh', 'rollback', 'scalar' ) """ session = scoped_session(Session) # ############# 执行ORM操作 ############# obj1 = Users(name="alex1") session.add(obj1) # 提交事务 session.commit() # 关闭session session.close()
flask-session默认也是使用的第二种方式：scoped_session
其它
- SQL a. select * from A where id in (select id from B) #不能使用*必须与id类型匹配 b. #1表示常量，每条数据都添加1 select id, name, 1 from A select id, name, 1, (select max(id) from B) as b from A select id, name, 1, (select max(id) from B where B.xid=A.id) as b from A +----+---------------+ | id | name | +----+---------------+ | 1 | 全栈1期099 | | 2 | 全栈2期099 | +----+---------------+ +----+---------+ | id | caption | xid +----+---------+ | 1 | 篮球 | 1 | 2 | 球 | 1 +----+---------+ subqry = session.query(func.count(Server.id).label("sid")).filter(Server.id == Group.id).correlate(Group).as_scalar() result = session.query(Group.name, subqry) """ SELECT `group`.name AS group_name, (SELECT count(server.id) AS sid FROM server WHERE server.id = `group`.id) AS anon_1 FROM `group` """ # 也可以使用原生SQL """ # 查询 cursor = session.execute('select * from users') result = cursor.fetchall() # 添加 cursor = session.execute('insert into users(name) values(:value)',params={"value":'wupeiqi'}) session.commit() print(cursor.lastrowid) """
