Python SQL数据持久层框架之SQLAlchemy

Python SQL 工具包和对象关系映射器

SQLAlchemy 是 Python SQL 工具包和对象关系映射器，为应用程序开发人员提供 SQL 的全部功能和灵活性。

它提供了一整套众所周知的企业级持久化模式，专为高效、高性能的数据库访问而设计，并适应于简单且Pythonic的领域语言。

SQLAlchemy官方文档

Engine，框架的引擎

Connection Pooling ，数据库连接池

Dialect，选择连接数据库的DB API种类（MySQL, oracle...）

Schema/Types，架构和类型

SQL Exprression Language，SQL表达式语言

一、安装

pip install SQLAlchemy

二、Dialects

Dialects是 SQLAlchemy 用于与各种类型的DBAPI实现和数据库进行通信的系统。接下来的部分包含特定于每个后端的使用的参考文档和注释，以及各种 DBAPI 的注释。
组成部分：

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...]

psycopg2  
    postgresql+psycopg2://user:password@host:port/dbname[?key=value&key=value...]

更多：http://docs.sqlalchemy.org/en/latest/dialects/index.html

三、简单使用

SQLAlchemy能创建表、删除表，不能修改表(不支持修改字段)。django ORM直接在model中修改/添加字段，执行数据库迁移命令。

SQLAlchemy如何修改表？先在数据库添加字段(不推荐这样操作)，再去model中对应的修改/添加字段。

3.1、执行原生SQL语句

跟pymysql使用基本一致，但SQLAlchemy自动维护了数据库连接池，不需要利用DButils模块自己维护数据库连接池。

import time
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.engine.base import Engine

# 第一步 生成一个engine对象
engine = create_engine(
    "mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8",  #连接的库是t1，这里的utf8是MySQL自己设计的编码方式，跟字符编码utf-8不是一个东西
    max_overflow=0,   # 超过连接池大小外最多创建的连接
    pool_size=5,      # 连接池大小
    pool_timeout=30,  # 池中没有线程，最多等待的时间，否则报错
    pool_recycle=-1   # 多久之后对线程池中的线程进行一次连接的回收，-1表示不回收（重置），为了防止内存溢出，可以设置回收
)
 
 
def task(arg):
    # 创建连接(raw_connection原生连接，表示执行原生sql)
    conn = engine.raw_connection()
    # 拿到游标对象
    cursor = conn.cursor()
    # 具体操作
    cursor.execute(
        "select * from users"
    )
    result = cursor.fetchall()
    cursor.close()
    conn.close()

3.2、ORM使用

单表的创建与删除

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  #字段和字段属性，字段统一用Column类，在Column类中再指定字段属性(类型)

Base = declarative_base()    #造了一个类，用这个类作为所有模型类的基类，相当于django ORM的models.Model类

class Users(Base):
    __tablename__ = 'users'  #数据库表名，如果不写，默认以类名小写作为表名，django ORM是在Meta中给表取名
                
    id = Column(Integer, primary_key=True)                    #id 主键, mysql中主键自动建索引(聚簇索引)，其他键建的索引叫辅助索引
    name = Column(String(32), index=True, nullable=False)     #name   字符类型maxlenth=32, 索引，不能为空
    email = Column(String(32), unique=True)                   #email  唯一则不能为空
    ctime = Column(DateTime, default=datetime.datetime.now)   #ctime  default默认值
    extra = Column(Text, nullable=True)
    
    #类似于django ORM的class Meta
    __table_args__ = (
      UniqueConstraint('id', 'name', name='uix_id_name'),  #id和name字段，联合唯一
      Index('uix_id_name', 'name', 'email'),               #再给id和name字段建索引
    )

    
def init_db():
    """
    根据类创建数据库表
    :return: 
    """
    # 创建engine对象
    engine = create_engine(
        "mysql+pymysql://root:123@127.0.0.1:3306/test?charset=utf8",
        max_overflow=0,   # 超过连接池大小外最多创建的连接
        pool_size=5,      # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1   # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )
    # 通过engine对象创建表，创建出被Base管理的所有表
    Base.metadata.create_all(engine)

    
def drop_db():
    """
    根据类删除数据库表
    :return: 
    """
    # 创建engine对象
    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   # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )
    # 通过engine对象删除表，删除被Base管理的所有表
    Base.metadata.drop_all(engine)

if __name__ == '__main__':
    drop_db()
    init_db()