Hide keyboard shortcuts

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 

7 

8r"""Provides an API for creation of custom ClauseElements and compilers. 

9 

10Synopsis 

11======== 

12 

13Usage involves the creation of one or more 

14:class:`~sqlalchemy.sql.expression.ClauseElement` subclasses and one or 

15more callables defining its compilation:: 

16 

17 from sqlalchemy.ext.compiler import compiles 

18 from sqlalchemy.sql.expression import ColumnClause 

19 

20 class MyColumn(ColumnClause): 

21 pass 

22 

23 @compiles(MyColumn) 

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

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

26 

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

31 

32 from sqlalchemy import select 

33 

34 s = select([MyColumn('x'), MyColumn('y')]) 

35 print(str(s)) 

36 

37Produces:: 

38 

39 SELECT [x], [y] 

40 

41Dialect-specific compilation rules 

42================================== 

43 

44Compilers can also be made dialect-specific. The appropriate compiler will be 

45invoked for the dialect in use:: 

46 

47 from sqlalchemy.schema import DDLElement 

48 

49 class AlterColumn(DDLElement): 

50 

51 def __init__(self, column, cmd): 

52 self.column = column 

53 self.cmd = cmd 

54 

55 @compiles(AlterColumn) 

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

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

58 

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) 

63 

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

65dialect is used. 

66 

67Compiling sub-elements of a custom expression construct 

68======================================================= 

69 

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

77 

78 from sqlalchemy.sql.expression import Executable, ClauseElement 

79 

80 class InsertFromSelect(Executable, ClauseElement): 

81 def __init__(self, table, select): 

82 self.table = table 

83 self.select = select 

84 

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 ) 

91 

92 insert = InsertFromSelect(t1, select([t1]).where(t1.c.x>5)) 

93 print(insert) 

94 

95Produces:: 

96 

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

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

99 

100.. note:: 

101 

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. 

105 

106.. note:: 

107 

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

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

110 

111Cross Compiling between SQL and DDL compilers 

112--------------------------------------------- 

113 

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

119 

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 ) 

128 

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. 

136 

137 

138.. _enabling_compiled_autocommit: 

139 

140Enabling Autocommit on a Construct 

141================================== 

142 

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`). 

156 

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

160 

161 from sqlalchemy.sql.expression import Executable, ClauseElement 

162 

163 class MyInsertThing(Executable, ClauseElement): 

164 _execution_options = \ 

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

166 

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

171 

172 from sqlalchemy.sql.expression import UpdateBase 

173 

174 class MyInsertThing(UpdateBase): 

175 def __init__(self, ...): 

176 ... 

177 

178 

179 

180 

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

182"autocommit" flag turned on. 

183 

184 

185 

186 

187Changing the default compilation of existing constructs 

188======================================================= 

189 

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()``). 

195 

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

200 

201 from sqlalchemy.sql.expression import Insert 

202 

203 @compiles(Insert) 

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

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

206 

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

208compiled. 

209 

210.. _type_compilation_extension: 

211 

212Changing Compilation of Types 

213============================= 

214 

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

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

217 

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) 

225 

226 foo = Table('foo', metadata, 

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

228 ) 

229 

230Subclassing Guidelines 

231====================== 

232 

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: 

237 

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. 

242 

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. 

247 

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

252 

253 class timestamp(ColumnElement): 

254 type = TIMESTAMP() 

255 

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

262 

263 from sqlalchemy.sql.expression import FunctionElement 

264 

265 class coalesce(FunctionElement): 

266 name = 'coalesce' 

267 

268 @compiles(coalesce) 

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

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

271 

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) 

277 

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. 

284 

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``. 

289 

290Further Examples 

291================ 

292 

293"UTC timestamp" function 

294------------------------- 

295 

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

303 

304For PostgreSQL and Microsoft SQL Server:: 

305 

306 from sqlalchemy.sql import expression 

307 from sqlalchemy.ext.compiler import compiles 

308 from sqlalchemy.types import DateTime 

309 

310 class utcnow(expression.FunctionElement): 

311 type = DateTime() 

312 

313 @compiles(utcnow, 'postgresql') 

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

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

316 

317 @compiles(utcnow, 'mssql') 

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

319 return "GETUTCDATE()" 

320 

321Example usage:: 

322 

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 ) 

332 

333"GREATEST" function 

334------------------- 

335 

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

340 

341 from sqlalchemy.sql import expression, case 

342 from sqlalchemy.ext.compiler import compiles 

343 from sqlalchemy.types import Numeric 

344 

345 class greatest(expression.FunctionElement): 

346 type = Numeric() 

347 name = 'greatest' 

348 

349 @compiles(greatest) 

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

351 return compiler.visit_function(element) 

352 

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) 

359 

360Example usage:: 

361 

362 Session.query(Account).\ 

363 filter( 

364 greatest( 

365 Account.checking_balance, 

366 Account.savings_balance) > 10000 

367 ) 

368 

369"false" expression 

370------------------ 

371 

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

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

374 

375 from sqlalchemy.sql import expression 

376 from sqlalchemy.ext.compiler import compiles 

377 

378 class sql_false(expression.ColumnElement): 

379 pass 

380 

381 @compiles(sql_false) 

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

383 return "false" 

384 

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" 

390 

391Example usage:: 

392 

393 from sqlalchemy import select, union_all 

394 

395 exp = union_all( 

396 select([users.c.name, sql_false().label("enrolled")]), 

397 select([customers.c.name, customers.c.enrolled]) 

398 ) 

399 

400""" 

401from .. import exc 

402from .. import util 

403from ..sql import visitors 

404 

405 

406def compiles(class_, *specs): 

407 """Register a function as a compiler for a 

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

409 

410 def decorate(fn): 

411 # get an existing @compiles handler 

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

413 

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) 

417 

418 if not existing: 

419 existing = _dispatcher() 

420 

421 if existing_dispatch: 

422 

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 ) 

434 

435 existing.specs["default"] = _wrap_existing_dispatch 

436 

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) 

444 

445 if specs: 

446 for s in specs: 

447 existing.specs[s] = fn 

448 

449 else: 

450 existing.specs["default"] = fn 

451 return fn 

452 

453 return decorate 

454 

455 

456def deregister(class_): 

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

458 :class:`_expression.ClauseElement` type.""" 

459 

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 

465 

466 

467class _dispatcher(object): 

468 def __init__(self): 

469 self.specs = {} 

470 

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 ) 

485 

486 return fn(element, compiler, **kw)