Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# ext/compiler.py
2# Copyright (C) 2005-2025 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8r"""Provides an API for creation of custom ClauseElements and compilers.
10Synopsis
11========
13Usage involves the creation of one or more
14:class:`~sqlalchemy.sql.expression.ClauseElement` subclasses and one or
15more callables defining its compilation::
17 from sqlalchemy.ext.compiler import compiles
18 from sqlalchemy.sql.expression import ColumnClause
21 class MyColumn(ColumnClause):
22 inherit_cache = True
25 @compiles(MyColumn)
26 def compile_mycolumn(element, compiler, **kw):
27 return "[%s]" % element.name
29Above, ``MyColumn`` extends :class:`~sqlalchemy.sql.expression.ColumnClause`,
30the base expression element for named column objects. The ``compiles``
31decorator registers itself with the ``MyColumn`` class so that it is invoked
32when the object is compiled to a string::
34 from sqlalchemy import select
36 s = select(MyColumn("x"), MyColumn("y"))
37 print(str(s))
39Produces:
41.. sourcecode:: sql
43 SELECT [x], [y]
45Dialect-specific compilation rules
46==================================
48Compilers can also be made dialect-specific. The appropriate compiler will be
49invoked for the dialect in use::
51 from sqlalchemy.schema import DDLElement
54 class AlterColumn(DDLElement):
55 inherit_cache = False
57 def __init__(self, column, cmd):
58 self.column = column
59 self.cmd = cmd
62 @compiles(AlterColumn)
63 def visit_alter_column(element, compiler, **kw):
64 return "ALTER COLUMN %s ..." % element.column.name
67 @compiles(AlterColumn, "postgresql")
68 def visit_alter_column(element, compiler, **kw):
69 return "ALTER TABLE %s ALTER COLUMN %s ..." % (
70 element.table.name,
71 element.column.name,
72 )
74The second ``visit_alter_table`` will be invoked when any ``postgresql``
75dialect is used.
77.. _compilerext_compiling_subelements:
79Compiling sub-elements of a custom expression construct
80=======================================================
82The ``compiler`` argument is the
83:class:`~sqlalchemy.engine.interfaces.Compiled` object in use. This object
84can be inspected for any information about the in-progress compilation,
85including ``compiler.dialect``, ``compiler.statement`` etc. The
86:class:`~sqlalchemy.sql.compiler.SQLCompiler` and
87:class:`~sqlalchemy.sql.compiler.DDLCompiler` both include a ``process()``
88method which can be used for compilation of embedded attributes::
90 from sqlalchemy.sql.expression import Executable, ClauseElement
93 class InsertFromSelect(Executable, ClauseElement):
94 inherit_cache = False
96 def __init__(self, table, select):
97 self.table = table
98 self.select = select
101 @compiles(InsertFromSelect)
102 def visit_insert_from_select(element, compiler, **kw):
103 return "INSERT INTO %s (%s)" % (
104 compiler.process(element.table, asfrom=True, **kw),
105 compiler.process(element.select, **kw),
106 )
109 insert = InsertFromSelect(t1, select(t1).where(t1.c.x > 5))
110 print(insert)
112Produces (formatted for readability):
114.. sourcecode:: sql
116 INSERT INTO mytable (
117 SELECT mytable.x, mytable.y, mytable.z
118 FROM mytable
119 WHERE mytable.x > :x_1
120 )
122.. note::
124 The above ``InsertFromSelect`` construct is only an example, this actual
125 functionality is already available using the
126 :meth:`_expression.Insert.from_select` method.
129Cross Compiling between SQL and DDL compilers
130---------------------------------------------
132SQL and DDL constructs are each compiled using different base compilers -
133``SQLCompiler`` and ``DDLCompiler``. A common need is to access the
134compilation rules of SQL expressions from within a DDL expression. The
135``DDLCompiler`` includes an accessor ``sql_compiler`` for this reason, such as
136below where we generate a CHECK constraint that embeds a SQL expression::
138 @compiles(MyConstraint)
139 def compile_my_constraint(constraint, ddlcompiler, **kw):
140 kw["literal_binds"] = True
141 return "CONSTRAINT %s CHECK (%s)" % (
142 constraint.name,
143 ddlcompiler.sql_compiler.process(constraint.expression, **kw),
144 )
146Above, we add an additional flag to the process step as called by
147:meth:`.SQLCompiler.process`, which is the ``literal_binds`` flag. This
148indicates that any SQL expression which refers to a :class:`.BindParameter`
149object or other "literal" object such as those which refer to strings or
150integers should be rendered **in-place**, rather than being referred to as
151a bound parameter; when emitting DDL, bound parameters are typically not
152supported.
155Changing the default compilation of existing constructs
156=======================================================
158The compiler extension applies just as well to the existing constructs. When
159overriding the compilation of a built in SQL construct, the @compiles
160decorator is invoked upon the appropriate class (be sure to use the class,
161i.e. ``Insert`` or ``Select``, instead of the creation function such
162as ``insert()`` or ``select()``).
164Within the new compilation function, to get at the "original" compilation
165routine, use the appropriate visit_XXX method - this
166because compiler.process() will call upon the overriding routine and cause
167an endless loop. Such as, to add "prefix" to all insert statements::
169 from sqlalchemy.sql.expression import Insert
172 @compiles(Insert)
173 def prefix_inserts(insert, compiler, **kw):
174 return compiler.visit_insert(insert.prefix_with("some prefix"), **kw)
176The above compiler will prefix all INSERT statements with "some prefix" when
177compiled.
179.. _type_compilation_extension:
181Changing Compilation of Types
182=============================
184``compiler`` works for types, too, such as below where we implement the
185MS-SQL specific 'max' keyword for ``String``/``VARCHAR``::
187 @compiles(String, "mssql")
188 @compiles(VARCHAR, "mssql")
189 def compile_varchar(element, compiler, **kw):
190 if element.length == "max":
191 return "VARCHAR('max')"
192 else:
193 return compiler.visit_VARCHAR(element, **kw)
196 foo = Table("foo", metadata, Column("data", VARCHAR("max")))
198Subclassing Guidelines
199======================
201A big part of using the compiler extension is subclassing SQLAlchemy
202expression constructs. To make this easier, the expression and
203schema packages feature a set of "bases" intended for common tasks.
204A synopsis is as follows:
206* :class:`~sqlalchemy.sql.expression.ClauseElement` - This is the root
207 expression class. Any SQL expression can be derived from this base, and is
208 probably the best choice for longer constructs such as specialized INSERT
209 statements.
211* :class:`~sqlalchemy.sql.expression.ColumnElement` - The root of all
212 "column-like" elements. Anything that you'd place in the "columns" clause of
213 a SELECT statement (as well as order by and group by) can derive from this -
214 the object will automatically have Python "comparison" behavior.
216 :class:`~sqlalchemy.sql.expression.ColumnElement` classes want to have a
217 ``type`` member which is expression's return type. This can be established
218 at the instance level in the constructor, or at the class level if its
219 generally constant::
221 class timestamp(ColumnElement):
222 type = TIMESTAMP()
223 inherit_cache = True
225* :class:`~sqlalchemy.sql.functions.FunctionElement` - This is a hybrid of a
226 ``ColumnElement`` and a "from clause" like object, and represents a SQL
227 function or stored procedure type of call. Since most databases support
228 statements along the line of "SELECT FROM <some function>"
229 ``FunctionElement`` adds in the ability to be used in the FROM clause of a
230 ``select()`` construct::
232 from sqlalchemy.sql.expression import FunctionElement
235 class coalesce(FunctionElement):
236 name = "coalesce"
237 inherit_cache = True
240 @compiles(coalesce)
241 def compile(element, compiler, **kw):
242 return "coalesce(%s)" % compiler.process(element.clauses, **kw)
245 @compiles(coalesce, "oracle")
246 def compile(element, compiler, **kw):
247 if len(element.clauses) > 2:
248 raise TypeError(
249 "coalesce only supports two arguments on " "Oracle Database"
250 )
251 return "nvl(%s)" % compiler.process(element.clauses, **kw)
253* :class:`.ExecutableDDLElement` - The root of all DDL expressions,
254 like CREATE TABLE, ALTER TABLE, etc. Compilation of
255 :class:`.ExecutableDDLElement` subclasses is issued by a
256 :class:`.DDLCompiler` instead of a :class:`.SQLCompiler`.
257 :class:`.ExecutableDDLElement` can also be used as an event hook in
258 conjunction with event hooks like :meth:`.DDLEvents.before_create` and
259 :meth:`.DDLEvents.after_create`, allowing the construct to be invoked
260 automatically during CREATE TABLE and DROP TABLE sequences.
262 .. seealso::
264 :ref:`metadata_ddl_toplevel` - contains examples of associating
265 :class:`.DDL` objects (which are themselves :class:`.ExecutableDDLElement`
266 instances) with :class:`.DDLEvents` event hooks.
268* :class:`~sqlalchemy.sql.expression.Executable` - This is a mixin which
269 should be used with any expression class that represents a "standalone"
270 SQL statement that can be passed directly to an ``execute()`` method. It
271 is already implicit within ``DDLElement`` and ``FunctionElement``.
273Most of the above constructs also respond to SQL statement caching. A
274subclassed construct will want to define the caching behavior for the object,
275which usually means setting the flag ``inherit_cache`` to the value of
276``False`` or ``True``. See the next section :ref:`compilerext_caching`
277for background.
280.. _compilerext_caching:
282Enabling Caching Support for Custom Constructs
283==============================================
285SQLAlchemy as of version 1.4 includes a
286:ref:`SQL compilation caching facility <sql_caching>` which will allow
287equivalent SQL constructs to cache their stringified form, along with other
288structural information used to fetch results from the statement.
290For reasons discussed at :ref:`caching_caveats`, the implementation of this
291caching system takes a conservative approach towards including custom SQL
292constructs and/or subclasses within the caching system. This includes that
293any user-defined SQL constructs, including all the examples for this
294extension, will not participate in caching by default unless they positively
295assert that they are able to do so. The :attr:`.HasCacheKey.inherit_cache`
296attribute when set to ``True`` at the class level of a specific subclass
297will indicate that instances of this class may be safely cached, using the
298cache key generation scheme of the immediate superclass. This applies
299for example to the "synopsis" example indicated previously::
301 class MyColumn(ColumnClause):
302 inherit_cache = True
305 @compiles(MyColumn)
306 def compile_mycolumn(element, compiler, **kw):
307 return "[%s]" % element.name
309Above, the ``MyColumn`` class does not include any new state that
310affects its SQL compilation; the cache key of ``MyColumn`` instances will
311make use of that of the ``ColumnClause`` superclass, meaning it will take
312into account the class of the object (``MyColumn``), the string name and
313datatype of the object::
315 >>> MyColumn("some_name", String())._generate_cache_key()
316 CacheKey(
317 key=('0', <class '__main__.MyColumn'>,
318 'name', 'some_name',
319 'type', (<class 'sqlalchemy.sql.sqltypes.String'>,
320 ('length', None), ('collation', None))
321 ), bindparams=[])
323For objects that are likely to be **used liberally as components within many
324larger statements**, such as :class:`_schema.Column` subclasses and custom SQL
325datatypes, it's important that **caching be enabled as much as possible**, as
326this may otherwise negatively affect performance.
328An example of an object that **does** contain state which affects its SQL
329compilation is the one illustrated at :ref:`compilerext_compiling_subelements`;
330this is an "INSERT FROM SELECT" construct that combines together a
331:class:`_schema.Table` as well as a :class:`_sql.Select` construct, each of
332which independently affect the SQL string generation of the construct. For
333this class, the example illustrates that it simply does not participate in
334caching::
336 class InsertFromSelect(Executable, ClauseElement):
337 inherit_cache = False
339 def __init__(self, table, select):
340 self.table = table
341 self.select = select
344 @compiles(InsertFromSelect)
345 def visit_insert_from_select(element, compiler, **kw):
346 return "INSERT INTO %s (%s)" % (
347 compiler.process(element.table, asfrom=True, **kw),
348 compiler.process(element.select, **kw),
349 )
351While it is also possible that the above ``InsertFromSelect`` could be made to
352produce a cache key that is composed of that of the :class:`_schema.Table` and
353:class:`_sql.Select` components together, the API for this is not at the moment
354fully public. However, for an "INSERT FROM SELECT" construct, which is only
355used by itself for specific operations, caching is not as critical as in the
356previous example.
358For objects that are **used in relative isolation and are generally
359standalone**, such as custom :term:`DML` constructs like an "INSERT FROM
360SELECT", **caching is generally less critical** as the lack of caching for such
361a construct will have only localized implications for that specific operation.
364Further Examples
365================
367"UTC timestamp" function
368-------------------------
370A function that works like "CURRENT_TIMESTAMP" except applies the
371appropriate conversions so that the time is in UTC time. Timestamps are best
372stored in relational databases as UTC, without time zones. UTC so that your
373database doesn't think time has gone backwards in the hour when daylight
374savings ends, without timezones because timezones are like character
375encodings - they're best applied only at the endpoints of an application
376(i.e. convert to UTC upon user input, re-apply desired timezone upon display).
378For PostgreSQL and Microsoft SQL Server::
380 from sqlalchemy.sql import expression
381 from sqlalchemy.ext.compiler import compiles
382 from sqlalchemy.types import DateTime
385 class utcnow(expression.FunctionElement):
386 type = DateTime()
387 inherit_cache = True
390 @compiles(utcnow, "postgresql")
391 def pg_utcnow(element, compiler, **kw):
392 return "TIMEZONE('utc', CURRENT_TIMESTAMP)"
395 @compiles(utcnow, "mssql")
396 def ms_utcnow(element, compiler, **kw):
397 return "GETUTCDATE()"
399Example usage::
401 from sqlalchemy import Table, Column, Integer, String, DateTime, MetaData
403 metadata = MetaData()
404 event = Table(
405 "event",
406 metadata,
407 Column("id", Integer, primary_key=True),
408 Column("description", String(50), nullable=False),
409 Column("timestamp", DateTime, server_default=utcnow()),
410 )
412"GREATEST" function
413-------------------
415The "GREATEST" function is given any number of arguments and returns the one
416that is of the highest value - its equivalent to Python's ``max``
417function. A SQL standard version versus a CASE based version which only
418accommodates two arguments::
420 from sqlalchemy.sql import expression, case
421 from sqlalchemy.ext.compiler import compiles
422 from sqlalchemy.types import Numeric
425 class greatest(expression.FunctionElement):
426 type = Numeric()
427 name = "greatest"
428 inherit_cache = True
431 @compiles(greatest)
432 def default_greatest(element, compiler, **kw):
433 return compiler.visit_function(element)
436 @compiles(greatest, "sqlite")
437 @compiles(greatest, "mssql")
438 @compiles(greatest, "oracle")
439 def case_greatest(element, compiler, **kw):
440 arg1, arg2 = list(element.clauses)
441 return compiler.process(case((arg1 > arg2, arg1), else_=arg2), **kw)
443Example usage::
445 Session.query(Account).filter(
446 greatest(Account.checking_balance, Account.savings_balance) > 10000
447 )
449"false" expression
450------------------
452Render a "false" constant expression, rendering as "0" on platforms that
453don't have a "false" constant::
455 from sqlalchemy.sql import expression
456 from sqlalchemy.ext.compiler import compiles
459 class sql_false(expression.ColumnElement):
460 inherit_cache = True
463 @compiles(sql_false)
464 def default_false(element, compiler, **kw):
465 return "false"
468 @compiles(sql_false, "mssql")
469 @compiles(sql_false, "mysql")
470 @compiles(sql_false, "oracle")
471 def int_false(element, compiler, **kw):
472 return "0"
474Example usage::
476 from sqlalchemy import select, union_all
478 exp = union_all(
479 select(users.c.name, sql_false().label("enrolled")),
480 select(customers.c.name, customers.c.enrolled),
481 )
483"""
484from __future__ import annotations
486from typing import Any
487from typing import Callable
488from typing import Dict
489from typing import Type
490from typing import TYPE_CHECKING
491from typing import TypeVar
493from .. import exc
494from ..sql import sqltypes
496if TYPE_CHECKING:
497 from ..sql.compiler import SQLCompiler
499_F = TypeVar("_F", bound=Callable[..., Any])
502def compiles(class_: Type[Any], *specs: str) -> Callable[[_F], _F]:
503 """Register a function as a compiler for a
504 given :class:`_expression.ClauseElement` type."""
506 def decorate(fn: _F) -> _F:
507 # get an existing @compiles handler
508 existing = class_.__dict__.get("_compiler_dispatcher", None)
510 # get the original handler. All ClauseElement classes have one
511 # of these, but some TypeEngine classes will not.
512 existing_dispatch = getattr(class_, "_compiler_dispatch", None)
514 if not existing:
515 existing = _dispatcher()
517 if existing_dispatch:
519 def _wrap_existing_dispatch(
520 element: Any, compiler: SQLCompiler, **kw: Any
521 ) -> Any:
522 try:
523 return existing_dispatch(element, compiler, **kw)
524 except exc.UnsupportedCompilationError as uce:
525 raise exc.UnsupportedCompilationError(
526 compiler,
527 type(element),
528 message="%s construct has no default "
529 "compilation handler." % type(element),
530 ) from uce
532 existing.specs["default"] = _wrap_existing_dispatch
534 # TODO: why is the lambda needed ?
535 setattr(
536 class_,
537 "_compiler_dispatch",
538 lambda *arg, **kw: existing(*arg, **kw),
539 )
540 setattr(class_, "_compiler_dispatcher", existing)
542 if specs:
543 for s in specs:
544 existing.specs[s] = fn
546 else:
547 existing.specs["default"] = fn
548 return fn
550 return decorate
553def deregister(class_: Type[Any]) -> None:
554 """Remove all custom compilers associated with a given
555 :class:`_expression.ClauseElement` type.
557 """
559 if hasattr(class_, "_compiler_dispatcher"):
560 class_._compiler_dispatch = class_._original_compiler_dispatch
561 del class_._compiler_dispatcher
564class _dispatcher:
565 def __init__(self) -> None:
566 self.specs: Dict[str, Callable[..., Any]] = {}
568 def __call__(self, element: Any, compiler: SQLCompiler, **kw: Any) -> Any:
569 # TODO: yes, this could also switch off of DBAPI in use.
570 fn = self.specs.get(compiler.dialect.name, None)
571 if not fn:
572 try:
573 fn = self.specs["default"]
574 except KeyError as ke:
575 raise exc.UnsupportedCompilationError(
576 compiler,
577 type(element),
578 message="%s construct has no default "
579 "compilation handler." % type(element),
580 ) from ke
582 # if compilation includes add_to_result_map, collect add_to_result_map
583 # arguments from the user-defined callable, which are probably none
584 # because this is not public API. if it wasn't called, then call it
585 # ourselves.
586 arm = kw.get("add_to_result_map", None)
587 if arm:
588 arm_collection = []
589 kw["add_to_result_map"] = lambda *args: arm_collection.append(args)
591 expr = fn(element, compiler, **kw)
593 if arm:
594 if not arm_collection:
595 arm_collection.append(
596 (None, None, (element,), sqltypes.NULLTYPE)
597 )
598 for tup in arm_collection:
599 arm(*tup)
600 return expr