Custom SQL Constructs and Compilation Extension

Custom SQL Constructs and Compilation Extension

Provides an API for creation of custom ClauseElements and compilers.

Synopsis

Usage involves the creation of one or moreClauseElement subclasses and one ormore callables defining its compilation:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import ColumnClause
 
class MyColumn(ColumnClause):
    pass
 
@compiles(MyColumn)
def compile_mycolumn(element, compiler, **kw):
    return "[%s]" % element.name

Above, MyColumn extends ColumnClause,the base expression element for named column objects. The compilesdecorator registers itself with the MyColumn class so that it is invokedwhen the object is compiled to a string:

from sqlalchemy import select
 
s = select([MyColumn('x'), MyColumn('y')])
print str(s)

Produces:

SELECT [x], [y]

Dialect-specific compilation rules

Compilers can also be made dialect-specific. The appropriate compiler will beinvoked for the dialect in use:

from sqlalchemy.schema import DDLElement
 
class AlterColumn(DDLElement):
 
    def __init__(self, column, cmd):
        self.column = column
        self.cmd = cmd
 
@compiles(AlterColumn)
def visit_alter_column(element, compiler, **kw):
    return "ALTER COLUMN %s ..." % element.column.name
 
@compiles(AlterColumn, 'postgresql')
def visit_alter_column(element, compiler, **kw):
    return "ALTER TABLE %s ALTER COLUMN %s ..." % (element.table.name,
                                                   element.column.name)

The second visit_alter_table will be invoked when any postgresqldialect is used.

Compiling sub-elements of a custom expression construct

The compiler argument is theCompiled object in use. This objectcan be inspected for any information about the in-progress compilation,including compiler.dialect, compiler.statement etc. TheSQLCompiler andDDLCompiler both include a process()method which can be used for compilation of embedded attributes:

from sqlalchemy.sql.expression import Executable, ClauseElement
 
class InsertFromSelect(Executable, ClauseElement):
    def __init__(self, table, select):
        self.table = table
        self.select = select
 
@compiles(InsertFromSelect)
def visit_insert_from_select(element, compiler, **kw):
    return "INSERT INTO %s (%s)" % (
        compiler.process(element.table, asfrom=True, **kw),
        compiler.process(element.select, **kw)
    )
 
insert = InsertFromSelect(t1, select([t1]).where(t1.c.x>5))
print insert

Produces:

"INSERT INTO mytable (SELECT mytable.x, mytable.y, mytable.z
                      FROM mytable WHERE mytable.x > :x_1)"

Note

The above InsertFromSelect construct is only an example, this actualfunctionality is already available using theInsert.from_select() method.

Note

The above InsertFromSelect construct probably wants to have “autocommit”enabled. See Enabling Autocommit on a Construct for this step.

Cross Compiling between SQL and DDL compilers

SQL and DDL constructs are each compiled using different base compilers -SQLCompiler and DDLCompiler. A common need is to access thecompilation rules of SQL expressions from within a DDL expression. TheDDLCompiler includes an accessor sql_compiler for this reason, such asbelow where we generate a CHECK constraint that embeds a SQL expression:

@compiles(MyConstraint)def compile_my_constraint(constraint, ddlcompiler, kw):    kw['literal_binds'] = True    return "CONSTRAINT %s CHECK (%s)" % (        constraint.name,        ddlcompiler.sql_compiler.process(            constraint.expression, kw)    )

Above, we add an additional flag to the process step as called bySQLCompiler.process(), which is the literal_binds flag. Thisindicates that any SQL expression which refers to a BindParameterobject or other “literal” object such as those which refer to strings orintegers should be rendered in-place, rather than being referred to asa bound parameter; when emitting DDL, bound parameters are typically notsupported.

Enabling Autocommit on a Construct

Recall from the section Understanding Autocommit that the Engine, whenasked to execute a construct in the absence of a user-defined transaction,detects if the given construct represents DML or DDL, that is, a datamodification or data definition statement, which requires (or may require,in the case of DDL) that the transaction generated by the DBAPI be committed(recall that DBAPI always has a transaction going on regardless of whatSQLAlchemy does). Checking for this is actually accomplished by checking forthe “autocommit” execution option on the construct. When building aconstruct like an INSERT derivation, a new DDL type, or perhaps a storedprocedure that alters data, the “autocommit” option needs to be set in orderfor the statement to function with “connectionless” execution(as described in Connectionless Execution, Implicit Execution).

Currently a quick way to do this is to subclass Executable, thenadd the “autocommit” flag to the _execution_options dictionary (note thisis a “frozen” dictionary which supplies a generative union() method):

from sqlalchemy.sql.expression import Executable, ClauseElement
 
class MyInsertThing(Executable, ClauseElement):
    _execution_options = \
        Executable._execution_options.union({'autocommit': True})

More succinctly, if the construct is truly similar to an INSERT, UPDATE, orDELETE, UpdateBase can be used, which already is a subclassof Executable, ClauseElement and includes theautocommit flag:

from sqlalchemy.sql.expression import UpdateBase
 
class MyInsertThing(UpdateBase):
    def __init__(self, ...):
        ...

DDL elements that subclass DDLElement already have the“autocommit” flag turned on.

Changing the default compilation of existing constructs

The compiler extension applies just as well to the existing constructs. Whenoverriding the compilation of a built in SQL construct, the @compilesdecorator is invoked upon the appropriate class (be sure to use the class,i.e. Insert or Select, instead of the creation function suchas insert() or select()).

Within the new compilation function, to get at the “original” compilationroutine, use the appropriate visit_XXX method - thisbecause compiler.process() will call upon the overriding routine and causean endless loop. Such as, to add “prefix” to all insert statements:

from sqlalchemy.sql.expression import Insert
 
@compiles(Insert)
def prefix_inserts(insert, compiler, **kw):
    return compiler.visit_insert(insert.prefix_with("some prefix"), **kw)

The above compiler will prefix all INSERT statements with “some prefix” whencompiled.

Changing Compilation of Types

compiler works for types, too, such as below where we implement theMS-SQL specific ‘max’ keyword for String/VARCHAR:

@compiles(String, 'mssql')@compiles(VARCHAR, 'mssql')def compile_varchar(element, compiler, kw):    if element.length == 'max':        return "VARCHAR('max')"    else:        return compiler.visit_VARCHAR(element, kw)
foo = Table('foo', metadata,    Column('data', VARCHAR('max')))

Subclassing Guidelines

A big part of using the compiler extension is subclassing SQLAlchemyexpression constructs. To make this easier, the expression andschema packages feature a set of “bases” intended for common tasks.A synopsis is as follows:

ClauseElement - This is the rootexpression class. Any SQL expression can be derived from this base, and isprobably the best choice for longer constructs such as specialized INSERTstatements.
ColumnElement - The root of all“column-like” elements. Anything that you’d place in the “columns” clause ofa SELECT statement (as well as order by and group by) can derive from this -the object will automatically have Python “comparison” behavior.

ColumnElement classes want to have atype member which is expression’s return type. This can be establishedat the instance level in the constructor, or at the class level if itsgenerally constant:

class timestamp(ColumnElement):
    type = TIMESTAMP()

FunctionElement - This is a hybrid of aColumnElement and a “from clause” like object, and represents a SQLfunction or stored procedure type of call. Since most databases supportstatements along the line of “SELECT FROM ”FunctionElement adds in the ability to be used in the FROM clause of aselect() construct:

from sqlalchemy.sql.expression import FunctionElement
 
class coalesce(FunctionElement):
    name = 'coalesce'
 
@compiles(coalesce)
def compile(element, compiler, **kw):
    return "coalesce(%s)" % compiler.process(element.clauses, **kw)
 
@compiles(coalesce, 'oracle')
def compile(element, compiler, **kw):
    if len(element.clauses) > 2:
        raise TypeError("coalesce only supports two arguments on Oracle")
    return "nvl(%s)" % compiler.process(element.clauses, **kw)

DDLElement - The root of all DDL expressions,like CREATE TABLE, ALTER TABLE, etc. Compilation of DDLElementsubclasses is issued by a DDLCompiler instead of a SQLCompiler.DDLElement also features Table and MetaData event hooks via theexecute_at() method, allowing the construct to be invoked during CREATETABLE and DROP TABLE sequences.
Executable - This is a mixin whichshould be used with any expression class that represents a “standalone”SQL statement that can be passed directly to an execute() method. Itis already implicit within DDLElement and FunctionElement.

Further Examples

“UTC timestamp” function

A function that works like “CURRENT_TIMESTAMP” except applies theappropriate conversions so that the time is in UTC time. Timestamps are beststored in relational databases as UTC, without time zones. UTC so that yourdatabase doesn’t think time has gone backwards in the hour when daylightsavings ends, without timezones because timezones are like characterencodings - they’re best applied only at the endpoints of an application(i.e. convert to UTC upon user input, re-apply desired timezone upon display).

For PostgreSQL and Microsoft SQL Server:

from sqlalchemy.sql import expression
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.types import DateTime
 
class utcnow(expression.FunctionElement):
    type = DateTime()
 
@compiles(utcnow, 'postgresql')
def pg_utcnow(element, compiler, **kw):
    return "TIMEZONE('utc', CURRENT_TIMESTAMP)"
 
@compiles(utcnow, 'mssql')
def ms_utcnow(element, compiler, **kw):
    return "GETUTCDATE()"

Example usage:

from sqlalchemy import (
            Table, Column, Integer, String, DateTime, MetaData
        )
metadata = MetaData()
event = Table("event", metadata,
    Column("id", Integer, primary_key=True),
    Column("description", String(50), nullable=False),
    Column("timestamp", DateTime, server_default=utcnow())
)

“GREATEST” function

The “GREATEST” function is given any number of arguments and returns the onethat is of the highest value - its equivalent to Python’s maxfunction. A SQL standard version versus a CASE based version which onlyaccommodates two arguments:

from sqlalchemy.sql import expression, case
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.types import Numeric
 
class greatest(expression.FunctionElement):
    type = Numeric()
    name = 'greatest'
 
@compiles(greatest)
def default_greatest(element, compiler, **kw):
    return compiler.visit_function(element)
 
@compiles(greatest, 'sqlite')
@compiles(greatest, 'mssql')
@compiles(greatest, 'oracle')
def case_greatest(element, compiler, **kw):
    arg1, arg2 = list(element.clauses)
    return compiler.process(case([(arg1 > arg2, arg1)], else_=arg2), **kw)

Example usage:

Session.query(Account).\
        filter(
            greatest(
                Account.checking_balance,
                Account.savings_balance) > 10000
        )

“false” expression

Render a “false” constant expression, rendering as “0” on platforms thatdon’t have a “false” constant:

from sqlalchemy.sql import expression
from sqlalchemy.ext.compiler import compiles
 
class sql_false(expression.ColumnElement):
    pass
 
@compiles(sql_false)
def default_false(element, compiler, **kw):
    return "false"
 
@compiles(sql_false, 'mssql')
@compiles(sql_false, 'mysql')
@compiles(sql_false, 'oracle')
def int_false(element, compiler, **kw):
    return "0"

Example usage:

from sqlalchemy import select, union_all
 
exp = union_all(
    select([users.c.name, sql_false().label("enrolled")]),
    select([customers.c.name, customers.c.enrolled])
)

sqlalchemy.ext.compiler.compiles(class__, *specs_)
Register a function as a compiler for agiven ClauseElement type.
sqlalchemy.ext.compiler.deregister(class_)
Remove all custom compilers associated with a givenClauseElement type.