Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/compiler.py: 48%

1# ext/compiler.py

3# <see AUTHORS file>

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

20 class MyColumn(ColumnClause):

21 inherit_cache = True

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):

50 inherit_cache = False

52 def __init__(self, column, cmd):

53 self.column = column

54 self.cmd = cmd

56 @compiles(AlterColumn)

57 def visit_alter_column(element, compiler, **kw):

58 return "ALTER COLUMN %s ..." % element.column.name

60 @compiles(AlterColumn, 'postgresql')

61 def visit_alter_column(element, compiler, **kw):

62 return "ALTER TABLE %s ALTER COLUMN %s ..." % (element.table.name,

63 element.column.name)

65The second ``visit_alter_table`` will be invoked when any ``postgresql``

66dialect is used.

68.. _compilerext_compiling_subelements:

70Compiling sub-elements of a custom expression construct

71=======================================================

73The ``compiler`` argument is the

74:class:`~sqlalchemy.engine.interfaces.Compiled` object in use. This object

75can be inspected for any information about the in-progress compilation,

76including ``compiler.dialect``, ``compiler.statement`` etc. The

77:class:`~sqlalchemy.sql.compiler.SQLCompiler` and

78:class:`~sqlalchemy.sql.compiler.DDLCompiler` both include a ``process()``

79method which can be used for compilation of embedded attributes::

81 from sqlalchemy.sql.expression import Executable, ClauseElement

83 class InsertFromSelect(Executable, ClauseElement):

84 inherit_cache = False

86 def __init__(self, table, select):

87 self.table = table

88 self.select = select

90 @compiles(InsertFromSelect)

91 def visit_insert_from_select(element, compiler, **kw):

92 return "INSERT INTO %s (%s)" % (

93 compiler.process(element.table, asfrom=True, **kw),

94 compiler.process(element.select, **kw)

95 )

97 insert = InsertFromSelect(t1, select(t1).where(t1.c.x>5))

98 print(insert)

100Produces::

101

102 "INSERT INTO mytable (SELECT mytable.x, mytable.y, mytable.z

103 FROM mytable WHERE mytable.x > :x_1)"

104

105.. note::

106

107 The above ``InsertFromSelect`` construct is only an example, this actual

108 functionality is already available using the

109 :meth:`_expression.Insert.from_select` method.

110

111.. note::

112

113 The above ``InsertFromSelect`` construct probably wants to have "autocommit"

114 enabled. See :ref:`enabling_compiled_autocommit` for this step.

115

116Cross Compiling between SQL and DDL compilers

117---------------------------------------------

118

119SQL and DDL constructs are each compiled using different base compilers -

120``SQLCompiler`` and ``DDLCompiler``. A common need is to access the

121compilation rules of SQL expressions from within a DDL expression. The

122``DDLCompiler`` includes an accessor ``sql_compiler`` for this reason, such as

123below where we generate a CHECK constraint that embeds a SQL expression::

124

125 @compiles(MyConstraint)

126 def compile_my_constraint(constraint, ddlcompiler, **kw):

127 kw['literal_binds'] = True

128 return "CONSTRAINT %s CHECK (%s)" % (

129 constraint.name,

130 ddlcompiler.sql_compiler.process(

131 constraint.expression, **kw)

132 )

133

134Above, we add an additional flag to the process step as called by

135:meth:`.SQLCompiler.process`, which is the ``literal_binds`` flag. This

136indicates that any SQL expression which refers to a :class:`.BindParameter`

137object or other "literal" object such as those which refer to strings or

138integers should be rendered **in-place**, rather than being referred to as

139a bound parameter; when emitting DDL, bound parameters are typically not

140supported.

141

142

143.. _enabling_compiled_autocommit:

144

145Enabling Autocommit on a Construct

146==================================

147

148Recall from the section :ref:`autocommit` that the :class:`_engine.Engine`,

149when

150asked to execute a construct in the absence of a user-defined transaction,

151detects if the given construct represents DML or DDL, that is, a data

152modification or data definition statement, which requires (or may require,

153in the case of DDL) that the transaction generated by the DBAPI be committed

154(recall that DBAPI always has a transaction going on regardless of what

155SQLAlchemy does). Checking for this is actually accomplished by checking for

156the "autocommit" execution option on the construct. When building a

157construct like an INSERT derivation, a new DDL type, or perhaps a stored

158procedure that alters data, the "autocommit" option needs to be set in order

159for the statement to function with "connectionless" execution

160(as described in :ref:`dbengine_implicit`).

161

162Currently a quick way to do this is to subclass :class:`.Executable`, then

163add the "autocommit" flag to the ``_execution_options`` dictionary (note this

164is a "frozen" dictionary which supplies a generative ``union()`` method)::

165

166 from sqlalchemy.sql.expression import Executable, ClauseElement

167

168 class MyInsertThing(Executable, ClauseElement):

169 _execution_options = \

170 Executable._execution_options.union({'autocommit': True})

171

172More succinctly, if the construct is truly similar to an INSERT, UPDATE, or

173DELETE, :class:`.UpdateBase` can be used, which already is a subclass

174of :class:`.Executable`, :class:`_expression.ClauseElement` and includes the

175``autocommit`` flag::

176

177 from sqlalchemy.sql.expression import UpdateBase

178

179 class MyInsertThing(UpdateBase):

180 def __init__(self, ...):

181 ...

186DDL elements that subclass :class:`.DDLElement` already have the

187"autocommit" flag turned on.

192Changing the default compilation of existing constructs

193=======================================================

194

195The compiler extension applies just as well to the existing constructs. When

196overriding the compilation of a built in SQL construct, the @compiles

197decorator is invoked upon the appropriate class (be sure to use the class,

198i.e. ``Insert`` or ``Select``, instead of the creation function such

199as ``insert()`` or ``select()``).

200

201Within the new compilation function, to get at the "original" compilation

202routine, use the appropriate visit_XXX method - this

203because compiler.process() will call upon the overriding routine and cause

204an endless loop. Such as, to add "prefix" to all insert statements::

205

206 from sqlalchemy.sql.expression import Insert

207

208 @compiles(Insert)

209 def prefix_inserts(insert, compiler, **kw):

210 return compiler.visit_insert(insert.prefix_with("some prefix"), **kw)

211

212The above compiler will prefix all INSERT statements with "some prefix" when

213compiled.

214

215.. _type_compilation_extension:

216

217Changing Compilation of Types

218=============================

219

220``compiler`` works for types, too, such as below where we implement the

221MS-SQL specific 'max' keyword for ``String``/``VARCHAR``::

222

223 @compiles(String, 'mssql')

224 @compiles(VARCHAR, 'mssql')

225 def compile_varchar(element, compiler, **kw):

226 if element.length == 'max':

227 return "VARCHAR('max')"

228 else:

229 return compiler.visit_VARCHAR(element, **kw)

230

231 foo = Table('foo', metadata,

232 Column('data', VARCHAR('max'))

233 )

234

235Subclassing Guidelines

236======================

237

238A big part of using the compiler extension is subclassing SQLAlchemy

239expression constructs. To make this easier, the expression and

240schema packages feature a set of "bases" intended for common tasks.

241A synopsis is as follows:

242

243* :class:`~sqlalchemy.sql.expression.ClauseElement` - This is the root

244 expression class. Any SQL expression can be derived from this base, and is

245 probably the best choice for longer constructs such as specialized INSERT

246 statements.

247

248* :class:`~sqlalchemy.sql.expression.ColumnElement` - The root of all

249 "column-like" elements. Anything that you'd place in the "columns" clause of

250 a SELECT statement (as well as order by and group by) can derive from this -

251 the object will automatically have Python "comparison" behavior.

252

253 :class:`~sqlalchemy.sql.expression.ColumnElement` classes want to have a

254 ``type`` member which is expression's return type. This can be established

255 at the instance level in the constructor, or at the class level if its

256 generally constant::

257

258 class timestamp(ColumnElement):

259 type = TIMESTAMP()

260 inherit_cache = True

261

262* :class:`~sqlalchemy.sql.functions.FunctionElement` - This is a hybrid of a

263 ``ColumnElement`` and a "from clause" like object, and represents a SQL

264 function or stored procedure type of call. Since most databases support

265 statements along the line of "SELECT FROM <some function>"

266 ``FunctionElement`` adds in the ability to be used in the FROM clause of a

267 ``select()`` construct::

268

269 from sqlalchemy.sql.expression import FunctionElement

270

271 class coalesce(FunctionElement):

272 name = 'coalesce'

273 inherit_cache = True

274

275 @compiles(coalesce)

276 def compile(element, compiler, **kw):

277 return "coalesce(%s)" % compiler.process(element.clauses, **kw)

278

279 @compiles(coalesce, 'oracle')

280 def compile(element, compiler, **kw):

281 if len(element.clauses) > 2:

282 raise TypeError("coalesce only supports two arguments on Oracle")

283 return "nvl(%s)" % compiler.process(element.clauses, **kw)

284

285* :class:`.DDLElement` - The root of all DDL expressions,

286 like CREATE TABLE, ALTER TABLE, etc. Compilation of :class:`.DDLElement`

287 subclasses is issued by a :class:`.DDLCompiler` instead of a

288 :class:`.SQLCompiler`. :class:`.DDLElement` can also be used as an event hook

289 in conjunction with event hooks like :meth:`.DDLEvents.before_create` and

290 :meth:`.DDLEvents.after_create`, allowing the construct to be invoked

291 automatically during CREATE TABLE and DROP TABLE sequences.

292

293 .. seealso::

294

295 :ref:`metadata_ddl_toplevel` - contains examples of associating

296 :class:`.DDL` objects (which are themselves :class:`.DDLElement`

297 instances) with :class:`.DDLEvents` event hooks.

298

299* :class:`~sqlalchemy.sql.expression.Executable` - This is a mixin which

300 should be used with any expression class that represents a "standalone"

301 SQL statement that can be passed directly to an ``execute()`` method. It

302 is already implicit within ``DDLElement`` and ``FunctionElement``.

303

304Most of the above constructs also respond to SQL statement caching. A

305subclassed construct will want to define the caching behavior for the object,

306which usually means setting the flag ``inherit_cache`` to the value of

307``False`` or ``True``. See the next section :ref:`compilerext_caching`

308for background.

309

310

311.. _compilerext_caching:

312

313Enabling Caching Support for Custom Constructs

314==============================================

315

316SQLAlchemy as of version 1.4 includes a

317:ref:`SQL compilation caching facility <sql_caching>` which will allow

318equivalent SQL constructs to cache their stringified form, along with other

319structural information used to fetch results from the statement.

320

321For reasons discussed at :ref:`caching_caveats`, the implementation of this

322caching system takes a conservative approach towards including custom SQL

323constructs and/or subclasses within the caching system. This includes that

324any user-defined SQL constructs, including all the examples for this

325extension, will not participate in caching by default unless they positively

326assert that they are able to do so. The :attr:`.HasCacheKey.inherit_cache`

327attribute when set to ``True`` at the class level of a specific subclass

328will indicate that instances of this class may be safely cached, using the

329cache key generation scheme of the immediate superclass. This applies

330for example to the "synopsis" example indicated previously::

331

332 class MyColumn(ColumnClause):

333 inherit_cache = True

334

335 @compiles(MyColumn)

336 def compile_mycolumn(element, compiler, **kw):

337 return "[%s]" % element.name

338

339Above, the ``MyColumn`` class does not include any new state that

340affects its SQL compilation; the cache key of ``MyColumn`` instances will

341make use of that of the ``ColumnClause`` superclass, meaning it will take

342into account the class of the object (``MyColumn``), the string name and

343datatype of the object::

344

345 >>> MyColumn("some_name", String())._generate_cache_key()

346 CacheKey(

347 key=('0', <class '__main__.MyColumn'>,

348 'name', 'some_name',

349 'type', (<class 'sqlalchemy.sql.sqltypes.String'>,

350 ('length', None), ('collation', None))

351 ), bindparams=[])

352

353For objects that are likely to be **used liberally as components within many

354larger statements**, such as :class:`_schema.Column` subclasses and custom SQL

355datatypes, it's important that **caching be enabled as much as possible**, as

356this may otherwise negatively affect performance.

357

358An example of an object that **does** contain state which affects its SQL

359compilation is the one illustrated at :ref:`compilerext_compiling_subelements`;

360this is an "INSERT FROM SELECT" construct that combines together a

361:class:`_schema.Table` as well as a :class:`_sql.Select` construct, each of

362which independently affect the SQL string generation of the construct. For

363this class, the example illustrates that it simply does not participate in

364caching::

365

366 class InsertFromSelect(Executable, ClauseElement):

367 inherit_cache = False

368

369 def __init__(self, table, select):

370 self.table = table

371 self.select = select

372

373 @compiles(InsertFromSelect)

374 def visit_insert_from_select(element, compiler, **kw):

375 return "INSERT INTO %s (%s)" % (

376 compiler.process(element.table, asfrom=True, **kw),

377 compiler.process(element.select, **kw)

378 )

379

380While it is also possible that the above ``InsertFromSelect`` could be made to

381produce a cache key that is composed of that of the :class:`_schema.Table` and

382:class:`_sql.Select` components together, the API for this is not at the moment

383fully public. However, for an "INSERT FROM SELECT" construct, which is only

384used by itself for specific operations, caching is not as critical as in the

385previous example.

386

387For objects that are **used in relative isolation and are generally

388standalone**, such as custom :term:`DML` constructs like an "INSERT FROM

389SELECT", **caching is generally less critical** as the lack of caching for such

390a construct will have only localized implications for that specific operation.

391

392

393Further Examples

394================

395

396"UTC timestamp" function

397-------------------------

398

399A function that works like "CURRENT_TIMESTAMP" except applies the

400appropriate conversions so that the time is in UTC time. Timestamps are best

401stored in relational databases as UTC, without time zones. UTC so that your

402database doesn't think time has gone backwards in the hour when daylight

403savings ends, without timezones because timezones are like character

404encodings - they're best applied only at the endpoints of an application

405(i.e. convert to UTC upon user input, re-apply desired timezone upon display).

406

407For PostgreSQL and Microsoft SQL Server::

408

409 from sqlalchemy.sql import expression

410 from sqlalchemy.ext.compiler import compiles

411 from sqlalchemy.types import DateTime

412

413 class utcnow(expression.FunctionElement):

414 type = DateTime()

415 inherit_cache = True

416

417 @compiles(utcnow, 'postgresql')

418 def pg_utcnow(element, compiler, **kw):

419 return "TIMEZONE('utc', CURRENT_TIMESTAMP)"

420

421 @compiles(utcnow, 'mssql')

422 def ms_utcnow(element, compiler, **kw):

423 return "GETUTCDATE()"

424

425Example usage::

426

427 from sqlalchemy import (

428 Table, Column, Integer, String, DateTime, MetaData

429 )

430 metadata = MetaData()

431 event = Table("event", metadata,

432 Column("id", Integer, primary_key=True),

433 Column("description", String(50), nullable=False),

434 Column("timestamp", DateTime, server_default=utcnow())

435 )

436

437"GREATEST" function

438-------------------

439

440The "GREATEST" function is given any number of arguments and returns the one

441that is of the highest value - its equivalent to Python's ``max``

442function. A SQL standard version versus a CASE based version which only

443accommodates two arguments::

444

445 from sqlalchemy.sql import expression, case

446 from sqlalchemy.ext.compiler import compiles

447 from sqlalchemy.types import Numeric

448

449 class greatest(expression.FunctionElement):

450 type = Numeric()

451 name = 'greatest'

452 inherit_cache = True

453

454 @compiles(greatest)

455 def default_greatest(element, compiler, **kw):

456 return compiler.visit_function(element)

457

458 @compiles(greatest, 'sqlite')

459 @compiles(greatest, 'mssql')

460 @compiles(greatest, 'oracle')

461 def case_greatest(element, compiler, **kw):

462 arg1, arg2 = list(element.clauses)

463 return compiler.process(case([(arg1 > arg2, arg1)], else_=arg2), **kw)

464

465Example usage::

466

467 Session.query(Account).\

468 filter(

469 greatest(

470 Account.checking_balance,

471 Account.savings_balance) > 10000

472 )

473

474"false" expression

475------------------

476

477Render a "false" constant expression, rendering as "0" on platforms that

478don't have a "false" constant::

479

480 from sqlalchemy.sql import expression

481 from sqlalchemy.ext.compiler import compiles

482

483 class sql_false(expression.ColumnElement):

484 inherit_cache = True

485

486 @compiles(sql_false)

487 def default_false(element, compiler, **kw):

488 return "false"

489

490 @compiles(sql_false, 'mssql')

491 @compiles(sql_false, 'mysql')

492 @compiles(sql_false, 'oracle')

493 def int_false(element, compiler, **kw):

494 return "0"

495

496Example usage::

497

498 from sqlalchemy import select, union_all

499

500 exp = union_all(

501 select(users.c.name, sql_false().label("enrolled")),

502 select(customers.c.name, customers.c.enrolled)

503 )

504

505"""

506from .. import exc

507from .. import util

508from ..sql import sqltypes

509

510

511def compiles(class_, *specs):

512 """Register a function as a compiler for a

513 given :class:`_expression.ClauseElement` type."""

514

515 def decorate(fn):

516 # get an existing @compiles handler

517 existing = class_.__dict__.get("_compiler_dispatcher", None)

518

519 # get the original handler. All ClauseElement classes have one

520 # of these, but some TypeEngine classes will not.

521 existing_dispatch = getattr(class_, "_compiler_dispatch", None)

522

523 if not existing:

524 existing = _dispatcher()

525

526 if existing_dispatch:

527

528 def _wrap_existing_dispatch(element, compiler, **kw):

529 try:

530 return existing_dispatch(element, compiler, **kw)

531 except exc.UnsupportedCompilationError as uce:

532 util.raise_(

533 exc.UnsupportedCompilationError(

534 compiler,

535 type(element),

536 message="%s construct has no default "

537 "compilation handler." % type(element),

538 ),

539 from_=uce,

540 )

541

542 existing.specs["default"] = _wrap_existing_dispatch

543

544 # TODO: why is the lambda needed ?

545 setattr(

546 class_,

547 "_compiler_dispatch",

548 lambda *arg, **kw: existing(*arg, **kw),

549 )

550 setattr(class_, "_compiler_dispatcher", existing)

551

552 if specs:

553 for s in specs:

554 existing.specs[s] = fn

555

556 else:

557 existing.specs["default"] = fn

558 return fn

559

560 return decorate

561

562

563def deregister(class_):

564 """Remove all custom compilers associated with a given

565 :class:`_expression.ClauseElement` type.

566

567 """

568

569 if hasattr(class_, "_compiler_dispatcher"):

570 class_._compiler_dispatch = class_._original_compiler_dispatch

571 del class_._compiler_dispatcher

572

573

574class _dispatcher(object):

575 def __init__(self):

576 self.specs = {}

577

578 def __call__(self, element, compiler, **kw):

579 # TODO: yes, this could also switch off of DBAPI in use.

580 fn = self.specs.get(compiler.dialect.name, None)

581 if not fn:

582 try:

583 fn = self.specs["default"]

584 except KeyError as ke:

585 util.raise_(

586 exc.UnsupportedCompilationError(

587 compiler,

588 type(element),

589 message="%s construct has no default "

590 "compilation handler." % type(element),

591 ),

592 replace_context=ke,

593 )

594

595 # if compilation includes add_to_result_map, collect add_to_result_map

596 # arguments from the user-defined callable, which are probably none

597 # because this is not public API. if it wasn't called, then call it

598 # ourselves.

599 arm = kw.get("add_to_result_map", None)

600 if arm:

601 arm_collection = []

602 kw["add_to_result_map"] = lambda *args: arm_collection.append(args)

603

604 expr = fn(element, compiler, **kw)

605

606 if arm:

607 if not arm_collection:

608 arm_collection.append(

609 (None, None, (element,), sqltypes.NULLTYPE)

610 )

611 for tup in arm_collection:

612 arm(*tup)

613 return expr