Flask组件之SQLAlchem（一）
SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上，使用关系对象映射进行数据库操作，简言之便是：将类和对象转换成SQL，然后使用数据API执行SQL并获取执行结果!
安装SQLAlchemy：
pip3 install sqlalchemy
SQLAlchemy -- 类/对象操作 -- SQL -- pymysql/mysqldb -- 数据库中执行
SQLAlchemy本身是无法直接连接数据库，需要依赖 pymysql/mysqldb 来执行数据库语句
Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：
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
使用
执行原生SQL语句
""" 简单使用一 """ import time import threading import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.engine.base import Engine engine = create_engine( # 连接数据库参数 "mysql+pymysql://root:123456@127.0.0.1:3306/jd?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 jddata" ) result = cursor.fetchall() cursor.close() conn.close() return result for i in range(20): t = threading.Thread(target=task, args=(i,)) t.start() data = task(arg="sql") print(data)
#!/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:123456@127.0.0.1:3306/jd", max_overflow=0, pool_size=5) def task(arg): conn = engine.contextual_connect() with conn: cur = conn.execute( "select * from jddata" ) 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:123456@127.0.0.1:3306/jd", max_overflow=0, pool_size=5) def task(arg): cur = engine.execute("select * from jddata") result = cur.fetchall() cur.close() print(result) for i in range(20): t = threading.Thread(target=task, args=(i,)) t.start()
注意: 查看连接 show status like 'Threads%';
ORM
1、简单demo目录
a. 创建数据库表
""" 创建表/删除表 """ import datetime from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index from sqlalchemy.orm import relationship Base = declarative_base() class Users(Base): # 生成得数据库表名 __tablename__ = 'users' # Integer：字段类型，primary_key：主键 id = Column(Integer, primary_key=True) # String(32)：字段类型/长度，index：索引，nullable：是否为空/False不能为空 name = Column(String(32), index=True, nullable=False) # String(32)：字段类型/长度，unique：唯一索引 email = Column(String(32), unique=True) # DateTime：字段类型，default：默认值 ctime = Column(DateTime, default=datetime.datetime.now) extra = Column(Text, nullable=True) # 指定参数 __table_args__ = ( # UniqueConstraint：联合唯一索引 UniqueConstraint('id', 'name', name='uix_id_name'), # 普通索引 # Index('ix_id_name', 'name', 'email'), ) # ##################### 一对多示例 ######################### class Hobby(Base): __tablename__ = 'hobby' 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) # ForeignKey：外键关联表名__tablename__ hobby_id = Column(Integer, ForeignKey("hobby.id")) # 与生成表结构无关，仅用于查询方便 hobby = relationship("Hobby", backref='pers') # ##################### 多对多示例 ######################### sqlalchemy不支持帮助生成多对多的第三张表 class Girl2Boy(Base): __tablename__ = 'girl2boy' id = Column(Integer, primary_key=True, autoincrement=True) girl_id = Column(Integer, ForeignKey('girl.id')) boy_id = Column(Integer, ForeignKey('boy.id')) class Girl(Base): __tablename__ = 'girl' id = Column(Integer, primary_key=True) name = Column(String(64), unique=True, nullable=False) # 与生成表结构无关，仅用于查询方便 boys = relationship('Boy', secondary='girl2boy', backref='girls') class Boy(Base): __tablename__ = 'boy' id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(64), unique=True, nullable=False) # 只能创建和删除/不能修改 class CreatTable(object): def __init__(self,username,password,host,port,db,charset): self.engine = create_engine( "mysql+pymysql://%s:%s@%s:%s/%s?charset=%s"%(username,password,host,port,db,charset), max_overflow=0, # 超过连接池大小外最多创建的连接 pool_size=5, # 连接池大小 pool_timeout=30, # 池中没有线程最多等待的时间，否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置） ) def init_DB(self): # 读取base 里面所有得表，生成在数据库中 Base.metadata.create_all(self.engine) def drop_DB(self): # 读取base 里面所有得表，删除 Base.metadata.drop_all(self.engine) if __name__ == '__main__': db_data = CreatTable( username='root', password='123456', host='127.0.0.1', port=3306, db='t1', charset='utf8' ) # db_data.drop_DB() db_data.init_DB()
b. 操作数据库表
""" 基本的增加操作 """ from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine import models # 创建数据库连接池 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/t1", max_overflow=0, pool_size=5) Connection = sessionmaker(bind=engine) # 从连接池中获取数据库连接 conn = Connection() # ############# 执行ORM操作 ############# obj = models.Users(name="xxx",email='123456789@qq.com') conn.add(obj) # 提交事务 conn.commit() # 关闭数据库连接 conn.close()
2、更多操作
from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine from sqlalchemy.orm import scoped_session from sqlalchemy.sql import text from models import Users # 创建数据库连接池 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/t1", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) # 从连接池中获取数据库连接 # 第一种连接方式 session = Session() # ----------------------------------------------条件 # 查询条件为name='alex'的数据 # ret = session.query(Users).filter_by(name='alex').all() # 查询条件id大于1 和name=eric # 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() # 查询在列表里面的数据 # ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() # 查询不再列表里面的数据 ~非的意思 # ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() # 查询表1 user_id在表2 user_id里面的数据 # ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() # and_, or_ 另一种表现形式 and_(里面是用and连接)/or_(里面是用or连接) 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() # ----------------------------------------------通配符 like模糊查询 # ret = session.query(Users).filter(Users.name.like('e%')).all() # ret = session.query(Users).filter(~Users.name.like('e%')).all() # ----------------------------------------------限制 只取[1~2]的数据 # ret = session.query(Users)[1:2] # ----------------------------------------------排序 desc：从大到小 asc：从小到大 # 优先 Users.name.desc() 再按照 Users.id.asc() # 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 # group_by 根据Users.extra分组 # ret = session.query(Users).group_by(Users.extra).all() # func 使用聚合函数时需要导入 分组后求聚合 # ret = session.query( # func.max(Users.id), # func.sum(Users.id), # func.min(Users.id)).group_by(Users.name).all() # having 使用分组查询后按照聚合来再次筛选 # 比如需要查询部门人数大于2的数据，将部门分组(group_by)再按人数(having)聚合查询 # 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() # ----------------------------------------------连表 """ 注意注意 使用join连表时默认使用外键来ON 否则需要带参数 session.query(Person).join(Favor,Favor.id==Person.id isouter=True).all() """ # 直接查询两个表 # ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all() # join 默认内联表查询 == inner join # ret = session.query(Person).join(Favor).all() # join(table2,isouter=True) 外联表查询 == left join 没有right join # ret = session.query(Person).join(Favor, isouter=True).all() # ----------------------------------------------组合 # 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()
""" 基本的增加操作 """ from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine from sqlalchemy.orm import scoped_session import models # 创建数据库连接池 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/t1", max_overflow=0, pool_size=5) Connection = sessionmaker(bind=engine) # 从连接池中获取数据库连接 # 第一种连接方式 # conn = Connection() # 第二种连接方式 from sqlalchemy.orm import scoped_session """ # 线程安全，基于本地线程实现每个线程用同一个conn # 特殊的：scoped_session中有原来方法的Connection中的一下方法： 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' ) """ conn = scoped_session(Connection) # ############# 执行ORM操作 ############# obj = models.Users(name="xxx",email='123456789@qq.com') conn.add(obj) # 提交事务 conn.commit() # 关闭数据库连接 conn.close()
""" 增删改查操作 """ from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine from sqlalchemy.orm import scoped_session from sqlalchemy.sql import text import models # 创建数据库连接池 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/t1", max_overflow=0, pool_size=5) Connection = sessionmaker(bind=engine) # 从连接池中获取数据库连接 # 第一种连接方式 conn = Connection() # 第二种连接方式 from sqlalchemy.orm import scoped_session # conn = scoped_session(Connection) """ # 线程安全，基于本地线程实现每个线程用同一个conn # 特殊的：scoped_session中有原来方法的Connection中的一下方法： 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' ) """ # ---------------- 增加操作 ---------------- """ # 新增数据 obj = models.Users(name="xxx2",email='1234567892@qq.com') conn.add(obj) # 批量增加 conn.add_all([ models.Users(name="xxx3",email='1234567893@qq.com'), models.Users(name="xxx4",email='1234567894@qq.com'), ]) """ # ---------------- 查询操作 ---------------- """ # 拿到对象列表 == * user_list_obj = conn.query(models.Users).all() # 拿到name和email字段列表 label == as 取别名可循环列表按照names来取值 user_list_obj = conn.query(models.Users.name.label('names'), models.Users.email).all() # 拿到ID大于2的对象列表 filter == where filter传表达式 user_list_obj = conn.query(models.Users).filter(models.Users.id > 2) # 拿到name='小明099099099099099'的所有数据 filter_by传参数 user_list_obj = conn.query(models.Users).filter_by(name='小明099099099099099').all() # 拿到name='小明099099099099099'的第一条数据 filter_by传参数 user_list_obj = conn.query(models.Users).filter_by(name='小明099099099099099').first() # 需要引入from sqlalchemy.sql import text # filter(text("id<:value and name=:name")).params(value=224, name='fred') :后边是占位符 params传的参数 user_list_obj = conn.query(models.Users).filter(text("id<:value and name=:name")).params(value=20, name='xxx').order_by(models.Users.id).all() # 使用text来操作sql语句 params携带参数 user_list_obj = conn.query(models.Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='xxx').all() print(user_list_obj) for user_obj in user_list_obj: print(user_obj.name) print(user_obj.email) print(user_obj.ctime) """ # ---------------- 删除操作 ---------------- """ # 删除ID大于2的所有对象 conn.query(models.Users).filter(models.Users.id > 2).delete() """ # ---------------- 修改操作 ---------------- """ # 修改ID等于1的name字段为小明 conn.query(models.Users).filter(models.Users.id == 1).update({"name" : "小明"}) # 在每一个原有的name值追加一个字符串，注意携带参数 synchronize_session=False conn.query(models.Users).filter(models.Users.id > 0).update({models.Users.name:models.Users.name + "099"}, synchronize_session=False) # 在每一个原有的int类型追加，注意携带参数 synchronize_session="evaluate" conn.query(models.Users).filter(models.Users.id > 0).update({"age":models.Users.age + 1}, synchronize_session="evaluate") """ # 提交事务 更新/添加/删除都需要commit一下 conn.commit() # 关闭数据库连接 conn.close()
#!/usr/bin/env python # -*- coding:utf-8 -*- import time import threading from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine import models engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/t1?charset=utf8", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) session = Session() """ 添加数据 """ # session.add_all([ # models.Hobby(caption='排球'), # models.Hobby(caption='棒球'), # models.Person(name='小明',hobby_id=1), # models.Person(name='小红',hobby_id=2), # models.Person(name='小白',hobby_id=1), # ]) # session.commit() """ -----------------------------------------------跨表查询数据----------------------------------------------- """ # 方式一 使用join连表查询 # person_list = session.query(models.Person.name,models.Hobby.caption).join(models.Hobby,isouter=True).all() # for person in person_list: # print(person.name) # # 取爱好的中文 # print(person.caption) # 方式二 relationship快速查询 # relationship正向查询 # models 中需要使用relationship 建关联关系 hobby = relationship("Hobby", backref='pers') # person_list = session.query(models.Person).all() # for person in person_list: # print(person.name) # # 取爱好的中文 # print(person.hobby.caption) # relationship反向查询 喜欢Hobby.id == 1的所有人 # hobby_obj = session.query(models.Hobby).filter(models.Hobby.id == 1).first() # person_list = hobby_obj.pers # for i in person_list: # print(i.name) """ -----------------------------------------------跨表增加数据----------------------------------------------- """ # 使用relationship增加数据 # 正向添加数据 # hb = models.Person(name='刘五',hobby=models.Hobby(caption='篮球')) # session.add(hb) # session.commit() # 反向添加数据 # hb = models.Hobby(caption='羽毛球') # hb.pers = [models.Person(name='小黑'),models.Person(name='大黑')] # session.add(hb) # session.commit() session.close()
""" M2M操作 """ import time import threading from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine import models engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/t1?charset=utf8", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) session = Session() # 普通添加数据 # session.add_all([ # models.Girl(name='小美女'), # models.Girl(name='小可爱'), # models.Boy(name='小帅哥'), # models.Boy(name='大帅哥'), # ]) # session.commit() # g2b = models.Girl2Boy(girl_id=1, boy_id=1) # session.add(g2b) # session.commit() """ -----------------------------------------------跨表增加数据----------------------------------------------- """ # relationship 操作M2M """ class Girl(Base): __tablename__ = 'girl' id = Column(Integer, primary_key=True) name = Column(String(64), unique=True, nullable=False) # 与生成表结构无关，仅用于查询方便 # 第一个参数关联Boy表，第二个参数通过girl2boy表来关联，第三个参数Boy表反向操作使用字段girls boys = relationship('Boy', secondary='girl2boy', backref='girls') """ # 正向操作增加 # girl = models.Girl(name='黑人') # girl.boys = [models.Boy(name='大帅哥'),models.Boy(name='小帅哥')] # session.add(girl) # session.commit() # 反向操作增加 # boy = models.Boy(name='黑帅哥') # boy.girls = [models.Girl(name='小小美女'),models.Girl(name='小小可爱')] # session.add(boy) # session.commit() """ -----------------------------------------------跨表查询数据----------------------------------------------- """ # 正向查询 # v = session.query(models.Girl).all() # for i in v: # print(i.name) # print(i.boys) # 反向查询 # boy_obj = session.query(models.Boy).filter_by(id=3).first() # girl_list = boy_obj.girls # for i in girl_list: # print(i.name) session.close()
""" 执行原生sql """ 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 sqlalchemy.sql import text from sqlalchemy.engine.result import ResultProxy import models engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/t1", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) session = Session() # 查询 # 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) session.close()
""" 其他操作 """ import time import threading from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine from sqlalchemy.sql import text, func import models engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/t1?charset=utf8", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) session = Session() # 关联子查询 subqry = session.query(func.count(models.Girl2Boy.girl_id).label("g2ball")).filter(models.Girl2Boy.girl_id == models.Girl.id).correlate(models.Girl).as_scalar() print(subqry) """ (SELECT count(girl2boy.girl_id) AS g2ball FROM girl2boy, girl WHERE girl2boy.girl_id = girl.id) """ result = session.query(models.Girl.name, subqry).all() print(result) """ SELECT girl.name AS girl_name, (SELECT count(girl2boy.girl_id) AS g2ball FROM girl2boy WHERE girl2boy.girl_id = girl.id) AS anon_1 FROM girl """ # 原生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) """ session.close()
浙公网安备 33010602011771号