Python ORM框架之SQLAlchemy
前言：
Django的ORM虽然强大，但是毕竟局限在Django，而SQLAlchemy是Python中的ORM框架；
SQLAlchemy的作用是：类/对象--->SQL语句--->通过pymysql/MySQLdb模块--->提交到数据库执行；
组成部分：
- Engine，框架的引擎
- Connection Pooling ，数据库连接池
- Dialect，选择连接数据库的DB API种类
- Schema/Types，架构和类型
- SQL Exprression Language，SQL表达式语言
SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，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
pip3 install sqlalchemy #安装sqlalchemy模块
一、 基本使用
1.原生SQL
import time
import threading
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.engine.base import Engine
conn_pool=create_engine( #创建sqlalchemy引擎
"mysql+pymysql://webproject:web@192.168.1.18:3306/web?charset=utf8",
max_overflow=2, #超过连接池大小之后，允许最大扩展连接数；
pool_size=5, #连接池大小
pool_timeout=30,#连接池如果没有连接了，最长等待时间
pool_recycle=-1,#多久之后对连接池中连接进行一次回收
)
#单线程操作线程池
conn = conn_pool.raw_connection() #从连接池中获取1个连接,开始连接
cursor = conn.cursor()
cursor.execute(
"select * from cmdb_worker_order"
)
result = cursor.fetchall()
print(result)
cursor.close()
conn.close()
import time
import threading
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.engine.base import Engine
conn_pool=create_engine( #创建sqlalchemy引擎
"mysql+pymysql://webproject:web@192.168.1.18:3306/web?charset=utf8",
max_overflow=2, #超过连接池大小之后，允许最大扩展连接数；
pool_size=5, #连接池大小
pool_timeout=30,#连接池如果没有连接了，最长等待时间
pool_recycle=-1,#多久之后对连接池中连接进行一次回收
)
#多线程操作线程池
def task(arg):
conn = conn_pool.raw_connection()
cursor = conn.cursor()
cursor.execute(
#"select * from cmdb_worker_order"
"select sleep(2)"
)
result = cursor.fetchall()
cursor.close()
conn.close()
for i in range(20):
t = threading.Thread(target=task, args=(i,)) #5个线程 执行2秒 然后5个线程在去执行2秒
t.start()
mysql> show status like 'Threads%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 1 | | Threads_connected | 8 | | Threads_created | 11 | | Threads_running | 8 | +-------------------+-------+ 4 rows in set (0.00 sec)
2.ORM
2.1单表
a. 创建数据库单表
#创建单表
#!/usr/bin/env python
# -*- coding:utf-8 -*-
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) # primary_key=True设置主键
name = Column(String(32), index=True, nullable=False) #index=True创建索引， nullable=False不为空。
def init_db(): #根据类创建数据库表
engine = create_engine(
"mysql+pymysql://webproject:web@192.168.1.18:3306/web?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间，否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
Base.metadata.create_all(engine) #这行代码很关键哦！！ 读取继承了Base类的所有表在数据库中进行创建
if __name__ == '__main__':
init_db() #执行创建
b.删除表
#!/usr/bin/env python
# -*- coding:utf-8 -*-
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) # primary_key=True设置主键
name = Column(String(32), index=True, nullable=False) #index=True创建索引， nullable=False不为空。
def drop_db(): #根据类 删除数据库表
engine = create_engine(
"mysql+pymysql://webproject:web@192.168.1.18:3306/web?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间，否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
Base.metadata.drop_all(engine) #这行代码很关键哦！！ 读取继承了Base类的所有表在数据库中进行删除表
if __name__ == '__main__':
drop_db() #执行创建
c.添加1条记录
models.py app01.py
2.2.多表
创建多表
二、源码剖析
使用scoped_session(Session) 和Session() 创建的连接的区别？
A.发现问题：
为什么 session =scoped_session(Session) 和 session = Session()，明明是2个没有继承关系的类实例化出来的对象，却有相同的add/commit/...方法？
源码流程分析伪代码B.session =scoped_session(Session) 实例化执行scoped_session的__init__方法，Session参数也就是原session类；
class scoped_session(object): #没有继承
session_factory = None
def __init__(self, session_factory, scopefunc=None):
self.session_factory = session_factory #1.0 ：session_factory=原来的session类
if scopefunc: # 1.1：scopefunc=None 走else分支
self.registry = ScopedRegistry(session_factory, scopefunc)
else:
'''
class ThreadLocalRegistry(ScopedRegistry):
def __init__(self, createfunc):
self.createfunc = createfunc #源session类
self.registry = threading.local() #封装了1个可以隔离多线程之间数据的threading.local()对象：
'''
self.registry = ThreadLocalRegistry(session_factory) #返回1个封装了源session类和threading.local对象的ThreadLocalRegistry对象
C.给 scoped_session类设置 属性 = 1个封装了闭包函数do，封装了这些属性，在用户app里实例化 scoped_session()之后就可以去执行这些do函数了！
def instrument(name):
def do(self, *args, **kwargs): #self=scoped_session对象
return getattr(self.registry(), name)(*args, **kwargs) #self name=add /commit 闭包封装进来的
'''
把一下代码封装到 scoped_session类中去，接下如果执行self就是scoped_session对象 或者ScopedRegistry对象了
了！ self.session_factory = session_factory #session_factory=原来的session类 self.registry = ScopedRegistry(session_factory, scopefunc) #ScopedRegistry对象 name: def do(self, *args, **kwargs): return getattr(self.registry(), add )(*args, **kwargs) ''' return do
D.app中执行session.add(obj1)本质是执行scoped_session类中封装的add属性对应的do函数
def do(self, *args, **kwargs):
# self.registry()=执行ThreadLocalRegistry 或者 scoped_session对象 的__call__方法
return getattr(self.registry(), name)(*args, **kwargs) #self name=add /commit 闭包封装进来的
#最后执行下面的代码！
def __call__(self):
try:
return self.registry.value #去threading.local()获取
except AttributeError: #如果获取不到
val = self.registry.value = self.createfunc() #去源session对象中获取方法
return val
E.得出结论：
scoped_session(Session) 内部使用了threading.local() 实现了对多线程的支持；
F.知识：
__all__ = ['scoped_session'] ：1个py文件中使用了__all__=[ ]限制了导入的变量；
threading.local()：为每1个线程，另外开辟1块新内存，来保存local_value，所以每个线程都可以获取到自己设置的值。
闭包：可以把外部函数数据，传递到内部函数中保存；
三、进阶操作
注意无论SQLalchemy的增、删、改、查操作，最后都需要commit，数据库才会执行SQL；
1.增、删、改操作
单条增加 批量增加 删除操作 修改操作
2.单表查询操作
基本查询操作 条件查询 多查询条件 逻辑运算、嵌套查询 字符串符 模糊匹配查询 限制分页 排序 group_by分组查询和.having二次筛选 连表查询 组合
3.基于relationship的增加、查询操作
relationship可以帮助我们 快速在存在1对多、多对多关系的表之间做反向连表查询和数据添加；
基于relationship 做1对多操作 基于relationship 多对多操作以下为相亲表表结构：
models
4.关联子查询
什么是SQL子查询？
mysql> select id,name,(select max(id) from girl) as maxgirl from boy; #SQL子查询 +----+--------------+---------+ | id | name | maxgirl | +----+--------------+---------+ | 2 | 宋青书 | 7 | | 5 | 尹志平 | 7 | | 3 | 张三丰 | 7 | | 1 | 张无忌 | 7 | | 4 | 方正大师 | 7 | +----+--------------+---------+ 5 rows in set (0.00 sec) mysql>
查询每个学生的平均分！
First, query the SID from Student
Second,with SID query everyone `s socres compute average score。
select id,name，(select avg(score) from 成绩表 where 成绩表.sid =学生表.id ) as avg_socre from 学生表；
SQLALchemy关联子查询操作
四、Flask-SQLAlchemy组件
FlaskSQLAlchemy是flask和SQLALchemy的管理者，其本质是在flask项目中 通过对文件管理、导入，把Flask和QLAlchemy两个组件无缝连接在一起，
还可以帮助我们实现自动开启、关闭连接、配置提升开发效率；
根据一个常见flask项目的目录结构，梳理一下它的运行流程便知；
程序入口run.py导入sansa包执行__init__.py文件
0.导入sansa包会执行sanas的__init__.py文件导入create_app
1.执行create_app函数
run.py
执行sansa.__init__.py
0.导入flask_sqlalchemy,注意这里导入的是flask_sqlalchemy不是原始的sqlalchemy
1.读取、注册flask的配置文件
2.通过配置文件，将flask_sqlalchemy注册到app中
3.通过flask蓝图把account.account(路由和视图) 注册到app里(导入视图)
项目\__init__.py
db对象在执行run.py刚刚启动调用了sansa\__init__.py程序的时候就实例化好了并封装好了 配置文件、self.Model = self.make_declarative_base(model_class, metadata)现在就可以使用了db对象创建models文件了。
开始创建model
0.db在执行run.py刚刚启动调用了sansa\__init__.py程序的时候就实例化好了
1.导入sansa.__init__中的实例化完成的db对象class Users(db.Model):
2.db对象封装好了 配置信息、ORM基类、create_all方法
model.py
读取models.py中的映射去执行SQL创建表
0.加载models表映射关系
1.创建app对象
2.使用db对象根据model这种映射关系执行创建表操作
create_table.py
通过视图操作表
0.导入db对象，包含了engin和 创建连接；
1.导入models；
2. db.session直接获取连接，开始操作。。。。
views\account.py
五、pipreqs组件
拿到别人的新项目之后发现缺少 这个、那个....模块运行不起来，然后根据报错逐一得去pip到最后发现安装得版本不一致；
这不是你的问题而是项目开发者的不够规范；
1.安装pipreqs组件
pip install pipreqs
2.在项目/目录下执行pipreqs ./，搜集项目中所有使用得第三方包；
[root@cmdb cmdb_rbac_arya]# pipreqs ./ INFO: Successfully saved requirements file in ./requirements.txt [root@cmdb cmdb_rbac_arya]# ls 123.txt cron_ansible_api.py manage.py requirements.txt webcron ansible_api_runner.py cron_close_order.py Monaco.ttf static web.sql arya cron_writesql.py multitask templates work_order_remind.py cmdb DBshow nohup.out tools cmdb_rbac_arya Get_biying_image.py rbac w8.pid [root@cmdb cmdb_rbac_arya]# cat requirements.txt paramiko==2.4.1 ansible==2.6.3 PyMySQL==0.8.0 pandas==0.22.0 Django==1.11.7 XlsxWriter==1.0.4 redis==2.10.6 requests==2.18.4 Pillow==5.3.0 [root@cmdb cmdb_rbac_arya]#
