pymysql模块+ 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条记录

# 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()


#多线程操作线程池
# 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()
#
#


#创建单表
#!/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 create_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__':
    create_db()                          #执行创建

from SqlALchemy.models import Users
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine( "mysql+pymysql://webproject:web@192.168.1.18:3306/web?charset=utf8")
Session = sessionmaker(bind=engine)

# 每次执行数据库操作时，都需要创建一个会话
session = Session()

# ############# 执行ORM操作 #############
obj1 = Users(name="张根") #创建Users对象=1行数据
session.add(obj1)          #添加到表中

# 提交事务
session.commit()
# 关闭session
session.close()

 

2.2.多表

#!/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
from sqlalchemy.orm import relationship

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不为空。
    age = Column(Integer, default=18)        #数字字段
    email = Column(String(32), unique=True)  #设置唯一索引
    ctime = Column(DateTime, default=datetime.datetime.now) #设置默认值为当前时间（注意千万不要datetime.datetime.now（））
    extra = Column(Text, nullable=True)         #文本内容字段
    __table_args__ = (
        # UniqueConstraint('id', 'name', name='uix_id_name'), #设置联合唯一索引
        # Index('ix_id_name', 'name', 'extra'),                #设置联合索引
    )




class Hosts(Base):
    __tablename__ = 'hosts'

    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True)
    ctime = Column(DateTime, default=datetime.datetime.now)


# ##################### 一对多示例 #########################
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://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)





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__':
    init_db()                      #执行创建

 

 

二、源码剖析

使用scoped_session(Session) 和Session() 创建的连接的区别？

 

A.发现问题：

为什么 session =scoped_session(Session)  和 session = Session()，明明是2个没有继承关系的类实例化出来的对象，却有相同的add/commit/...方法？

 

class A(object):
    fields=('f1','f2' )
    def f1(self):
        print('A类的f1方法')
    def f2(self):
        print('A的f2方法')

    def __call__(self,*args, **kwargs):
        getattr(self,*args)()

class B(object):
    def __init__(self,class_A):
        self.class_a=class_A()


def waper(name):
    def do(self):
       self.class_a(name)
    return do


for name in A.fields:
    setattr(B,name,waper(name))


obj=B(A)
obj.f1()
obj.f2()

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.增、删、改操作

obj1 = Users(name="张根",age=18,email='13220198866@163.com',extra='sss')
session.add(obj1)

session.add_all([
Users(name="张根1",age=19,email='645172205@qq.com',extra='sss'),
Users(name="张根2",age=20,email='13220198866@139.com',extra='sss')

])

session.query(Users).filter(Users.id==5).delete()

###################修改##########################
session.query(Users).filter(Users.id==4).update({'name':'Martin'})
session.query(Users).filter(Users.id==4).update({Users.name: Users.name + "666"}, synchronize_session=False)#在原来的基础上做字符串
session.query(Users).filter(Users.id==18).update({Users.id: Users.id - 12},synchronize_session="evaluate")#在原来的基础上做数字+,-操作

 

2.单表查询操作

r0=session.query(Users).all()                             #查询user表中全部数据；
r1=session.query(Users).filter(Users.id > 2)              #查询user表中，id>2的记录；
r2=session.query(Users.age).all()                         #查询User表中的 age列； ##[(18,), (19,)]
r3=session.query(Users.age.label('cname')).all()          #使用别名查询

r4=session.query(Users).filter(Users.name=='Martin').all()      #查询姓名==Martin的用户
r5=session.query(Users).filter_by(name='Martin',age=19).all()   #filter_by方式查询
r6=session.query(Users).filter_by(name='Martin',age=19).first() #获取第 单个对象 print(r6.name)
r7=session.query(Users).filter(text("id<:value and name=:name")).params(value=18, name='Martin').order_by(Users.id).all()
                                                                    #查询 id>18 name=Martin的Users 根据 id排序，params支持传参数；
r8 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()#from_statement,申请使用原生sql

#条件查询
ret0 = session.query(Users).filter(Users.id.between(0,7), Users.name == 'Martin').all()
# #between: 查询 user id在0--7之间，用户名为Martin 的数据;
ret1= session.query(Users).filter(Users.id.in_([1,6])).all()
#查询user_id in [1,6] 的数据
ret2 = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
#查询user_id  not in [1,6] 的数据

ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='Martin'))).all()
#1.session.query(Users.id).filter_by(name='Martin') 查询name=Martin'的id
#2.在user表中 按1的结果 查询

#################################逻辑运算#####################################

from sqlalchemy import and_, or_
ret0 = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
ret1 = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
ret2 = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()

###################################字符串符 模糊匹配查询###############################

ret0 = session.query(Users).filter(Users.name.like('M%')).first().name
ret1 = session.query(Users).filter(~Users.name.like('%r%')).first().name
print(ret0,ret1)

########################### 限制（分页）############################
ret = session.query(Users)[0:2]
print(ret)

##########################排序##############################
ret0 = session.query(Users).order_by(Users.id.desc()).all()                   #根据id,由大到小排序(desc).
ret1 = session.query(Users).order_by(Users.id.asc(),Users.age.desc()).all()   #根据id,由小到大排序(asc)，如id相等,由大到小排序(desc).
print([i.id for i in ret0 ]  )
print([i.id for i in ret1 ]  )

################################分组###############################

from sqlalchemy.sql import func

ret0 = session.query(Users).group_by(Users.age).all() #根据name字段进行分组

ret1 = session.query(     #使用name字段进行分组，求每组中 最大id 、最小id 、id 之和
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)) .group_by(Users.name).all()


ret2 = session.query(
    func.max(Users.id),
    func.sum(Users.id),                          #对分组之后的数据进行 having筛选，
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()
'''
having的作用：
例如：查询公司中 部门人数大于3人的部门，先按照部门分组，然后求人数，然后再having 大于3的；

'''

##################################连表查询###############################################

ret = session.query(models.Users).join(models.Hobby,models.Users.id == models.Hobby.id,isouter=True).filter(models.Users.id >1)
#2个没有外键关系的表 做连表查询
print(ret)

ret = session.query(models.Person).join(models.Hobby).all()              #inin_join
print(ret)
ret = session.query(models.Person).join(models.Hobby,isouter=True).all() #left_join 调换位置 更换为 right_join
print(ret)

''''
left join：以左表为准，显示符合搜索条件的记录；

aID　　　　　aNum　　　　　bID　　　　　bName
5　　　　　a20050115　　　　NULL　　　　　NULL


right join：以右表为准，显示符合搜索条件的记录；

aID　　　　　aNum　　　　　bID　　　　　bName
NULL　　　　　NULL　　　　　8　　　　　2006032408


inin_join：并不以谁为基础,它只显示符合条件
aID　　　　　aNum　　　　　bID　　　　　bName


'''

# 组合
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()

 

3.基于relationship的增加、查询操作

relationship可以帮助我们 快速在存在1对多、多对多关系的表之间做反向连表查询和数据添加；

#连表方式1：指定字段获取
persons=session.query(models.Person.name,models.Hobby.caption.label('hobby_caption')).join(models.Hobby)
for row in  persons:
    print(row.name,row.hobby_caption,)

#连表方式2：获取所有字段
persons = session.query(models.Person, models.Hobby).join(models.Hobby)
for row in persons:
    #print(row)=2个对象
    print(row[0].name,row[1].caption)

#连表方式3：relationship 连表查询
persons = session.query(models.Person).all()
for row in persons:
    print(row.name,row.hobby.caption)

'''
 hobby = relationship("Hobby", backref='pers')
 Hobby：正向查询
 backref：反向查询

ps:查询喜欢姑娘的所有人
hobby_obj=session.query(models.Hobby).filter(models.Hobby.id==2).first()
print(hobby_obj.pers)
'''

################################relationship增加######################
#1.relationship正向增加
person_obj=models.Person(name='Tony',hobby=models.Hobby(caption='any'))


#2.relationship 反向增加
hobby_obj=models.Hobby(caption='人妖')
hobby_obj.pers=[
        models.Person(name='李渊'),
        models.Person(name='西门庆'),
    ]
session.add(person_obj,hobby_obj)

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 SqlALchemy import models

engine = create_engine( "mysql+pymysql://webproject:web@192.168.1.18:3306/web?charset=utf8")
Session = sessionmaker(bind=engine)
session = Session()

#############################多对多添加#################################

#添加方式1：同时添加男、女对象，直接添加相亲表；前提是 知道新添加男、女对象的ID；
session.add_all(
    [
    models.Boy(name='张无忌'),
    models.Boy(name='宋青书'),
    models.Girl(name='周芷若'),
    models.Girl(name='赵敏'),
])
session.commit()

s2g =models.G2B(girl_id=1,boy_id=1)
session.add(s2g)
session.commit()

#添加方式2：通过女生对象 添加 相亲记录表
girl_obj = models.Girl(name='灭绝师太')                                     #创建1位女性朋友 灭绝
girl_obj.boys = [models.Boy(name='张三丰'),models.Boy(name='方正大师')]    #然后灭绝和 张三丰、方正大师分别相了1次亲
session.add(girl_obj)
session.commit()

##添加方式3：通过男生对象 添加 相亲记录
boy_obj = session.query(models.Boy).filter(models.Boy.name=='尹志平').first()  #创建1位男性朋友 尹志平
boy_obj.girls = [models.Girl(name='小龙女'),models.Girl(name='黄蓉')]         #然后尹志平 和小龙女、黄蓉分别 相了一次亲
session.add(boy_obj)
session.commit()

##################################多对多查询###################################

#boys = relationship('Boy', secondary='g2b', backref='girls')
#1.基于 relationship 正向查询
mie_jue = session.query(models.Girl).filter(models.Girl.name=='灭绝师太').first()
print( [i.name for i in mie_jue.boys]) #['方正大师', '张三丰']

#2.基于 relationship 反向查询
yi_zhi_ping = session.query(models.Boy).filter(models.Boy.name=='尹志平').first()
print( [i.name for i in yi_zhi_ping.girls]) #['小龙女', '黄蓉']

以下为相亲表表结构：

###################### 相亲表多对多示例 #########################
class G2B(Base):
    __tablename__ = 'g2b'
    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='g2b', backref='girls')


class Boy(Base):
    __tablename__ = 'boy'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), unique=True, nullable=False)

 

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 学生表；

#!/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`
"""


# 原生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组件

FlaskSQLAlchemy是flask和SQLALchemy的管理者，其本质是在flask项目中 通过对文件管理、导入，把Flask和QLAlchemy两个组件无缝连接在一起，

还可以帮助我们实现自动开启、关闭连接、配置提升开发效率；

 

根据一个常见flask项目的目录结构，梳理一下它的运行流程便知；

sansa项目

 

 

程序入口run.py导入sansa包执行__init__.py文件

0.导入sansa包会执行sanas的__init__.py文件导入create_app

1.执行create_app函数

#!/usr/bin/env python
# -*- coding:utf-8 -*-
"""
生成依赖文件：
    pipreqs ./

"""
from sansa import create_app  #0.导入sansa包会执行sanas的__init__.py文件导入create_app

app = create_app()              #1.执行create_app函数

if __name__ == '__main__':
    app.run()

 

执行sansa.__init__.py

0.导入flask_sqlalchemy,注意这里导入的是flask_sqlalchemy不是原始的sqlalchemy

1.读取、注册flask的配置文件

2.通过配置文件，将flask_sqlalchemy注册到app中

3.通过flask蓝图把account.account(路由和视图) 注册到app里(导入视图)

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from flask import Flask
#0、导入flask_sqlalchemy,注意这里导入的是flask_sqlalchemy不是原始的sqlalchemy
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

from .models import *
from .views import account

def create_app():
    app = Flask(__name__)
    #1、读取、注册flask的配置文件
    app.config.from_object('settings.DevelopmentConfig')
    #2、通过配置文件，将flask_sqlalchemy注册到app中
    db.init_app(app)
    #3、通过蓝图把account.account(路由和视图) 注册到app里
    app.register_blueprint(account.account)  #导入视图
    return app

 

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方法

#!/usr/bin/env python
# -*- coding:utf-8 -*      #0.db在执行run.py刚刚启动调用了sansa\__init__.py程序的时候就实例化好了
from . import db         #1.导入sansa.__init__中的实例化完成的db对象
class Users(db.Model):    #2.db在启动的时候 已经封装好了 配置文件、self.Model = self.make_declarative_base(model_class, metadata) 就可以使用了
    """
    用户表
    """
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return '<User %r>' % self.username

 

读取models.py中的映射去执行SQL创建表

0.加载models表映射关系

1.创建app对象

2.使用db对象根据model这种映射关系执行创建表操作

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sansa import create_app
from sansa import db
app = create_app()
from sansa.models import * #0.加载models表映射关系
with app.app_context():     #1.创建app对象
    db.create_all()          #2.使用db对象根据model这种映射关系执行创建表操作

 

通过视图操作表

0.导入db对象，包含了engin和 创建连接；

1.导入models；

2. db.session直接获取连接，开始操作。。。。

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sansa import create_app
from sansa import db
app = create_app()
from sansa.models import * #0.加载models表映射关系
with app.app_context():     #1.创建app对象
    db.create_all()          #2.使用db对象根据model这种映射关系执行创建表操作
    #db.drop_all()           #3.使用db对象 删除表

 

 

五、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]# 

 

 

pymysql

1.批量更新

import pymysql
import datetime
import sys
import os
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
import conf

#MySQL基础配置类
class MySQLHandler(object):
    def __init__(self,timedelta,mysql_conf,):
        self.mysql_conf=mysql_conf
        self.logpath='/data/work_data/logs/{0}.log'.format(__file__.split('/')[-1][:-3])
        self.timedelta=timedelta
    def query(self, sql):
        self.ret = []
        mysql_conn = pymysql.connect(**self.mysql_conf)
        cursor = mysql_conn.cursor()
        cursor.execute(sql)
        columns = [x[0] for x in cursor.description]
        for row in cursor.fetchall():
            d = dict(zip(columns, row))
            self.ret.append(d)
        cursor.execute('commit')
        cursor.close()
        mysql_conn.close()
        return self.ret
    def update(self, sql):
        self.ret = []
        mysql_conn = pymysql.connect(**self.mysql_conf)
        cursor = mysql_conn.cursor()
        cursor.execute(sql)
        cursor.execute('commit')
        cursor.close()
        mysql_conn.close()
        return self.ret
    def update_many(self,sql,args):
        self.ret = []
        mysql_conn = pymysql.connect(**self.mysql_conf)
        cursor = mysql_conn.cursor()
        cursor.executemany(sql,args)
        cursor.execute('commit')
        cursor.close()
        mysql_conn.close()
        return self.ret


    def log(self,record):
        # print(record)
        with open(file=self.logpath,mode='a', encoding='utf-8') as f:
            current_record = record
            f.write(current_record)

 

2.事务

class MultipleManualHandler(base_handler.BaseHandler):
    @base_handler.error_if_not_super
    def post(self,customer_id):
        data = json.loads(self.request.body.decode('utf8'))
        sql = 'select id from customer_product_manual_version where customer_id="%s"' % (customer_id)
        old_db_records = [str(r['id']) for r in  self.query(sql=sql)]
        import pymysql
        from conf import mysql_conf
        mysql_con = pymysql.connect(**mysql_conf)
        cursor=mysql_con.cursor()
        try:#事物操作
            sql = 'delete from customer_product_manual_version where id in(%s)' % (','.join(old_db_records))
            cursor.execute(sql)
            for p,v in data.items():
                value_list=(customer_id,p,cache.version_to_major(v),v,self.user,datetime.datetime.now())
                sql = 'insert into customer_product_manual_version (customer_id,product,major_version,version,last_modify_user_id,last_modify_time) values("%s","%s","%s","%s","%s","%s");'%(value_list)
                cursor.execute(sql)
        except Exception as e:
            print(e)
            mysql_con.rollback()  # 出错事务回滚
        else:
            mysql_con.commit()    #没有出错提交
        finally:
            cursor.close()
            mysql_con.close()
        sql = 'select product,version from customer_product_manual_version where customer_id="%s"' % (customer_id)
        newest_result=self.query(sql=sql)
        response = {d['product']: d['version'] for d in newest_result}
        self.write(self.to_json(response))

 

 

 

 

 

参考：银角大王