`Query.count()` woes with Flask-SQLAlchemy and multiple binds

Question

Abstract

I am having trouble using multiple binds with Flask-SQLAlchemy. In particular, the count() method on query objects doesn’t seem to work.

Particulars

My Flask application works on a PostgreSQL database. Additionally, it also retrieves data from a legacy Simple Machines Forum installation that runs on MySQL.

To facilitate usage, I use a second Flask-SQLAlchemy bind for the MySQL database and setup the classes via reflection. Querying works fine in general, but using count() raises a sqlalchemy.exc.ProgrammingError that the corresponding table would not exist.

Code

myapp/app.py:

from flask import Flask

class Config(object):
    SQLALCHEMY_DATABASE_URI = 'postgres://localhost/igor'
    SQLALCHEMY_BINDS = {
        'smf': 'mysql://myuser:mypass@localhost/my_db',
    }

app = Flask('myapp')
app.config.from_object(Config)

myapp/model.py:

from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy import MetaData

from .app import app

__all__ = []

def _add_global(key, value):
    globals()[key] = value
    __all__.append(key)


bind_key = 'smf'
table_prefix = 'smf_'
table_prefix_len = len(table_prefix)

db = SQLAlchemy(app)
engine = db.get_engine(app, bind=bind_key)
meta = MetaData(bind=engine)

# Reflect SMF database
meta.reflect()
for tablename, table in meta.tables.items():
    # Skip non-SMF tables
    if not tablename.startswith(table_prefix):
        continue
    # Strip table name prefix
    tablename = tablename[table_prefix_len:]
    # Do not create a class for tables without primary key
    if not table.primary_key:
        _add_global(tablename, table)
        continue
    # Derive class name from table name by camel-casing,
    # e.g. `smf_personal_messages` -> `PersonalMessages`
    classname = ''.join(x.capitalize() for x in str(tablename).split('_'))
    # Create class
    class_ = type(classname, (db.Model,), {
        '__table__': table,
        '__bind_key__': bind_key,
    })
    _add_global(classname, class_)

Example (file paths in the error stack trace shortened for legibility):

% python
Python 2.7.6 (default, Dec  2 2013, 11:07:48)
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from myapp.model import Topics
>>> len(Topics.query.all())
10162
>>> Topics.query.count()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "…/sqlalchemy/orm/query.py", line 2573, in count
    return self.from_self(col).scalar()
  File "…/sqlalchemy/orm/query.py", line 2379, in scalar
    ret = self.one()
  File "…/sqlalchemy/orm/query.py", line 2348, in one
    ret = list(self)
  File "…/sqlalchemy/orm/query.py", line 2391, in __iter__
    return self._execute_and_instances(context)
  File "…/sqlalchemy/orm/query.py", line 2406, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "…/sqlalchemy/engine/base.py", line 717, in execute
    return meth(self, multiparams, params)
  File "…/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "…/sqlalchemy/engine/base.py", line 814, in _execute_clauseelement
    compiled_sql, distilled_params
  File "…/sqlalchemy/engine/base.py", line 927, in _execute_context
    context)
  File "…/sqlalchemy/engine/base.py", line 1076, in _handle_dbapi_exception
    exc_info
  File "…/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "…/sqlalchemy/engine/base.py", line 920, in _execute_context
    context)
  File "…/sqlalchemy/engine/default.py", line 425, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation "smf_topics" does not exist
LINE 3: FROM smf_topics) AS anon_1
             ^
 'SELECT count(*) AS count_1 \nFROM (SELECT smf_topics.id_topic AS smf_topics_id_topic, […] \nFROM smf_topics) AS anon_1' {}

In the latter case, the statement is obviously run against the primary PostgreSQL bind instead of the MySQL bind. (This can easily be proven by creating a smf_topics table in the connected PostgreSQL database.)

I also tried providing the __tablename__ attribute in addition to (and also in place of) __table__ when creating the classes, but to no avail.

I guess I am missing something crucial here. Unfortunately, time constraints prohibit migrating the forum to PostgreSQL. Any help is appreciated.

Update (Feb 23, 2015)

Here is a minimal example to reproduce the error. Using count() with a conventional model class on the other bind—see class Topic(db.Model)—works, though.

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy import MetaData

# Application setup
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://localhost/igor'
app.config['SQLALCHEMY_BINDS'] = {
    'smf': 'mysql://myuser:mypass@localhost/my_db',
}
db = SQLAlchemy(app)

# Database reflection
smf_meta = MetaData(bind=db.get_engine(app, bind='smf'))
smf_meta.reflect()
topic_class = type(db.Model)('Topic', (db.Model,), {
    '__bind_key__': 'smf',
    '__tablename__': 'smf_topics',
    '__table__': smf_meta.tables['smf_topics'],
})

# Conventional model class
class Topic(db.Model):
    __bind_key__ = 'smf'
    __tablename__ = 'smf_topics'
    id_topic = db.Column(db.Integer, primary_key=True)
    num_replies = db.Column(db.Integer, nullable=False, default=0)

# Run it
if __name__ == '__main__':
    print('1. {}'.format(Topic.query.count()))
    print('2. {}'.format(len(topic_class.query.all())))
    print('3. {}'.format(topic_class.query.count()))

And the output when running the script:

1. 10400
2. 10400
Traceback (most recent call last):
  File "./test.py", line 35, in <module>
    print('3. {}'.format(topic_class.query.count()))
  File "…/sqlalchemy/orm/query.py", line 2640, in count
    return self.from_self(col).scalar()
  File "…/sqlalchemy/orm/query.py", line 2426, in scalar
    ret = self.one()
  File "…/sqlalchemy/orm/query.py", line 2395, in one
    ret = list(self)
  File "…/sqlalchemy/orm/query.py", line 2438, in __iter__
    return self._execute_and_instances(context)
  File "…/sqlalchemy/orm/query.py", line 2453, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "…/sqlalchemy/engine/base.py", line 729, in execute
    return meth(self, multiparams, params)
  File "…/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "…/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
    compiled_sql, distilled_params
  File "…/sqlalchemy/engine/base.py", line 958, in _execute_context
    context)
  File "…/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
    exc_info
  File "…/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "…/sqlalchemy/engine/base.py", line 951, in _execute_context
    context)
  File "…/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation "smf_topics" does not exist
LINE 3: FROM smf_topics) AS anon_1
             ^
 'SELECT count(*) AS count_1 \nFROM (SELECT smf_topics.id_topic AS smf_topics_id_topic, […] \nFROM smf_topics) AS anon_1' {}

Answer 1

And here’s how it works. ^^ The key is to use db.reflect() and then provide db.Model.metadata as the newly created class’ metadata:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://localhost/igor'
app.config['SQLALCHEMY_BINDS'] = {
    'smf': 'mysql://myuser:mypass@localhost/my_db',
}
db = SQLAlchemy(app)
db.reflect(bind='smf', app=app)
topic_class = type(db.Model)('Topic', (db.Model,), {
    '__bind_key__': 'smf',
    '__table__': db.Model.metadata.tables['smf_topics'],
    '__tablename__': 'smf_topics',
    '__module__': __name__,
    'metadata': db.Model.metadata,
})

if __name__ == '__main__':
    print('1. {}'.format(len(topic_class.query.all())))
    print('2. {}'.format(topic_class.query.count()))