How to use Postgres' Hstore column with flask-sqlalchemy?

flask, flask-sqlalchemy, hstore, python, sqlalchemy

Solution

Try this:

from flaskext.sqlalchemy import SQLAlchemy
import psycopg2
import psycopg2.extras

class _SQLAlchemy(SQLAlchemy):
    def apply_driver_hacks(self, app, info, options):
        """This method adds option to support hstore on psycopg2"""

        if info.drivername == "postgres":
            def _connect():
                conn = psycopg2.connect(user=info.username,
                                        host=info.host,
                                        port=info.port,
                                        dbname=info.database,
                                        password=info.password)
                psycopg2.extras.register_hstore(conn)
                return conn
            options["creator"] = _connect
        SQLAlchemy.apply_driver_hacks(self, app, info, options)

Also see:

- http://packages.python.org/Flask-SQLAlchemy/api.html?highlight=apply_driver_hack#flask.ext.sqlalchemy.SQLAlchemy.apply_driver_hacks

- http://docs.sqlalchemy.org/en/rel_0_7/core/engines.html#custom-dbapi-connect-arguments

Problem

I am attempting to implement this code https://gist.github.com/1859653 that allows sqlalchemy to interact with an hstore column. Its mentioned in that gist's comments the need to run the psycopg2.extras.register_hstore. When and were should this function be run? If i do: ``` @app.before_request def reg_hstore() : register_hstore(db.engine.raw_connection(), True) ``` heroku errors with 'too many connections' there is also mention of using pghstore (http://pypi.python.org/pypi/pghstore) instead of psycopg2 but it gives no indication how to set it up. Also, I'm wondering if the use of hstore indexes is supported in this add-on code.

Original source