Cross-database join in Flask-SQLAlchemy

Question

I'm trying to do a cross-database join in Flask-SQLAlchemy:

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = '...Master...'
app.config['SQLALCHEMY_BINDS'] = { 'Billing': '...Billing...' }
db = SQLAlchemy(app)

class Account(db.Model):
    __tablename__ = 'Accounts'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(255))

class Setting(db.Model):
    __tablename__ = 'Settings'
    AccountId = db.Column(db.Integer, db.ForeignKey(Account.id), primary_key=True)
    Enabled = db.Column(db.Boolean)

class BillingAccount(db.Model):
    __tablename__ = 'Account'
    __bind_key__ = 'Billing'
    id = db.Column(db.Integer, primary_key=True)
    AccountId = db.Column(db.Integer, db.ForeignKey(Account.id))
    currency = db.Column(db.Integer)

class AccountSetting(db.Model):
    __table__ = db.join(Account, AccountSetting)
    id = db.column_property(Account.id, AccountSetting.AccountId)
    username = Account.username
    enabled = Setting.Enabled

class AccountSettingBilling(db.Model):
    __table__ = db.join(Account, AccountSetting).join(BillingAccount)

    id = db.column_property(Account.id, AccountSetting.AccountId, BillingAccount.AccountId)
    username = Account.username
    enabled = Setting.Enabled
    currency = BillingAccount.currency

With this I can successfully query AccountSetting.query.all() but not AccountSettingBilling.query.all(), which fails with error 208 (MSSQL for 'object does not exist').

If I examine the generated SQL I can clearly see that it is doing a JOIN on Account.AccountId=Accounts.id when I'd expect to see some reference to Billing, e.g. Billing.Account.AccountId=Accounts.id.

Having followed Cross database join in sqlalchemy and http://pythonhosted.org/Flask-SQLAlchemy/binds.html it looks to me as though I've done things correctly. What gives?

score 1 · Answer 1 · answered May 28 '13 at 20:47

You define an object db = SQLAlchemy(app) - it is Database1. You refer to it everywhere, but there is no reference to Database2. Also note code refers to columns for the join using 2 part identifiers:

Account . AccountId and Accounts . id

whereas you wish to have 3 part identifiers:

Billing . Account . AccountId and [Accounts] . Accounts . id

You are missing this property for db name from definition of each Class:

__table_args__ = {'schema': 'Accounts'}
__table_args__ = {'schema': 'Billing'}

Cross-database join in Flask-SQLAlchemy

1 Answers1