How to specify the PostgreSQL DateStyle property with SQLAlchemy ORM

postgresql, python, sqlalchemy

Solution

SQLAlchemy makes use of the DBAPI, usually psycopg2, to marshal date values to and from python datetime objects - you can then format/parse any way you want using standard python techniques. So no database-side date formatting features are needed.

If you do want to set this variable, you can just execute PG's SET statement:

conn = engine.connect()
conn.execute("SET DateStyle='somestring'")
# work with conn

to make this global to all connections:

from sqlalchemy import event
from sqlalchemy.engine import Engine

@event.listens_for(Engine, "connect")
def connect(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("SET DateStyle='somestring'")
    cursor.close()

Problem

PostgreSQL supports specifying Date Formats using the DateStyle Property as mentioned here, http://www.postgresql.org/docs/current/interactive/runtime-config-client.html#GUC-DATESTYLE. (link was originally to 8.3 version of docs). I could not find any SQLAlchemy ORM documentation reference on to how to define this property. Is it possible to do it?

Original source