Flask框架（三）多个数据库间切换

本文介绍Flask连接多个数据库，不同数据库间切换，下面详细介绍：

一、数据库配置

class Config:
    DEBUG = False
    SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://{0}:{1}@{2}:3306'.format(DB_USER, DB_PWD, DB_ADDR)  # 默认数据库引擎，数据库根目录
    # 配置多个数据库连接
    SQLALCHEMY_BINDS = {
        'client': 'mysql+pymysql://{0}:{1}@{2}:3306/{3}?charset=utf8'.format(DB_USER, DB_PWD, DB_ADDR, DB_NAME)
    }

    # 在app设置里开启自动提交会出现 sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid transaction is rolled back
    SQLALCHEMY_COMMIT_ON_TEARDOWN = False
    SQLALCHEMY_TRACK_MODIFICATIONS = False  # 关闭数据追踪，避免内存资源浪费
}

二、定义数据模型基类

定义数据模型基类BaseModel，数据模型继承这个基类

from flask_sqlalchemy import SQLAlchemy
import logging
import json

# 客户数据库连接，用作数据模型
# 此处db是默认的数据库引擎连接SQLALCHEMY_DATABASE_URI，指向数据库根目录，运行sql语句时要指定具体数据库
db = SQLAlchemy()

class BaseModel(object):
    def update(self):  # 更新记录
        try:
            db.session.commit()  # 事务提交
        except Exception as e:
            db.session.rollback()  # 事务回滚
            logging.error('[BaseModel->update]Failed to run update, {0}'.format(e))
            return False

        return True

    def add_update(self):  # 增加一条记录
        try:
            db.session.add(self)  # 添加数据对象
            db.session.commit()  # 事务提交
        except Exception as e:
            db.session.rollback()  # 事务回滚
            logging.error('[BaseModel->add_update]Failed to run add_update, {0}'.format(e))
            return False

        return True

    def add_all_update(self, datas: list):  # 增加多条记录
        try:
            db.session.add_all(datas)  # 添加数据对象
            db.session.commit()  # 事务提交
        except Exception as e:
            db.session.rollback()  # 事务回滚
            logging.error('[BaseModel->add_all_update]Failed to run add_all_update, {0}'.format(e))
            return False

        return True

    def delete(self):  # 删除一条记录
        try:
            db.session.delete(self)  # 删除数据对象
            db.session.commit()  # 事务提交
        except Exception as e:
            db.session.rollback()  # 事务回滚
            logging.error('[BaseModel->delete]Failed to run delete, {0}'.format(e))
            return False

        return True

    def to_json(self):  # SQLAlchemy转json对象
        fields = {}
        for field in [x for x in self.__dict__ if not x.startswith('_') and x != 'metadata']:
            data = self.__getattribute__(field)
            try:
            	# 非json识别的数据类型在这里转换，这里以DataTime类型转换举例
                if isinstance(data, datetime):
                    data = data.strftime('%Y-%m-%d %H:%M:%S')
                json.dumps(data)  # this will fail on non-encodable values, like other classes
                fields[field] = data
            except TypeError:
                fields[field] = None
                logging.error(traceback.format_exc())
        return fields


# 执行自定义sql语句查询，失败最多重试3次
def mysql_execute(sql: str):
    results = []
    session = db.session()

    n = 0
    while (not results and n < 3):
        n += 1
        try:
            resultproxy = session.execute(sql)
            if resultproxy.keys():
                results = resultproxy.fetchall()
        except Exception as e:
            session.rollback()  # 事务回滚
            logging.error('[mysql_execute]Failed to run mysql_execute, {0}'.format(e))
    session.close()

    return results

创建数据库函数

def create_db(dbname: str):
    if not mysql_execute("show databases like '{0}';".format(dbname)):
        sql = 'CREATE DATABASE IF NOT EXISTS {0} default charset utf8 COLLATE utf8_general_ci;'.format(dbname)
        mysql_execute(sql)

生成数据表函数

def create_db_client(dbname: str):
    try:
        create_db(dbname)  # 创建数据库
        db.create_all()  # 创建客户数据表
    except Exception as e:
        logging.error('[create_db_client]Failed to run create_db_client, {0}'.format(e))

三、切换不同数据库

切换数据库有两种方法：flask数据库配置、sql语句修改
方法一： flask数据库配置，通过app.config配置SQLALCHEMY_DATABASE_URI，根据数据库名称配置成当前数据库

def changeDbEngine(dbname: str):
    from FlaskDemo import app
    app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://{0}:{1}@{2}:3306/{3}?charset=utf8'.\
        format(DB_USER, DB_PWD, DB_ADDR, dbname)
    return app.config['SQLALCHEMY_DATABASE_URI']

方法二：

def changeDbEngine(dbname: str):
    mysql_execute("use {0};".format(dbname))
    return dbname

四、完整教程地址

Flask搭建项目完整教程：Flask+Vue搭建系统