Custom SQL Constructs and Compilation Extension

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

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

    1. from sqlalchemy import select
    3. s = select(MyColumn('x'), MyColumn('y'))
    4. print(str(s))

    Produces:

    1. SELECT [x], [y]

    Dialect-specific compilation rules

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

    1. from sqlalchemy.schema import DDLElement
    3. class AlterColumn(DDLElement):
    4.     inherit_cache = False
    6.     def __init__(self, column, cmd):
    7.         self.column = column
    8.         self.cmd = cmd
    10. @compiles(AlterColumn)
    11. def visit_alter_column(element, compiler, **kw):
    12.     return "ALTER COLUMN %s ..." % element.column.name
    14. @compiles(AlterColumn, 'postgresql')
    15. def visit_alter_column(element, compiler, **kw):
    16.     return "ALTER TABLE %s ALTER COLUMN %s ..." % (element.table.name,
    17.                                                    element.column.name)

    The second visit_alter_table will be invoked when any postgresql dialect is used.

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

    1. from sqlalchemy.sql.expression import Executable, ClauseElement
    3. class InsertFromSelect(Executable, ClauseElement):
    4.     inherit_cache = False
    6.     def __init__(self, table, select):
    7.         self.table = table
    8.         self.select = select
    10. @compiles(InsertFromSelect)
    11. def visit_insert_from_select(element, compiler, **kw):
    12.     return "INSERT INTO %s (%s)" % (
    13.         compiler.process(element.table, asfrom=True, **kw),
    14.         compiler.process(element.select, **kw)
    15.     )
    17. insert = InsertFromSelect(t1, select(t1).where(t1.c.x>5))
    18. print(insert)

    Produces:

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

    Note

    The above InsertFromSelect construct is only an example, this actual functionality is already available using the method.

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

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

    Changing the default compilation of existing constructs

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

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

    2. @compiles(Insert)
    3. def prefix_inserts(insert, compiler, **kw):
    4.     return compiler.visit_insert(insert.prefix_with("some prefix"), **kw)

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

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

    1. @compiles(String, 'mssql')
    2. @compiles(VARCHAR, 'mssql')
    3. def compile_varchar(element, compiler, **kw):
    4.     if element.length == 'max':
    5.         return "VARCHAR('max')"
    6.     else:
    7.         return compiler.visit_VARCHAR(element, **kw)
    9. foo = Table('foo', metadata,
    10.     Column('data', VARCHAR('max'))
    11. )

    Subclassing Guidelines

    • - This is the root expression class. Any SQL expression can be derived from this base, and is probably the best choice for longer constructs such as specialized INSERT statements.

    • ColumnElement - The root of all “column-like” elements. Anything that you’d place in the “columns” clause of a SELECT statement (as well as order by and group by) can derive from this - the object will automatically have Python “comparison” behavior.

      classes want to have a type member which is expression’s return type. This can be established at the instance level in the constructor, or at the class level if its generally constant:

      1. class timestamp(ColumnElement):
      2.     type = TIMESTAMP()
      3.     inherit_cache = True

    • FunctionElement - This is a hybrid of a ColumnElement and a “from clause” like object, and represents a SQL function or stored procedure type of call. Since most databases support statements along the line of “SELECT FROM <some function>” FunctionElement adds in the ability to be used in the FROM clause of a select() construct:

      1. from sqlalchemy.sql.expression import FunctionElement
      3. class coalesce(FunctionElement):
      4.     name = 'coalesce'
      5.     inherit_cache = True
      7. @compiles(coalesce)
      8. def compile(element, compiler, **kw):
      9.     return "coalesce(%s)" % compiler.process(element.clauses, **kw)
      11. @compiles(coalesce, 'oracle')
      12. def compile(element, compiler, **kw):
      13.     if len(element.clauses) > 2:
      14.         raise TypeError("coalesce only supports two arguments on Oracle")
      15.     return "nvl(%s)" % compiler.process(element.clauses, **kw)

    • - The root of all DDL expressions, like CREATE TABLE, ALTER TABLE, etc. Compilation of ExecutableDDLElement subclasses is issued by a instead of a SQLCompiler. can also be used as an event hook in conjunction with event hooks like DDLEvents.before_create() and , allowing the construct to be invoked automatically during CREATE TABLE and DROP TABLE sequences.

      See also

      Customizing DDL - contains examples of associating objects (which are themselves ExecutableDDLElement instances) with event hooks.

    • Executable - This is a mixin which should be used with any expression class that represents a “standalone” SQL statement that can be passed directly to an execute() method. It is already implicit within DDLElement and FunctionElement.

    Most of the above constructs also respond to SQL statement caching. A subclassed construct will want to define the caching behavior for the object, which usually means setting the flag inherit_cache to the value of False or True. See the next section for background.

    SQLAlchemy as of version 1.4 includes a which will allow equivalent SQL constructs to cache their stringified form, along with other structural information used to fetch results from the statement.

    For reasons discussed at Object will not produce a cache key, Performance Implications, the implementation of this caching system takes a conservative approach towards including custom SQL constructs and/or subclasses within the caching system. This includes that any user-defined SQL constructs, including all the examples for this extension, will not participate in caching by default unless they positively assert that they are able to do so. The attribute when set to True at the class level of a specific subclass will indicate that instances of this class may be safely cached, using the cache key generation scheme of the immediate superclass. This applies for example to the “synopsis” example indicated previously:

    1. class MyColumn(ColumnClause):
    2.     inherit_cache = True
    4. @compiles(MyColumn)
    5. def compile_mycolumn(element, compiler, **kw):
    6.     return "[%s]" % element.name

    Above, the MyColumn class does not include any new state that affects its SQL compilation; the cache key of MyColumn instances will make use of that of the ColumnClause superclass, meaning it will take into account the class of the object (MyColumn), the string name and datatype of the object:

    For objects that are likely to be used liberally as components within many larger statements, such as Column subclasses and custom SQL datatypes, it’s important that caching be enabled as much as possible, as this may otherwise negatively affect performance.

    An example of an object that does contain state which affects its SQL compilation is the one illustrated at ; this is an “INSERT FROM SELECT” construct that combines together a Table as well as a construct, each of which independently affect the SQL string generation of the construct. For this class, the example illustrates that it simply does not participate in caching:

    1. class InsertFromSelect(Executable, ClauseElement):
    2.     inherit_cache = False
    4.     def __init__(self, table, select):
    5.         self.table = table
    8. @compiles(InsertFromSelect)
    9. def visit_insert_from_select(element, compiler, **kw):
    10.     return "INSERT INTO %s (%s)" % (
    11.         compiler.process(element.table, asfrom=True, **kw),
    12.         compiler.process(element.select, **kw)

    While it is also possible that the above InsertFromSelect could be made to produce a cache key that is composed of that of the Table and components together, the API for this is not at the moment fully public. However, for an “INSERT FROM SELECT” construct, which is only used by itself for specific operations, caching is not as critical as in the previous example.

    Further Examples

    A function that works like “CURRENT_TIMESTAMP” except applies the appropriate conversions so that the time is in UTC time. Timestamps are best stored in relational databases as UTC, without time zones. UTC so that your database doesn’t think time has gone backwards in the hour when daylight savings ends, without timezones because timezones are like character encodings - 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:

    1. from sqlalchemy.sql import expression
    2. from sqlalchemy.ext.compiler import compiles
    3. from sqlalchemy.types import DateTime
    5. class utcnow(expression.FunctionElement):
    6.     type = DateTime()
    7.     inherit_cache = True
    9. @compiles(utcnow, 'postgresql')
    10. def pg_utcnow(element, compiler, **kw):
    11.     return "TIMEZONE('utc', CURRENT_TIMESTAMP)"
    13. @compiles(utcnow, 'mssql')
    14. def ms_utcnow(element, compiler, **kw):
    15.     return "GETUTCDATE()"

    Example usage:

    1. from sqlalchemy import (
    2.             Table, Column, Integer, String, DateTime, MetaData
    3.         )
    4. metadata = MetaData()
    5. event = Table("event", metadata,
    6.     Column("id", Integer, primary_key=True),
    7.     Column("description", String(50), nullable=False),
    8.     Column("timestamp", DateTime, server_default=utcnow())
    9. )

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

    1. from sqlalchemy.sql import expression, case
    2. from sqlalchemy.ext.compiler import compiles
    3. from sqlalchemy.types import Numeric
    5. class greatest(expression.FunctionElement):
    6.     type = Numeric()
    7.     name = 'greatest'
    8.     inherit_cache = True
    10. @compiles(greatest)
    11. def default_greatest(element, compiler, **kw):
    12.     return compiler.visit_function(element)
    14. @compiles(greatest, 'sqlite')
    15. @compiles(greatest, 'mssql')
    16. @compiles(greatest, 'oracle')
    17. def case_greatest(element, compiler, **kw):
    18.     arg1, arg2 = list(element.clauses)
    19.     return compiler.process(case([(arg1 > arg2, arg1)], else_=arg2), **kw)

    Example usage:

    1. Session.query(Account).\
    2.         filter(
    3.             greatest(
    4.                 Account.checking_balance,
    5.                 Account.savings_balance) > 10000
    6.         )

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

    Example usage:

    1. from sqlalchemy import select, union_all
    3. exp = union_all(
    4.     select(users.c.name, sql_false().label("enrolled")),
    6. )

    function sqlalchemy.ext.compiler.compiles(class__, *specs_)

    Register a function as a compiler for a given ClauseElement type.

    function sqlalchemy.ext.compiler.deregister(class_)

    Remove all custom compilers associated with a given type.