2

I'm building a Flask-Restful API using python and sqlalchemy, and I'm trying to join two tables from different databases. It appears that I'm only able to search for tables in one database at a time. Am I missing something? 

from flask_sqlalchemy import SQLAlchemy
from flask import Flask, jsonify, request 

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:password@host:8000/database1'
app.config['SQLALCHEMY_BINDS'] = {
    'database2': 'mysql://username:password@host:8000/database2'
}


db = SQLAlchemy(app)
db.create_all(bind='database2')

class Table1(db.Model):
    __tablename__ = "table1"
    __table_args__ = {'schema':'database1'}
    location_id = db.Column(db.Integer, primary_key=True)

    def __init__(self, location_id):
        self.location_id = location_id
    def __repr__(self):
        return '{}'.format(self.location_id)

class Table2(db.Model):
    __bind_key__ = "database2"
    __tablename__ = "table2"
    __table_args__ = {'schema':'database2'}
    other_id = db.Column(db.Integer, primary_key=True)
    location_id = db.Column(db.Integer, db.ForeignKey('database1.table1.location_id'))

    def __init__(self, other_id, location_id):
        self.other_id = other_id
        self.location_id = location_id

    def __repr__(self):
        return '{}'.format(self.other_id)


@app.route('/', methods=['GET'])
def returnRes():
    session = db.session
    q = session.query(table1).join(table2, table1.location_id==table2.location_id).all()
return str(q)

In my browser, I am getting the error: 'sqlalchemy.exc.ProgrammingError: (_mysql_exceptions.ProgrammingError) (1146, "Table 'database1.table2' doesn't exist").

Both tables indeed exist, as when I change my query to q = session.query(table2).join(table1, table2.location_id==table1.location_id).all() I get an error that database2.table1 doesn't exist.

I'm using python==3.6.1, Flask==0.11.1 and Flask-SQLAlchemy==2.1

Kairsten
  • 91
  • 7
  • Can you confirm that both tables actually exist? – ACV Oct 09 '17 at 15:50
  • Yes, they both exist. Please see my edit above for clarification. – Kairsten Oct 09 '17 at 18:28
  • These seem to be relevant answers: https://stackoverflow.com/questions/44564369/join-tables-in-two-databases-using-sqlalchemy; https://stackoverflow.com/questions/9416871/qualifying-table-names-with-database-names-in-sqlalchemy – ACV Oct 09 '17 at 20:23

2 Answers2

4

Adding a data base schema argument to my table classes and adding a foreign key fixed this problem. I found the answer at this link: https://github.com/mitsuhiko/flask-sqlalchemy/issues/172

I've updated the question to reflect the answer in case it helps anyone else.

I'm not sure if the binds are redundant, but I've left them in because they don't seem to interfere with anything.

Kairsten
  • 91
  • 7
  • 1
    Thank you! The flask-sqlalchemy doc doesn't cover this nor reflecting tables. You made my day! :-) – cowgill Mar 26 '19 at 15:21
0

You need to add Schema declarations. It is strange that Flask-SQLAlchemy doesn't mention this at all in their documentation.

The Foreign Keys are unnecessary for this to work.

class Table1(db.Model):
    __tablename__ = "table1"
    __table_args__ = {'schema':'database1'}
    #...

    def __init__(self, location_id):
        self.location_id = location_id
    def __repr__(self):
        return '{}'.format(self.location_id)

class Table2(db.Model):
    __bind_key__ = "database2"
    __tablename__ = "table2"
    __table_args__ = {'schema':'database2'}
    #...

so long as your config file looks like this

SQLALCHEMY_DATABASE_URI = 'postgres:////path/to/database1'
SQLALCHEMY_BINDS = {
    'database2':        'mysqldb:////path/to/database2'
}
dabblerabble
  • 11
  • 2