SQLAlchemy相关（连接池、在eventlet中使用、backref/back_populates、lazy/eager loading）

连接池

• 在使用create_engine(url)创建引擎时，SQLAlchemy会默认绑定一个容量（pool_size）为5，上溢（max_overflow）为10的连接池（QueuePool对象）

from sqlalchemy import create_engine

engine = create_engine(url)
# 查看连接池状态
print(engine.pool.status())

输出
Pool size: 5 Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0
连接池状态说明

• 可通过在create_engine函数中传入pool_size、max_overflow、pool_recycle、pool_timeout等参数配置连接池

from sqlalchemy import create_engine

engine = create_engine(url,
      pool_size=15,  # 连接池容量
      max_overflow=10,  # 上溢：超过连接池容量外最多创建的连接数，之后就不能创建新的连接了，只能等待。上溢建立的连接用完后会直接被连接池销毁
      pool_timeout=10,  # 放弃从池中获取连接之前等待的秒数（默认值为30秒）， 池中没有可用连接时不立即抛异常，而是阻塞等待指定秒数（此处设为10秒），如果超过指定秒数仍然没有可用连接，才抛出异常
      pool_recycle=3600)  # 多少秒之后对池中的连接进行一次回收重置（默认值为-1，表示不回收重置），这对MySQL是必要的。对于mysql建立的连接，在8小时内都没有访问请求的话，mysql server将主动断开这条连接，后续在该连接上进行的查询操作都将抛出异常，为避免这种情况，需设置pool_recycle小于8小时（例如设为3600秒，即1小时）
print(engine.pool.status())

输出
Pool size: 15 Connections in pool: 0 Current Overflow: -15 Current Checked out connections: 0

• 连接池允许的最大并发连接数为pool_size + max_overflow
• 禁用连接池：在create_engine函数中传入pool_class=NullPool

from sqlalchemy.pool import NullPool

engine = create_engine(url, poolclass=NullPool)

• 使用自定义的创建连接函数：在create_engine函数中传入creator=getconn，其中getconn是一个创建数据库连接的callable对象。例如：使用eventlet的db_pool的创建连接函数
• SQLAlchemy自带的连接池在初始化时是个空池，即不会预先建立任何连接，只有当需要真正访问数据库且池中没有可用连接时才建立一个连接。
  如果因为特殊情况一定要在初始化时预建一定数量的连接：

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

class DBoption(object):
    def __init__(self, url, pool_size):
        self.engine = create_engine(url, pool_size=pool_size)
        self.session_maker = sessionmaker(bind=self.engine)
    
        sl = list()
        for i in range(pool_size):
            session = self.session_maker()
            session.connection()
            sl.append(session)
    
        for session in sl:
            session.close()
    
        print(self.engine.pool.status())

在eventlet中使用SQLAlchemy

• 方法一（openstack中使用的方法）：在创建引擎时指定创建连接函数为eventlet的db_pool的创建连接函数。（Eventlet的db_pool模块仅支持使用MySQLdb和psycopg2作为DBAPI）。

import eventlet.db_pool

getconn= eventlet.db_pool.ConnectionPool(MySQLdb, **url_args).create
engine = create_engine(url, creator=getconn, pool_size=15, max_overflow=10, pool_timeout=10, pool_recycle=3600)

• 方法二：使用scoped_session创建线程安全的session，同时eventlet.monkey patch(thread=True)，从而变为协程安全的session。此方法对DBAPI没有限制。

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
import eventlet

eventlet.monkey_patch(thread=True)
engine = create_engine(url, pool_size=15, max_overflow=10, pool_timeout=10, pool_recycle=3600)
session_factory = scoped_session(sessionmaker(bind=engine))

def query_user():
	session = session_factory()
	ret = session_factory.query(User)
	
	# 注意：由于使用了scoped_session，在退出会话时必须通过session_factory.remove()方法退出，不能直接通过常规的session.close()方法退出
	# remove内部实现的是对当前session先取消注册，再session.close()
	session_factory.remove()

创建表

• 除了SQLite、PostgreSQL以外，其他数据库在创建表时需要给出字段的长度，特别是String字段
• Firebird和Oracle在创建表时需要给出用于生成新的主键值的Sequence，其他数据库将自动忽略给出的Sequence

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Sequence
DBBase = declarative_base()

class User(DBBase):
	__tablename__ = 'users'
	id = Column(Integer, Sequence('user_id_seq'), primary_key=True)

外键（ForeignKey）

• ForeignKey放在一对多关系、多对一关系中的多方类，例如：一个用户有多个地址，则在Address类（多方）中使用ForeignKey

• 多对多关系外键（即在数据库中新建一个表，用于保存两个多方的外键），删除或修改多对多关系中的关联数据时，实际上是修改这张外键表，而不是修改关联表

# 单独建一张表保存BlogPost与Keyword多对多关系外键
post_keywords = Table('post_keywords', DBBase.metadata,
                      Column('post_id', ForeignKey('posts.id'), primary_key=True),
                      Column('keyword_id', ForeignKey('keywords.id'), primary_key=True))


class BlogPost(DBBase):
    __tablename__ = 'posts'
    
    id = Column(Integer, primary_key=True)
    headline = Column(String(255), nullable=False)
    body = Column(Text)
    
    # secondary=post_keywords指定了BlogPost与Keyword多对多关系外键
    keywords = relationship('Keyword',
                            secondary=post_keywords,
                            back_populates='posts')
           
                            
class Keyword(DBBase):
    __tablename__ = 'keywords'

    id = Column(Integer, primary_key=True)
    keyword = Column(String(50), nullable=False, unique=True)
    
    # secondary=post_keywords指定了BlogPost与Keyword多对多关系外键
    posts = relationship('BlogPost',
                         secondary=post_keywords,
                         back_populates='keywords')                        

• 例如：修改id为3的BlogPost.keywords=[key_obj_1, key_obj_2]，实际上是修改post_keywords这张外键表，而不是修改keywords这张关联表
• orm会先做外键表和keyword表的多表查询，查找post_id为3的keywords.id和keywords.keywords（例如：查询结果为keywords.id分别为2、6）
• 然后会判断key_obj_1, key_obj_2这两个的keywords.id是否在上一步的查询结果中
• 如果都在查询结果中，则不对外键表做任何修改，即不发出任何sql语句（例如key_obj_1, key_obj_2这两个的keywords.id为2、6）
• 如果都不在查询结果中，则在外键表中删除keyword_id等于查询结果中的keywords.id，且post_id为3的数据 （例如key_obj_1, key_obj_2这两个的keywords.id为7、8，则在外键表中删除所有keyword_id等于查询结果的keywords.id（2、6），且post_id为3的数据）
• 对于一个key_obj在查询结果中的情况，则在外键表中只删除keyword_id等于查询结果中剩余的keywords.id，且post_id为3的数据（例如key_obj_1, key_obj_2这两个的keywords.id为2、7，则只在外键表中删除6这条查询结果的剩余数据）

关系（backref/back_populates）

• 在relationship中使用参数backref或back_populates的优点是当Address.user变化时无需flush和commit（无需提交到数据库）就能立即看到关联属性User.addresses的变化
• 否则只有Address.user的变化在flush和commit（提交到数据库）后才能看到关联属性User.addresses的变化。
• 这种双向映射的过程在python层完成的，并没有与数据库交互
• 带有参数back_populates的relationship必须在两个关联表中成对出现，即【双表显示声明】
• 而带有参数backref的relationship只需在一个表中指定，将自动在关联表中创建一条对应的relationship，即【单表显示声明，关联表隐式创建】

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import Column, Integer, String, ForeignKey
DBBase = declarative_base()

class User(DBBase):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    nickname = Column(String(50))
    # relationship指定了User.addresses引用Address类的实例（back_populates指定了双向映射的是Address.user属性)
    addresses = relationship("Address", order_by=Address.id, back_populates="user")

class Address(DBBase):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String(50), nullable=False)

    # ForeignKey限制了user_id这个字段的值都来自于users表的id字段（users表的主键）
    # Address表是多方，ForeignKey放在Address表中，即一个User类的实例对应多个Address的实例，从而User.addresses属性是的数据类型是列表
    user_id = Column(Integer, ForeignKey('users.id'))
    
    # relationship指定了Address.user引用User类的实例（back_populates指定了双向映射的是User.addresses属性)
    user = relationship("User", back_populates="addresses")

级联行为（cascade）

1. 设置orm层级的级联行为（对创建表的SQL语句没有任何影响）

addresses = relationship("Address", back_populates="user", cascade="save-update, merge, delete, delete-orphan")

• cascade参数的缺省默认值为save-update, merge
• 可选值delete表示删除一对多关系中一方数据时，同时删除多方关联数据
• 没有设置delete时：在User表中删除id为7的数据时，其关联的所有Address中数据的user_id都会被设为null（注意：SQLAlchemy对Address发出的是Update语句，这些关联数据并没有被删除，仍保留在数据库中，只是外键user_id都被设为null了）
• 设置了delete时：在User表中删除id为7的数据时，其关联的所有Address中数据都被删除（注意：SQLAlchemy对Address发出的是Delete语句）
• 可选值delete-orphan表示修改一对多关系中一方数据的关联属性时，同时删除多方关联数据的旧值
• 没有设置delete-orphan时：修改id为7的User.addresses = [Address(one), Address(two)]时，是将Address中原有的 user_id=7 的旧数据的user_id都设为null（注意：SQLAlchemy对Address发出的是Update语句，没有删除这些旧数据，仍保留在数据库中），然后在Address中新增one、two两条数据（user_id=7）
• 设置了delete-orphan时：则删除 user_id=7 的旧数据（注意：SQLAlchemy对Address发出的是Delete语句）

2. 设置设置数据库层级的级联行为（影响创建表的SQL语句）

user_id = Column(Integer, ForeignKey('users.id', onupdate="CASCADE", ondelete="CASCADE"))

addresses = relationship("Address", back_populates="user", passive_deletes=True，passive_updates=True)

• 需在ForeignKey中传入onupdate = “CASCADE”, ondelete =
  “CASCADE”

• 同时在relationship中传入passive_deletes=True，passive_updates=True

动态关系 （lazy=“dynamic”）

• 将relationship的返回结果设为Query对象，而不是固定的集合（例如：list）
• 适用于关联表的结果集非常大的情况， 例如一个user可能有非常多的blogs，在访问User.blogs这个关系时并不希望将所有blogs都加载到一个list中，而是希望能够对blogs进一步过滤，通过lazy="dynamic"将User.blogs设为一个query对象（而不是list），从而可以在查询User.blogs的SQL语句中设置过滤条件，而不是被迫一次性都提取到内存中在程序中过滤
• 注意：动态关系不能与lazy loading 或 eager loading同时使用，因为动态关系的返回是Query对象，而lazy loading 或 eager loading的返回都是固定的集合（例如：list）

class User(DBBase):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    # 参数lazy="dynamic"将User.blogs这个关系设为动态关系
    posts = relationship("BlogPost", back_populates="author", lazy="dynamic")

user = session.query(User).filter_by(id=3).one_or_none()
# 由于设置了lazy="dynamic"，则user.posts是Query对象，可以对其进行filter、limit、offset等各种过滤，从而只返回符合过滤条件的blog，而不是此user的所有blog
blog = user.posts.filter_by(headline='funny story').limit(2).all()

延迟加载（lazy loading）

【结论】官方推荐保持lazy loading的默认设置，只在场景需要较多关联表数据时单独将Query对象设为eager loading

• 查询一个表时不同时加载其关联表数据，直到访问某个具体对象的关联表属性（某个具体的user的user.addresses属性）时才发出一条额外SQL语句单独获取该具体对象的关联表数据
• 声明relationship的缺省默认加载方式就是lazy loading，即默认设置了参数lazy=‘select’
• 优点：不加载场景中不需要的关联表数据
• 缺点：场景中需要使用关联表数据，为了获取100个个具体对象的关联表数据可能要发出100条额外的SQL语句，效率低
• 一对多关系中使用lazy loading：先发一条SQL语句查询一方数据（例如query(User)有10条结果），为了获取每个user的address，对每个user分别发出一条SQL（即额外发送了10条SQL）
• 多对一关系中使用lazy loading：先发一条SQL语句查询多方数据（例如query(Address)有10条结果），为了获取每个address对应的user，根据多方结果集中的外键有几个不同的值（例如这10条结果中共有3个不同的user_id）就发出几条SQL（3个）来查询一方数据

立即加载（eager loading）

【结论】多方关联数据集合较小时使用joinedload，其他情况能用selectinload就用selectinload，用不了就改用subqueryload

• 查询一个表时同时一次性加载其关联表数据，即查询返回时，关联表属性（所有符合查询条件的user的user.addresses属性）已填充了数据
• 可以通过在声明relationship时设置lazy=‘joined’、‘subquery’、‘selectin’等值将eager loading指定为默认加载方式，官方不推荐这样做
• 官方推荐通过对Query对象设置options来单独将此次查询设置为eager loading

from sqlalchemy.orm import joinedload
# 在查询User的同时加载User.addresses的关联表数据
user= session.query(User).options(joinedload(User.addresses)).all()

一对多关系中希望立即加载多方关联数据

• 多方关联数据集合较小时使用joinedload
• 总共1条SQL语句，默认为LEFT OUTER JOIN，可通过传入innerjoin=True提升性能，但innerjoin可能改变一方查询结果

from sqlalchemy.orm import joinedload

user= session.query(User).options(joinedload(User.addresses)).all()

SELECT 
    users.id AS users_id,
    users.name AS users_name,
    users.fullname AS users_fullname,
    users.nickname AS users_nickname,
    addresses_1.id AS addresses_1_id,
    addresses_1.email_address AS addresses_1_email_address,
    addresses_1.user_id AS addresses_1_user_id
FROM
    users
        LEFT OUTER JOIN
    addresses AS addresses_1 ON users.id = addresses_1.user_id
ORDER BY addresses_1.id;

• 多方关联数据集合很大时使用subqueryload
• 总共2条SQL语句，第1条查询user，第2条填充user.addresses，使用INNER JOIN，不改变一方查询结果，且子查询的字段仅为一方表主键
• 缺点：如果查询中使用了first、limit、offset，则必须同时使用order_by，否则可能产生错误结果

from sqlalchemy.orm import subqueryload

user= session.query(User).options(subqueryload(User.addresses)).all()

SELECT 
    users.id AS users_id,
    users.name AS users_name,
    users.fullname AS users_fullname,
    users.nickname AS users_nickname
FROM
    users;
    
SELECT 
    addresses.id AS addresses_id,
    addresses.email_address AS addresses_email_address,
    addresses.user_id AS addresses_user_id,
    anon_1.users_id AS anon_1_users_id
FROM
    (SELECT 
        users.id AS users_id
    FROM
        users) AS anon_1
        INNER JOIN
    addresses ON anon_1.users_id = addresses.user_id
ORDER BY anon_1.users_id , addresses.id;

• 当要同时加载多个不同的多方关联表数据（及需要join多个表）时使用selectinload
• 总共1+（N / 500）条SQL语句，第1条查询user，第2条（及以后）填充user.addresses，不会产生笛卡尔积问题，不会多次join，无需order_by，性能高
• 缺点：一个SQL语句一次只能获取多方关联数据集合的500条数据，集合数据量超过500时，将每500个发出一个SQL
• 缺点：对于复合主键，selectin加载不是平台无关的，已知支持的DBAPI为PostgreSQL, MySQL, SQLite，对于不支持的DBAPI将抛异常

from sqlalchemy.orm import selectinload

user= session.query(User).options(selectinload(User.addresses)).all()

SELECT 
    users.id AS users_id,
    users.name AS users_name,
    users.fullname AS users_fullname,
    users.nickname AS users_nickname
FROM
    users;

SELECT 
    addresses.user_id AS addresses_user_id,
    addresses.id AS addresses_id,
    addresses.email_address AS addresses_email_address
FROM
    addresses
WHERE
    addresses.user_id IN (第1条SQL语句获得的所有user的主键)
ORDER BY addresses.user_id , addresses.id

多对一关系中希望立即加载一方关联数据

• 由于一方数据是单个的，不是集合，使用上述3中加载方式性能都差不多

完整例子

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session, relationship
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, Table, Text
import database_global_conf
import eventlet

eventlet.monkey_patch(thread=True)


# 单例模式元类
class Singleton(type):
    def __init__(self, *args, **kwargs):
        self.__instance = None
        super(Singleton, self).__init__(*args, **kwargs)

    def __call__(self, *args, **kwargs):
        if self.__instance is None:
            self.__instance = super(Singleton, self).__call__(*args, **kwargs)
        return self.__instance

# sqlalchemy声明表结构的基类
DBBase = declarative_base()


class DBEngines(metaclass=Singleton):
    """
    所属单元: 数据库连接池建立单元
    """

    def __init__(self):
        self.engines = dict()

    def get(self, url):
        """
        获取连接url数据库的引擎（单例的Engine对象）
        """
        if url not in self.engines:
            # 创建一个连接url数据库的引擎，默认带容量为5，上溢为10的连接池（QueuePool对象）
            # 可通过在create_engine函数中传入pool_size、max_overflow、pool_recycle、pool_timeout等参数配置连接池
            # 连接池允许的最大并发连接数为pool_size + max_overflow
            engine = create_engine(url, pool_size=15, max_overflow=10, pool_timeout=10, pool_recycle=3600)
            self.engines[url] = engine

        return self.engines[url]


class DBSessions(metaclass=Singleton):
    """
    所属单元: 数据库访问通道申请单元
    """

    def __init__(self):
        self._sessions = dict()

    def get(self, url):
        """
        获取url数据库对应的工厂类（单例的session_maker对象）
        """
        if url not in self._sessions:
            # 获取连接url数据库的引擎（单例的Engine对象）
            engine = DBEngines().get(url)
            
            # 创建一个绑定到该引擎的工厂类（单例的session_maker对象）
            # 注意：必须使用scoped_session才能保证线程安全
            # 注意：在eventlet中,由于scoped_session使用了threading.local()，因此必须对thread进行monkey_patch才能保证协程安全
            session = scoped_session(sessionmaker(bind=engine))
            self._sessions[url] = session
        return self._sessions[url]


class DBOption(object):
    """
    所属单元: 数据库操作请求响应单元

    数据库操作提取封装单元 在 sqlalchemy Session 中
    用法:
        with DBOption(db_url) as session:
            session.exec(sql)
    """

    def __init__(self, url):
        self._url = url
        self.session = None

    def __enter__(self):
        """
        with语句进入时获取一个会话对象，用于访问数据库
        """
        # 获取url数据库对应的工厂类（单例的session_maker对象）
        self.session_maker = DBSessions().get(self._url)
        # 由工厂类实例化一个会话对象（Session对象）
        self.session = self.session_maker()
        return self.session

    def __exit__(self, exc_type, exc_val, exc_tb):
        """
        with语句退出时如果有异常则回滚，最后无论是否有异常都会将会话对象访问数据库所用的连接还给连接池
        """
        # print(DBEngines().engines[self._url].pool.status())
        if exc_val:
            self.session.rollback()
            print('exec error ', exc_val)
        # 注意：由于使用了scoped_session，在退出会话时必须通过工厂类的remove方法退出（即对当前会话对象先取消注册，再退出），不能直接通过会话对象的close方法退出
        self.session_maker.remove()


class Address(DBBase):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String(50), nullable=False)
    # ForeignKey限制了user_id这个字段的值都来自于users表的id字段（users表的主键）
    # ForeignKey放在Address表中，Address表就是多方，即一个User类的实例对应多个Address的实例，从而User.addresses属性是列表
    user_id = Column(Integer, ForeignKey('users.id'))
    
    # relationship指定了Address.user引用User类的实例（back_populates指定了双向映射的是User.addresses属性)
    user = relationship("User", back_populates="addresses")
    
    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address


class User(DBBase):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    # 除了SQLite、PostgreSQL以外，其他数据库在创建表时都需要给出String字段的长度
    name = Column(String(50))
    fullname = Column(String(50))
    nickname = Column(String(50))
    
    # relationship指定了User.addresses引用Address类的实例（back_populates指定了双向映射的是Address.user属性)
    # cascade指定了对关联表Address的级联行为
    addresses = relationship("Address", order_by=Address.id, back_populates="user", cascade="save-update, merge, delete, delete-orphan")
    # 由于设置了lazy="dynamic"，则user.posts是Query对象，可以对其进行filter、limit、offset等各种过滤，从而只返回符合过滤条件的blog，而不是此user的所有blog
    posts = relationship("BlogPost", back_populates="author", lazy="dynamic")
    
    def __repr__(self):
       return "<User(id='%s', name='%s', fullname='%s', nickname='%s')>" % (self.id,
                            self.name, self.fullname, self.nickname)


# 多对多关系外键
post_keywords = Table('post_keywords', DBBase.metadata,
                      Column('post_id', ForeignKey('posts.id'), primary_key=True),
                      Column('keyword_id', ForeignKey('keywords.id'), primary_key=True))


class BlogPost(DBBase):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    headline = Column(String(255), nullable=False)
    body = Column(Text)

    author = relationship(User, back_populates="posts")

    # 指定BlogPost表与Keyword表多对多关系的外键
    keywords = relationship('Keyword',
                            secondary=post_keywords,
                            back_populates='posts')

    def __init__(self, headline, body, author):
        self.author = author
        self.headline = headline
        self.body = body

    def __repr__(self):
        return "BlogPost(%r, %r, %r)" % (self.headline, self.body, self.author)


class Keyword(DBBase):
    __tablename__ = 'keywords'

    id = Column(Integer, primary_key=True)
    keyword = Column(String(50), nullable=False, unique=True)
    
    # 指定BlogPost表与Keyword表多对多关系的外键
    posts = relationship('BlogPost',
                         secondary=post_keywords,
                         back_populates='keywords')

    def __init__(self, keyword):
        self.keyword = keyword


if __name__ == '__main__':
    mysql_conf = database_global_conf['mysql']
    url = "mysql+pymysql://{user}:{pwd}@{host}:{port}/{db}".format(**mysql_conf)

    # 在数据库中创建所有表
    engine = DBEngines().get(url)
    DBBase.metadata.create_all(engine)

    # 获取一个session
    mysql_ins = DBOption(url)
    with mysql_ins as session:
        # insert一条数据
        ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
        session.add(ed_user)

        # insert多条数据
        session.add_all([
                User(name='wendy', fullname='Wendy Williams', nickname='windy'),
                User(name='mary', fullname='Mary Contrary', nickname='mary'),
                User(name='fred', fullname='Fred Flintstone', nickname='freddy')])

        # 同时insert关联表数据
        jack = User(name='jack', fullname='Jack Bean', nickname='gjffdd')
        jack.addresses = [Address(email_address='jack@google.com'), Address(email_address='j25@yahoo.com')]
        session.add(jack)

        # 向数据库提交上述sql语句
        session.commit()

        # join查询
        ret = session.query(User).join(Address, User.id==Address.user_id).filter(Address.email_address == 'jack@google.com').all()

参考文档

1. Using eventlet with SQLAlchemy
2. python的sqlalchemy数据库连接池原理的说明
3. sqlalchemy官方文档–Pool相关
4. sqlalchemy官方文档–ORM入门教程
5. sqlalchemy官方文档–动态关系
6. sqlalchemy官方文档–关系加载技术
7. sqlalchemy官方文档–ORM级联Cascades
8. sqlalchemy官方文档–定义外键等约束、设置数据库级联
9. sqlalchemy官方文档–基本关系模式（一对多，多对一，多对多）