sqlalchemy封装自用

前言

• 网上的文章抄来抄去，本文也是😅
• 之前用pymysql很爽，因为简单
• 用cx_Oracle操作数据库，bug蛮多，尤其在公司Oracle命名不规范的情况下
• 只好改用sqlalchemy，bug少了，但是代码丑了不少，找接口的过程也是呕心沥血
• 下面是sqlalchemy的一些个人总结，包含一些藏得比较深的接口
  （alchemy 🔉 /ˈælkəmi/ 📖 n. 点金术；魔力）

ORM

• 对象关系映射（Object Relational Mapping）：
  对象，是指 面向对象编程语言 中的对象
  关系，是指 关系数据库模型
  映射，是对 面向对象编程语言中的对象 和 关系数据库模型中的数据 建立关系

• 例如：
  用一个Python的Student类，去对应数据库中的一张student表
  类中的属性 对应 表中的列
  一个Student对象 对应 student表中的一行数据
  Student对象的add()方法 对应 数据库的insert语句

• 优点：
  ORM框架能自动生成SQL语句，提高了开发效率，让程序员可以专注于业务代码上
  提高代码移植性

• 缺点
  执行效率低于直接写SQL
  不适用于复杂的查询

sqlalchemy

SQL Alchemy是Python的一款ORM框架，可将 对象操作 转换成 SQL

# 创建连接引擎，这个engine是lazy模式，直到第一次被使用才真实创建
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/z?charset=utf8')

# 设计 表的基类
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer
Base = declarative_base()
class Student(Base):
    __tablename__ = 'student'  # 表名
    sid = Column(String(20), primary_key=True, comment='学号')  # 表字段
    age = Column(Integer, comment='年龄')  # 表字段
    __table_args__ = {'comment': '学生信息表'}  # 表注释
# 执行建表操作（继承Base的所有表）
Base.metadata.create_all(bind=engine)

# 创建ORM
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)  # 创建ORM基类
session = Session()  # 创建ORM对象
# 添加数据
tb_obj = Student(sid='a6', age=1)  # 创建表对象
session.add(tb_obj)  # 添加到ORM对象
session.commit()  # 提交
# 查询、修改
result = session.query(Student).filter_by(sid='a6').first()  # 查询
print(result)
result.age = 2  # 修改
session.commit()  # 提交
# 关闭ORM对象
session.close()

封装思想（3 parts）

• 1️⃣ 创建连接引擎，这个engine是lazy模式，直到第一次被使用才真实创建
• 这个engine可直接执行sql语句，其execute还包含commit操作，返回对象具有fetchall、fetchone等方法

from sqlalchemy import create_engine
# 创建引擎
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/z?charset=utf8')
# 执行sql
engine.execute('create table if not EXISTS t1(sid int PRIMARY KEY auto_increment,name char(32));')  # 建表
engine.execute('insert into t1 values(%(sid)s,%(name)s);', name='egon2', sid=2)  # 插入
engine.execute('insert into t1 values(%(sid)s,%(name)s);', name='egon7', sid=7)  # 插入
cur = engine.execute('select * from t1')  # 查询
print(cur.fetchone(), cur.fetchall())
print(type(cur), dir(cur))
engine.execute('drop table t1;')  # 删表

• 2️⃣ 类 -> 表

from sqlalchemy import Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()  # 表的基类

class Student(Base):
    __tablename__ = 'student'
    sid = Column(String(20), primary_key=True)
    age = Column(Integer)

def create_tables(bind=engine):
    Base.metadata.create_all(bind=bind)  # 执行建表操作（继承Base的所有表）

def drop_tables(bind=engine):
    Base.metadata.drop_all(bind=bind)  # 执行删表操作（继承Base的所有表）

• 3️⃣ 对象 -> 表中的记录
• 4️⃣ sqlalchemy.orm.sessionmaker进行增删查改操作

class ORM:
    def __init__(self):
        from sqlalchemy.orm import sessionmaker
        Session = sessionmaker(bind=engine)  # 创建ORM基类
        self.session = Session()  # 创建ORM对象

    def __del__(self):
        self.session.close()  # 关闭ORM对象

    def add(self, tb_obj):
        self.session.add(tb_obj)  # 添加到ORM对象
        self.session.commit()  # 提交

    def yield_per(self, tb_class, n=1000, **kwargs):
        """查询返回生成器"""
        return self.session.query(tb_class).filter_by(**kwargs).yield_per(n)

查询返回生成器

session.query().filter().yield_per(1000)

获取各表字段名

from sqlalchemy import Column, VARCHAR, __version__
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Human:
    id_no = Column(VARCHAR(20), primary_key=True, comment='号码')

class Student(Base, Human):
    __tablename__ = 'student'
    height = Column(VARCHAR(20), comment='身高')

class Teacher(Base, Human):
    __tablename__ = 'teacher'
    weight = Column(VARCHAR(20), comment='体重')

# 所有表名和字段名（immutable：不可变的）
tables = Base.metadata.tables  # <class 'sqlalchemy.util._collections.immutabledict'>
tb_names = list(tables.keys())  # 按序表名
tb2col = {k: [i.name for i in v.columns] for k, v in tables.items()}
tb2Tb = {i: eval(i.title()) for i in tb_names}
print(__version__, tb_names, tb2col, tb2Tb, sep='\n')

打印结果

1.3.9
[‘student’, ‘teacher’]
{‘student’: [‘id_no’, ‘height’], ‘teacher’: [‘id_no’, ‘weight’]}
{'student': <class '__main__.Student'>, 'teacher': <class '__main__.Teacher'>}

from sqlalchemy import Column, VARCHAR, __version__
from sqlalchemy.ext.declarative import declarative_base

prefix = 'a'
names = ('student', 'teacher')
tb_names = [prefix+'_'+i for i in names]
Base = declarative_base()

class A0(Base):
    __tablename__ = tb_names[0]
    id_no = Column(VARCHAR(20), primary_key=True, comment='号码')
    height = Column(VARCHAR(20), comment='身高')

class A1(Base):
    __tablename__ = tb_names[1]
    id_no = Column(VARCHAR(20), primary_key=True, comment='号码')
    weight = Column(VARCHAR(20), comment='体重')

# 所有表名和字段名（immutable：不可变的）
tb2col = {k: [i.name for i in v.columns] for k, v in Base.metadata.tables.items()}
name2Tb = {names[i]: eval('%s%d' % (prefix.title(), i)) for i in range(len(names))}
print(__version__, tb2col, name2Tb, sep='\n')

打印结果

1.3.9
{‘a_student’: [‘id_no’, ‘height’], ‘a_teacher’: [‘id_no’, ‘weight’]}
{'student': <class '__main__.A0'>, 'teacher': <class '__main__.A1'>}

封装成类

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


class Engine:
    def __init__(self, conn):
        self.engine = create_engine(conn, encoding='utf8')  # 这engine直到第一次被使用才真实创建

    def execute(self, sql, **kwargs):
        """含commit操作，返回<class 'sqlalchemy.engine.result.ResultProxy'>"""
        return self.engine.execute(sql, **kwargs)

    def fetchall(self, sql):
        return self.execute(sql).fetchall()  # 能解释CLOB

    def fetchone(self, sql, n=999999):
        self.yellow(sql)
        result = self.execute(sql)
        for _ in range(n):
            one = result.fetchone()
            if one:
                yield one  # <class 'sqlalchemy.engine.result.RowProxy'>

    def fetchone_dt(self, sql, n=999999):
        self.yellow(sql)
        result = self.execute(sql)
        columns = result.keys()
        length = len(columns)
        for _ in range(n):
            one = result.fetchone()
            if one:
                yield {columns[i]: one[i] for i in range(length)}

    @staticmethod
    def yellow(x):
        print('\033[033m{}\033[0m'.format(x))

    @staticmethod
    def red(x):
        print('\033[031m{}\033[0m'.format(x))


class ORM(Engine):
    """对象关系映射（Object Relational Mapping）"""
    def __init__(self, conn, Base):
        super().__init__(conn)
        _Session = sessionmaker(bind=self.engine)  # 创建ORM基类
        self.session = _Session()  # 创建ORM对象
        self.Base = Base
        self.create_all()

    def __del__(self):
        self.session.close()

    def create_all(self):
        """创建所有表"""
        self.Base.metadata.create_all(bind=self.engine)

    def add(self, Table, dt):
        """插入"""
        self.session.add(Table(**dt))  # 添加到ORM对象
        self.session.commit()  # 提交

    def update(self, Table, condition, dt):
        """有则更新，没则插入"""
        q = self.session.query(Table).filter_by(**condition)
        if q.all():
            q.update(dt)  # 更新
            self.session.commit()  # 提交
        else:
            self.add(Table, dt)


orm = ORM()

if __name__ == '__main__':
    orm.execute('create table if not EXISTS t1(sid int PRIMARY KEY auto_increment,name char(32));')  # 建表
    orm.execute('insert into t1 values(%(sid)s,%(name)s);', name='egon2', sid=1)  # 插入
    orm.execute('insert into t1 values(%(sid)s,%(name)s);', name='egon7', sid=2)  # 插入
    for dt in orm.fetchone_dt('select * from t1;'):
        print(dt)
    orm.execute('drop table t1;')  # 删表

继承上面ORM类的【Oracle版】

class Oracle(ORM):
    def show_tables(self):
        for i in self.fetchone('SELECT t.table_name,t.num_rows FROM user_tables t'):
            print(*i)

    def show_create_table(self, tb=''):
        """查看表注释"""
        if tb:
            tb = tb.upper()
            self.yellow('表注释')
            sql = "SELECT comments FROM user_tab_comments WHERE table_name='%s'" % tb
            print(self.fetchall(sql)[0][0])
            self.yellow('字段注释')
            sql = "SELECT column_name,comments FROM user_col_comments WHERE table_name='%s'" % tb
            for i in self.fetchall(sql):print(i)
            self.yellow('建表语句')
            sql = "SELECT dbms_metadata.get_ddl('TABLE','%s')FROM dual" % tb
            print(self.fetchall(sql)[0][0].strip())
        else:
            sql = 'SELECT table_name,column_name,comments FROM user_col_comments ORDER BY table_name'
            for i in self.fetchall(sql):print(i)

    def drop(self, tb):
        sql = 'DROP TABLE %s PURGE' % tb
        self.yellow(sql)
        self.execute(sql)  # 彻底删表

    def truncate(self, tb):
        sql = 'TRUNCATE TABLE %s DROP STORAGE' % tb
        self.yellow(sql)
        self.execute(sql)  # 彻底清空表

    def recreate(self, tb='all'):
        """慎用！重建数据仓库"""
        tables = self.Base.metadata.tables.keys() if tb == 'all' else [tb]
        for table in tables:
            self.drop(table)
        self.create_all()

    def count(self, tb=None):
        if tb:
            return self.fetchall('SELECT COUNT(*) FROM ' + tb)[0][0]
        for tb in self.Base.metadata.tables.keys():
            print(tb, self.count(tb))

    def proportion_not_null(self, table, field=None):
        """非空占比"""
        if field:
            # oracle表和字段是有大小写的区别；若用双引号括起，就区分大小写；若冇，系统会默认转大写
            sql = '''
            SELECT
                t1.f1 amount,
                t1.f1/t2.f1 proportion
            FROM
                (SELECT COUNT(*)f1 FROM {table} WHERE "{field}" IS NOT NULL)t1,
                (SELECT COUNT(*)f1 FROM {table})t2
            '''.format(table=table, field=field.upper())  # 将双引号里面的字段名转成大写
            result = self.fetchall(sql)[0]
            print(field, result[0], '%.2f%%' % (result[1] * 100), sep=' '*6)
        else:
            sql = "SELECT column_name FROM user_tab_columns WHERE table_name='%s'" % table.upper()
            for i, in self.fetchall(sql):
                self.proportion_not_null(table, i)

    def proportion_group_by(self, table, field, prints=''):
        """单字段各项占比"""
        sql = '''
        SELECT
            t1.f2 fullname,
            t1.f1 amount,
            t1.f1/t2.f1 proportion
        FROM
            (SELECT COUNT(*)f1,"{field}"f2 FROM {table} GROUP BY "{field}")t1,
            (SELECT COUNT(*)f1 FROM {table})t2
        ORDER BY amount DESC
        '''.format(table=table, field=field.upper())  # 将双引号里面的字段名转成大写
        result = self.fetchall(sql)
        for i, j, k in prints and result:
            print(i, j, '%.2f%%' % (k*100), sep=' '*6)
        return result

    def discretize(self, table, field, n=10, start=0.0, end=1.0):
        """单字段离散化"""
        scope = (end - start) / n
        when = '\n'.join(
            "\t\t\tWHEN {field}>=%.2f AND {field}<%.2f THEN '[%.2f,%.2f)'".format(field=field) % (
                start+i*scope, start+(i+1)*scope, start+i*scope, start+(i+1)*scope)
            for i in range(n)
        )
        sql = '''
        SELECT {field},COUNT(1) FROM
        (
        SELECT CASE\n{when}
        END {field}
        FROM {table}
        )
        GROUP BY {field}
        ORDER BY {field}
        '''.format(table=table, field=field, when=when)
        for i in self.fetchone(sql):
            print(*i)

Oracle专用字段和环境

from sqlalchemy.dialects.oracle import VARCHAR2, NUMBER
from os import environ
environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'