第五篇 Flask组件之SQLAchemy及Flask-SQLAlchemy插件/Flask-Script/Flask-migrate/pipreqs模块
SQLAlchemy组件
一. 介绍
SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上,使用关系对象映射进行数据库操作,简言之便是:将类和对象转换成SQL,然后使用数据API执行SQL并获取执行结果。
# 安装 pip3 install sqlalchemy
组成部分:
- Engine,框架的引擎
- Connection Pooling ,数据库连接池
- Dialect,选择连接数据库的DB API种类(即选择是用pymysql还是mysqldb)
- Schema/Types,架构和类型
- SQL Exprression Language,SQL表达式语言
SQLAlchemy本身无法操作数据库,其必须以pymsql等第三方插件,Dialect用于和数据API进行交流,根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作,如:
下面这些链接是字符串:在Dialect里
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
要使用这些,必须先安装对应的 mysqldb、pymysql、mysqlconnector、 cx_oracle
二.基本使用(一般不按照该示例怎么写,只为了说明)
1. 连接池
示例1:连接池始终只有一个链接
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import time import threading import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.engine.base import Engine engine = create_engine( # 用pymysql链接mysql; # root:123 用户名:密码 # 127.0.0.1:3006 数据库ip及端口 # t1:数据库名 # charset=utf8:编码 "mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8", max_overflow=2, # 超过连接池大小外最多创建的连接(即5个已经不够用了,最多再能创建2个,也就是总共最多创建7个链接池) pool_size=5, # 连接池大小,最多5个 pool_timeout=30, # 池中没有线程最多等待的时间(秒),否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置) ) conn = engine.raw_connection() # 去链接池拿一个链接 cursor = conn.cursor() # 在链接里拿个cursor,这里其实已经执行了pymysql里的功能了 # 执行sql语句 cursor.execute( "select * from t1" ) result = cursor.fetchall() cursor.close() conn.close()
示例二:连接池有多个链接
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import time import threading import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.engine.base import Engine engine = create_engine( # 用pymysql链接mysql; # root:123 用户名:密码 # 127.0.0.1:3006 数据库ip及端口 # t1:数据库名 # charset=utf8:编码 "mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8", max_overflow=0, # 超过连接池大小外最多创建的连接(即5个已经不够用了,最多再能创建2个,也就是总共最多创建7个链接池) pool_size=5, # 连接池大小,最多5个 pool_timeout=30, # 池中没有线程最多等待的时间(秒),否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置) ) def task(arg): conn = engine.raw_connection() # 去链接池拿一个链接 cursor = conn.cursor() # 在链接里拿个cursor,这里其实已经执行了pymysql里的功能了 # 执行sql语句 cursor.execute( "select * from t1" "select sleep(2)" ) result = cursor.fetchall() cursor.close() conn.close() # 创建了20个线程 # 如果速度特别快,可能一个链接就够了 # 如果速度特别慢,可能是5个5个的执行的。 for i in range(20): t = threading.Thread(target=task, args=(i,)) t.start()
2. ORM
a.定义数据库表、创建表单、删除表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index # 标准写法: Base = declarative_base() # 1. 定义表名及表里的字段,继承Base class Users(Base): __tablename__ = 'users' # 生成的数据库表名 # 表里的具体字段 # id列,id是主键 id = Column(Integer, primary_key=True) # name列,字符串类型(最大32个字符),index是索引,nullable:是否可为空,Flase表示不可为空 name = Column(String(32), index=True, nullable=False) # 2. 单纯使用sqlAlchemy创建表 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) # 读取Base里所有的表,在数据库里生成表 # 删除表 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) # 3.修改表单纯使用sqlalchemy做不到,需要用其他组件才可以。 if __name__ == '__main__': drop_db() init_db()
b.操作数据库表- 增删改查
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine from models import Users # 创建链接池 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5) Connection = sessionmaker(bind=engine) # 每次执行数据库操作时,都需要创建一个Connection链接 conn = Connection() # ############# 执行ORM操作-增加操作 ############# obj1 = Users(name="alex1") conn.add(obj1) # 提交事务 conn.commit() # 关闭session conn.close()
三.具体(重点)
1. 定义单表
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 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) # unique 表示唯一索引 ctime = Column(DateTime, default=datetime.datetime.now) # 创建时间:datetime.datetime.now,now后面不能加(),因为它是静态字段 extra = Column(Text, nullable=True) # 创建联合唯一索引 __table_args__ = ( # UniqueConstraint('id', 'name', name='uix_id_name'), # id 和 name 做了联合唯一 # Index('ix_id_name', 'name', 'email'), # name 和 email 做了联合索引 ) # 问题: # 1. 字符编码怎么指定?
2. 定义多表
# ##################### 一对多示例 ######################### 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) hobby_id = Column(Integer, ForeignKey("hobby.id")) # 通过表名.字段名关联 # 与生成表结构无关,仅用于查询方便 hobby = relationship("Hobby", backref='pers')
# ##################### 多对多示例 ######################### # 多对多关系表 class Server2Group(Base): __tablename__ = 'server2group' id = Column(Integer, primary_key=True, autoincrement=True) # 在这里生成多对多关系的 server_id = Column(Integer, ForeignKey('server.id')) group_id = Column(Integer, ForeignKey('group.id')) class Group(Base): __tablename__ = 'group' id = Column(Integer, primary_key=True) name = Column(String(64), unique=True, nullable=False) # 与生成表结构无关,仅用于查询方便 servers = relationship('Server', secondary='server2group', backref='groups') class Server(Base): __tablename__ = 'server' id = Column(Integer, primary_key=True, autoincrement=True) hostname = Column(String(64), unique=True, nullable=False)
3. 执行生成并创建表
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) if __name__ == '__main__': init_db()
4. 执行删除表
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()
5. 操作表
上面分别介绍了表的创建,下面对表进行操作的详细介绍
创建表一般只操作一次,所以放到 models.py文件里
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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' # 表里的字段 id = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=False) email = Column(String(32), unique=True) # unique 表示唯一索引 ctime = Column(DateTime, default=datetime.datetime.now) # 创建时间:datetime.datetime.now,now后面不能加(),因为它是静态字段 extra = Column(Text, nullable=True) # 创建联合唯一索引 __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), # id 和 name 做了联合唯一 Index('ix_id_name', 'name', 'email'), # name 和 email 做了联合索引 ) # 问题: # 1. 字符编码怎么指定? # ##################### 一对多示例 ######################### 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) hobby_id = Column(Integer, ForeignKey("hobby.id")) # 通过表名.字段名关联 # 与生成表结构无关,仅用于查询方便 hobby = relationship("Hobby", backref='pers') # ##################### 多对多示例 ######################### # 多对多关系表 class Server2Group(Base): __tablename__ = 'server2group' id = Column(Integer, primary_key=True, autoincrement=True) # 在这里生成多对多关系的 server_id = Column(Integer, ForeignKey('server.id')) group_id = Column(Integer, ForeignKey('group.id')) class Group(Base): __tablename__ = 'group' id = Column(Integer, primary_key=True) name = Column(String(64), unique=True, nullable=False) # 与生成表结构无关,仅用于查询方便 servers = relationship('Server', secondary='server2group', backref='groups') class Server(Base): __tablename__ = 'server' id = Column(Integer, primary_key=True, autoincrement=True) hostname = Column(String(64), unique=True, nullable=False) 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) if __name__ == '__main__': init_db() 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()
SQLAlchemy详细介绍
1. SQLAlchemy之两种连接方式:
(1)第一种数据库连接方式 sessionmaker
from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine import models # 1.创建连接池 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf-8",max_overflow = 0 , pool_size = 5) Conn = sessionmaker(bind=engine) # 2.从连接池中获取数据库连接 conn = Conn() # ###############执行ORM操作##################### # 3.执行ORM操作 obj1 = models.Users(name="alex1",email="alex1@xx.com") conn.add(obj1) conn.commit() # 4.关闭数据库连接(将连接放回连接池) conn.close()
(2)第二种数据库连接方式 scoped_session --- 推荐这种
from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine from sqlalchemy.orm import scoped_session # 第二种方式 import models # 1.创建连接池 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf-8",max_overflow = 0 , pool_size = 5) Conn = sessionmaker(bind=engine) # 2.从连接池中获取数据库连接 conn = scoped_session(Conn) # ###############执行ORM操作##################### # 3.执行ORM操作 obj1 = models.Users(name="alex1",email="alex1@xx.com") # 本质执行do函数:add conn.add(obj1) # 本质调用do函数:commit conn.commit() # 4.关闭数据库连接(将连接放回连接池) conn.close()
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/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()
2. SQLAlchemy的基本操作-增删改查(*****)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine import models # 1.创建连接池 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf-8",max_overflow = 0 , pool_size = 5) Conn = sessionmaker(bind=engine) # 2.从连接池中获取数据库连接 conn = Conn() # ###############执行ORM操作##################### # 3.执行ORM操作 ############### 增加 ############# # add 增单条增加 obj1 = models.Users(name="alex1",email="alex1@xx.com") # 增加的时候先创建一个对象,然后放到add()或者add_all() conn.add(obj1) conn.commit() # add_all():批量增加 conn.add_all([ models.Users(name="alex2",email="alex2@xx.com"), models.Users(name="alex3",email="alex3@xx.com"), models.Users(name="alex4",email="alex4@xx.com") ]) conn.commit() ############### 查询 ############# # 查的表:models.Users; user_list = conn.query(models.Users).all() # all()查出所有的内容了 for row in user_list: print(row.id) print(row.name) print(row.email) print(row.ctime) conn.commit() """ 其他查询,下面的Users前面都省略了models.,用的时候加上 r1 = conn.query(Users).all() r2 = conn.query(Users.name.label('xx'), Users.age).all() # label('xx') 相当于取了个别名 r3 = conn.query(Users).filter(Users.name == "alex").all() # filter里传的是表达式 r4 = conn.query(Users).filter_by(name='alex').all() # filter_by 里面传的是参数 r5 = conn.query(Users).filter_by(name='alex').first() # first,第一个对象 # 构造复杂点的sql # text("id<:value and name=:name"):意识是id<x,name=y,后面的params是具体的参数;order_by:是排序 r6 = conn.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all() # 构造更复杂点的sql r7 = conn.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all() """ # 查询出id > 2的数据 user_list = conn.query(models.Users).filter(models.Users.id > 2) conn.commit() ############### 删除 ############# conn.query(models.Users).filter(models.Users.id > 2).delete() conn.commit() ############### 更改 ############# # 改的时候,update传的是字典 conn.query(models.Users).filter(models.Users.id == 1).update({"name":"eric"}) # 字符串相加,后面要写synchronize_session = False conn.query(models.Users).filter(models.Users.id > 0).update({models.Users.name:models.Users.name + "999"}, synchronize_session = False) # 如果是数字相加,要加上synchronize_session = "evaluate" conn.query(models.Users).filter(models.Users.id > 0).update({models.Users.age:models.Users.age + 1}, synchronize_session = "evaluate") conn.commit() # 4.关闭数据库连接(将连接放回连接池) conn.close()
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/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 sqlalchemy.sql import text from db import Users, Hosts engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) session = Session() # ################ 添加 ################ """ obj1 = Users(name="wupeiqi") session.add(obj1) session.add_all([ Users(name="wupeiqi"), Users(name="alex"), Hosts(name="c1.com"), ]) session.commit() """ # ################ 删除 ################ """ session.query(Users).filter(Users.id > 2).delete() session.commit() """ # ################ 修改 ################ """ session.query(Users).filter(Users.id > 0).update({"name" : "099"}) session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "099"}, synchronize_session=False) session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate") session.commit() """ # ################ 查询 ################ """ r1 = session.query(Users).all() r2 = session.query(Users.name.label('xx'), Users.age).all() r3 = session.query(Users).filter(Users.name == "alex").all() r4 = session.query(Users).filter_by(name='alex').all() r5 = session.query(Users).filter_by(name='alex').first() r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all() r7 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all() """ session.close() 基本增删改查示例
3. SQLAlchemy的常用操作(*****)
分组、分页、模糊查询等
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
########### 条件 ########### # filter与filter_by的区别:filter里传参数,filter_by里传表达式 ret = session.query(Users).filter_by(name='alex').all() ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all() # ,表示 and 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() # in_ 固定搭配 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() # 嵌套 # 导入 and_ 和 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() # 表示or_里的两个都是or的关系 # 可以嵌套 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() # 以e开头,%代表所有字符 ret = session.query(Users).filter(~Users.name.like('e%')).all() # 不以e开头,%代表所有字符 ############ 限制 ############ ret = session.query(Users)[1:2] ########### 排序 ############## ret = session.query(Users).order_by(Users.name.desc()).all() # 根据name按照从大到小排序 ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() # 写多个,优先按照name从大到小排序,如有重名,再按id从小到大排 ############## 分组 ############### from sqlalchemy.sql import func # 导入聚合函数 ret = session.query(Users).group_by(Users.extra).all() # 根据extra分组 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 和 left join的区别? join(Favor).all()是一个整体 # 表里有外键,才可以这么连表 ret = session.query(Person).join(Favor, isouter=True).all() # left join Person left join Favor # 如果没有外键,可以写参数 ''' .all():表示取值 如果想看sql语句是什么,就先去掉.all() ret = session.query(Person).join(Favor, isouter=True) print(ret) 得到的就是 sql 语句 ''' ############# 组合 ############ # union 和 union_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()
4.SqlAlchemy也支持原生sql(重点也支持原生sql)
上面是orm里的sql操作,如果还有更复杂的sql,就可以写原生sql
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/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 sqlalchemy.sql import text from sqlalchemy.engine.result import ResultProxy from db import Users, Hosts engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", 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()
5.SQLAlchemy之一对多relationship(****)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 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) hobby_id = Column(Integer,ForeignKey('hobby.id')) # relationship与数据库没关系,不会再数据库里生成这个字段的。关联的是Hobby表,作用是快速帮你做连表操作 hobby = relationship("Hobby", backref = 'pers') # backref 表示可以反向关联
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
# -*- 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 sqlalchemy.sql import text from sqlalchemy.engine.result import ResultProxy # 1.创建连接池 engine = create_engine( "mysql+pymysql://root@127.0.0.1:3306/s7?charset=utf-8", max_overflow = 0, pool_sise =5 ) Session = sessionmaker(bind=engine) # 2. 从连接池中获取数据库连接 session = Session() # 3. 执行ORM操作 # 先分别给两张表里新增数据 # 给hobby里新增数据 session.add_all([ models.Hobby(caption='姑娘'), models.Hobby(caption='足球'), ]) session.commit() # 给person表里新增人 session.add_all([ models.Person(name='李志',id = 2), models.Person(name='龙龙',id = 1), models.Person(name='大龙',id = 3), ]) session.commit() # 查所有的用户表person表 person_list = session.query(models.Person).all() for row in person_list: print(row.name, row.hobby_id) # 需求:把hobby_id对应的中文名字全部拿出来--连表操作 喜欢足球的所有人 # 方式一 person_list = session.query(models.Person.name, models.Hobby.caption).join(models.Hobby, isouter=True).all() for row in person_list: print(row.name, row.hobby_id, row.caption) # 方式二:通过加relationship自动,进行自动连表查询 # 正向关联 person_list = session.query(models.Person).all() for row in person_list: print(row.name, row.hobby.caption) # 或:也可以进行反向关联 喜欢姑娘的所有人 obj = session.query(models.Hobby).filter(models.Hobby.id == 1).first() persons = obj.pers print(persons) # releationship也可以做增加 hb = models.Hobby(caption = "人妖") hb.pers = [models.Person(name = "liuwu"),models.Person=(name = "liz")] session.add(hb) session.commit() # obj = models.Person(name = "lili", hobby = models.Hobby(caption = "人妖2")) session.add(obj) session.commit() # 4. 关闭数据库连接(将连接放回连接池)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/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 sqlalchemy.sql import text from sqlalchemy.engine.result import ResultProxy from db import Users, Hosts, Hobby, Person engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) session = Session() # 添加 """ session.add_all([ Hobby(caption='乒乓球'), Hobby(caption='羽毛球'), Person(name='张三', hobby_id=3), Person(name='李四', hobby_id=4), ]) person = Person(name='张九', hobby=Hobby(caption='姑娘')) session.add(person) hb = Hobby(caption='人妖') hb.pers = [Person(name='文飞'), Person(name='博雅')] session.add(hb) session.commit() """ # 使用relationship正向查询 """ v = session.query(Person).first() print(v.name) print(v.hobby.caption) """ # 使用relationship反向查询 """ v = session.query(Hobby).first() print(v.caption) print(v.pers) """ session.close() 基于relationship操作ForeignKey
6.SQLAlchemy之多对多relationship
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 Server2Group(Base): __tablename__ = 'server2group' id = Column(Integer, primary_key=True, autoincrement=True) # 在这里生成多对多关系的 server_id = Column(Integer, ForeignKey('server.id')) group_id = Column(Integer, ForeignKey('group.id')) class Group(Base): __tablename__ = 'group' id = Column(Integer, primary_key=True) name = Column(String(64), unique=True, nullable=False) # 与生成表结构无关,仅用于查询方便 servers = relationship('Server', secondary='server2group', backref='groups') class Server(Base): __tablename__ = 'server' id = Column(Integer, primary_key=True, autoincrement=True) hostname = Column(String(64), unique=True, nullable=False)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/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 sqlalchemy.sql import text from sqlalchemy.engine.result import ResultProxy from db import Users, Hosts, Hobby, Person, Group, Server, Server2Group engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) session = Session() # 添加 """ session.add_all([ Server(hostname='c1.com'), Server(hostname='c2.com'), Group(name='A组'), Group(name='B组'), ]) session.commit() s2g = Server2Group(server_id=1, group_id=1) session.add(s2g) session.commit() gp = Group(name='C组') gp.servers = [Server(hostname='c3.com'),Server(hostname='c4.com')] session.add(gp) session.commit() ser = Server(hostname='c6.com') ser.groups = [Group(name='F组'),Group(name='G组')] session.add(ser) session.commit() """ # 使用relationship正向查询 """ v = session.query(Group).first() print(v.name) print(v.servers) """ # 使用relationship反向查询 """ v = session.query(Server).first() print(v.hostname) print(v.groups) """ session.close()
7.其他
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/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 sqlalchemy.sql import text, func from sqlalchemy.engine.result import ResultProxy from db import Users, Hosts, Hobby, Person, Group, Server, Server2Group engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) session = Session() # 关联子查询 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` """ # (SELECT count(server.id) 只能一个值 # 原生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() 其他
Flask-SQLAlchemy插件
就是Flask和SQLALchemy的管理者,让flask和sqlAlchemy无缝连接起来
本质上还是目录和文件的管理
所以目录结构要保存好。
文件见:链接:https://pan.baidu.com/s/1aOaeCGCEPkTQnLe27Sdnow 密码:gzsq
Flask-Migrate组件
SqlAlchemy本身不支持更改表结构,所以需要借助Flask-Migrate第三方组件操作
作用:flask-migrate用于实现类似Django数据库迁移:makemigrations/migrate ->migrate/upgrade
# 安装 pip install flask-migrate
from flask_script import Manager from flask import Flask from sansa import create_app, db # 数据库迁移需要配置的项 # 第一:导入 from flask_migrate import Migrate, MigrateCommand app = create_app() manage = Manager(app) # 第二 migrate = Migrate(app, db) # 第三 manage.add_command('db', MigrateCommand) ''' 配置好上面三项,就可以在cmd里执行下面的命令迁移数据库了 数据库迁移命令: python manage.py db init python manage.py db migrate python manage.py db upgrade ''' if __name__ == '__main__': manage.run()
Flask-script组件
作用:用于实现类似 django python manage.py runserver...这样的脚本
# 安装 pip install flask-script
# 使用 from flask_script import Manager # 导入 Manage from flask import Flask app = Flask(__name__) manage = Manager(app) # 实例化manage app.route("/") def index(): return "hello flask-script" if __name__ == '__main__': manage.run()
先右键run起来程序,然后就可以在命令行里通过命令运行了
# 通过命令运行 python manage.py runserver
from flask_script import Manager from flask import Flask # 类似位置参数方式 @manage.command() def custom(arg): ''' 自定义命令 执行: python manage.py custom 123 ( 123 是传入的参数 ) custom 表示要执行这个函数 :param arg: :return: ''' # 可以把离线脚本放入这里 from sansa import create_app from sansa import db app = create_app() with app.app_context(): db.create_all() # 类似关键字参数方式 @manage.option('-n', '--name', dest = 'name') @manage.option('-u','--url',dest = 'url') def cmd(name,url): ''' 自定义命令 执行: python manage.py cmd - n mamingchen -u http://www.baidu.com - n ,-u: 都表示要传参数了 :param name: :param url: :return: ''' print(name,url) if __name__ == '__main__': manage.run()
Flask-RESTful组件
pipreqs模块
一个项目经常会安装很多组件或者插件,都有不同的版本,怎么才能知道这项目都用到了哪些模块,什么版本呢?
python有个模块可以很方便的干这件事.
1. 安装
pip install pipreqs
2. 检查并生成一个requirements.txt
# 必须在程序的根目录执行下面的命令 pipreqs ./
3. pipreqs 还可以导入自动安装还没有安装的插件
4. pycharm本身也可以自动检查程序并提示你安装还没有安装的插件
研究一下:
fabric
ansible
saltstack