Creating a custom function in SQLAlchemy

Question

SQLAlchemy provides a very clean interface for defining database tables:

engine = create_engine('sqlite:///:memory:') metadata = MetaData() user = Table('user', metadata, Column('user_id', Integer, primary_key = True), Column('user_name', String(16), nullable = False), Column('email_address', String(60), key='email'), Column('password', String(20), nullable = False) ) user_prefs = Table('user_prefs', metadata, Column('pref_id', Integer, primary_key=True), Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False), Column('pref_name', String(40), nullable=False), Column('pref_value', String(100)) ) 

And once these tables are defined, it is very easy to create these tables using the metadata.create_all(engine) function. This is especially good for testing when you want to create tables that will not interfere with existing tables used in the production process.

The project I'm working on now is heavily dependent on custom functions in postgres. Is there an easy way to define these functions using SQLAlchemy so that metadata.create_all(engine) correctly creates the functions along with the corresponding tables?

Answer 1

Today I worked on something similar. So far, the best way I've found to do this is to use sqlalchemy before_create event before_create . For general functions, you can bind events to metadata, but for specific functions of a table, you can bind to tables. For instance:

 import sqlalchemy from sqlalchemy.schema import DDL sqlalchemy.event.listen( metadata, 'before_create', DDL('CREATE OR REPLACE FUNCTION myfunc() ...') ) 

You can simply replace the metadata with your table if you want to create a function before creating the table.

This DDL seems to run every time you call metadata.create_all , so it is important to use CREATE OR REPLACE . If you want more control over the creation of functions, you can better study migration with alembic or similar.

Some applications of DDL are described in the sqlalchemy docs here: http://docs.sqlalchemy.org/en/latest/core/ddl.html#custom-ddl