SQLAlchemy配置连接多个数据库

 1.定义配置项

首先定义两个数据库的配置信息

# PostgreSQL database configuration
DB_USERNAME=postgres
DB_PASSWORD=passord
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=test

# mysql database configuration
DB_USERNAME_MYSQL=root
DB_PASSWORD_MYSQL=password
DB_HOST_MYSQL=127.0.0.1
DB_PORT_MYSQL=3306
DB_DATABASE_MYSQL=test

2.定义Config类

首先读取上面的数据库配置项。

“db_credentials ”和“db_mysql_credentials”是我储存对应数据库配置的自定义字段，随意起名即可。

“self.SQLALCHEMY_DATABASE_URI”字段名代表sqlalchemy的主数据库，这里我使用的是postgres，里面按照文档要求填上对应的请求地址。

“self.SQLALCHEMY_BINDS”可以理解为副数据库，可以把其他数据库都写在这里，其值是dict格式。格式为{"key":"databaseUri"}。每个数据库都必须以唯一的键值区分，键值按自己喜好取就行。mysql1，mysql2，mysql3什么的....

"self.SQLALCHEMY_ENGINE_OPTIONS"代表连接池大小

“self.SQLALCHEMY_ECHO”代表sqlalchemy在每次请求时是否在控制台打印sql语句

注意：

SQLAlchemy采用的是加载对应数据库DBAPI 的方式来连接的，而具体加载哪个DBAPI 是你在数据库URI中声明的，如“mysql+pymysql”代表使用PyMySQL连接mysql，也就是说你需要先安装PyMySQL库。这其实和使用java时用JDBC映射的方式连接mysql很像

pip install PyMySQL

Config代码内容:

class Config:
    """Application configuration class."""

    def __init__(self):
  
        # ------------------------
        # Database Configurations.
        # ------------------------
        db_credentials = {
            key: get_env(key) for key in
            ['DB_USERNAME', 'DB_PASSWORD', 'DB_HOST', 'DB_PORT', 'DB_DATABASE', 'DB_CHARSET']
        }
        self.SQLALCHEMY_DATABASE_URI_SCHEME = get_env('SQLALCHEMY_DATABASE_URI_SCHEME')
        db_extras = f"?client_encoding={db_credentials['DB_CHARSET']}" if db_credentials['DB_CHARSET'] else ""
        self.SQLALCHEMY_DATABASE_URI = f"{self.SQLALCHEMY_DATABASE_URI_SCHEME}://{db_credentials['DB_USERNAME']}:{db_credentials['DB_PASSWORD']}@{db_credentials['DB_HOST']}:{db_credentials['DB_PORT']}/{db_credentials['DB_DATABASE']}{db_extras}"

        db_mysql_credentials = {
            key: get_env(key) for key in
            ['DB_USERNAME_MYSQL', 'DB_PASSWORD_MYSQL', 'DB_HOST_MYSQL', 'DB_PORT_MYSQL', 'DB_DATABASE_MYSQL']
        }

        # password = db_mysql_credentials['DB_PASSWORD_MYSQL']
        # pwd = parse.quote_plus(password)
        self.SQLALCHEMY_BINDS = {
            "mysqlkey": f"mysql+pymysql://{db_mysql_credentials['DB_USERNAME_MYSQL']}:{db_mysql_credentials['DB_PASSWORD_MYSQL']}@{db_mysql_credentials['DB_HOST_MYSQL']}:{db_mysql_credentials['DB_PORT_MYSQL']}/{db_mysql_credentials['DB_DATABASE_MYSQL']}?charset=utf8mb4"
        }
        self.SQLALCHEMY_ENGINE_OPTIONS = {
            'pool_size': int(get_env('SQLALCHEMY_POOL_SIZE')),
            'max_overflow': int(get_env('SQLALCHEMY_MAX_OVERFLOW')),
            'pool_recycle': int(get_env('SQLALCHEMY_POOL_RECYCLE')),
            'pool_pre_ping': get_bool_env('SQLALCHEMY_POOL_PRE_PING'),
            'connect_args': {'options': '-c timezone=UTC'},
        }

        self.SQLALCHEMY_ECHO = get_bool_env('SQLALCHEMY_ECHO')

3.初始化SQLAlchemy

编写好Config类后，就可以初始化sqlalchemy了。

from flask_sqlalchemy import SQLAlchemy
from myconfig import Config
from flask import Flask
import flask_migrate
 
 
app = Flask()
 
app.config.from_object(Config())
 
db = SQLAlchemy()
 
db.init_app(app)
 
flask_migrate.Migrate(app, db)

4.定义不同数据库的表结构

如果表所对应的数据库是通过“self.SQLALCHEMY_DATABASE_URI”字段绑定到SQLAlchemy上的，那么它所对应的类不需要做额外的修改，SQLAlchemy默认使用主数据库。

 

from flask_sqlalchemy import SQLAlchemy
 
db = SQLAlchemy()
 
class Setup(db.Model):
    __tablename__ = 'setups'
    __table_args__ = (
        db.PrimaryKeyConstraint('version', name='setup_pkey'),
    )
 
    version = db.Column(db.String(255), nullable=False)
    setup_at = db.Column(db.DateTime, nullable=False, server_default=db.text('CURRENT_TIMESTAMP(0)'))

如果表所对应的数据库是通过“self.SQLALCHEMY_BINDS”字段的方式绑定到SQLAlchemy上的，那么就需要在类的开头用__bind_key__声明这张表是在哪一个数据库上，值填写你在“self.SQLALCHEMY_BINDS”的dict上对应数据库的key，我的是“mysqlkey”，其他照旧。

from flask_sqlalchemy import SQLAlchemy
 
db = SQLAlchemy()
 
class MysqlTestTable(db.Model):
    __bind_key__ = "mysqlkey"
    __tablename__ = "test_table"
    __table_args__ = (
        db.PrimaryKeyConstraint("id", name="PRIMARY"),
        db.Index("idx_unique", "phoneNo"),
    )
    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
    phoneNo = db.Column(db.BigInteger, nullable=False, comment="")
    name = db.Column(db.String(20), nullable=False, comment="")