flask-sqlalchemy

flask-sqlalchemy copied to clipboard

When using reflection to populate model from existing database, the bind is not populated correctly. This results in unusable model created when using non-default bind.

Using default connection, and another bind for a second database schema. However the returned Table instances are incorrectly set with bind to the default schema, i.e., None. Querying such table then apparently fails.

See minimal example below, which is based on the quick start from documentation:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config[
    "SQLALCHEMY_DATABASE_URI"
] = "postgresql://postgres:passwd@localhost:5432/ex1"
app.config["SQLALCHEMY_BINDS"] = {
    "ex2": "postgresql://postgres:passwd@localhost:5432/ex2"
}
app.config["SQLALCHEMY_POOL_RECYCLE"] = 30
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["SQLALCHEMY_ECHO"] = False

db = SQLAlchemy(app)
db.reflect(app=app)  # reflecting tables from database


for name, table in db.metadata.tables.items():
    print("TABLE", name, "AS", repr(table))

# prints the following:
# TABLE a1 AS Table('a1', MetaData(bind=None), Column('id', INTEGER(), table=<a1>, primary_key=True, nullable=False), Column('name', VARCHAR(length=255), table=<a1>, nullable=False), schema=None)
# TABLE a2 AS Table('a2', MetaData(bind=None), Column('id', INTEGER(), table=<a2>, primary_key=True, nullable=False), Column('name', VARCHAR(length=255), table=<a2>, nullable=False), schema=None)

# querying table from default schema first
a1 = db.metadata.tables["a1"]
records_a1 = db.session.query(a1).limit(5).all()
print("IN DATABASE ex1 IN TABLE a1", len(records_a1))

# querying table from the second schema fails!
a2 = db.metadata.tables["a2"]
records_a2 = db.session.query(a2).limit(5).all() 
print("IN DATABASE ex2 IN TABLE a2", len(records_a2))

Complete traceback:

Traceback (most recent call last):
  File "/home/.../venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/.../venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
psycopg2.ProgrammingError: relation "a2" does not exist
LINE 2: FROM a2 
             ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/.../test_flask_sqlalchemy_bind.py", line 32, in <module>
    records_a2 = db.session.query(a2).limit(5).all()
  File "/home/.../venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2843, in all
    return list(self)
  File "/home/.../venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2995, in __iter__
    return self._execute_and_instances(context)
  File "/home/.../venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3018, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/.../venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/home/.../venv/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/.../venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/.../venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/home/.../venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/home/.../venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/.../venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/home/.../venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/.../venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "a2" does not exist
LINE 2: FROM a2 
             ^
 [SQL: 'SELECT a2.id AS a2_id, a2.name AS a2_name \nFROM a2 \n LIMIT %(param_1)s'] [parameters: {'param_1': 5}] (Background on this error at: http://sqlalche.me/e/f405)