Automap multiple databases with Flask-SQLAlchemy

Question

I have an app currently working fine Automapping one database, but I need to access another database now as well. I tried to follow the Flask-SQLAlchemy documentation here: http://flask-sqlalchemy.pocoo.org/2.1/binds/, but it doesn't seem to work with the automap_base.

The only changes I made were creating SQLALCHEMY_BINDS and adding the __bind_key__ in models.py. The error I get is

sqlalchemy.exc.ArgumentError: Mapper Mapper|Table2|table2 could not assemble any primary key columns for mapped table 'table2'

However, both tables have a primary key column, and if I get rid of SQLALCHEMY_BINDS, set the URI to that of db2, and only have table2 in models.py, everything works fine.

I'm clearly doing something wrong, but I have no idea what it is. It looks like Flask is still looking for table2 in db1. I think my problem is that some change needs to be made to __init__.py as well, but I don't know what that would be.

config.py

SQLALCHEMY_DATABASE_URI = 'mysql://user@host/db1'
SQLALCHEMY_BINDS = {
    'otherDB':        'mysql://user@host/db2',
}

__init__.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.ext.automap import automap_base

app = Flask(__name__)
app.config.from_object('config')

db = SQLAlchemy(app)
db.Model = automap_base(db.Model)
from app import models
db.Model.prepare(db.engine, reflect=True)

models.py

class Table1(db.Model):
    __tablename__ = 'table1'

class Table2(db.Model):
    __bind_key__ = 'otherDB'
    __tablename__ = 'table2'

ichbinblau ichbinblau · Accepted Answer · 2016-12-18T15:10:36

Automap is a extension of sqlalchemy to reflect an existing database into a new model. It has not been baked into flask-sqlalchemy. Plz see the issue here. You can connect to multiple databases with Flask-SQLAlchemy like this:

__init__.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config.from_object('config')

db = SQLAlchemy(app)

from app import models  

if __name__ == "__main__":
    # show the table schema
    print m3.Table1.__table__.c
    print m3.Table2.__table__.c

models.py

db.reflect() # reflection to get table meta


class Table1(db.Model):
    __tablename__ = 'table1'


class Table2(db.Model):
    __tablename__ = 'table2'
    __bind_key__ = 'otherDB'

Automap multiple databases with Flask-SQLAlchemy

1 Answers