`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](http://simplemachines.org) 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: 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()))
    

