Run SQL in several different databases with Flask-SQLAlchemy

Question

I'm coding a project with Flask and Flask-SQLAlchemy, and in that project, I need to access unknown databases, and in those databases, execute queries with db.engine.execute(<SQL>).

But how can I specify which database will receive the query?

Example:

app = Flask(__name__, static_url_path="")
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:******@127.0.0.1:3306/api'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

app.config['SQLALCHEMY_BINDS'] = {
    'db1':        'mysqldb://localhost/users',
    'db2':      'sqlite:////path/to/appmeta.db'
}
api = Api(app)
auth = HTTPBasicAuth()
db = SQLAlchemy()
db.init_app(app)
@app.before_first_request
def create_database():
    db.drop_all()
    db.create_all()


 @app.route('/test')
 def test():
    result = db.engine.execute("show tables;")
    names = []
    for row in result:
        names.append(row[0])
    return str(names)

How can I run show tables; In db1? And in db2?

abigperson · Accepted Answer · 2016-12-22 21:17:18Z

3

You can use the get_engine() method in Flask-SQLAlchemy:

engine_db1 = db.get_engine(app, 'db1')
engine_db2 = db.get_engine(app, 'db2')

sql = text("SHOW TABLES")
results_db1 = engine_db1.execute(sql)
results_db2 = engine_db2.execute(sql)

answered Dec 22, 2016 at 21:17

abigperson

5,3823 gold badges25 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Run SQL in several different databases with Flask-SQLAlchemy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related