Plugin that enables unicode decoding for PostgreSQL's CITEXT column type when using psycopg2 and SQLAlchemy

gistfile1.py

from sqlalchemy.event import listen
def register_citext_type(conn, con_record):
    from psycopg2.extensions import new_type, register_type
    from contextlib import closing
    def cast_citext(in_str, cursor):
        if in_str == None:
            return None
        return unicode(in_str, cursor.connection.encoding)
    with closing(conn.cursor()) as c:
        c.execute("SELECT pg_type.oid FROM pg_type WHERE typname = 'citext'")
        citext_oid = c.fetchone()
        if citext_oid != None:
            citext_type = new_type(citext_oid, "CITEXT", cast_citext)
            register_type(citext_type)

# Replace db.engine with your SQLAlchemy engine
listen(db.engine, "first_connect", register_citext_type)

Thank you for posting this!