Coverage for /home/martinb/.local/share/virtualenvs/camcops/lib/python3.6/site-packages/sqlalchemy/ext/compiler.py : 65%

Hot-keys on this page
r m x p 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-2020 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: http://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
20 class MyColumn(ColumnClause):
21 pass
23 @compiles(MyColumn)
24 def compile_mycolumn(element, compiler, **kw):
25 return "[%s]" % element.name
27Above, ``MyColumn`` extends :class:`~sqlalchemy.sql.expression.ColumnClause`,
28the base expression element for named column objects. The ``compiles``
29decorator registers itself with the ``MyColumn`` class so that it is invoked
30when the object is compiled to a string::
32 from sqlalchemy import select
34 s = select([MyColumn('x'), MyColumn('y')])
35 print(str(s))
37Produces::
39 SELECT [x], [y]
41Dialect-specific compilation rules
42==================================
44Compilers can also be made dialect-specific. The appropriate compiler will be
45invoked for the dialect in use::
47 from sqlalchemy.schema import DDLElement
49 class AlterColumn(DDLElement):
51 def __init__(self, column, cmd):
52 self.column = column
53 self.cmd = cmd
55 @compiles(AlterColumn)
56 def visit_alter_column(element, compiler, **kw):
57 return "ALTER COLUMN %s ..." % element.column.name
59 @compiles(AlterColumn, 'postgresql')
60 def visit_alter_column(element, compiler, **kw):
61 return "ALTER TABLE %s ALTER COLUMN %s ..." % (element.table.name,
62 element.column.name)
64The second ``visit_alter_table`` will be invoked when any ``postgresql``
65dialect is used.
67Compiling sub-elements of a custom expression construct
68=======================================================
70The ``compiler`` argument is the
71:class:`~sqlalchemy.engine.interfaces.Compiled` object in use. This object
72can be inspected for any information about the in-progress compilation,
73including ``compiler.dialect``, ``compiler.statement`` etc. The
74:class:`~sqlalchemy.sql.compiler.SQLCompiler` and
75:class:`~sqlalchemy.sql.compiler.DDLCompiler` both include a ``process()``
76method which can be used for compilation of embedded attributes::
78 from sqlalchemy.sql.expression import Executable, ClauseElement
80 class InsertFromSelect(Executable, ClauseElement):
81 def __init__(self, table, select):
82 self.table = table
83 self.select = select
85 @compiles(InsertFromSelect)
86 def visit_insert_from_select(element, compiler, **kw):
87 return "INSERT INTO %s (%s)" % (
88 compiler.process(element.table, asfrom=True, **kw),
89 compiler.process(element.select, **kw)
90 )
92 insert = InsertFromSelect(t1, select([t1]).where(t1.c.x>5))
93 print(insert)
95Produces::
97 "INSERT INTO mytable (SELECT mytable.x, mytable.y, mytable.z
98 FROM mytable WHERE mytable.x > :x_1)"
100.. note::
102 The above ``InsertFromSelect`` construct is only an example, this actual
103 functionality is already available using the
104 :meth:`_expression.Insert.from_select` method.
106.. note::
108 The above ``InsertFromSelect`` construct probably wants to have "autocommit"
109 enabled. See :ref:`enabling_compiled_autocommit` for this step.
111Cross Compiling between SQL and DDL compilers
112---------------------------------------------
114SQL and DDL constructs are each compiled using different base compilers -
115``SQLCompiler`` and ``DDLCompiler``. A common need is to access the
116compilation rules of SQL expressions from within a DDL expression. The
117``DDLCompiler`` includes an accessor ``sql_compiler`` for this reason, such as
118below where we generate a CHECK constraint that embeds a SQL expression::
120 @compiles(MyConstraint)
121 def compile_my_constraint(constraint, ddlcompiler, **kw):
122 kw['literal_binds'] = True
123 return "CONSTRAINT %s CHECK (%s)" % (
124 constraint.name,
125 ddlcompiler.sql_compiler.process(
126 constraint.expression, **kw)
127 )
129Above, we add an additional flag to the process step as called by
130:meth:`.SQLCompiler.process`, which is the ``literal_binds`` flag. This
131indicates that any SQL expression which refers to a :class:`.BindParameter`
132object or other "literal" object such as those which refer to strings or
133integers should be rendered **in-place**, rather than being referred to as
134a bound parameter; when emitting DDL, bound parameters are typically not
135supported.
138.. _enabling_compiled_autocommit:
140Enabling Autocommit on a Construct
141==================================
143Recall from the section :ref:`autocommit` that the :class:`_engine.Engine`,
144when
145asked to execute a construct in the absence of a user-defined transaction,
146detects if the given construct represents DML or DDL, that is, a data
147modification or data definition statement, which requires (or may require,
148in the case of DDL) that the transaction generated by the DBAPI be committed
149(recall that DBAPI always has a transaction going on regardless of what
150SQLAlchemy does). Checking for this is actually accomplished by checking for
151the "autocommit" execution option on the construct. When building a
152construct like an INSERT derivation, a new DDL type, or perhaps a stored
153procedure that alters data, the "autocommit" option needs to be set in order
154for the statement to function with "connectionless" execution
155(as described in :ref:`dbengine_implicit`).
157Currently a quick way to do this is to subclass :class:`.Executable`, then
158add the "autocommit" flag to the ``_execution_options`` dictionary (note this
159is a "frozen" dictionary which supplies a generative ``union()`` method)::
161 from sqlalchemy.sql.expression import Executable, ClauseElement
163 class MyInsertThing(Executable, ClauseElement):
164 _execution_options = \
165 Executable._execution_options.union({'autocommit': True})
167More succinctly, if the construct is truly similar to an INSERT, UPDATE, or
168DELETE, :class:`.UpdateBase` can be used, which already is a subclass
169of :class:`.Executable`, :class:`_expression.ClauseElement` and includes the
170``autocommit`` flag::
172 from sqlalchemy.sql.expression import UpdateBase
174 class MyInsertThing(UpdateBase):
175 def __init__(self, ...):
176 ...
181DDL elements that subclass :class:`.DDLElement` already have the
182"autocommit" flag turned on.
187Changing the default compilation of existing constructs
188=======================================================
190The compiler extension applies just as well to the existing constructs. When
191overriding the compilation of a built in SQL construct, the @compiles
192decorator is invoked upon the appropriate class (be sure to use the class,
193i.e. ``Insert`` or ``Select``, instead of the creation function such
194as ``insert()`` or ``select()``).
196Within the new compilation function, to get at the "original" compilation
197routine, use the appropriate visit_XXX method - this
198because compiler.process() will call upon the overriding routine and cause
199an endless loop. Such as, to add "prefix" to all insert statements::
201 from sqlalchemy.sql.expression import Insert
203 @compiles(Insert)
204 def prefix_inserts(insert, compiler, **kw):
205 return compiler.visit_insert(insert.prefix_with("some prefix"), **kw)
207The above compiler will prefix all INSERT statements with "some prefix" when
208compiled.
210.. _type_compilation_extension:
212Changing Compilation of Types
213=============================
215``compiler`` works for types, too, such as below where we implement the
216MS-SQL specific 'max' keyword for ``String``/``VARCHAR``::
218 @compiles(String, 'mssql')
219 @compiles(VARCHAR, 'mssql')
220 def compile_varchar(element, compiler, **kw):
221 if element.length == 'max':
222 return "VARCHAR('max')"
223 else:
224 return compiler.visit_VARCHAR(element, **kw)
226 foo = Table('foo', metadata,
227 Column('data', VARCHAR('max'))
228 )
230Subclassing Guidelines
231======================
233A big part of using the compiler extension is subclassing SQLAlchemy
234expression constructs. To make this easier, the expression and
235schema packages feature a set of "bases" intended for common tasks.
236A synopsis is as follows:
238* :class:`~sqlalchemy.sql.expression.ClauseElement` - This is the root
239 expression class. Any SQL expression can be derived from this base, and is
240 probably the best choice for longer constructs such as specialized INSERT
241 statements.
243* :class:`~sqlalchemy.sql.expression.ColumnElement` - The root of all
244 "column-like" elements. Anything that you'd place in the "columns" clause of
245 a SELECT statement (as well as order by and group by) can derive from this -
246 the object will automatically have Python "comparison" behavior.
248 :class:`~sqlalchemy.sql.expression.ColumnElement` classes want to have a
249 ``type`` member which is expression's return type. This can be established
250 at the instance level in the constructor, or at the class level if its
251 generally constant::
253 class timestamp(ColumnElement):
254 type = TIMESTAMP()
256* :class:`~sqlalchemy.sql.functions.FunctionElement` - This is a hybrid of a
257 ``ColumnElement`` and a "from clause" like object, and represents a SQL
258 function or stored procedure type of call. Since most databases support
259 statements along the line of "SELECT FROM <some function>"
260 ``FunctionElement`` adds in the ability to be used in the FROM clause of a
261 ``select()`` construct::
263 from sqlalchemy.sql.expression import FunctionElement
265 class coalesce(FunctionElement):
266 name = 'coalesce'
268 @compiles(coalesce)
269 def compile(element, compiler, **kw):
270 return "coalesce(%s)" % compiler.process(element.clauses, **kw)
272 @compiles(coalesce, 'oracle')
273 def compile(element, compiler, **kw):
274 if len(element.clauses) > 2:
275 raise TypeError("coalesce only supports two arguments on Oracle")
276 return "nvl(%s)" % compiler.process(element.clauses, **kw)
278* :class:`~sqlalchemy.schema.DDLElement` - The root of all DDL expressions,
279 like CREATE TABLE, ALTER TABLE, etc. Compilation of ``DDLElement``
280 subclasses is issued by a ``DDLCompiler`` instead of a ``SQLCompiler``.
281 ``DDLElement`` also features ``Table`` and ``MetaData`` event hooks via the
282 ``execute_at()`` method, allowing the construct to be invoked during CREATE
283 TABLE and DROP TABLE sequences.
285* :class:`~sqlalchemy.sql.expression.Executable` - This is a mixin which
286 should be used with any expression class that represents a "standalone"
287 SQL statement that can be passed directly to an ``execute()`` method. It
288 is already implicit within ``DDLElement`` and ``FunctionElement``.
290Further Examples
291================
293"UTC timestamp" function
294-------------------------
296A function that works like "CURRENT_TIMESTAMP" except applies the
297appropriate conversions so that the time is in UTC time. Timestamps are best
298stored in relational databases as UTC, without time zones. UTC so that your
299database doesn't think time has gone backwards in the hour when daylight
300savings ends, without timezones because timezones are like character
301encodings - they're best applied only at the endpoints of an application
302(i.e. convert to UTC upon user input, re-apply desired timezone upon display).
304For PostgreSQL and Microsoft SQL Server::
306 from sqlalchemy.sql import expression
307 from sqlalchemy.ext.compiler import compiles
308 from sqlalchemy.types import DateTime
310 class utcnow(expression.FunctionElement):
311 type = DateTime()
313 @compiles(utcnow, 'postgresql')
314 def pg_utcnow(element, compiler, **kw):
315 return "TIMEZONE('utc', CURRENT_TIMESTAMP)"
317 @compiles(utcnow, 'mssql')
318 def ms_utcnow(element, compiler, **kw):
319 return "GETUTCDATE()"
321Example usage::
323 from sqlalchemy import (
324 Table, Column, Integer, String, DateTime, MetaData
325 )
326 metadata = MetaData()
327 event = Table("event", metadata,
328 Column("id", Integer, primary_key=True),
329 Column("description", String(50), nullable=False),
330 Column("timestamp", DateTime, server_default=utcnow())
331 )
333"GREATEST" function
334-------------------
336The "GREATEST" function is given any number of arguments and returns the one
337that is of the highest value - its equivalent to Python's ``max``
338function. A SQL standard version versus a CASE based version which only
339accommodates two arguments::
341 from sqlalchemy.sql import expression, case
342 from sqlalchemy.ext.compiler import compiles
343 from sqlalchemy.types import Numeric
345 class greatest(expression.FunctionElement):
346 type = Numeric()
347 name = 'greatest'
349 @compiles(greatest)
350 def default_greatest(element, compiler, **kw):
351 return compiler.visit_function(element)
353 @compiles(greatest, 'sqlite')
354 @compiles(greatest, 'mssql')
355 @compiles(greatest, 'oracle')
356 def case_greatest(element, compiler, **kw):
357 arg1, arg2 = list(element.clauses)
358 return compiler.process(case([(arg1 > arg2, arg1)], else_=arg2), **kw)
360Example usage::
362 Session.query(Account).\
363 filter(
364 greatest(
365 Account.checking_balance,
366 Account.savings_balance) > 10000
367 )
369"false" expression
370------------------
372Render a "false" constant expression, rendering as "0" on platforms that
373don't have a "false" constant::
375 from sqlalchemy.sql import expression
376 from sqlalchemy.ext.compiler import compiles
378 class sql_false(expression.ColumnElement):
379 pass
381 @compiles(sql_false)
382 def default_false(element, compiler, **kw):
383 return "false"
385 @compiles(sql_false, 'mssql')
386 @compiles(sql_false, 'mysql')
387 @compiles(sql_false, 'oracle')
388 def int_false(element, compiler, **kw):
389 return "0"
391Example usage::
393 from sqlalchemy import select, union_all
395 exp = union_all(
396 select([users.c.name, sql_false().label("enrolled")]),
397 select([customers.c.name, customers.c.enrolled])
398 )
400"""
401from .. import exc
402from .. import util
403from ..sql import visitors
406def compiles(class_, *specs):
407 """Register a function as a compiler for a
408 given :class:`_expression.ClauseElement` type."""
410 def decorate(fn):
411 # get an existing @compiles handler
412 existing = class_.__dict__.get("_compiler_dispatcher", None)
414 # get the original handler. All ClauseElement classes have one
415 # of these, but some TypeEngine classes will not.
416 existing_dispatch = getattr(class_, "_compiler_dispatch", None)
418 if not existing:
419 existing = _dispatcher()
421 if existing_dispatch:
423 def _wrap_existing_dispatch(element, compiler, **kw):
424 try:
425 return existing_dispatch(element, compiler, **kw)
426 except exc.UnsupportedCompilationError as uce:
427 util.raise_(
428 exc.CompileError(
429 "%s construct has no default "
430 "compilation handler." % type(element)
431 ),
432 from_=uce,
433 )
435 existing.specs["default"] = _wrap_existing_dispatch
437 # TODO: why is the lambda needed ?
438 setattr(
439 class_,
440 "_compiler_dispatch",
441 lambda *arg, **kw: existing(*arg, **kw),
442 )
443 setattr(class_, "_compiler_dispatcher", existing)
445 if specs:
446 for s in specs:
447 existing.specs[s] = fn
449 else:
450 existing.specs["default"] = fn
451 return fn
453 return decorate
456def deregister(class_):
457 """Remove all custom compilers associated with a given
458 :class:`_expression.ClauseElement` type."""
460 if hasattr(class_, "_compiler_dispatcher"):
461 # regenerate default _compiler_dispatch
462 visitors._generate_dispatch(class_)
463 # remove custom directive
464 del class_._compiler_dispatcher
467class _dispatcher(object):
468 def __init__(self):
469 self.specs = {}
471 def __call__(self, element, compiler, **kw):
472 # TODO: yes, this could also switch off of DBAPI in use.
473 fn = self.specs.get(compiler.dialect.name, None)
474 if not fn:
475 try:
476 fn = self.specs["default"]
477 except KeyError as ke:
478 util.raise_(
479 exc.CompileError(
480 "%s construct has no default "
481 "compilation handler." % type(element)
482 ),
483 replace_context=ke,
484 )
486 return fn(element, compiler, **kw)