Automap multiple databases with Flask-SQLAlchemy
<p>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: <a href="http://flask-sqlalchemy.pocoo.org/2.1/binds/" rel="nofollow noreferrer">http://flask-sqlalchemy.pocoo.org/2.1/binds/</a>, but it doesn't seem to work with the automap_base. </p>

<p>The only changes I made were creating <code>SQLALCHEMY_BINDS</code> and adding the <code>__bind_key__</code> in <strong>models.py</strong>. The error I get is </p>

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

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

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

<p><strong>config.py</strong></p>

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

<p><strong>__init__.py</strong> </p>

<pre><code>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)
</code></pre>

<p><strong>models.py</strong></p>

<pre><code>class Table1(db.Model):
    __tablename__ = 'table1'

class Table2(db.Model):
    __bind_key__ = 'otherDB'
    __tablename__ = 'table2'
</code></pre>