SQLAlchemy gotchas (and headaches) I've dealt with

sqla_cheatsheet.md

Define local model class from remote table

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/database1.db'
app.config['SQLALCHEMY_BINDS'] = {'remote': 'sqlite:////tmp/database1.db'}


db = SQLAlchemy(app)

class MyRemoteTable(db.Model):
    __table__ = Table('sles', db.Model.metadata, autoload=True)

Use two databases with Flask-SQLAlchemy, when there is a name collision between tables

class MyTable(db.Model):
    __tablename__ = 'mytable'

class MyRemoteTable(db.Model):
    __bind_key__ = 'remote'
    __tablename__ = 'mytable' # produces a name collision

Solution

Use two different declarative_base instances

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/database1.db'
app.config['SQLALCHEMY_BINDS'] = {'remote': 'sqlite:////tmp/database1.db'}

db1 = SQLAlchemy(app)

class MyTable(db1.Model):
    __tablename__ = 'mytable'

db2 = SQLAlchemy(app)

class MyRemoteTable(db2.Model):
    __bind_key__ = 'remote'
    __tablename__ = 'mytable'

Alternative solution

Instantiate the new declarative_base from the current Base. I have not tried this one

app = Flask(__name__)
db = SQLAlchemy(app)
db.NewModel = db.make_declarative_base()

class MyTable(db.Model):
    __tablename__ = 'mytable'

class MyRemoteTable(db.NewModel):
    __tablename__ = 'mytable'

About

• https://stackoverflow.com/questions/15336778/using-same-name-of-tables-with-different-binds-in-flask?noredirect=1&lq=1
• https://stackoverflow.com/questions/8264686/sqlalchemy-multiple-databases-with-same-table-names-not-working