1# sql/compiler.py 2# Copyright (C) 2005-2021 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 7 8"""Base SQL and DDL compiler implementations. 9 10Classes provided include: 11 12:class:`.compiler.SQLCompiler` - renders SQL 13strings 14 15:class:`.compiler.DDLCompiler` - renders DDL 16(data definition language) strings 17 18:class:`.compiler.GenericTypeCompiler` - renders 19type specification strings. 20 21To generate user-defined SQL strings, see 22:doc:`/ext/compiler`. 23 24""" 25 26import collections 27import contextlib 28import itertools 29import operator 30import re 31 32from . import base 33from . import coercions 34from . import crud 35from . import elements 36from . import functions 37from . import operators 38from . import schema 39from . import selectable 40from . import sqltypes 41from .base import NO_ARG 42from .base import prefix_anon_map 43from .elements import quoted_name 44from .. import exc 45from .. import util 46 47RESERVED_WORDS = set( 48 [ 49 "all", 50 "analyse", 51 "analyze", 52 "and", 53 "any", 54 "array", 55 "as", 56 "asc", 57 "asymmetric", 58 "authorization", 59 "between", 60 "binary", 61 "both", 62 "case", 63 "cast", 64 "check", 65 "collate", 66 "column", 67 "constraint", 68 "create", 69 "cross", 70 "current_date", 71 "current_role", 72 "current_time", 73 "current_timestamp", 74 "current_user", 75 "default", 76 "deferrable", 77 "desc", 78 "distinct", 79 "do", 80 "else", 81 "end", 82 "except", 83 "false", 84 "for", 85 "foreign", 86 "freeze", 87 "from", 88 "full", 89 "grant", 90 "group", 91 "having", 92 "ilike", 93 "in", 94 "initially", 95 "inner", 96 "intersect", 97 "into", 98 "is", 99 "isnull", 100 "join", 101 "leading", 102 "left", 103 "like", 104 "limit", 105 "localtime", 106 "localtimestamp", 107 "natural", 108 "new", 109 "not", 110 "notnull", 111 "null", 112 "off", 113 "offset", 114 "old", 115 "on", 116 "only", 117 "or", 118 "order", 119 "outer", 120 "overlaps", 121 "placing", 122 "primary", 123 "references", 124 "right", 125 "select", 126 "session_user", 127 "set", 128 "similar", 129 "some", 130 "symmetric", 131 "table", 132 "then", 133 "to", 134 "trailing", 135 "true", 136 "union", 137 "unique", 138 "user", 139 "using", 140 "verbose", 141 "when", 142 "where", 143 ] 144) 145 146LEGAL_CHARACTERS = re.compile(r"^[A-Z0-9_$]+$", re.I) 147LEGAL_CHARACTERS_PLUS_SPACE = re.compile(r"^[A-Z0-9_ $]+$", re.I) 148ILLEGAL_INITIAL_CHARACTERS = {str(x) for x in range(0, 10)}.union(["$"]) 149 150FK_ON_DELETE = re.compile( 151 r"^(?:RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT)$", re.I 152) 153FK_ON_UPDATE = re.compile( 154 r"^(?:RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT)$", re.I 155) 156FK_INITIALLY = re.compile(r"^(?:DEFERRED|IMMEDIATE)$", re.I) 157BIND_PARAMS = re.compile(r"(?<![:\w\$\x5c]):([\w\$]+)(?![:\w\$])", re.UNICODE) 158BIND_PARAMS_ESC = re.compile(r"\x5c(:[\w\$]*)(?![:\w\$])", re.UNICODE) 159 160BIND_TEMPLATES = { 161 "pyformat": "%%(%(name)s)s", 162 "qmark": "?", 163 "format": "%%s", 164 "numeric": ":[_POSITION]", 165 "named": ":%(name)s", 166} 167 168_BIND_TRANSLATE_RE = re.compile(r"[%\(\):\[\]]") 169_BIND_TRANSLATE_CHARS = dict(zip("%():[]", "PAZC__")) 170 171OPERATORS = { 172 # binary 173 operators.and_: " AND ", 174 operators.or_: " OR ", 175 operators.add: " + ", 176 operators.mul: " * ", 177 operators.sub: " - ", 178 operators.div: " / ", 179 operators.mod: " % ", 180 operators.truediv: " / ", 181 operators.neg: "-", 182 operators.lt: " < ", 183 operators.le: " <= ", 184 operators.ne: " != ", 185 operators.gt: " > ", 186 operators.ge: " >= ", 187 operators.eq: " = ", 188 operators.is_distinct_from: " IS DISTINCT FROM ", 189 operators.is_not_distinct_from: " IS NOT DISTINCT FROM ", 190 operators.concat_op: " || ", 191 operators.match_op: " MATCH ", 192 operators.not_match_op: " NOT MATCH ", 193 operators.in_op: " IN ", 194 operators.not_in_op: " NOT IN ", 195 operators.comma_op: ", ", 196 operators.from_: " FROM ", 197 operators.as_: " AS ", 198 operators.is_: " IS ", 199 operators.is_not: " IS NOT ", 200 operators.collate: " COLLATE ", 201 # unary 202 operators.exists: "EXISTS ", 203 operators.distinct_op: "DISTINCT ", 204 operators.inv: "NOT ", 205 operators.any_op: "ANY ", 206 operators.all_op: "ALL ", 207 # modifiers 208 operators.desc_op: " DESC", 209 operators.asc_op: " ASC", 210 operators.nulls_first_op: " NULLS FIRST", 211 operators.nulls_last_op: " NULLS LAST", 212} 213 214FUNCTIONS = { 215 functions.coalesce: "coalesce", 216 functions.current_date: "CURRENT_DATE", 217 functions.current_time: "CURRENT_TIME", 218 functions.current_timestamp: "CURRENT_TIMESTAMP", 219 functions.current_user: "CURRENT_USER", 220 functions.localtime: "LOCALTIME", 221 functions.localtimestamp: "LOCALTIMESTAMP", 222 functions.random: "random", 223 functions.sysdate: "sysdate", 224 functions.session_user: "SESSION_USER", 225 functions.user: "USER", 226 functions.cube: "CUBE", 227 functions.rollup: "ROLLUP", 228 functions.grouping_sets: "GROUPING SETS", 229} 230 231EXTRACT_MAP = { 232 "month": "month", 233 "day": "day", 234 "year": "year", 235 "second": "second", 236 "hour": "hour", 237 "doy": "doy", 238 "minute": "minute", 239 "quarter": "quarter", 240 "dow": "dow", 241 "week": "week", 242 "epoch": "epoch", 243 "milliseconds": "milliseconds", 244 "microseconds": "microseconds", 245 "timezone_hour": "timezone_hour", 246 "timezone_minute": "timezone_minute", 247} 248 249COMPOUND_KEYWORDS = { 250 selectable.CompoundSelect.UNION: "UNION", 251 selectable.CompoundSelect.UNION_ALL: "UNION ALL", 252 selectable.CompoundSelect.EXCEPT: "EXCEPT", 253 selectable.CompoundSelect.EXCEPT_ALL: "EXCEPT ALL", 254 selectable.CompoundSelect.INTERSECT: "INTERSECT", 255 selectable.CompoundSelect.INTERSECT_ALL: "INTERSECT ALL", 256} 257 258 259RM_RENDERED_NAME = 0 260RM_NAME = 1 261RM_OBJECTS = 2 262RM_TYPE = 3 263 264 265ExpandedState = collections.namedtuple( 266 "ExpandedState", 267 [ 268 "statement", 269 "additional_parameters", 270 "processors", 271 "positiontup", 272 "parameter_expansion", 273 ], 274) 275 276 277NO_LINTING = util.symbol("NO_LINTING", "Disable all linting.", canonical=0) 278 279COLLECT_CARTESIAN_PRODUCTS = util.symbol( 280 "COLLECT_CARTESIAN_PRODUCTS", 281 "Collect data on FROMs and cartesian products and gather " 282 "into 'self.from_linter'", 283 canonical=1, 284) 285 286WARN_LINTING = util.symbol( 287 "WARN_LINTING", "Emit warnings for linters that find problems", canonical=2 288) 289 290FROM_LINTING = util.symbol( 291 "FROM_LINTING", 292 "Warn for cartesian products; " 293 "combines COLLECT_CARTESIAN_PRODUCTS and WARN_LINTING", 294 canonical=COLLECT_CARTESIAN_PRODUCTS | WARN_LINTING, 295) 296 297 298class FromLinter(collections.namedtuple("FromLinter", ["froms", "edges"])): 299 def lint(self, start=None): 300 froms = self.froms 301 if not froms: 302 return None, None 303 304 edges = set(self.edges) 305 the_rest = set(froms) 306 307 if start is not None: 308 start_with = start 309 the_rest.remove(start_with) 310 else: 311 start_with = the_rest.pop() 312 313 stack = collections.deque([start_with]) 314 315 while stack and the_rest: 316 node = stack.popleft() 317 the_rest.discard(node) 318 319 # comparison of nodes in edges here is based on hash equality, as 320 # there are "annotated" elements that match the non-annotated ones. 321 # to remove the need for in-python hash() calls, use native 322 # containment routines (e.g. "node in edge", "edge.index(node)") 323 to_remove = {edge for edge in edges if node in edge} 324 325 # appendleft the node in each edge that is not 326 # the one that matched. 327 stack.extendleft(edge[not edge.index(node)] for edge in to_remove) 328 edges.difference_update(to_remove) 329 330 # FROMS left over? boom 331 if the_rest: 332 return the_rest, start_with 333 else: 334 return None, None 335 336 def warn(self): 337 the_rest, start_with = self.lint() 338 339 # FROMS left over? boom 340 if the_rest: 341 342 froms = the_rest 343 if froms: 344 template = ( 345 "SELECT statement has a cartesian product between " 346 "FROM element(s) {froms} and " 347 'FROM element "{start}". Apply join condition(s) ' 348 "between each element to resolve." 349 ) 350 froms_str = ", ".join( 351 '"{elem}"'.format(elem=self.froms[from_]) 352 for from_ in froms 353 ) 354 message = template.format( 355 froms=froms_str, start=self.froms[start_with] 356 ) 357 358 util.warn(message) 359 360 361class Compiled(object): 362 363 """Represent a compiled SQL or DDL expression. 364 365 The ``__str__`` method of the ``Compiled`` object should produce 366 the actual text of the statement. ``Compiled`` objects are 367 specific to their underlying database dialect, and also may 368 or may not be specific to the columns referenced within a 369 particular set of bind parameters. In no case should the 370 ``Compiled`` object be dependent on the actual values of those 371 bind parameters, even though it may reference those values as 372 defaults. 373 """ 374 375 _cached_metadata = None 376 377 _result_columns = None 378 379 schema_translate_map = None 380 381 execution_options = util.EMPTY_DICT 382 """ 383 Execution options propagated from the statement. In some cases, 384 sub-elements of the statement can modify these. 385 """ 386 387 _annotations = util.EMPTY_DICT 388 389 compile_state = None 390 """Optional :class:`.CompileState` object that maintains additional 391 state used by the compiler. 392 393 Major executable objects such as :class:`_expression.Insert`, 394 :class:`_expression.Update`, :class:`_expression.Delete`, 395 :class:`_expression.Select` will generate this 396 state when compiled in order to calculate additional information about the 397 object. For the top level object that is to be executed, the state can be 398 stored here where it can also have applicability towards result set 399 processing. 400 401 .. versionadded:: 1.4 402 403 """ 404 405 cache_key = None 406 _gen_time = None 407 408 def __init__( 409 self, 410 dialect, 411 statement, 412 schema_translate_map=None, 413 render_schema_translate=False, 414 compile_kwargs=util.immutabledict(), 415 ): 416 """Construct a new :class:`.Compiled` object. 417 418 :param dialect: :class:`.Dialect` to compile against. 419 420 :param statement: :class:`_expression.ClauseElement` to be compiled. 421 422 :param schema_translate_map: dictionary of schema names to be 423 translated when forming the resultant SQL 424 425 .. versionadded:: 1.1 426 427 .. seealso:: 428 429 :ref:`schema_translating` 430 431 :param compile_kwargs: additional kwargs that will be 432 passed to the initial call to :meth:`.Compiled.process`. 433 434 435 """ 436 437 self.dialect = dialect 438 self.preparer = self.dialect.identifier_preparer 439 if schema_translate_map: 440 self.schema_translate_map = schema_translate_map 441 self.preparer = self.preparer._with_schema_translate( 442 schema_translate_map 443 ) 444 445 if statement is not None: 446 self.statement = statement 447 self.can_execute = statement.supports_execution 448 self._annotations = statement._annotations 449 if self.can_execute: 450 self.execution_options = statement._execution_options 451 self.string = self.process(self.statement, **compile_kwargs) 452 453 if render_schema_translate: 454 self.string = self.preparer._render_schema_translates( 455 self.string, schema_translate_map 456 ) 457 self._gen_time = util.perf_counter() 458 459 def _execute_on_connection( 460 self, connection, multiparams, params, execution_options 461 ): 462 if self.can_execute: 463 return connection._execute_compiled( 464 self, multiparams, params, execution_options 465 ) 466 else: 467 raise exc.ObjectNotExecutableError(self.statement) 468 469 def visit_unsupported_compilation(self, element, err): 470 util.raise_( 471 exc.UnsupportedCompilationError(self, type(element)), 472 replace_context=err, 473 ) 474 475 @property 476 def sql_compiler(self): 477 """Return a Compiled that is capable of processing SQL expressions. 478 479 If this compiler is one, it would likely just return 'self'. 480 481 """ 482 483 raise NotImplementedError() 484 485 def process(self, obj, **kwargs): 486 return obj._compiler_dispatch(self, **kwargs) 487 488 def __str__(self): 489 """Return the string text of the generated SQL or DDL.""" 490 491 return self.string or "" 492 493 def construct_params(self, params=None, extracted_parameters=None): 494 """Return the bind params for this compiled object. 495 496 :param params: a dict of string/object pairs whose values will 497 override bind values compiled in to the 498 statement. 499 """ 500 501 raise NotImplementedError() 502 503 @property 504 def params(self): 505 """Return the bind params for this compiled object.""" 506 return self.construct_params() 507 508 509class TypeCompiler(util.with_metaclass(util.EnsureKWArgType, object)): 510 """Produces DDL specification for TypeEngine objects.""" 511 512 ensure_kwarg = r"visit_\w+" 513 514 def __init__(self, dialect): 515 self.dialect = dialect 516 517 def process(self, type_, **kw): 518 return type_._compiler_dispatch(self, **kw) 519 520 def visit_unsupported_compilation(self, element, err, **kw): 521 util.raise_( 522 exc.UnsupportedCompilationError(self, element), 523 replace_context=err, 524 ) 525 526 527# this was a Visitable, but to allow accurate detection of 528# column elements this is actually a column element 529class _CompileLabel(elements.ColumnElement): 530 531 """lightweight label object which acts as an expression.Label.""" 532 533 __visit_name__ = "label" 534 __slots__ = "element", "name" 535 536 def __init__(self, col, name, alt_names=()): 537 self.element = col 538 self.name = name 539 self._alt_names = (col,) + alt_names 540 541 @property 542 def proxy_set(self): 543 return self.element.proxy_set 544 545 @property 546 def type(self): 547 return self.element.type 548 549 def self_group(self, **kw): 550 return self 551 552 553class SQLCompiler(Compiled): 554 """Default implementation of :class:`.Compiled`. 555 556 Compiles :class:`_expression.ClauseElement` objects into SQL strings. 557 558 """ 559 560 extract_map = EXTRACT_MAP 561 562 compound_keywords = COMPOUND_KEYWORDS 563 564 isdelete = isinsert = isupdate = False 565 """class-level defaults which can be set at the instance 566 level to define if this Compiled instance represents 567 INSERT/UPDATE/DELETE 568 """ 569 570 isplaintext = False 571 572 returning = None 573 """holds the "returning" collection of columns if 574 the statement is CRUD and defines returning columns 575 either implicitly or explicitly 576 """ 577 578 returning_precedes_values = False 579 """set to True classwide to generate RETURNING 580 clauses before the VALUES or WHERE clause (i.e. MSSQL) 581 """ 582 583 render_table_with_column_in_update_from = False 584 """set to True classwide to indicate the SET clause 585 in a multi-table UPDATE statement should qualify 586 columns with the table name (i.e. MySQL only) 587 """ 588 589 ansi_bind_rules = False 590 """SQL 92 doesn't allow bind parameters to be used 591 in the columns clause of a SELECT, nor does it allow 592 ambiguous expressions like "? = ?". A compiler 593 subclass can set this flag to False if the target 594 driver/DB enforces this 595 """ 596 597 _textual_ordered_columns = False 598 """tell the result object that the column names as rendered are important, 599 but they are also "ordered" vs. what is in the compiled object here. 600 """ 601 602 _ordered_columns = True 603 """ 604 if False, means we can't be sure the list of entries 605 in _result_columns is actually the rendered order. Usually 606 True unless using an unordered TextualSelect. 607 """ 608 609 _loose_column_name_matching = False 610 """tell the result object that the SQL statement is textual, wants to match 611 up to Column objects, and may be using the ._tq_label in the SELECT rather 612 than the base name. 613 614 """ 615 616 _numeric_binds = False 617 """ 618 True if paramstyle is "numeric". This paramstyle is trickier than 619 all the others. 620 621 """ 622 623 _render_postcompile = False 624 """ 625 whether to render out POSTCOMPILE params during the compile phase. 626 627 """ 628 629 insert_single_values_expr = None 630 """When an INSERT is compiled with a single set of parameters inside 631 a VALUES expression, the string is assigned here, where it can be 632 used for insert batching schemes to rewrite the VALUES expression. 633 634 .. versionadded:: 1.3.8 635 636 """ 637 638 literal_execute_params = frozenset() 639 """bindparameter objects that are rendered as literal values at statement 640 execution time. 641 642 """ 643 644 post_compile_params = frozenset() 645 """bindparameter objects that are rendered as bound parameter placeholders 646 at statement execution time. 647 648 """ 649 650 escaped_bind_names = util.EMPTY_DICT 651 """Late escaping of bound parameter names that has to be converted 652 to the original name when looking in the parameter dictionary. 653 654 """ 655 656 has_out_parameters = False 657 """if True, there are bindparam() objects that have the isoutparam 658 flag set.""" 659 660 insert_prefetch = update_prefetch = () 661 662 postfetch_lastrowid = False 663 """if True, and this in insert, use cursor.lastrowid to populate 664 result.inserted_primary_key. """ 665 666 _cache_key_bind_match = None 667 """a mapping that will relate the BindParameter object we compile 668 to those that are part of the extracted collection of parameters 669 in the cache key, if we were given a cache key. 670 671 """ 672 673 positiontup = None 674 """for a compiled construct that uses a positional paramstyle, will be 675 a sequence of strings, indicating the names of bound parameters in order. 676 677 This is used in order to render bound parameters in their correct order, 678 and is combined with the :attr:`_sql.Compiled.params` dictionary to 679 render parameters. 680 681 .. seealso:: 682 683 :ref:`faq_sql_expression_string` - includes a usage example for 684 debugging use cases. 685 686 """ 687 688 inline = False 689 690 def __init__( 691 self, 692 dialect, 693 statement, 694 cache_key=None, 695 column_keys=None, 696 for_executemany=False, 697 linting=NO_LINTING, 698 **kwargs 699 ): 700 """Construct a new :class:`.SQLCompiler` object. 701 702 :param dialect: :class:`.Dialect` to be used 703 704 :param statement: :class:`_expression.ClauseElement` to be compiled 705 706 :param column_keys: a list of column names to be compiled into an 707 INSERT or UPDATE statement. 708 709 :param for_executemany: whether INSERT / UPDATE statements should 710 expect that they are to be invoked in an "executemany" style, 711 which may impact how the statement will be expected to return the 712 values of defaults and autoincrement / sequences and similar. 713 Depending on the backend and driver in use, support for retrieving 714 these values may be disabled which means SQL expressions may 715 be rendered inline, RETURNING may not be rendered, etc. 716 717 :param kwargs: additional keyword arguments to be consumed by the 718 superclass. 719 720 """ 721 self.column_keys = column_keys 722 723 self.cache_key = cache_key 724 725 if cache_key: 726 self._cache_key_bind_match = ckbm = { 727 b.key: b for b in cache_key[1] 728 } 729 ckbm.update({b: [b] for b in cache_key[1]}) 730 731 # compile INSERT/UPDATE defaults/sequences to expect executemany 732 # style execution, which may mean no pre-execute of defaults, 733 # or no RETURNING 734 self.for_executemany = for_executemany 735 736 self.linting = linting 737 738 # a dictionary of bind parameter keys to BindParameter 739 # instances. 740 self.binds = {} 741 742 # a dictionary of BindParameter instances to "compiled" names 743 # that are actually present in the generated SQL 744 self.bind_names = util.column_dict() 745 746 # stack which keeps track of nested SELECT statements 747 self.stack = [] 748 749 # relates label names in the final SQL to a tuple of local 750 # column/label name, ColumnElement object (if any) and 751 # TypeEngine. CursorResult uses this for type processing and 752 # column targeting 753 self._result_columns = [] 754 755 # true if the paramstyle is positional 756 self.positional = dialect.positional 757 if self.positional: 758 self.positiontup = [] 759 self._numeric_binds = dialect.paramstyle == "numeric" 760 self.bindtemplate = BIND_TEMPLATES[dialect.paramstyle] 761 762 self.ctes = None 763 764 self.label_length = ( 765 dialect.label_length or dialect.max_identifier_length 766 ) 767 768 # a map which tracks "anonymous" identifiers that are created on 769 # the fly here 770 self.anon_map = prefix_anon_map() 771 772 # a map which tracks "truncated" names based on 773 # dialect.label_length or dialect.max_identifier_length 774 self.truncated_names = {} 775 776 Compiled.__init__(self, dialect, statement, **kwargs) 777 778 if self.isinsert or self.isupdate or self.isdelete: 779 if statement._returning: 780 self.returning = statement._returning 781 782 if self.isinsert or self.isupdate: 783 if statement._inline: 784 self.inline = True 785 elif self.for_executemany and ( 786 not self.isinsert 787 or ( 788 self.dialect.insert_executemany_returning 789 and statement._return_defaults 790 ) 791 ): 792 self.inline = True 793 794 if self.positional and self._numeric_binds: 795 self._apply_numbered_params() 796 797 if self._render_postcompile: 798 self._process_parameters_for_postcompile(_populate_self=True) 799 800 @property 801 def current_executable(self): 802 """Return the current 'executable' that is being compiled. 803 804 This is currently the :class:`_sql.Select`, :class:`_sql.Insert`, 805 :class:`_sql.Update`, :class:`_sql.Delete`, 806 :class:`_sql.CompoundSelect` object that is being compiled. 807 Specifically it's assigned to the ``self.stack`` list of elements. 808 809 When a statement like the above is being compiled, it normally 810 is also assigned to the ``.statement`` attribute of the 811 :class:`_sql.Compiler` object. However, all SQL constructs are 812 ultimately nestable, and this attribute should never be consulted 813 by a ``visit_`` method, as it is not guaranteed to be assigned 814 nor guaranteed to correspond to the current statement being compiled. 815 816 .. versionadded:: 1.3.21 817 818 For compatibility with previous versions, use the following 819 recipe:: 820 821 statement = getattr(self, "current_executable", False) 822 if statement is False: 823 statement = self.stack[-1]["selectable"] 824 825 For versions 1.4 and above, ensure only .current_executable 826 is used; the format of "self.stack" may change. 827 828 829 """ 830 try: 831 return self.stack[-1]["selectable"] 832 except IndexError as ie: 833 util.raise_( 834 IndexError("Compiler does not have a stack entry"), 835 replace_context=ie, 836 ) 837 838 @property 839 def prefetch(self): 840 return list(self.insert_prefetch + self.update_prefetch) 841 842 @util.memoized_property 843 def _global_attributes(self): 844 return {} 845 846 @util.memoized_instancemethod 847 def _init_cte_state(self): 848 """Initialize collections related to CTEs only if 849 a CTE is located, to save on the overhead of 850 these collections otherwise. 851 852 """ 853 # collect CTEs to tack on top of a SELECT 854 # To store the query to print - Dict[cte, text_query] 855 self.ctes = util.OrderedDict() 856 857 # Detect same CTE references - Dict[(level, name), cte] 858 # Level is required for supporting nesting 859 self.ctes_by_level_name = {} 860 861 # To retrieve key/level in ctes_by_level_name - 862 # Dict[cte_reference, (level, cte_name)] 863 self.level_name_by_cte = {} 864 865 self.ctes_recursive = False 866 if self.positional: 867 self.cte_positional = {} 868 869 @contextlib.contextmanager 870 def _nested_result(self): 871 """special API to support the use case of 'nested result sets'""" 872 result_columns, ordered_columns = ( 873 self._result_columns, 874 self._ordered_columns, 875 ) 876 self._result_columns, self._ordered_columns = [], False 877 878 try: 879 if self.stack: 880 entry = self.stack[-1] 881 entry["need_result_map_for_nested"] = True 882 else: 883 entry = None 884 yield self._result_columns, self._ordered_columns 885 finally: 886 if entry: 887 entry.pop("need_result_map_for_nested") 888 self._result_columns, self._ordered_columns = ( 889 result_columns, 890 ordered_columns, 891 ) 892 893 def _apply_numbered_params(self): 894 poscount = itertools.count(1) 895 self.string = re.sub( 896 r"\[_POSITION\]", lambda m: str(util.next(poscount)), self.string 897 ) 898 899 @util.memoized_property 900 def _bind_processors(self): 901 return dict( 902 (key, value) 903 for key, value in ( 904 ( 905 self.bind_names[bindparam], 906 bindparam.type._cached_bind_processor(self.dialect) 907 if not bindparam.type._is_tuple_type 908 else tuple( 909 elem_type._cached_bind_processor(self.dialect) 910 for elem_type in bindparam.type.types 911 ), 912 ) 913 for bindparam in self.bind_names 914 ) 915 if value is not None 916 ) 917 918 def is_subquery(self): 919 return len(self.stack) > 1 920 921 @property 922 def sql_compiler(self): 923 return self 924 925 def construct_params( 926 self, 927 params=None, 928 _group_number=None, 929 _check=True, 930 extracted_parameters=None, 931 ): 932 """return a dictionary of bind parameter keys and values""" 933 934 has_escaped_names = bool(self.escaped_bind_names) 935 936 if extracted_parameters: 937 # related the bound parameters collected in the original cache key 938 # to those collected in the incoming cache key. They will not have 939 # matching names but they will line up positionally in the same 940 # way. The parameters present in self.bind_names may be clones of 941 # these original cache key params in the case of DML but the .key 942 # will be guaranteed to match. 943 try: 944 orig_extracted = self.cache_key[1] 945 except TypeError as err: 946 util.raise_( 947 exc.CompileError( 948 "This compiled object has no original cache key; " 949 "can't pass extracted_parameters to construct_params" 950 ), 951 replace_context=err, 952 ) 953 954 ckbm = self._cache_key_bind_match 955 resolved_extracted = { 956 bind: extracted 957 for b, extracted in zip(orig_extracted, extracted_parameters) 958 for bind in ckbm[b] 959 } 960 else: 961 resolved_extracted = None 962 963 if params: 964 pd = {} 965 for bindparam, name in self.bind_names.items(): 966 escaped_name = ( 967 self.escaped_bind_names.get(name, name) 968 if has_escaped_names 969 else name 970 ) 971 972 if bindparam.key in params: 973 pd[escaped_name] = params[bindparam.key] 974 elif name in params: 975 pd[escaped_name] = params[name] 976 977 elif _check and bindparam.required: 978 if _group_number: 979 raise exc.InvalidRequestError( 980 "A value is required for bind parameter %r, " 981 "in parameter group %d" 982 % (bindparam.key, _group_number), 983 code="cd3x", 984 ) 985 else: 986 raise exc.InvalidRequestError( 987 "A value is required for bind parameter %r" 988 % bindparam.key, 989 code="cd3x", 990 ) 991 else: 992 if resolved_extracted: 993 value_param = resolved_extracted.get( 994 bindparam, bindparam 995 ) 996 else: 997 value_param = bindparam 998 999 if bindparam.callable: 1000 pd[escaped_name] = value_param.effective_value 1001 else: 1002 pd[escaped_name] = value_param.value 1003 return pd 1004 else: 1005 pd = {} 1006 for bindparam, name in self.bind_names.items(): 1007 escaped_name = ( 1008 self.escaped_bind_names.get(name, name) 1009 if has_escaped_names 1010 else name 1011 ) 1012 1013 if _check and bindparam.required: 1014 if _group_number: 1015 raise exc.InvalidRequestError( 1016 "A value is required for bind parameter %r, " 1017 "in parameter group %d" 1018 % (bindparam.key, _group_number), 1019 code="cd3x", 1020 ) 1021 else: 1022 raise exc.InvalidRequestError( 1023 "A value is required for bind parameter %r" 1024 % bindparam.key, 1025 code="cd3x", 1026 ) 1027 1028 if resolved_extracted: 1029 value_param = resolved_extracted.get(bindparam, bindparam) 1030 else: 1031 value_param = bindparam 1032 1033 if bindparam.callable: 1034 pd[escaped_name] = value_param.effective_value 1035 else: 1036 pd[escaped_name] = value_param.value 1037 return pd 1038 1039 @util.memoized_instancemethod 1040 def _get_set_input_sizes_lookup( 1041 self, include_types=None, exclude_types=None 1042 ): 1043 if not hasattr(self, "bind_names"): 1044 return None 1045 1046 dialect = self.dialect 1047 dbapi = self.dialect.dbapi 1048 1049 # _unwrapped_dialect_impl() is necessary so that we get the 1050 # correct dialect type for a custom TypeDecorator, or a Variant, 1051 # which is also a TypeDecorator. Special types like Interval, 1052 # that use TypeDecorator but also might be mapped directly 1053 # for a dialect impl, also subclass Emulated first which overrides 1054 # this behavior in those cases to behave like the default. 1055 1056 if include_types is None and exclude_types is None: 1057 1058 def _lookup_type(typ): 1059 dbtype = typ.dialect_impl(dialect).get_dbapi_type(dbapi) 1060 return dbtype 1061 1062 else: 1063 1064 def _lookup_type(typ): 1065 # note we get dbtype from the possibly TypeDecorator-wrapped 1066 # dialect_impl, but the dialect_impl itself that we use for 1067 # include/exclude is the unwrapped version. 1068 1069 dialect_impl = typ._unwrapped_dialect_impl(dialect) 1070 1071 dbtype = typ.dialect_impl(dialect).get_dbapi_type(dbapi) 1072 1073 if ( 1074 dbtype is not None 1075 and ( 1076 exclude_types is None 1077 or dbtype not in exclude_types 1078 and type(dialect_impl) not in exclude_types 1079 ) 1080 and ( 1081 include_types is None 1082 or dbtype in include_types 1083 or type(dialect_impl) in include_types 1084 ) 1085 ): 1086 return dbtype 1087 else: 1088 return None 1089 1090 inputsizes = {} 1091 literal_execute_params = self.literal_execute_params 1092 1093 for bindparam in self.bind_names: 1094 if bindparam in literal_execute_params: 1095 continue 1096 1097 if bindparam.type._is_tuple_type: 1098 inputsizes[bindparam] = [ 1099 _lookup_type(typ) for typ in bindparam.type.types 1100 ] 1101 else: 1102 inputsizes[bindparam] = _lookup_type(bindparam.type) 1103 1104 return inputsizes 1105 1106 @property 1107 def params(self): 1108 """Return the bind param dictionary embedded into this 1109 compiled object, for those values that are present. 1110 1111 .. seealso:: 1112 1113 :ref:`faq_sql_expression_string` - includes a usage example for 1114 debugging use cases. 1115 1116 """ 1117 return self.construct_params(_check=False) 1118 1119 def _process_parameters_for_postcompile( 1120 self, parameters=None, _populate_self=False 1121 ): 1122 """handle special post compile parameters. 1123 1124 These include: 1125 1126 * "expanding" parameters -typically IN tuples that are rendered 1127 on a per-parameter basis for an otherwise fixed SQL statement string. 1128 1129 * literal_binds compiled with the literal_execute flag. Used for 1130 things like SQL Server "TOP N" where the driver does not accommodate 1131 N as a bound parameter. 1132 1133 """ 1134 if parameters is None: 1135 parameters = self.construct_params() 1136 1137 expanded_parameters = {} 1138 if self.positional: 1139 positiontup = [] 1140 else: 1141 positiontup = None 1142 1143 processors = self._bind_processors 1144 1145 new_processors = {} 1146 1147 if self.positional and self._numeric_binds: 1148 # I'm not familiar with any DBAPI that uses 'numeric'. 1149 # strategy would likely be to make use of numbers greater than 1150 # the highest number present; then for expanding parameters, 1151 # append them to the end of the parameter list. that way 1152 # we avoid having to renumber all the existing parameters. 1153 raise NotImplementedError( 1154 "'post-compile' bind parameters are not supported with " 1155 "the 'numeric' paramstyle at this time." 1156 ) 1157 1158 replacement_expressions = {} 1159 to_update_sets = {} 1160 1161 # notes: 1162 # *unescaped* parameter names in: 1163 # self.bind_names, self.binds, self._bind_processors 1164 # 1165 # *escaped* parameter names in: 1166 # construct_params(), replacement_expressions 1167 1168 for name in ( 1169 self.positiontup if self.positional else self.bind_names.values() 1170 ): 1171 escaped_name = ( 1172 self.escaped_bind_names.get(name, name) 1173 if self.escaped_bind_names 1174 else name 1175 ) 1176 parameter = self.binds[name] 1177 if parameter in self.literal_execute_params: 1178 if escaped_name not in replacement_expressions: 1179 value = parameters.pop(escaped_name) 1180 1181 replacement_expressions[ 1182 escaped_name 1183 ] = self.render_literal_bindparam( 1184 parameter, render_literal_value=value 1185 ) 1186 continue 1187 1188 if parameter in self.post_compile_params: 1189 if escaped_name in replacement_expressions: 1190 to_update = to_update_sets[escaped_name] 1191 else: 1192 # we are removing the parameter from parameters 1193 # because it is a list value, which is not expected by 1194 # TypeEngine objects that would otherwise be asked to 1195 # process it. the single name is being replaced with 1196 # individual numbered parameters for each value in the 1197 # param. 1198 values = parameters.pop(escaped_name) 1199 1200 leep = self._literal_execute_expanding_parameter 1201 to_update, replacement_expr = leep( 1202 escaped_name, parameter, values 1203 ) 1204 1205 to_update_sets[escaped_name] = to_update 1206 replacement_expressions[escaped_name] = replacement_expr 1207 1208 if not parameter.literal_execute: 1209 parameters.update(to_update) 1210 if parameter.type._is_tuple_type: 1211 new_processors.update( 1212 ( 1213 "%s_%s_%s" % (name, i, j), 1214 processors[name][j - 1], 1215 ) 1216 for i, tuple_element in enumerate(values, 1) 1217 for j, value in enumerate(tuple_element, 1) 1218 if name in processors 1219 and processors[name][j - 1] is not None 1220 ) 1221 else: 1222 new_processors.update( 1223 (key, processors[name]) 1224 for key, value in to_update 1225 if name in processors 1226 ) 1227 if self.positional: 1228 positiontup.extend(name for name, value in to_update) 1229 expanded_parameters[name] = [ 1230 expand_key for expand_key, value in to_update 1231 ] 1232 elif self.positional: 1233 positiontup.append(name) 1234 1235 def process_expanding(m): 1236 key = m.group(1) 1237 expr = replacement_expressions[key] 1238 1239 # if POSTCOMPILE included a bind_expression, render that 1240 # around each element 1241 if m.group(2): 1242 tok = m.group(2).split("~~") 1243 be_left, be_right = tok[1], tok[3] 1244 expr = ", ".join( 1245 "%s%s%s" % (be_left, exp, be_right) 1246 for exp in expr.split(", ") 1247 ) 1248 return expr 1249 1250 statement = re.sub( 1251 r"__\[POSTCOMPILE_(\S+?)(~~.+?~~)?\]", 1252 process_expanding, 1253 self.string, 1254 ) 1255 1256 expanded_state = ExpandedState( 1257 statement, 1258 parameters, 1259 new_processors, 1260 positiontup, 1261 expanded_parameters, 1262 ) 1263 1264 if _populate_self: 1265 # this is for the "render_postcompile" flag, which is not 1266 # otherwise used internally and is for end-user debugging and 1267 # special use cases. 1268 self.string = expanded_state.statement 1269 self._bind_processors.update(expanded_state.processors) 1270 self.positiontup = expanded_state.positiontup 1271 self.post_compile_params = frozenset() 1272 for key in expanded_state.parameter_expansion: 1273 bind = self.binds.pop(key) 1274 self.bind_names.pop(bind) 1275 for value, expanded_key in zip( 1276 bind.value, expanded_state.parameter_expansion[key] 1277 ): 1278 self.binds[expanded_key] = new_param = bind._with_value( 1279 value 1280 ) 1281 self.bind_names[new_param] = expanded_key 1282 1283 return expanded_state 1284 1285 @util.preload_module("sqlalchemy.engine.cursor") 1286 def _create_result_map(self): 1287 """utility method used for unit tests only.""" 1288 cursor = util.preloaded.engine_cursor 1289 return cursor.CursorResultMetaData._create_description_match_map( 1290 self._result_columns 1291 ) 1292 1293 @util.memoized_property 1294 @util.preload_module("sqlalchemy.engine.result") 1295 def _inserted_primary_key_from_lastrowid_getter(self): 1296 result = util.preloaded.engine_result 1297 1298 key_getter = self._key_getters_for_crud_column[2] 1299 table = self.statement.table 1300 1301 getters = [ 1302 (operator.methodcaller("get", key_getter(col), None), col) 1303 for col in table.primary_key 1304 ] 1305 1306 autoinc_col = table._autoincrement_column 1307 if autoinc_col is not None: 1308 # apply type post processors to the lastrowid 1309 proc = autoinc_col.type._cached_result_processor( 1310 self.dialect, None 1311 ) 1312 else: 1313 proc = None 1314 1315 row_fn = result.result_tuple([col.key for col in table.primary_key]) 1316 1317 def get(lastrowid, parameters): 1318 if proc is not None: 1319 lastrowid = proc(lastrowid) 1320 1321 if lastrowid is None: 1322 return row_fn(getter(parameters) for getter, col in getters) 1323 else: 1324 return row_fn( 1325 lastrowid if col is autoinc_col else getter(parameters) 1326 for getter, col in getters 1327 ) 1328 1329 return get 1330 1331 @util.memoized_property 1332 @util.preload_module("sqlalchemy.engine.result") 1333 def _inserted_primary_key_from_returning_getter(self): 1334 result = util.preloaded.engine_result 1335 1336 key_getter = self._key_getters_for_crud_column[2] 1337 table = self.statement.table 1338 1339 ret = {col: idx for idx, col in enumerate(self.returning)} 1340 1341 getters = [ 1342 (operator.itemgetter(ret[col]), True) 1343 if col in ret 1344 else (operator.methodcaller("get", key_getter(col), None), False) 1345 for col in table.primary_key 1346 ] 1347 1348 row_fn = result.result_tuple([col.key for col in table.primary_key]) 1349 1350 def get(row, parameters): 1351 return row_fn( 1352 getter(row) if use_row else getter(parameters) 1353 for getter, use_row in getters 1354 ) 1355 1356 return get 1357 1358 def default_from(self): 1359 """Called when a SELECT statement has no froms, and no FROM clause is 1360 to be appended. 1361 1362 Gives Oracle a chance to tack on a ``FROM DUAL`` to the string output. 1363 1364 """ 1365 return "" 1366 1367 def visit_grouping(self, grouping, asfrom=False, **kwargs): 1368 return "(" + grouping.element._compiler_dispatch(self, **kwargs) + ")" 1369 1370 def visit_select_statement_grouping(self, grouping, **kwargs): 1371 return "(" + grouping.element._compiler_dispatch(self, **kwargs) + ")" 1372 1373 def visit_label_reference( 1374 self, element, within_columns_clause=False, **kwargs 1375 ): 1376 if self.stack and self.dialect.supports_simple_order_by_label: 1377 compile_state = self.stack[-1]["compile_state"] 1378 1379 ( 1380 with_cols, 1381 only_froms, 1382 only_cols, 1383 ) = compile_state._label_resolve_dict 1384 if within_columns_clause: 1385 resolve_dict = only_froms 1386 else: 1387 resolve_dict = only_cols 1388 1389 # this can be None in the case that a _label_reference() 1390 # were subject to a replacement operation, in which case 1391 # the replacement of the Label element may have changed 1392 # to something else like a ColumnClause expression. 1393 order_by_elem = element.element._order_by_label_element 1394 1395 if ( 1396 order_by_elem is not None 1397 and order_by_elem.name in resolve_dict 1398 and order_by_elem.shares_lineage( 1399 resolve_dict[order_by_elem.name] 1400 ) 1401 ): 1402 kwargs[ 1403 "render_label_as_label" 1404 ] = element.element._order_by_label_element 1405 return self.process( 1406 element.element, 1407 within_columns_clause=within_columns_clause, 1408 **kwargs 1409 ) 1410 1411 def visit_textual_label_reference( 1412 self, element, within_columns_clause=False, **kwargs 1413 ): 1414 if not self.stack: 1415 # compiling the element outside of the context of a SELECT 1416 return self.process(element._text_clause) 1417 1418 compile_state = self.stack[-1]["compile_state"] 1419 with_cols, only_froms, only_cols = compile_state._label_resolve_dict 1420 try: 1421 if within_columns_clause: 1422 col = only_froms[element.element] 1423 else: 1424 col = with_cols[element.element] 1425 except KeyError as err: 1426 coercions._no_text_coercion( 1427 element.element, 1428 extra=( 1429 "Can't resolve label reference for ORDER BY / " 1430 "GROUP BY / DISTINCT etc." 1431 ), 1432 exc_cls=exc.CompileError, 1433 err=err, 1434 ) 1435 else: 1436 kwargs["render_label_as_label"] = col 1437 return self.process( 1438 col, within_columns_clause=within_columns_clause, **kwargs 1439 ) 1440 1441 def visit_label( 1442 self, 1443 label, 1444 add_to_result_map=None, 1445 within_label_clause=False, 1446 within_columns_clause=False, 1447 render_label_as_label=None, 1448 result_map_targets=(), 1449 **kw 1450 ): 1451 # only render labels within the columns clause 1452 # or ORDER BY clause of a select. dialect-specific compilers 1453 # can modify this behavior. 1454 render_label_with_as = ( 1455 within_columns_clause and not within_label_clause 1456 ) 1457 render_label_only = render_label_as_label is label 1458 1459 if render_label_only or render_label_with_as: 1460 if isinstance(label.name, elements._truncated_label): 1461 labelname = self._truncated_identifier("colident", label.name) 1462 else: 1463 labelname = label.name 1464 1465 if render_label_with_as: 1466 if add_to_result_map is not None: 1467 add_to_result_map( 1468 labelname, 1469 label.name, 1470 (label, labelname) + label._alt_names + result_map_targets, 1471 label.type, 1472 ) 1473 return ( 1474 label.element._compiler_dispatch( 1475 self, 1476 within_columns_clause=True, 1477 within_label_clause=True, 1478 **kw 1479 ) 1480 + OPERATORS[operators.as_] 1481 + self.preparer.format_label(label, labelname) 1482 ) 1483 elif render_label_only: 1484 return self.preparer.format_label(label, labelname) 1485 else: 1486 return label.element._compiler_dispatch( 1487 self, within_columns_clause=False, **kw 1488 ) 1489 1490 def _fallback_column_name(self, column): 1491 raise exc.CompileError( 1492 "Cannot compile Column object until " "its 'name' is assigned." 1493 ) 1494 1495 def visit_lambda_element(self, element, **kw): 1496 sql_element = element._resolved 1497 return self.process(sql_element, **kw) 1498 1499 def visit_column( 1500 self, 1501 column, 1502 add_to_result_map=None, 1503 include_table=True, 1504 result_map_targets=(), 1505 **kwargs 1506 ): 1507 name = orig_name = column.name 1508 if name is None: 1509 name = self._fallback_column_name(column) 1510 1511 is_literal = column.is_literal 1512 if not is_literal and isinstance(name, elements._truncated_label): 1513 name = self._truncated_identifier("colident", name) 1514 1515 if add_to_result_map is not None: 1516 targets = (column, name, column.key) + result_map_targets 1517 if column._tq_label: 1518 targets += (column._tq_label,) 1519 1520 add_to_result_map(name, orig_name, targets, column.type) 1521 1522 if is_literal: 1523 # note we are not currently accommodating for 1524 # literal_column(quoted_name('ident', True)) here 1525 name = self.escape_literal_column(name) 1526 else: 1527 name = self.preparer.quote(name) 1528 table = column.table 1529 if table is None or not include_table or not table.named_with_column: 1530 return name 1531 else: 1532 effective_schema = self.preparer.schema_for_object(table) 1533 1534 if effective_schema: 1535 schema_prefix = ( 1536 self.preparer.quote_schema(effective_schema) + "." 1537 ) 1538 else: 1539 schema_prefix = "" 1540 tablename = table.name 1541 if isinstance(tablename, elements._truncated_label): 1542 tablename = self._truncated_identifier("alias", tablename) 1543 1544 return schema_prefix + self.preparer.quote(tablename) + "." + name 1545 1546 def visit_collation(self, element, **kw): 1547 return self.preparer.format_collation(element.collation) 1548 1549 def visit_fromclause(self, fromclause, **kwargs): 1550 return fromclause.name 1551 1552 def visit_index(self, index, **kwargs): 1553 return index.name 1554 1555 def visit_typeclause(self, typeclause, **kw): 1556 kw["type_expression"] = typeclause 1557 kw["identifier_preparer"] = self.preparer 1558 return self.dialect.type_compiler.process(typeclause.type, **kw) 1559 1560 def post_process_text(self, text): 1561 if self.preparer._double_percents: 1562 text = text.replace("%", "%%") 1563 return text 1564 1565 def escape_literal_column(self, text): 1566 if self.preparer._double_percents: 1567 text = text.replace("%", "%%") 1568 return text 1569 1570 def visit_textclause(self, textclause, add_to_result_map=None, **kw): 1571 def do_bindparam(m): 1572 name = m.group(1) 1573 if name in textclause._bindparams: 1574 return self.process(textclause._bindparams[name], **kw) 1575 else: 1576 return self.bindparam_string(name, **kw) 1577 1578 if not self.stack: 1579 self.isplaintext = True 1580 1581 if add_to_result_map: 1582 # text() object is present in the columns clause of a 1583 # select(). Add a no-name entry to the result map so that 1584 # row[text()] produces a result 1585 add_to_result_map(None, None, (textclause,), sqltypes.NULLTYPE) 1586 1587 # un-escape any \:params 1588 return BIND_PARAMS_ESC.sub( 1589 lambda m: m.group(1), 1590 BIND_PARAMS.sub( 1591 do_bindparam, self.post_process_text(textclause.text) 1592 ), 1593 ) 1594 1595 def visit_textual_select( 1596 self, taf, compound_index=None, asfrom=False, **kw 1597 ): 1598 1599 toplevel = not self.stack 1600 entry = self._default_stack_entry if toplevel else self.stack[-1] 1601 1602 populate_result_map = ( 1603 toplevel 1604 or ( 1605 compound_index == 0 1606 and entry.get("need_result_map_for_compound", False) 1607 ) 1608 or entry.get("need_result_map_for_nested", False) 1609 ) 1610 1611 if populate_result_map: 1612 self._ordered_columns = ( 1613 self._textual_ordered_columns 1614 ) = taf.positional 1615 1616 # enable looser result column matching when the SQL text links to 1617 # Column objects by name only 1618 self._loose_column_name_matching = not taf.positional and bool( 1619 taf.column_args 1620 ) 1621 1622 for c in taf.column_args: 1623 self.process( 1624 c, 1625 within_columns_clause=True, 1626 add_to_result_map=self._add_to_result_map, 1627 ) 1628 1629 return self.process(taf.element, **kw) 1630 1631 def visit_null(self, expr, **kw): 1632 return "NULL" 1633 1634 def visit_true(self, expr, **kw): 1635 if self.dialect.supports_native_boolean: 1636 return "true" 1637 else: 1638 return "1" 1639 1640 def visit_false(self, expr, **kw): 1641 if self.dialect.supports_native_boolean: 1642 return "false" 1643 else: 1644 return "0" 1645 1646 def _generate_delimited_list(self, elements, separator, **kw): 1647 return separator.join( 1648 s 1649 for s in (c._compiler_dispatch(self, **kw) for c in elements) 1650 if s 1651 ) 1652 1653 def _generate_delimited_and_list(self, clauses, **kw): 1654 1655 lcc, clauses = elements.BooleanClauseList._process_clauses_for_boolean( 1656 operators.and_, 1657 elements.True_._singleton, 1658 elements.False_._singleton, 1659 clauses, 1660 ) 1661 if lcc == 1: 1662 return clauses[0]._compiler_dispatch(self, **kw) 1663 else: 1664 separator = OPERATORS[operators.and_] 1665 return separator.join( 1666 s 1667 for s in (c._compiler_dispatch(self, **kw) for c in clauses) 1668 if s 1669 ) 1670 1671 def visit_tuple(self, clauselist, **kw): 1672 return "(%s)" % self.visit_clauselist(clauselist, **kw) 1673 1674 def visit_clauselist(self, clauselist, **kw): 1675 sep = clauselist.operator 1676 if sep is None: 1677 sep = " " 1678 else: 1679 sep = OPERATORS[clauselist.operator] 1680 1681 return self._generate_delimited_list(clauselist.clauses, sep, **kw) 1682 1683 def visit_case(self, clause, **kwargs): 1684 x = "CASE " 1685 if clause.value is not None: 1686 x += clause.value._compiler_dispatch(self, **kwargs) + " " 1687 for cond, result in clause.whens: 1688 x += ( 1689 "WHEN " 1690 + cond._compiler_dispatch(self, **kwargs) 1691 + " THEN " 1692 + result._compiler_dispatch(self, **kwargs) 1693 + " " 1694 ) 1695 if clause.else_ is not None: 1696 x += ( 1697 "ELSE " + clause.else_._compiler_dispatch(self, **kwargs) + " " 1698 ) 1699 x += "END" 1700 return x 1701 1702 def visit_type_coerce(self, type_coerce, **kw): 1703 return type_coerce.typed_expression._compiler_dispatch(self, **kw) 1704 1705 def visit_cast(self, cast, **kwargs): 1706 return "CAST(%s AS %s)" % ( 1707 cast.clause._compiler_dispatch(self, **kwargs), 1708 cast.typeclause._compiler_dispatch(self, **kwargs), 1709 ) 1710 1711 def _format_frame_clause(self, range_, **kw): 1712 1713 return "%s AND %s" % ( 1714 "UNBOUNDED PRECEDING" 1715 if range_[0] is elements.RANGE_UNBOUNDED 1716 else "CURRENT ROW" 1717 if range_[0] is elements.RANGE_CURRENT 1718 else "%s PRECEDING" 1719 % (self.process(elements.literal(abs(range_[0])), **kw),) 1720 if range_[0] < 0 1721 else "%s FOLLOWING" 1722 % (self.process(elements.literal(range_[0]), **kw),), 1723 "UNBOUNDED FOLLOWING" 1724 if range_[1] is elements.RANGE_UNBOUNDED 1725 else "CURRENT ROW" 1726 if range_[1] is elements.RANGE_CURRENT 1727 else "%s PRECEDING" 1728 % (self.process(elements.literal(abs(range_[1])), **kw),) 1729 if range_[1] < 0 1730 else "%s FOLLOWING" 1731 % (self.process(elements.literal(range_[1]), **kw),), 1732 ) 1733 1734 def visit_over(self, over, **kwargs): 1735 if over.range_: 1736 range_ = "RANGE BETWEEN %s" % self._format_frame_clause( 1737 over.range_, **kwargs 1738 ) 1739 elif over.rows: 1740 range_ = "ROWS BETWEEN %s" % self._format_frame_clause( 1741 over.rows, **kwargs 1742 ) 1743 else: 1744 range_ = None 1745 1746 return "%s OVER (%s)" % ( 1747 over.element._compiler_dispatch(self, **kwargs), 1748 " ".join( 1749 [ 1750 "%s BY %s" 1751 % (word, clause._compiler_dispatch(self, **kwargs)) 1752 for word, clause in ( 1753 ("PARTITION", over.partition_by), 1754 ("ORDER", over.order_by), 1755 ) 1756 if clause is not None and len(clause) 1757 ] 1758 + ([range_] if range_ else []) 1759 ), 1760 ) 1761 1762 def visit_withingroup(self, withingroup, **kwargs): 1763 return "%s WITHIN GROUP (ORDER BY %s)" % ( 1764 withingroup.element._compiler_dispatch(self, **kwargs), 1765 withingroup.order_by._compiler_dispatch(self, **kwargs), 1766 ) 1767 1768 def visit_funcfilter(self, funcfilter, **kwargs): 1769 return "%s FILTER (WHERE %s)" % ( 1770 funcfilter.func._compiler_dispatch(self, **kwargs), 1771 funcfilter.criterion._compiler_dispatch(self, **kwargs), 1772 ) 1773 1774 def visit_extract(self, extract, **kwargs): 1775 field = self.extract_map.get(extract.field, extract.field) 1776 return "EXTRACT(%s FROM %s)" % ( 1777 field, 1778 extract.expr._compiler_dispatch(self, **kwargs), 1779 ) 1780 1781 def visit_scalar_function_column(self, element, **kw): 1782 compiled_fn = self.visit_function(element.fn, **kw) 1783 compiled_col = self.visit_column(element, **kw) 1784 return "(%s).%s" % (compiled_fn, compiled_col) 1785 1786 def visit_function(self, func, add_to_result_map=None, **kwargs): 1787 if add_to_result_map is not None: 1788 add_to_result_map(func.name, func.name, (), func.type) 1789 1790 disp = getattr(self, "visit_%s_func" % func.name.lower(), None) 1791 if disp: 1792 text = disp(func, **kwargs) 1793 else: 1794 name = FUNCTIONS.get(func._deannotate().__class__, None) 1795 if name: 1796 if func._has_args: 1797 name += "%(expr)s" 1798 else: 1799 name = func.name 1800 name = ( 1801 self.preparer.quote(name) 1802 if self.preparer._requires_quotes_illegal_chars(name) 1803 or isinstance(name, elements.quoted_name) 1804 else name 1805 ) 1806 name = name + "%(expr)s" 1807 text = ".".join( 1808 [ 1809 ( 1810 self.preparer.quote(tok) 1811 if self.preparer._requires_quotes_illegal_chars(tok) 1812 or isinstance(name, elements.quoted_name) 1813 else tok 1814 ) 1815 for tok in func.packagenames 1816 ] 1817 + [name] 1818 ) % {"expr": self.function_argspec(func, **kwargs)} 1819 1820 if func._with_ordinality: 1821 text += " WITH ORDINALITY" 1822 return text 1823 1824 def visit_next_value_func(self, next_value, **kw): 1825 return self.visit_sequence(next_value.sequence) 1826 1827 def visit_sequence(self, sequence, **kw): 1828 raise NotImplementedError( 1829 "Dialect '%s' does not support sequence increments." 1830 % self.dialect.name 1831 ) 1832 1833 def function_argspec(self, func, **kwargs): 1834 return func.clause_expr._compiler_dispatch(self, **kwargs) 1835 1836 def visit_compound_select( 1837 self, cs, asfrom=False, compound_index=None, **kwargs 1838 ): 1839 toplevel = not self.stack 1840 1841 compile_state = cs._compile_state_factory(cs, self, **kwargs) 1842 1843 if toplevel and not self.compile_state: 1844 self.compile_state = compile_state 1845 1846 compound_stmt = compile_state.statement 1847 1848 entry = self._default_stack_entry if toplevel else self.stack[-1] 1849 need_result_map = toplevel or ( 1850 not compound_index 1851 and entry.get("need_result_map_for_compound", False) 1852 ) 1853 1854 # indicates there is already a CompoundSelect in play 1855 if compound_index == 0: 1856 entry["select_0"] = cs 1857 1858 self.stack.append( 1859 { 1860 "correlate_froms": entry["correlate_froms"], 1861 "asfrom_froms": entry["asfrom_froms"], 1862 "selectable": cs, 1863 "compile_state": compile_state, 1864 "need_result_map_for_compound": need_result_map, 1865 } 1866 ) 1867 1868 if compound_stmt._independent_ctes: 1869 for cte in compound_stmt._independent_ctes: 1870 cte._compiler_dispatch(self, **kwargs) 1871 1872 keyword = self.compound_keywords.get(cs.keyword) 1873 1874 text = (" " + keyword + " ").join( 1875 ( 1876 c._compiler_dispatch( 1877 self, asfrom=asfrom, compound_index=i, **kwargs 1878 ) 1879 for i, c in enumerate(cs.selects) 1880 ) 1881 ) 1882 1883 kwargs["include_table"] = False 1884 text += self.group_by_clause(cs, **dict(asfrom=asfrom, **kwargs)) 1885 text += self.order_by_clause(cs, **kwargs) 1886 if cs._has_row_limiting_clause: 1887 text += self._row_limit_clause(cs, **kwargs) 1888 1889 if self.ctes: 1890 nesting_level = len(self.stack) if not toplevel else None 1891 text = ( 1892 self._render_cte_clause( 1893 nesting_level=nesting_level, include_following_stack=True 1894 ) 1895 + text 1896 ) 1897 1898 self.stack.pop(-1) 1899 return text 1900 1901 def _row_limit_clause(self, cs, **kwargs): 1902 if cs._fetch_clause is not None: 1903 return self.fetch_clause(cs, **kwargs) 1904 else: 1905 return self.limit_clause(cs, **kwargs) 1906 1907 def _get_operator_dispatch(self, operator_, qualifier1, qualifier2): 1908 attrname = "visit_%s_%s%s" % ( 1909 operator_.__name__, 1910 qualifier1, 1911 "_" + qualifier2 if qualifier2 else "", 1912 ) 1913 return getattr(self, attrname, None) 1914 1915 def visit_unary( 1916 self, unary, add_to_result_map=None, result_map_targets=(), **kw 1917 ): 1918 1919 if add_to_result_map is not None: 1920 result_map_targets += (unary,) 1921 kw["add_to_result_map"] = add_to_result_map 1922 kw["result_map_targets"] = result_map_targets 1923 1924 if unary.operator: 1925 if unary.modifier: 1926 raise exc.CompileError( 1927 "Unary expression does not support operator " 1928 "and modifier simultaneously" 1929 ) 1930 disp = self._get_operator_dispatch( 1931 unary.operator, "unary", "operator" 1932 ) 1933 if disp: 1934 return disp(unary, unary.operator, **kw) 1935 else: 1936 return self._generate_generic_unary_operator( 1937 unary, OPERATORS[unary.operator], **kw 1938 ) 1939 elif unary.modifier: 1940 disp = self._get_operator_dispatch( 1941 unary.modifier, "unary", "modifier" 1942 ) 1943 if disp: 1944 return disp(unary, unary.modifier, **kw) 1945 else: 1946 return self._generate_generic_unary_modifier( 1947 unary, OPERATORS[unary.modifier], **kw 1948 ) 1949 else: 1950 raise exc.CompileError( 1951 "Unary expression has no operator or modifier" 1952 ) 1953 1954 def visit_is_true_unary_operator(self, element, operator, **kw): 1955 if ( 1956 element._is_implicitly_boolean 1957 or self.dialect.supports_native_boolean 1958 ): 1959 return self.process(element.element, **kw) 1960 else: 1961 return "%s = 1" % self.process(element.element, **kw) 1962 1963 def visit_is_false_unary_operator(self, element, operator, **kw): 1964 if ( 1965 element._is_implicitly_boolean 1966 or self.dialect.supports_native_boolean 1967 ): 1968 return "NOT %s" % self.process(element.element, **kw) 1969 else: 1970 return "%s = 0" % self.process(element.element, **kw) 1971 1972 def visit_not_match_op_binary(self, binary, operator, **kw): 1973 return "NOT %s" % self.visit_binary( 1974 binary, override_operator=operators.match_op 1975 ) 1976 1977 def visit_not_in_op_binary(self, binary, operator, **kw): 1978 # The brackets are required in the NOT IN operation because the empty 1979 # case is handled using the form "(col NOT IN (null) OR 1 = 1)". 1980 # The presence of the OR makes the brackets required. 1981 return "(%s)" % self._generate_generic_binary( 1982 binary, OPERATORS[operator], **kw 1983 ) 1984 1985 def visit_empty_set_op_expr(self, type_, expand_op): 1986 if expand_op is operators.not_in_op: 1987 if len(type_) > 1: 1988 return "(%s)) OR (1 = 1" % ( 1989 ", ".join("NULL" for element in type_) 1990 ) 1991 else: 1992 return "NULL) OR (1 = 1" 1993 elif expand_op is operators.in_op: 1994 if len(type_) > 1: 1995 return "(%s)) AND (1 != 1" % ( 1996 ", ".join("NULL" for element in type_) 1997 ) 1998 else: 1999 return "NULL) AND (1 != 1" 2000 else: 2001 return self.visit_empty_set_expr(type_) 2002 2003 def visit_empty_set_expr(self, element_types): 2004 raise NotImplementedError( 2005 "Dialect '%s' does not support empty set expression." 2006 % self.dialect.name 2007 ) 2008 2009 def _literal_execute_expanding_parameter_literal_binds( 2010 self, parameter, values 2011 ): 2012 2013 typ_dialect_impl = parameter.type._unwrapped_dialect_impl(self.dialect) 2014 2015 if not values: 2016 if typ_dialect_impl._is_tuple_type: 2017 replacement_expression = ( 2018 "VALUES " if self.dialect.tuple_in_values else "" 2019 ) + self.visit_empty_set_op_expr( 2020 parameter.type.types, parameter.expand_op 2021 ) 2022 2023 else: 2024 replacement_expression = self.visit_empty_set_op_expr( 2025 [parameter.type], parameter.expand_op 2026 ) 2027 2028 elif typ_dialect_impl._is_tuple_type or ( 2029 typ_dialect_impl._isnull 2030 and isinstance(values[0], util.collections_abc.Sequence) 2031 and not isinstance( 2032 values[0], util.string_types + util.binary_types 2033 ) 2034 ): 2035 2036 replacement_expression = ( 2037 "VALUES " if self.dialect.tuple_in_values else "" 2038 ) + ", ".join( 2039 "(%s)" 2040 % ( 2041 ", ".join( 2042 self.render_literal_value(value, param_type) 2043 for value, param_type in zip( 2044 tuple_element, parameter.type.types 2045 ) 2046 ) 2047 ) 2048 for i, tuple_element in enumerate(values) 2049 ) 2050 else: 2051 replacement_expression = ", ".join( 2052 self.render_literal_value(value, parameter.type) 2053 for value in values 2054 ) 2055 2056 return (), replacement_expression 2057 2058 def _literal_execute_expanding_parameter(self, name, parameter, values): 2059 2060 if parameter.literal_execute: 2061 return self._literal_execute_expanding_parameter_literal_binds( 2062 parameter, values 2063 ) 2064 2065 typ_dialect_impl = parameter.type._unwrapped_dialect_impl(self.dialect) 2066 2067 if not values: 2068 to_update = [] 2069 if typ_dialect_impl._is_tuple_type: 2070 2071 replacement_expression = self.visit_empty_set_op_expr( 2072 parameter.type.types, parameter.expand_op 2073 ) 2074 else: 2075 replacement_expression = self.visit_empty_set_op_expr( 2076 [parameter.type], parameter.expand_op 2077 ) 2078 2079 elif typ_dialect_impl._is_tuple_type or ( 2080 typ_dialect_impl._isnull 2081 and isinstance(values[0], util.collections_abc.Sequence) 2082 and not isinstance( 2083 values[0], util.string_types + util.binary_types 2084 ) 2085 ): 2086 assert not typ_dialect_impl._is_array 2087 to_update = [ 2088 ("%s_%s_%s" % (name, i, j), value) 2089 for i, tuple_element in enumerate(values, 1) 2090 for j, value in enumerate(tuple_element, 1) 2091 ] 2092 replacement_expression = ( 2093 "VALUES " if self.dialect.tuple_in_values else "" 2094 ) + ", ".join( 2095 "(%s)" 2096 % ( 2097 ", ".join( 2098 self.bindtemplate 2099 % {"name": to_update[i * len(tuple_element) + j][0]} 2100 for j, value in enumerate(tuple_element) 2101 ) 2102 ) 2103 for i, tuple_element in enumerate(values) 2104 ) 2105 else: 2106 to_update = [ 2107 ("%s_%s" % (name, i), value) 2108 for i, value in enumerate(values, 1) 2109 ] 2110 replacement_expression = ", ".join( 2111 self.bindtemplate % {"name": key} for key, value in to_update 2112 ) 2113 2114 return to_update, replacement_expression 2115 2116 def visit_binary( 2117 self, 2118 binary, 2119 override_operator=None, 2120 eager_grouping=False, 2121 from_linter=None, 2122 lateral_from_linter=None, 2123 **kw 2124 ): 2125 if from_linter and operators.is_comparison(binary.operator): 2126 if lateral_from_linter is not None: 2127 enclosing_lateral = kw["enclosing_lateral"] 2128 lateral_from_linter.edges.update( 2129 itertools.product( 2130 binary.left._from_objects + [enclosing_lateral], 2131 binary.right._from_objects + [enclosing_lateral], 2132 ) 2133 ) 2134 else: 2135 from_linter.edges.update( 2136 itertools.product( 2137 binary.left._from_objects, binary.right._from_objects 2138 ) 2139 ) 2140 2141 # don't allow "? = ?" to render 2142 if ( 2143 self.ansi_bind_rules 2144 and isinstance(binary.left, elements.BindParameter) 2145 and isinstance(binary.right, elements.BindParameter) 2146 ): 2147 kw["literal_execute"] = True 2148 2149 operator_ = override_operator or binary.operator 2150 disp = self._get_operator_dispatch(operator_, "binary", None) 2151 if disp: 2152 return disp(binary, operator_, **kw) 2153 else: 2154 try: 2155 opstring = OPERATORS[operator_] 2156 except KeyError as err: 2157 util.raise_( 2158 exc.UnsupportedCompilationError(self, operator_), 2159 replace_context=err, 2160 ) 2161 else: 2162 return self._generate_generic_binary( 2163 binary, 2164 opstring, 2165 from_linter=from_linter, 2166 lateral_from_linter=lateral_from_linter, 2167 **kw 2168 ) 2169 2170 def visit_function_as_comparison_op_binary(self, element, operator, **kw): 2171 return self.process(element.sql_function, **kw) 2172 2173 def visit_mod_binary(self, binary, operator, **kw): 2174 if self.preparer._double_percents: 2175 return ( 2176 self.process(binary.left, **kw) 2177 + " %% " 2178 + self.process(binary.right, **kw) 2179 ) 2180 else: 2181 return ( 2182 self.process(binary.left, **kw) 2183 + " % " 2184 + self.process(binary.right, **kw) 2185 ) 2186 2187 def visit_custom_op_binary(self, element, operator, **kw): 2188 kw["eager_grouping"] = operator.eager_grouping 2189 return self._generate_generic_binary( 2190 element, 2191 " " + self.escape_literal_column(operator.opstring) + " ", 2192 **kw 2193 ) 2194 2195 def visit_custom_op_unary_operator(self, element, operator, **kw): 2196 return self._generate_generic_unary_operator( 2197 element, self.escape_literal_column(operator.opstring) + " ", **kw 2198 ) 2199 2200 def visit_custom_op_unary_modifier(self, element, operator, **kw): 2201 return self._generate_generic_unary_modifier( 2202 element, " " + self.escape_literal_column(operator.opstring), **kw 2203 ) 2204 2205 def _generate_generic_binary( 2206 self, binary, opstring, eager_grouping=False, **kw 2207 ): 2208 2209 _in_binary = kw.get("_in_binary", False) 2210 2211 kw["_in_binary"] = True 2212 kw["_binary_op"] = binary.operator 2213 text = ( 2214 binary.left._compiler_dispatch( 2215 self, eager_grouping=eager_grouping, **kw 2216 ) 2217 + opstring 2218 + binary.right._compiler_dispatch( 2219 self, eager_grouping=eager_grouping, **kw 2220 ) 2221 ) 2222 2223 if _in_binary and eager_grouping: 2224 text = "(%s)" % text 2225 return text 2226 2227 def _generate_generic_unary_operator(self, unary, opstring, **kw): 2228 return opstring + unary.element._compiler_dispatch(self, **kw) 2229 2230 def _generate_generic_unary_modifier(self, unary, opstring, **kw): 2231 return unary.element._compiler_dispatch(self, **kw) + opstring 2232 2233 @util.memoized_property 2234 def _like_percent_literal(self): 2235 return elements.literal_column("'%'", type_=sqltypes.STRINGTYPE) 2236 2237 def visit_contains_op_binary(self, binary, operator, **kw): 2238 binary = binary._clone() 2239 percent = self._like_percent_literal 2240 binary.right = percent.__add__(binary.right).__add__(percent) 2241 return self.visit_like_op_binary(binary, operator, **kw) 2242 2243 def visit_not_contains_op_binary(self, binary, operator, **kw): 2244 binary = binary._clone() 2245 percent = self._like_percent_literal 2246 binary.right = percent.__add__(binary.right).__add__(percent) 2247 return self.visit_not_like_op_binary(binary, operator, **kw) 2248 2249 def visit_startswith_op_binary(self, binary, operator, **kw): 2250 binary = binary._clone() 2251 percent = self._like_percent_literal 2252 binary.right = percent.__radd__(binary.right) 2253 return self.visit_like_op_binary(binary, operator, **kw) 2254 2255 def visit_not_startswith_op_binary(self, binary, operator, **kw): 2256 binary = binary._clone() 2257 percent = self._like_percent_literal 2258 binary.right = percent.__radd__(binary.right) 2259 return self.visit_not_like_op_binary(binary, operator, **kw) 2260 2261 def visit_endswith_op_binary(self, binary, operator, **kw): 2262 binary = binary._clone() 2263 percent = self._like_percent_literal 2264 binary.right = percent.__add__(binary.right) 2265 return self.visit_like_op_binary(binary, operator, **kw) 2266 2267 def visit_not_endswith_op_binary(self, binary, operator, **kw): 2268 binary = binary._clone() 2269 percent = self._like_percent_literal 2270 binary.right = percent.__add__(binary.right) 2271 return self.visit_not_like_op_binary(binary, operator, **kw) 2272 2273 def visit_like_op_binary(self, binary, operator, **kw): 2274 escape = binary.modifiers.get("escape", None) 2275 2276 # TODO: use ternary here, not "and"/ "or" 2277 return "%s LIKE %s" % ( 2278 binary.left._compiler_dispatch(self, **kw), 2279 binary.right._compiler_dispatch(self, **kw), 2280 ) + ( 2281 " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE) 2282 if escape 2283 else "" 2284 ) 2285 2286 def visit_not_like_op_binary(self, binary, operator, **kw): 2287 escape = binary.modifiers.get("escape", None) 2288 return "%s NOT LIKE %s" % ( 2289 binary.left._compiler_dispatch(self, **kw), 2290 binary.right._compiler_dispatch(self, **kw), 2291 ) + ( 2292 " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE) 2293 if escape 2294 else "" 2295 ) 2296 2297 def visit_ilike_op_binary(self, binary, operator, **kw): 2298 escape = binary.modifiers.get("escape", None) 2299 return "lower(%s) LIKE lower(%s)" % ( 2300 binary.left._compiler_dispatch(self, **kw), 2301 binary.right._compiler_dispatch(self, **kw), 2302 ) + ( 2303 " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE) 2304 if escape 2305 else "" 2306 ) 2307 2308 def visit_not_ilike_op_binary(self, binary, operator, **kw): 2309 escape = binary.modifiers.get("escape", None) 2310 return "lower(%s) NOT LIKE lower(%s)" % ( 2311 binary.left._compiler_dispatch(self, **kw), 2312 binary.right._compiler_dispatch(self, **kw), 2313 ) + ( 2314 " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE) 2315 if escape 2316 else "" 2317 ) 2318 2319 def visit_between_op_binary(self, binary, operator, **kw): 2320 symmetric = binary.modifiers.get("symmetric", False) 2321 return self._generate_generic_binary( 2322 binary, " BETWEEN SYMMETRIC " if symmetric else " BETWEEN ", **kw 2323 ) 2324 2325 def visit_not_between_op_binary(self, binary, operator, **kw): 2326 symmetric = binary.modifiers.get("symmetric", False) 2327 return self._generate_generic_binary( 2328 binary, 2329 " NOT BETWEEN SYMMETRIC " if symmetric else " NOT BETWEEN ", 2330 **kw 2331 ) 2332 2333 def visit_regexp_match_op_binary(self, binary, operator, **kw): 2334 raise exc.CompileError( 2335 "%s dialect does not support regular expressions" 2336 % self.dialect.name 2337 ) 2338 2339 def visit_not_regexp_match_op_binary(self, binary, operator, **kw): 2340 raise exc.CompileError( 2341 "%s dialect does not support regular expressions" 2342 % self.dialect.name 2343 ) 2344 2345 def visit_regexp_replace_op_binary(self, binary, operator, **kw): 2346 raise exc.CompileError( 2347 "%s dialect does not support regular expression replacements" 2348 % self.dialect.name 2349 ) 2350 2351 def visit_bindparam( 2352 self, 2353 bindparam, 2354 within_columns_clause=False, 2355 literal_binds=False, 2356 skip_bind_expression=False, 2357 literal_execute=False, 2358 render_postcompile=False, 2359 **kwargs 2360 ): 2361 if not skip_bind_expression: 2362 impl = bindparam.type.dialect_impl(self.dialect) 2363 if impl._has_bind_expression: 2364 bind_expression = impl.bind_expression(bindparam) 2365 wrapped = self.process( 2366 bind_expression, 2367 skip_bind_expression=True, 2368 within_columns_clause=within_columns_clause, 2369 literal_binds=literal_binds, 2370 literal_execute=literal_execute, 2371 render_postcompile=render_postcompile, 2372 **kwargs 2373 ) 2374 if bindparam.expanding: 2375 # for postcompile w/ expanding, move the "wrapped" part 2376 # of this into the inside 2377 m = re.match( 2378 r"^(.*)\(__\[POSTCOMPILE_(\S+?)\]\)(.*)$", wrapped 2379 ) 2380 wrapped = "(__[POSTCOMPILE_%s~~%s~~REPL~~%s~~])" % ( 2381 m.group(2), 2382 m.group(1), 2383 m.group(3), 2384 ) 2385 return wrapped 2386 2387 if not literal_binds: 2388 literal_execute = ( 2389 literal_execute 2390 or bindparam.literal_execute 2391 or (within_columns_clause and self.ansi_bind_rules) 2392 ) 2393 post_compile = literal_execute or bindparam.expanding 2394 else: 2395 post_compile = False 2396 2397 if literal_binds: 2398 ret = self.render_literal_bindparam( 2399 bindparam, within_columns_clause=True, **kwargs 2400 ) 2401 if bindparam.expanding: 2402 ret = "(%s)" % ret 2403 return ret 2404 2405 name = self._truncate_bindparam(bindparam) 2406 2407 if name in self.binds: 2408 existing = self.binds[name] 2409 if existing is not bindparam: 2410 if ( 2411 (existing.unique or bindparam.unique) 2412 and not existing.proxy_set.intersection( 2413 bindparam.proxy_set 2414 ) 2415 and not existing._cloned_set.intersection( 2416 bindparam._cloned_set 2417 ) 2418 ): 2419 raise exc.CompileError( 2420 "Bind parameter '%s' conflicts with " 2421 "unique bind parameter of the same name" % name 2422 ) 2423 elif existing._is_crud or bindparam._is_crud: 2424 raise exc.CompileError( 2425 "bindparam() name '%s' is reserved " 2426 "for automatic usage in the VALUES or SET " 2427 "clause of this " 2428 "insert/update statement. Please use a " 2429 "name other than column name when using bindparam() " 2430 "with insert() or update() (for example, 'b_%s')." 2431 % (bindparam.key, bindparam.key) 2432 ) 2433 2434 self.binds[bindparam.key] = self.binds[name] = bindparam 2435 2436 # if we are given a cache key that we're going to match against, 2437 # relate the bindparam here to one that is most likely present 2438 # in the "extracted params" portion of the cache key. this is used 2439 # to set up a positional mapping that is used to determine the 2440 # correct parameters for a subsequent use of this compiled with 2441 # a different set of parameter values. here, we accommodate for 2442 # parameters that may have been cloned both before and after the cache 2443 # key was been generated. 2444 ckbm = self._cache_key_bind_match 2445 if ckbm: 2446 for bp in bindparam._cloned_set: 2447 if bp.key in ckbm: 2448 cb = ckbm[bp.key] 2449 ckbm[cb].append(bindparam) 2450 2451 if bindparam.isoutparam: 2452 self.has_out_parameters = True 2453 2454 if post_compile: 2455 if render_postcompile: 2456 self._render_postcompile = True 2457 2458 if literal_execute: 2459 self.literal_execute_params |= {bindparam} 2460 else: 2461 self.post_compile_params |= {bindparam} 2462 2463 ret = self.bindparam_string( 2464 name, 2465 post_compile=post_compile, 2466 expanding=bindparam.expanding, 2467 **kwargs 2468 ) 2469 2470 if bindparam.expanding: 2471 ret = "(%s)" % ret 2472 return ret 2473 2474 def render_literal_bindparam( 2475 self, bindparam, render_literal_value=NO_ARG, **kw 2476 ): 2477 if render_literal_value is not NO_ARG: 2478 value = render_literal_value 2479 else: 2480 if bindparam.value is None and bindparam.callable is None: 2481 op = kw.get("_binary_op", None) 2482 if op and op not in (operators.is_, operators.is_not): 2483 util.warn_limited( 2484 "Bound parameter '%s' rendering literal NULL in a SQL " 2485 "expression; comparisons to NULL should not use " 2486 "operators outside of 'is' or 'is not'", 2487 (bindparam.key,), 2488 ) 2489 return self.process(sqltypes.NULLTYPE, **kw) 2490 value = bindparam.effective_value 2491 2492 if bindparam.expanding: 2493 leep = self._literal_execute_expanding_parameter_literal_binds 2494 to_update, replacement_expr = leep(bindparam, value) 2495 return replacement_expr 2496 else: 2497 return self.render_literal_value(value, bindparam.type) 2498 2499 def render_literal_value(self, value, type_): 2500 """Render the value of a bind parameter as a quoted literal. 2501 2502 This is used for statement sections that do not accept bind parameters 2503 on the target driver/database. 2504 2505 This should be implemented by subclasses using the quoting services 2506 of the DBAPI. 2507 2508 """ 2509 2510 processor = type_._cached_literal_processor(self.dialect) 2511 if processor: 2512 return processor(value) 2513 else: 2514 raise NotImplementedError( 2515 "Don't know how to literal-quote value %r" % value 2516 ) 2517 2518 def _truncate_bindparam(self, bindparam): 2519 if bindparam in self.bind_names: 2520 return self.bind_names[bindparam] 2521 2522 bind_name = bindparam.key 2523 if isinstance(bind_name, elements._truncated_label): 2524 bind_name = self._truncated_identifier("bindparam", bind_name) 2525 2526 # add to bind_names for translation 2527 self.bind_names[bindparam] = bind_name 2528 2529 return bind_name 2530 2531 def _truncated_identifier(self, ident_class, name): 2532 if (ident_class, name) in self.truncated_names: 2533 return self.truncated_names[(ident_class, name)] 2534 2535 anonname = name.apply_map(self.anon_map) 2536 2537 if len(anonname) > self.label_length - 6: 2538 counter = self.truncated_names.get(ident_class, 1) 2539 truncname = ( 2540 anonname[0 : max(self.label_length - 6, 0)] 2541 + "_" 2542 + hex(counter)[2:] 2543 ) 2544 self.truncated_names[ident_class] = counter + 1 2545 else: 2546 truncname = anonname 2547 self.truncated_names[(ident_class, name)] = truncname 2548 return truncname 2549 2550 def _anonymize(self, name): 2551 return name % self.anon_map 2552 2553 def bindparam_string( 2554 self, 2555 name, 2556 positional_names=None, 2557 post_compile=False, 2558 expanding=False, 2559 escaped_from=None, 2560 **kw 2561 ): 2562 2563 if self.positional: 2564 if positional_names is not None: 2565 positional_names.append(name) 2566 else: 2567 self.positiontup.append(name) 2568 elif not escaped_from: 2569 2570 if _BIND_TRANSLATE_RE.search(name): 2571 # not quite the translate use case as we want to 2572 # also get a quick boolean if we even found 2573 # unusual characters in the name 2574 new_name = _BIND_TRANSLATE_RE.sub( 2575 lambda m: _BIND_TRANSLATE_CHARS[m.group(0)], 2576 name, 2577 ) 2578 escaped_from = name 2579 name = new_name 2580 2581 if escaped_from: 2582 if not self.escaped_bind_names: 2583 self.escaped_bind_names = {} 2584 self.escaped_bind_names[escaped_from] = name 2585 if post_compile: 2586 return "__[POSTCOMPILE_%s]" % name 2587 else: 2588 return self.bindtemplate % {"name": name} 2589 2590 def visit_cte( 2591 self, 2592 cte, 2593 asfrom=False, 2594 ashint=False, 2595 fromhints=None, 2596 visiting_cte=None, 2597 from_linter=None, 2598 **kwargs 2599 ): 2600 self._init_cte_state() 2601 2602 kwargs["visiting_cte"] = cte 2603 2604 cte_name = cte.name 2605 2606 if isinstance(cte_name, elements._truncated_label): 2607 cte_name = self._truncated_identifier("alias", cte_name) 2608 2609 is_new_cte = True 2610 embedded_in_current_named_cte = False 2611 2612 _reference_cte = cte._get_reference_cte() 2613 2614 if _reference_cte in self.level_name_by_cte: 2615 cte_level, _ = self.level_name_by_cte[_reference_cte] 2616 assert _ == cte_name 2617 else: 2618 cte_level = len(self.stack) if cte.nesting else 1 2619 2620 cte_level_name = (cte_level, cte_name) 2621 if cte_level_name in self.ctes_by_level_name: 2622 existing_cte = self.ctes_by_level_name[cte_level_name] 2623 embedded_in_current_named_cte = visiting_cte is existing_cte 2624 2625 # we've generated a same-named CTE that we are enclosed in, 2626 # or this is the same CTE. just return the name. 2627 if cte is existing_cte._restates or cte is existing_cte: 2628 is_new_cte = False 2629 elif existing_cte is cte._restates: 2630 # we've generated a same-named CTE that is 2631 # enclosed in us - we take precedence, so 2632 # discard the text for the "inner". 2633 del self.ctes[existing_cte] 2634 2635 existing_cte_reference_cte = existing_cte._get_reference_cte() 2636 2637 # TODO: determine if these assertions are correct. they 2638 # pass for current test cases 2639 # assert existing_cte_reference_cte is _reference_cte 2640 # assert existing_cte_reference_cte is existing_cte 2641 2642 del self.level_name_by_cte[existing_cte_reference_cte] 2643 else: 2644 raise exc.CompileError( 2645 "Multiple, unrelated CTEs found with " 2646 "the same name: %r" % cte_name 2647 ) 2648 2649 if not asfrom and not is_new_cte: 2650 return None 2651 2652 if cte._cte_alias is not None: 2653 pre_alias_cte = cte._cte_alias 2654 cte_pre_alias_name = cte._cte_alias.name 2655 if isinstance(cte_pre_alias_name, elements._truncated_label): 2656 cte_pre_alias_name = self._truncated_identifier( 2657 "alias", cte_pre_alias_name 2658 ) 2659 else: 2660 pre_alias_cte = cte 2661 cte_pre_alias_name = None 2662 2663 if is_new_cte: 2664 self.ctes_by_level_name[cte_level_name] = cte 2665 self.level_name_by_cte[_reference_cte] = cte_level_name 2666 2667 if ( 2668 "autocommit" in cte.element._execution_options 2669 and "autocommit" not in self.execution_options 2670 ): 2671 self.execution_options = self.execution_options.union( 2672 { 2673 "autocommit": cte.element._execution_options[ 2674 "autocommit" 2675 ] 2676 } 2677 ) 2678 2679 if pre_alias_cte not in self.ctes: 2680 self.visit_cte(pre_alias_cte, **kwargs) 2681 2682 if not cte_pre_alias_name and cte not in self.ctes: 2683 if cte.recursive: 2684 self.ctes_recursive = True 2685 text = self.preparer.format_alias(cte, cte_name) 2686 if cte.recursive: 2687 if isinstance(cte.element, selectable.Select): 2688 col_source = cte.element 2689 elif isinstance(cte.element, selectable.CompoundSelect): 2690 col_source = cte.element.selects[0] 2691 else: 2692 assert False, "cte should only be against SelectBase" 2693 2694 # TODO: can we get at the .columns_plus_names collection 2695 # that is already (or will be?) generated for the SELECT 2696 # rather than calling twice? 2697 recur_cols = [ 2698 # TODO: proxy_name is not technically safe, 2699 # see test_cte-> 2700 # test_with_recursive_no_name_currently_buggy. not 2701 # clear what should be done with such a case 2702 fallback_label_name or proxy_name 2703 for ( 2704 _, 2705 proxy_name, 2706 fallback_label_name, 2707 c, 2708 repeated, 2709 ) in (col_source._generate_columns_plus_names(True)) 2710 if not repeated 2711 ] 2712 2713 text += "(%s)" % ( 2714 ", ".join( 2715 self.preparer.format_label_name( 2716 ident, anon_map=self.anon_map 2717 ) 2718 for ident in recur_cols 2719 ) 2720 ) 2721 2722 if self.positional: 2723 kwargs["positional_names"] = self.cte_positional[cte] = [] 2724 2725 assert kwargs.get("subquery", False) is False 2726 2727 if not self.stack: 2728 # toplevel, this is a stringify of the 2729 # cte directly. just compile the inner 2730 # the way alias() does. 2731 return cte.element._compiler_dispatch( 2732 self, asfrom=asfrom, **kwargs 2733 ) 2734 else: 2735 prefixes = self._generate_prefixes( 2736 cte, cte._prefixes, **kwargs 2737 ) 2738 inner = cte.element._compiler_dispatch( 2739 self, asfrom=True, **kwargs 2740 ) 2741 2742 text += " AS %s\n(%s)" % (prefixes, inner) 2743 2744 if cte._suffixes: 2745 text += " " + self._generate_prefixes( 2746 cte, cte._suffixes, **kwargs 2747 ) 2748 2749 self.ctes[cte] = text 2750 2751 if asfrom: 2752 if from_linter: 2753 from_linter.froms[cte] = cte_name 2754 2755 if not is_new_cte and embedded_in_current_named_cte: 2756 return self.preparer.format_alias(cte, cte_name) 2757 2758 if cte_pre_alias_name: 2759 text = self.preparer.format_alias(cte, cte_pre_alias_name) 2760 if self.preparer._requires_quotes(cte_name): 2761 cte_name = self.preparer.quote(cte_name) 2762 text += self.get_render_as_alias_suffix(cte_name) 2763 return text 2764 else: 2765 return self.preparer.format_alias(cte, cte_name) 2766 2767 def visit_table_valued_alias(self, element, **kw): 2768 if element._is_lateral: 2769 return self.visit_lateral(element, **kw) 2770 else: 2771 return self.visit_alias(element, **kw) 2772 2773 def visit_table_valued_column(self, element, **kw): 2774 return self.visit_column(element, **kw) 2775 2776 def visit_alias( 2777 self, 2778 alias, 2779 asfrom=False, 2780 ashint=False, 2781 iscrud=False, 2782 fromhints=None, 2783 subquery=False, 2784 lateral=False, 2785 enclosing_alias=None, 2786 from_linter=None, 2787 **kwargs 2788 ): 2789 2790 if lateral: 2791 if "enclosing_lateral" not in kwargs: 2792 # if lateral is set and enclosing_lateral is not 2793 # present, we assume we are being called directly 2794 # from visit_lateral() and we need to set enclosing_lateral. 2795 assert alias._is_lateral 2796 kwargs["enclosing_lateral"] = alias 2797 2798 # for lateral objects, we track a second from_linter that is... 2799 # lateral! to the level above us. 2800 if ( 2801 from_linter 2802 and "lateral_from_linter" not in kwargs 2803 and "enclosing_lateral" in kwargs 2804 ): 2805 kwargs["lateral_from_linter"] = from_linter 2806 2807 if enclosing_alias is not None and enclosing_alias.element is alias: 2808 inner = alias.element._compiler_dispatch( 2809 self, 2810 asfrom=asfrom, 2811 ashint=ashint, 2812 iscrud=iscrud, 2813 fromhints=fromhints, 2814 lateral=lateral, 2815 enclosing_alias=alias, 2816 **kwargs 2817 ) 2818 if subquery and (asfrom or lateral): 2819 inner = "(%s)" % (inner,) 2820 return inner 2821 else: 2822 enclosing_alias = kwargs["enclosing_alias"] = alias 2823 2824 if asfrom or ashint: 2825 if isinstance(alias.name, elements._truncated_label): 2826 alias_name = self._truncated_identifier("alias", alias.name) 2827 else: 2828 alias_name = alias.name 2829 2830 if ashint: 2831 return self.preparer.format_alias(alias, alias_name) 2832 elif asfrom: 2833 if from_linter: 2834 from_linter.froms[alias] = alias_name 2835 2836 inner = alias.element._compiler_dispatch( 2837 self, asfrom=True, lateral=lateral, **kwargs 2838 ) 2839 if subquery: 2840 inner = "(%s)" % (inner,) 2841 2842 ret = inner + self.get_render_as_alias_suffix( 2843 self.preparer.format_alias(alias, alias_name) 2844 ) 2845 2846 if alias._supports_derived_columns and alias._render_derived: 2847 ret += "(%s)" % ( 2848 ", ".join( 2849 "%s%s" 2850 % ( 2851 self.preparer.quote(col.name), 2852 " %s" 2853 % self.dialect.type_compiler.process( 2854 col.type, **kwargs 2855 ) 2856 if alias._render_derived_w_types 2857 else "", 2858 ) 2859 for col in alias.c 2860 ) 2861 ) 2862 2863 if fromhints and alias in fromhints: 2864 ret = self.format_from_hint_text( 2865 ret, alias, fromhints[alias], iscrud 2866 ) 2867 2868 return ret 2869 else: 2870 # note we cancel the "subquery" flag here as well 2871 return alias.element._compiler_dispatch( 2872 self, lateral=lateral, **kwargs 2873 ) 2874 2875 def visit_subquery(self, subquery, **kw): 2876 kw["subquery"] = True 2877 return self.visit_alias(subquery, **kw) 2878 2879 def visit_lateral(self, lateral_, **kw): 2880 kw["lateral"] = True 2881 return "LATERAL %s" % self.visit_alias(lateral_, **kw) 2882 2883 def visit_tablesample(self, tablesample, asfrom=False, **kw): 2884 text = "%s TABLESAMPLE %s" % ( 2885 self.visit_alias(tablesample, asfrom=True, **kw), 2886 tablesample._get_method()._compiler_dispatch(self, **kw), 2887 ) 2888 2889 if tablesample.seed is not None: 2890 text += " REPEATABLE (%s)" % ( 2891 tablesample.seed._compiler_dispatch(self, **kw) 2892 ) 2893 2894 return text 2895 2896 def visit_values(self, element, asfrom=False, from_linter=None, **kw): 2897 kw.setdefault("literal_binds", element.literal_binds) 2898 v = "VALUES %s" % ", ".join( 2899 self.process( 2900 elements.Tuple( 2901 types=element._column_types, *elem 2902 ).self_group(), 2903 **kw 2904 ) 2905 for chunk in element._data 2906 for elem in chunk 2907 ) 2908 2909 if isinstance(element.name, elements._truncated_label): 2910 name = self._truncated_identifier("values", element.name) 2911 else: 2912 name = element.name 2913 2914 if element._is_lateral: 2915 lateral = "LATERAL " 2916 else: 2917 lateral = "" 2918 2919 if asfrom: 2920 if from_linter: 2921 from_linter.froms[element] = ( 2922 name if name is not None else "(unnamed VALUES element)" 2923 ) 2924 2925 if name: 2926 v = "%s(%s)%s (%s)" % ( 2927 lateral, 2928 v, 2929 self.get_render_as_alias_suffix(self.preparer.quote(name)), 2930 ( 2931 ", ".join( 2932 c._compiler_dispatch( 2933 self, include_table=False, **kw 2934 ) 2935 for c in element.columns 2936 ) 2937 ), 2938 ) 2939 else: 2940 v = "%s(%s)" % (lateral, v) 2941 return v 2942 2943 def get_render_as_alias_suffix(self, alias_name_text): 2944 return " AS " + alias_name_text 2945 2946 def _add_to_result_map(self, keyname, name, objects, type_): 2947 if keyname is None or keyname == "*": 2948 self._ordered_columns = False 2949 self._textual_ordered_columns = True 2950 if type_._is_tuple_type: 2951 raise exc.CompileError( 2952 "Most backends don't support SELECTing " 2953 "from a tuple() object. If this is an ORM query, " 2954 "consider using the Bundle object." 2955 ) 2956 self._result_columns.append((keyname, name, objects, type_)) 2957 2958 def _label_returning_column(self, stmt, column, column_clause_args=None): 2959 """Render a column with necessary labels inside of a RETURNING clause. 2960 2961 This method is provided for individual dialects in place of calling 2962 the _label_select_column method directly, so that the two use cases 2963 of RETURNING vs. SELECT can be disambiguated going forward. 2964 2965 .. versionadded:: 1.4.21 2966 2967 """ 2968 return self._label_select_column( 2969 None, 2970 column, 2971 True, 2972 False, 2973 {} if column_clause_args is None else column_clause_args, 2974 ) 2975 2976 def _label_select_column( 2977 self, 2978 select, 2979 column, 2980 populate_result_map, 2981 asfrom, 2982 column_clause_args, 2983 name=None, 2984 proxy_name=None, 2985 fallback_label_name=None, 2986 within_columns_clause=True, 2987 column_is_repeated=False, 2988 need_column_expressions=False, 2989 ): 2990 """produce labeled columns present in a select().""" 2991 impl = column.type.dialect_impl(self.dialect) 2992 2993 if impl._has_column_expression and ( 2994 need_column_expressions or populate_result_map 2995 ): 2996 col_expr = impl.column_expression(column) 2997 else: 2998 col_expr = column 2999 3000 if populate_result_map: 3001 # pass an "add_to_result_map" callable into the compilation 3002 # of embedded columns. this collects information about the 3003 # column as it will be fetched in the result and is coordinated 3004 # with cursor.description when the query is executed. 3005 add_to_result_map = self._add_to_result_map 3006 3007 # if the SELECT statement told us this column is a repeat, 3008 # wrap the callable with one that prevents the addition of the 3009 # targets 3010 if column_is_repeated: 3011 _add_to_result_map = add_to_result_map 3012 3013 def add_to_result_map(keyname, name, objects, type_): 3014 _add_to_result_map(keyname, name, (), type_) 3015 3016 # if we redefined col_expr for type expressions, wrap the 3017 # callable with one that adds the original column to the targets 3018 elif col_expr is not column: 3019 _add_to_result_map = add_to_result_map 3020 3021 def add_to_result_map(keyname, name, objects, type_): 3022 _add_to_result_map( 3023 keyname, name, (column,) + objects, type_ 3024 ) 3025 3026 else: 3027 add_to_result_map = None 3028 3029 # this method is used by some of the dialects for RETURNING, 3030 # which has different inputs. _label_returning_column was added 3031 # as the better target for this now however for 1.4 we will keep 3032 # _label_select_column directly compatible with this use case. 3033 # these assertions right now set up the current expected inputs 3034 assert within_columns_clause, ( 3035 "_label_select_column is only relevant within " 3036 "the columns clause of a SELECT or RETURNING" 3037 ) 3038 3039 if isinstance(column, elements.Label): 3040 if col_expr is not column: 3041 result_expr = _CompileLabel( 3042 col_expr, column.name, alt_names=(column.element,) 3043 ) 3044 else: 3045 result_expr = col_expr 3046 3047 elif name: 3048 # here, _columns_plus_names has determined there's an explicit 3049 # label name we need to use. this is the default for 3050 # tablenames_plus_columnnames as well as when columns are being 3051 # deduplicated on name 3052 3053 assert ( 3054 proxy_name is not None 3055 ), "proxy_name is required if 'name' is passed" 3056 3057 result_expr = _CompileLabel( 3058 col_expr, 3059 name, 3060 alt_names=( 3061 proxy_name, 3062 # this is a hack to allow legacy result column lookups 3063 # to work as they did before; this goes away in 2.0. 3064 # TODO: this only seems to be tested indirectly 3065 # via test/orm/test_deprecations.py. should be a 3066 # resultset test for this 3067 column._tq_label, 3068 ), 3069 ) 3070 else: 3071 # determine here whether this column should be rendered in 3072 # a labelled context or not, as we were given no required label 3073 # name from the caller. Here we apply heuristics based on the kind 3074 # of SQL expression involved. 3075 3076 if col_expr is not column: 3077 # type-specific expression wrapping the given column, 3078 # so we render a label 3079 render_with_label = True 3080 elif isinstance(column, elements.ColumnClause): 3081 # table-bound column, we render its name as a label if we are 3082 # inside of a subquery only 3083 render_with_label = ( 3084 asfrom 3085 and not column.is_literal 3086 and column.table is not None 3087 ) 3088 elif isinstance(column, elements.TextClause): 3089 render_with_label = False 3090 elif isinstance(column, elements.UnaryExpression): 3091 render_with_label = column.wraps_column_expression or asfrom 3092 elif ( 3093 # general class of expressions that don't have a SQL-column 3094 # addressible name. includes scalar selects, bind parameters, 3095 # SQL functions, others 3096 not isinstance(column, elements.NamedColumn) 3097 # deeper check that indicates there's no natural "name" to 3098 # this element, which accommodates for custom SQL constructs 3099 # that might have a ".name" attribute (but aren't SQL 3100 # functions) but are not implementing this more recently added 3101 # base class. in theory the "NamedColumn" check should be 3102 # enough, however here we seek to maintain legacy behaviors 3103 # as well. 3104 and column._non_anon_label is None 3105 ): 3106 render_with_label = True 3107 else: 3108 render_with_label = False 3109 3110 if render_with_label: 3111 if not fallback_label_name: 3112 # used by the RETURNING case right now. we generate it 3113 # here as 3rd party dialects may be referring to 3114 # _label_select_column method directly instead of the 3115 # just-added _label_returning_column method 3116 assert not column_is_repeated 3117 fallback_label_name = column._anon_name_label 3118 3119 fallback_label_name = ( 3120 elements._truncated_label(fallback_label_name) 3121 if not isinstance( 3122 fallback_label_name, elements._truncated_label 3123 ) 3124 else fallback_label_name 3125 ) 3126 3127 result_expr = _CompileLabel( 3128 col_expr, fallback_label_name, alt_names=(proxy_name,) 3129 ) 3130 else: 3131 result_expr = col_expr 3132 3133 column_clause_args.update( 3134 within_columns_clause=within_columns_clause, 3135 add_to_result_map=add_to_result_map, 3136 ) 3137 return result_expr._compiler_dispatch(self, **column_clause_args) 3138 3139 def format_from_hint_text(self, sqltext, table, hint, iscrud): 3140 hinttext = self.get_from_hint_text(table, hint) 3141 if hinttext: 3142 sqltext += " " + hinttext 3143 return sqltext 3144 3145 def get_select_hint_text(self, byfroms): 3146 return None 3147 3148 def get_from_hint_text(self, table, text): 3149 return None 3150 3151 def get_crud_hint_text(self, table, text): 3152 return None 3153 3154 def get_statement_hint_text(self, hint_texts): 3155 return " ".join(hint_texts) 3156 3157 _default_stack_entry = util.immutabledict( 3158 [("correlate_froms", frozenset()), ("asfrom_froms", frozenset())] 3159 ) 3160 3161 def _display_froms_for_select( 3162 self, select_stmt, asfrom, lateral=False, **kw 3163 ): 3164 # utility method to help external dialects 3165 # get the correct from list for a select. 3166 # specifically the oracle dialect needs this feature 3167 # right now. 3168 toplevel = not self.stack 3169 entry = self._default_stack_entry if toplevel else self.stack[-1] 3170 3171 compile_state = select_stmt._compile_state_factory(select_stmt, self) 3172 3173 correlate_froms = entry["correlate_froms"] 3174 asfrom_froms = entry["asfrom_froms"] 3175 3176 if asfrom and not lateral: 3177 froms = compile_state._get_display_froms( 3178 explicit_correlate_froms=correlate_froms.difference( 3179 asfrom_froms 3180 ), 3181 implicit_correlate_froms=(), 3182 ) 3183 else: 3184 froms = compile_state._get_display_froms( 3185 explicit_correlate_froms=correlate_froms, 3186 implicit_correlate_froms=asfrom_froms, 3187 ) 3188 return froms 3189 3190 translate_select_structure = None 3191 """if not ``None``, should be a callable which accepts ``(select_stmt, 3192 **kw)`` and returns a select object. this is used for structural changes 3193 mostly to accommodate for LIMIT/OFFSET schemes 3194 3195 """ 3196 3197 def visit_select( 3198 self, 3199 select_stmt, 3200 asfrom=False, 3201 insert_into=False, 3202 fromhints=None, 3203 compound_index=None, 3204 select_wraps_for=None, 3205 lateral=False, 3206 from_linter=None, 3207 **kwargs 3208 ): 3209 assert select_wraps_for is None, ( 3210 "SQLAlchemy 1.4 requires use of " 3211 "the translate_select_structure hook for structural " 3212 "translations of SELECT objects" 3213 ) 3214 3215 # initial setup of SELECT. the compile_state_factory may now 3216 # be creating a totally different SELECT from the one that was 3217 # passed in. for ORM use this will convert from an ORM-state 3218 # SELECT to a regular "Core" SELECT. other composed operations 3219 # such as computation of joins will be performed. 3220 3221 kwargs["within_columns_clause"] = False 3222 3223 compile_state = select_stmt._compile_state_factory( 3224 select_stmt, self, **kwargs 3225 ) 3226 select_stmt = compile_state.statement 3227 3228 toplevel = not self.stack 3229 3230 if toplevel and not self.compile_state: 3231 self.compile_state = compile_state 3232 3233 is_embedded_select = compound_index is not None or insert_into 3234 3235 # translate step for Oracle, SQL Server which often need to 3236 # restructure the SELECT to allow for LIMIT/OFFSET and possibly 3237 # other conditions 3238 if self.translate_select_structure: 3239 new_select_stmt = self.translate_select_structure( 3240 select_stmt, asfrom=asfrom, **kwargs 3241 ) 3242 3243 # if SELECT was restructured, maintain a link to the originals 3244 # and assemble a new compile state 3245 if new_select_stmt is not select_stmt: 3246 compile_state_wraps_for = compile_state 3247 select_wraps_for = select_stmt 3248 select_stmt = new_select_stmt 3249 3250 compile_state = select_stmt._compile_state_factory( 3251 select_stmt, self, **kwargs 3252 ) 3253 select_stmt = compile_state.statement 3254 3255 entry = self._default_stack_entry if toplevel else self.stack[-1] 3256 3257 populate_result_map = need_column_expressions = ( 3258 toplevel 3259 or entry.get("need_result_map_for_compound", False) 3260 or entry.get("need_result_map_for_nested", False) 3261 ) 3262 3263 # indicates there is a CompoundSelect in play and we are not the 3264 # first select 3265 if compound_index: 3266 populate_result_map = False 3267 3268 # this was first proposed as part of #3372; however, it is not 3269 # reached in current tests and could possibly be an assertion 3270 # instead. 3271 if not populate_result_map and "add_to_result_map" in kwargs: 3272 del kwargs["add_to_result_map"] 3273 3274 froms = self._setup_select_stack( 3275 select_stmt, compile_state, entry, asfrom, lateral, compound_index 3276 ) 3277 3278 column_clause_args = kwargs.copy() 3279 column_clause_args.update( 3280 {"within_label_clause": False, "within_columns_clause": False} 3281 ) 3282 3283 text = "SELECT " # we're off to a good start ! 3284 3285 if select_stmt._hints: 3286 hint_text, byfrom = self._setup_select_hints(select_stmt) 3287 if hint_text: 3288 text += hint_text + " " 3289 else: 3290 byfrom = None 3291 3292 if select_stmt._independent_ctes: 3293 for cte in select_stmt._independent_ctes: 3294 cte._compiler_dispatch(self, **kwargs) 3295 3296 if select_stmt._prefixes: 3297 text += self._generate_prefixes( 3298 select_stmt, select_stmt._prefixes, **kwargs 3299 ) 3300 3301 text += self.get_select_precolumns(select_stmt, **kwargs) 3302 # the actual list of columns to print in the SELECT column list. 3303 inner_columns = [ 3304 c 3305 for c in [ 3306 self._label_select_column( 3307 select_stmt, 3308 column, 3309 populate_result_map, 3310 asfrom, 3311 column_clause_args, 3312 name=name, 3313 proxy_name=proxy_name, 3314 fallback_label_name=fallback_label_name, 3315 column_is_repeated=repeated, 3316 need_column_expressions=need_column_expressions, 3317 ) 3318 for ( 3319 name, 3320 proxy_name, 3321 fallback_label_name, 3322 column, 3323 repeated, 3324 ) in compile_state.columns_plus_names 3325 ] 3326 if c is not None 3327 ] 3328 3329 if populate_result_map and select_wraps_for is not None: 3330 # if this select was generated from translate_select, 3331 # rewrite the targeted columns in the result map 3332 3333 translate = dict( 3334 zip( 3335 [ 3336 name 3337 for ( 3338 key, 3339 proxy_name, 3340 fallback_label_name, 3341 name, 3342 repeated, 3343 ) in compile_state.columns_plus_names 3344 ], 3345 [ 3346 name 3347 for ( 3348 key, 3349 proxy_name, 3350 fallback_label_name, 3351 name, 3352 repeated, 3353 ) in compile_state_wraps_for.columns_plus_names 3354 ], 3355 ) 3356 ) 3357 3358 self._result_columns = [ 3359 (key, name, tuple(translate.get(o, o) for o in obj), type_) 3360 for key, name, obj, type_ in self._result_columns 3361 ] 3362 3363 text = self._compose_select_body( 3364 text, 3365 select_stmt, 3366 compile_state, 3367 inner_columns, 3368 froms, 3369 byfrom, 3370 toplevel, 3371 kwargs, 3372 ) 3373 3374 if select_stmt._statement_hints: 3375 per_dialect = [ 3376 ht 3377 for (dialect_name, ht) in select_stmt._statement_hints 3378 if dialect_name in ("*", self.dialect.name) 3379 ] 3380 if per_dialect: 3381 text += " " + self.get_statement_hint_text(per_dialect) 3382 3383 if self.ctes: 3384 # In compound query, CTEs are shared at the compound level 3385 if not is_embedded_select: 3386 nesting_level = len(self.stack) if not toplevel else None 3387 text = ( 3388 self._render_cte_clause(nesting_level=nesting_level) + text 3389 ) 3390 3391 if select_stmt._suffixes: 3392 text += " " + self._generate_prefixes( 3393 select_stmt, select_stmt._suffixes, **kwargs 3394 ) 3395 3396 self.stack.pop(-1) 3397 3398 return text 3399 3400 def _setup_select_hints(self, select): 3401 byfrom = dict( 3402 [ 3403 ( 3404 from_, 3405 hinttext 3406 % {"name": from_._compiler_dispatch(self, ashint=True)}, 3407 ) 3408 for (from_, dialect), hinttext in select._hints.items() 3409 if dialect in ("*", self.dialect.name) 3410 ] 3411 ) 3412 hint_text = self.get_select_hint_text(byfrom) 3413 return hint_text, byfrom 3414 3415 def _setup_select_stack( 3416 self, select, compile_state, entry, asfrom, lateral, compound_index 3417 ): 3418 correlate_froms = entry["correlate_froms"] 3419 asfrom_froms = entry["asfrom_froms"] 3420 3421 if compound_index == 0: 3422 entry["select_0"] = select 3423 elif compound_index: 3424 select_0 = entry["select_0"] 3425 numcols = len(select_0._all_selected_columns) 3426 3427 if len(compile_state.columns_plus_names) != numcols: 3428 raise exc.CompileError( 3429 "All selectables passed to " 3430 "CompoundSelect must have identical numbers of " 3431 "columns; select #%d has %d columns, select " 3432 "#%d has %d" 3433 % ( 3434 1, 3435 numcols, 3436 compound_index + 1, 3437 len(select._all_selected_columns), 3438 ) 3439 ) 3440 3441 if asfrom and not lateral: 3442 froms = compile_state._get_display_froms( 3443 explicit_correlate_froms=correlate_froms.difference( 3444 asfrom_froms 3445 ), 3446 implicit_correlate_froms=(), 3447 ) 3448 else: 3449 froms = compile_state._get_display_froms( 3450 explicit_correlate_froms=correlate_froms, 3451 implicit_correlate_froms=asfrom_froms, 3452 ) 3453 3454 new_correlate_froms = set(selectable._from_objects(*froms)) 3455 all_correlate_froms = new_correlate_froms.union(correlate_froms) 3456 3457 new_entry = { 3458 "asfrom_froms": new_correlate_froms, 3459 "correlate_froms": all_correlate_froms, 3460 "selectable": select, 3461 "compile_state": compile_state, 3462 } 3463 self.stack.append(new_entry) 3464 3465 return froms 3466 3467 def _compose_select_body( 3468 self, 3469 text, 3470 select, 3471 compile_state, 3472 inner_columns, 3473 froms, 3474 byfrom, 3475 toplevel, 3476 kwargs, 3477 ): 3478 text += ", ".join(inner_columns) 3479 3480 if self.linting & COLLECT_CARTESIAN_PRODUCTS: 3481 from_linter = FromLinter({}, set()) 3482 warn_linting = self.linting & WARN_LINTING 3483 if toplevel: 3484 self.from_linter = from_linter 3485 else: 3486 from_linter = None 3487 warn_linting = False 3488 3489 if froms: 3490 text += " \nFROM " 3491 3492 if select._hints: 3493 text += ", ".join( 3494 [ 3495 f._compiler_dispatch( 3496 self, 3497 asfrom=True, 3498 fromhints=byfrom, 3499 from_linter=from_linter, 3500 **kwargs 3501 ) 3502 for f in froms 3503 ] 3504 ) 3505 else: 3506 text += ", ".join( 3507 [ 3508 f._compiler_dispatch( 3509 self, 3510 asfrom=True, 3511 from_linter=from_linter, 3512 **kwargs 3513 ) 3514 for f in froms 3515 ] 3516 ) 3517 else: 3518 text += self.default_from() 3519 3520 if select._where_criteria: 3521 t = self._generate_delimited_and_list( 3522 select._where_criteria, from_linter=from_linter, **kwargs 3523 ) 3524 if t: 3525 text += " \nWHERE " + t 3526 3527 if warn_linting: 3528 from_linter.warn() 3529 3530 if select._group_by_clauses: 3531 text += self.group_by_clause(select, **kwargs) 3532 3533 if select._having_criteria: 3534 t = self._generate_delimited_and_list( 3535 select._having_criteria, **kwargs 3536 ) 3537 if t: 3538 text += " \nHAVING " + t 3539 3540 if select._order_by_clauses: 3541 text += self.order_by_clause(select, **kwargs) 3542 3543 if select._has_row_limiting_clause: 3544 text += self._row_limit_clause(select, **kwargs) 3545 3546 if select._for_update_arg is not None: 3547 text += self.for_update_clause(select, **kwargs) 3548 3549 return text 3550 3551 def _generate_prefixes(self, stmt, prefixes, **kw): 3552 clause = " ".join( 3553 prefix._compiler_dispatch(self, **kw) 3554 for prefix, dialect_name in prefixes 3555 if dialect_name is None or dialect_name == self.dialect.name 3556 ) 3557 if clause: 3558 clause += " " 3559 return clause 3560 3561 def _render_cte_clause( 3562 self, 3563 nesting_level=None, 3564 include_following_stack=False, 3565 ): 3566 """ 3567 include_following_stack 3568 Also render the nesting CTEs on the next stack. Useful for 3569 SQL structures like UNION or INSERT that can wrap SELECT 3570 statements containing nesting CTEs. 3571 """ 3572 if not self.ctes: 3573 return "" 3574 3575 if nesting_level and nesting_level > 1: 3576 ctes = util.OrderedDict() 3577 for cte in list(self.ctes.keys()): 3578 cte_level, cte_name = self.level_name_by_cte[ 3579 cte._get_reference_cte() 3580 ] 3581 is_rendered_level = cte_level == nesting_level or ( 3582 include_following_stack and cte_level == nesting_level + 1 3583 ) 3584 if not (cte.nesting and is_rendered_level): 3585 continue 3586 3587 ctes[cte] = self.ctes[cte] 3588 3589 else: 3590 ctes = self.ctes 3591 3592 if not ctes: 3593 return "" 3594 3595 ctes_recursive = any([cte.recursive for cte in ctes]) 3596 3597 if self.positional: 3598 self.positiontup = ( 3599 sum([self.cte_positional[cte] for cte in ctes], []) 3600 + self.positiontup 3601 ) 3602 cte_text = self.get_cte_preamble(ctes_recursive) + " " 3603 cte_text += ", \n".join([txt for txt in ctes.values()]) 3604 cte_text += "\n " 3605 3606 if nesting_level and nesting_level > 1: 3607 for cte in list(ctes.keys()): 3608 cte_level, cte_name = self.level_name_by_cte[ 3609 cte._get_reference_cte() 3610 ] 3611 del self.ctes[cte] 3612 del self.ctes_by_level_name[(cte_level, cte_name)] 3613 del self.level_name_by_cte[cte._get_reference_cte()] 3614 3615 return cte_text 3616 3617 def get_cte_preamble(self, recursive): 3618 if recursive: 3619 return "WITH RECURSIVE" 3620 else: 3621 return "WITH" 3622 3623 def get_select_precolumns(self, select, **kw): 3624 """Called when building a ``SELECT`` statement, position is just 3625 before column list. 3626 3627 """ 3628 if select._distinct_on: 3629 util.warn_deprecated( 3630 "DISTINCT ON is currently supported only by the PostgreSQL " 3631 "dialect. Use of DISTINCT ON for other backends is currently " 3632 "silently ignored, however this usage is deprecated, and will " 3633 "raise CompileError in a future release for all backends " 3634 "that do not support this syntax.", 3635 version="1.4", 3636 ) 3637 return "DISTINCT " if select._distinct else "" 3638 3639 def group_by_clause(self, select, **kw): 3640 """allow dialects to customize how GROUP BY is rendered.""" 3641 3642 group_by = self._generate_delimited_list( 3643 select._group_by_clauses, OPERATORS[operators.comma_op], **kw 3644 ) 3645 if group_by: 3646 return " GROUP BY " + group_by 3647 else: 3648 return "" 3649 3650 def order_by_clause(self, select, **kw): 3651 """allow dialects to customize how ORDER BY is rendered.""" 3652 3653 order_by = self._generate_delimited_list( 3654 select._order_by_clauses, OPERATORS[operators.comma_op], **kw 3655 ) 3656 3657 if order_by: 3658 return " ORDER BY " + order_by 3659 else: 3660 return "" 3661 3662 def for_update_clause(self, select, **kw): 3663 return " FOR UPDATE" 3664 3665 def returning_clause(self, stmt, returning_cols): 3666 raise exc.CompileError( 3667 "RETURNING is not supported by this " 3668 "dialect's statement compiler." 3669 ) 3670 3671 def limit_clause(self, select, **kw): 3672 text = "" 3673 if select._limit_clause is not None: 3674 text += "\n LIMIT " + self.process(select._limit_clause, **kw) 3675 if select._offset_clause is not None: 3676 if select._limit_clause is None: 3677 text += "\n LIMIT -1" 3678 text += " OFFSET " + self.process(select._offset_clause, **kw) 3679 return text 3680 3681 def fetch_clause(self, select, **kw): 3682 text = "" 3683 if select._offset_clause is not None: 3684 text += "\n OFFSET %s ROWS" % self.process( 3685 select._offset_clause, **kw 3686 ) 3687 if select._fetch_clause is not None: 3688 text += "\n FETCH FIRST %s%s ROWS %s" % ( 3689 self.process(select._fetch_clause, **kw), 3690 " PERCENT" if select._fetch_clause_options["percent"] else "", 3691 "WITH TIES" 3692 if select._fetch_clause_options["with_ties"] 3693 else "ONLY", 3694 ) 3695 return text 3696 3697 def visit_table( 3698 self, 3699 table, 3700 asfrom=False, 3701 iscrud=False, 3702 ashint=False, 3703 fromhints=None, 3704 use_schema=True, 3705 from_linter=None, 3706 **kwargs 3707 ): 3708 if from_linter: 3709 from_linter.froms[table] = table.fullname 3710 3711 if asfrom or ashint: 3712 effective_schema = self.preparer.schema_for_object(table) 3713 3714 if use_schema and effective_schema: 3715 ret = ( 3716 self.preparer.quote_schema(effective_schema) 3717 + "." 3718 + self.preparer.quote(table.name) 3719 ) 3720 else: 3721 ret = self.preparer.quote(table.name) 3722 if fromhints and table in fromhints: 3723 ret = self.format_from_hint_text( 3724 ret, table, fromhints[table], iscrud 3725 ) 3726 return ret 3727 else: 3728 return "" 3729 3730 def visit_join(self, join, asfrom=False, from_linter=None, **kwargs): 3731 if from_linter: 3732 from_linter.edges.update( 3733 itertools.product( 3734 join.left._from_objects, join.right._from_objects 3735 ) 3736 ) 3737 3738 if join.full: 3739 join_type = " FULL OUTER JOIN " 3740 elif join.isouter: 3741 join_type = " LEFT OUTER JOIN " 3742 else: 3743 join_type = " JOIN " 3744 return ( 3745 join.left._compiler_dispatch( 3746 self, asfrom=True, from_linter=from_linter, **kwargs 3747 ) 3748 + join_type 3749 + join.right._compiler_dispatch( 3750 self, asfrom=True, from_linter=from_linter, **kwargs 3751 ) 3752 + " ON " 3753 # TODO: likely need asfrom=True here? 3754 + join.onclause._compiler_dispatch( 3755 self, from_linter=from_linter, **kwargs 3756 ) 3757 ) 3758 3759 def _setup_crud_hints(self, stmt, table_text): 3760 dialect_hints = dict( 3761 [ 3762 (table, hint_text) 3763 for (table, dialect), hint_text in stmt._hints.items() 3764 if dialect in ("*", self.dialect.name) 3765 ] 3766 ) 3767 if stmt.table in dialect_hints: 3768 table_text = self.format_from_hint_text( 3769 table_text, stmt.table, dialect_hints[stmt.table], True 3770 ) 3771 return dialect_hints, table_text 3772 3773 def visit_insert(self, insert_stmt, **kw): 3774 3775 compile_state = insert_stmt._compile_state_factory( 3776 insert_stmt, self, **kw 3777 ) 3778 insert_stmt = compile_state.statement 3779 3780 toplevel = not self.stack 3781 3782 if toplevel: 3783 self.isinsert = True 3784 if not self.compile_state: 3785 self.compile_state = compile_state 3786 3787 self.stack.append( 3788 { 3789 "correlate_froms": set(), 3790 "asfrom_froms": set(), 3791 "selectable": insert_stmt, 3792 } 3793 ) 3794 3795 crud_params = crud._get_crud_params( 3796 self, insert_stmt, compile_state, **kw 3797 ) 3798 3799 if ( 3800 not crud_params 3801 and not self.dialect.supports_default_values 3802 and not self.dialect.supports_default_metavalue 3803 and not self.dialect.supports_empty_insert 3804 ): 3805 raise exc.CompileError( 3806 "The '%s' dialect with current database " 3807 "version settings does not support empty " 3808 "inserts." % self.dialect.name 3809 ) 3810 3811 if compile_state._has_multi_parameters: 3812 if not self.dialect.supports_multivalues_insert: 3813 raise exc.CompileError( 3814 "The '%s' dialect with current database " 3815 "version settings does not support " 3816 "in-place multirow inserts." % self.dialect.name 3817 ) 3818 crud_params_single = crud_params[0] 3819 else: 3820 crud_params_single = crud_params 3821 3822 preparer = self.preparer 3823 supports_default_values = self.dialect.supports_default_values 3824 3825 text = "INSERT " 3826 3827 if insert_stmt._prefixes: 3828 text += self._generate_prefixes( 3829 insert_stmt, insert_stmt._prefixes, **kw 3830 ) 3831 3832 text += "INTO " 3833 table_text = preparer.format_table(insert_stmt.table) 3834 3835 if insert_stmt._hints: 3836 _, table_text = self._setup_crud_hints(insert_stmt, table_text) 3837 3838 if insert_stmt._independent_ctes: 3839 for cte in insert_stmt._independent_ctes: 3840 cte._compiler_dispatch(self, **kw) 3841 3842 text += table_text 3843 3844 if crud_params_single or not supports_default_values: 3845 text += " (%s)" % ", ".join( 3846 [expr for c, expr, value in crud_params_single] 3847 ) 3848 3849 if self.returning or insert_stmt._returning: 3850 returning_clause = self.returning_clause( 3851 insert_stmt, self.returning or insert_stmt._returning 3852 ) 3853 3854 if self.returning_precedes_values: 3855 text += " " + returning_clause 3856 else: 3857 returning_clause = None 3858 3859 if insert_stmt.select is not None: 3860 # placed here by crud.py 3861 select_text = self.process( 3862 self.stack[-1]["insert_from_select"], insert_into=True, **kw 3863 ) 3864 3865 if self.ctes and self.dialect.cte_follows_insert: 3866 nesting_level = len(self.stack) if not toplevel else None 3867 text += " %s%s" % ( 3868 self._render_cte_clause( 3869 nesting_level=nesting_level, 3870 include_following_stack=True, 3871 ), 3872 select_text, 3873 ) 3874 else: 3875 text += " %s" % select_text 3876 elif not crud_params and supports_default_values: 3877 text += " DEFAULT VALUES" 3878 elif compile_state._has_multi_parameters: 3879 text += " VALUES %s" % ( 3880 ", ".join( 3881 "(%s)" 3882 % (", ".join(value for c, expr, value in crud_param_set)) 3883 for crud_param_set in crud_params 3884 ) 3885 ) 3886 else: 3887 insert_single_values_expr = ", ".join( 3888 [value for c, expr, value in crud_params] 3889 ) 3890 text += " VALUES (%s)" % insert_single_values_expr 3891 if toplevel and insert_stmt._post_values_clause is None: 3892 # don't assign insert_single_values_expr if _post_values_clause 3893 # is present. what this means concretely is that the 3894 # "fast insert executemany helper" won't be used, in other 3895 # words we won't convert "executemany()" of many parameter 3896 # sets into a single INSERT with many elements in VALUES. 3897 # We can't apply that optimization safely if for example the 3898 # statement includes a clause like "ON CONFLICT DO UPDATE" 3899 self.insert_single_values_expr = insert_single_values_expr 3900 3901 if insert_stmt._post_values_clause is not None: 3902 post_values_clause = self.process( 3903 insert_stmt._post_values_clause, **kw 3904 ) 3905 if post_values_clause: 3906 text += " " + post_values_clause 3907 3908 if returning_clause and not self.returning_precedes_values: 3909 text += " " + returning_clause 3910 3911 if self.ctes and not self.dialect.cte_follows_insert: 3912 nesting_level = len(self.stack) if not toplevel else None 3913 text = ( 3914 self._render_cte_clause( 3915 nesting_level=nesting_level, include_following_stack=True 3916 ) 3917 + text 3918 ) 3919 3920 self.stack.pop(-1) 3921 3922 return text 3923 3924 def update_limit_clause(self, update_stmt): 3925 """Provide a hook for MySQL to add LIMIT to the UPDATE""" 3926 return None 3927 3928 def update_tables_clause(self, update_stmt, from_table, extra_froms, **kw): 3929 """Provide a hook to override the initial table clause 3930 in an UPDATE statement. 3931 3932 MySQL overrides this. 3933 3934 """ 3935 kw["asfrom"] = True 3936 return from_table._compiler_dispatch(self, iscrud=True, **kw) 3937 3938 def update_from_clause( 3939 self, update_stmt, from_table, extra_froms, from_hints, **kw 3940 ): 3941 """Provide a hook to override the generation of an 3942 UPDATE..FROM clause. 3943 3944 MySQL and MSSQL override this. 3945 3946 """ 3947 raise NotImplementedError( 3948 "This backend does not support multiple-table " 3949 "criteria within UPDATE" 3950 ) 3951 3952 def visit_update(self, update_stmt, **kw): 3953 compile_state = update_stmt._compile_state_factory( 3954 update_stmt, self, **kw 3955 ) 3956 update_stmt = compile_state.statement 3957 3958 toplevel = not self.stack 3959 if toplevel: 3960 self.isupdate = True 3961 if not self.compile_state: 3962 self.compile_state = compile_state 3963 3964 extra_froms = compile_state._extra_froms 3965 is_multitable = bool(extra_froms) 3966 3967 if is_multitable: 3968 # main table might be a JOIN 3969 main_froms = set(selectable._from_objects(update_stmt.table)) 3970 render_extra_froms = [ 3971 f for f in extra_froms if f not in main_froms 3972 ] 3973 correlate_froms = main_froms.union(extra_froms) 3974 else: 3975 render_extra_froms = [] 3976 correlate_froms = {update_stmt.table} 3977 3978 self.stack.append( 3979 { 3980 "correlate_froms": correlate_froms, 3981 "asfrom_froms": correlate_froms, 3982 "selectable": update_stmt, 3983 } 3984 ) 3985 3986 text = "UPDATE " 3987 3988 if update_stmt._prefixes: 3989 text += self._generate_prefixes( 3990 update_stmt, update_stmt._prefixes, **kw 3991 ) 3992 3993 table_text = self.update_tables_clause( 3994 update_stmt, update_stmt.table, render_extra_froms, **kw 3995 ) 3996 crud_params = crud._get_crud_params( 3997 self, update_stmt, compile_state, **kw 3998 ) 3999 4000 if update_stmt._hints: 4001 dialect_hints, table_text = self._setup_crud_hints( 4002 update_stmt, table_text 4003 ) 4004 else: 4005 dialect_hints = None 4006 4007 if update_stmt._independent_ctes: 4008 for cte in update_stmt._independent_ctes: 4009 cte._compiler_dispatch(self, **kw) 4010 4011 text += table_text 4012 4013 text += " SET " 4014 text += ", ".join(expr + "=" + value for c, expr, value in crud_params) 4015 4016 if self.returning or update_stmt._returning: 4017 if self.returning_precedes_values: 4018 text += " " + self.returning_clause( 4019 update_stmt, self.returning or update_stmt._returning 4020 ) 4021 4022 if extra_froms: 4023 extra_from_text = self.update_from_clause( 4024 update_stmt, 4025 update_stmt.table, 4026 render_extra_froms, 4027 dialect_hints, 4028 **kw 4029 ) 4030 if extra_from_text: 4031 text += " " + extra_from_text 4032 4033 if update_stmt._where_criteria: 4034 t = self._generate_delimited_and_list( 4035 update_stmt._where_criteria, **kw 4036 ) 4037 if t: 4038 text += " WHERE " + t 4039 4040 limit_clause = self.update_limit_clause(update_stmt) 4041 if limit_clause: 4042 text += " " + limit_clause 4043 4044 if ( 4045 self.returning or update_stmt._returning 4046 ) and not self.returning_precedes_values: 4047 text += " " + self.returning_clause( 4048 update_stmt, self.returning or update_stmt._returning 4049 ) 4050 4051 if self.ctes: 4052 nesting_level = len(self.stack) if not toplevel else None 4053 text = self._render_cte_clause(nesting_level=nesting_level) + text 4054 4055 self.stack.pop(-1) 4056 4057 return text 4058 4059 def delete_extra_from_clause( 4060 self, update_stmt, from_table, extra_froms, from_hints, **kw 4061 ): 4062 """Provide a hook to override the generation of an 4063 DELETE..FROM clause. 4064 4065 This can be used to implement DELETE..USING for example. 4066 4067 MySQL and MSSQL override this. 4068 4069 """ 4070 raise NotImplementedError( 4071 "This backend does not support multiple-table " 4072 "criteria within DELETE" 4073 ) 4074 4075 def delete_table_clause(self, delete_stmt, from_table, extra_froms): 4076 return from_table._compiler_dispatch(self, asfrom=True, iscrud=True) 4077 4078 def visit_delete(self, delete_stmt, **kw): 4079 compile_state = delete_stmt._compile_state_factory( 4080 delete_stmt, self, **kw 4081 ) 4082 delete_stmt = compile_state.statement 4083 4084 toplevel = not self.stack 4085 if toplevel: 4086 self.isdelete = True 4087 if not self.compile_state: 4088 self.compile_state = compile_state 4089 4090 extra_froms = compile_state._extra_froms 4091 4092 correlate_froms = {delete_stmt.table}.union(extra_froms) 4093 self.stack.append( 4094 { 4095 "correlate_froms": correlate_froms, 4096 "asfrom_froms": correlate_froms, 4097 "selectable": delete_stmt, 4098 } 4099 ) 4100 4101 text = "DELETE " 4102 4103 if delete_stmt._prefixes: 4104 text += self._generate_prefixes( 4105 delete_stmt, delete_stmt._prefixes, **kw 4106 ) 4107 4108 text += "FROM " 4109 table_text = self.delete_table_clause( 4110 delete_stmt, delete_stmt.table, extra_froms 4111 ) 4112 4113 if delete_stmt._hints: 4114 dialect_hints, table_text = self._setup_crud_hints( 4115 delete_stmt, table_text 4116 ) 4117 else: 4118 dialect_hints = None 4119 4120 if delete_stmt._independent_ctes: 4121 for cte in delete_stmt._independent_ctes: 4122 cte._compiler_dispatch(self, **kw) 4123 4124 text += table_text 4125 4126 if delete_stmt._returning: 4127 if self.returning_precedes_values: 4128 text += " " + self.returning_clause( 4129 delete_stmt, delete_stmt._returning 4130 ) 4131 4132 if extra_froms: 4133 extra_from_text = self.delete_extra_from_clause( 4134 delete_stmt, 4135 delete_stmt.table, 4136 extra_froms, 4137 dialect_hints, 4138 **kw 4139 ) 4140 if extra_from_text: 4141 text += " " + extra_from_text 4142 4143 if delete_stmt._where_criteria: 4144 t = self._generate_delimited_and_list( 4145 delete_stmt._where_criteria, **kw 4146 ) 4147 if t: 4148 text += " WHERE " + t 4149 4150 if delete_stmt._returning and not self.returning_precedes_values: 4151 text += " " + self.returning_clause( 4152 delete_stmt, delete_stmt._returning 4153 ) 4154 4155 if self.ctes: 4156 nesting_level = len(self.stack) if not toplevel else None 4157 text = self._render_cte_clause(nesting_level=nesting_level) + text 4158 4159 self.stack.pop(-1) 4160 4161 return text 4162 4163 def visit_savepoint(self, savepoint_stmt): 4164 return "SAVEPOINT %s" % self.preparer.format_savepoint(savepoint_stmt) 4165 4166 def visit_rollback_to_savepoint(self, savepoint_stmt): 4167 return "ROLLBACK TO SAVEPOINT %s" % self.preparer.format_savepoint( 4168 savepoint_stmt 4169 ) 4170 4171 def visit_release_savepoint(self, savepoint_stmt): 4172 return "RELEASE SAVEPOINT %s" % self.preparer.format_savepoint( 4173 savepoint_stmt 4174 ) 4175 4176 4177class StrSQLCompiler(SQLCompiler): 4178 """A :class:`.SQLCompiler` subclass which allows a small selection 4179 of non-standard SQL features to render into a string value. 4180 4181 The :class:`.StrSQLCompiler` is invoked whenever a Core expression 4182 element is directly stringified without calling upon the 4183 :meth:`_expression.ClauseElement.compile` method. 4184 It can render a limited set 4185 of non-standard SQL constructs to assist in basic stringification, 4186 however for more substantial custom or dialect-specific SQL constructs, 4187 it will be necessary to make use of 4188 :meth:`_expression.ClauseElement.compile` 4189 directly. 4190 4191 .. seealso:: 4192 4193 :ref:`faq_sql_expression_string` 4194 4195 """ 4196 4197 def _fallback_column_name(self, column): 4198 return "<name unknown>" 4199 4200 @util.preload_module("sqlalchemy.engine.url") 4201 def visit_unsupported_compilation(self, element, err, **kw): 4202 if element.stringify_dialect != "default": 4203 url = util.preloaded.engine_url 4204 dialect = url.URL.create(element.stringify_dialect).get_dialect()() 4205 4206 compiler = dialect.statement_compiler(dialect, None) 4207 if not isinstance(compiler, StrSQLCompiler): 4208 return compiler.process(element) 4209 4210 return super(StrSQLCompiler, self).visit_unsupported_compilation( 4211 element, err 4212 ) 4213 4214 def visit_getitem_binary(self, binary, operator, **kw): 4215 return "%s[%s]" % ( 4216 self.process(binary.left, **kw), 4217 self.process(binary.right, **kw), 4218 ) 4219 4220 def visit_json_getitem_op_binary(self, binary, operator, **kw): 4221 return self.visit_getitem_binary(binary, operator, **kw) 4222 4223 def visit_json_path_getitem_op_binary(self, binary, operator, **kw): 4224 return self.visit_getitem_binary(binary, operator, **kw) 4225 4226 def visit_sequence(self, seq, **kw): 4227 return "<next sequence value: %s>" % self.preparer.format_sequence(seq) 4228 4229 def returning_clause(self, stmt, returning_cols): 4230 columns = [ 4231 self._label_select_column(None, c, True, False, {}) 4232 for c in base._select_iterables(returning_cols) 4233 ] 4234 4235 return "RETURNING " + ", ".join(columns) 4236 4237 def update_from_clause( 4238 self, update_stmt, from_table, extra_froms, from_hints, **kw 4239 ): 4240 kw["asfrom"] = True 4241 return "FROM " + ", ".join( 4242 t._compiler_dispatch(self, fromhints=from_hints, **kw) 4243 for t in extra_froms 4244 ) 4245 4246 def delete_extra_from_clause( 4247 self, update_stmt, from_table, extra_froms, from_hints, **kw 4248 ): 4249 kw["asfrom"] = True 4250 return ", " + ", ".join( 4251 t._compiler_dispatch(self, fromhints=from_hints, **kw) 4252 for t in extra_froms 4253 ) 4254 4255 def visit_empty_set_expr(self, type_): 4256 return "SELECT 1 WHERE 1!=1" 4257 4258 def get_from_hint_text(self, table, text): 4259 return "[%s]" % text 4260 4261 def visit_regexp_match_op_binary(self, binary, operator, **kw): 4262 return self._generate_generic_binary(binary, " <regexp> ", **kw) 4263 4264 def visit_not_regexp_match_op_binary(self, binary, operator, **kw): 4265 return self._generate_generic_binary(binary, " <not regexp> ", **kw) 4266 4267 def visit_regexp_replace_op_binary(self, binary, operator, **kw): 4268 replacement = binary.modifiers["replacement"] 4269 return "<regexp replace>(%s, %s, %s)" % ( 4270 binary.left._compiler_dispatch(self, **kw), 4271 binary.right._compiler_dispatch(self, **kw), 4272 replacement._compiler_dispatch(self, **kw), 4273 ) 4274 4275 4276class DDLCompiler(Compiled): 4277 @util.memoized_property 4278 def sql_compiler(self): 4279 return self.dialect.statement_compiler( 4280 self.dialect, None, schema_translate_map=self.schema_translate_map 4281 ) 4282 4283 @util.memoized_property 4284 def type_compiler(self): 4285 return self.dialect.type_compiler 4286 4287 def construct_params(self, params=None, extracted_parameters=None): 4288 return None 4289 4290 def visit_ddl(self, ddl, **kwargs): 4291 # table events can substitute table and schema name 4292 context = ddl.context 4293 if isinstance(ddl.target, schema.Table): 4294 context = context.copy() 4295 4296 preparer = self.preparer 4297 path = preparer.format_table_seq(ddl.target) 4298 if len(path) == 1: 4299 table, sch = path[0], "" 4300 else: 4301 table, sch = path[-1], path[0] 4302 4303 context.setdefault("table", table) 4304 context.setdefault("schema", sch) 4305 context.setdefault("fullname", preparer.format_table(ddl.target)) 4306 4307 return self.sql_compiler.post_process_text(ddl.statement % context) 4308 4309 def visit_create_schema(self, create, **kw): 4310 schema = self.preparer.format_schema(create.element) 4311 return "CREATE SCHEMA " + schema 4312 4313 def visit_drop_schema(self, drop, **kw): 4314 schema = self.preparer.format_schema(drop.element) 4315 text = "DROP SCHEMA " + schema 4316 if drop.cascade: 4317 text += " CASCADE" 4318 return text 4319 4320 def visit_create_table(self, create, **kw): 4321 table = create.element 4322 preparer = self.preparer 4323 4324 text = "\nCREATE " 4325 if table._prefixes: 4326 text += " ".join(table._prefixes) + " " 4327 4328 text += "TABLE " 4329 if create.if_not_exists: 4330 text += "IF NOT EXISTS " 4331 4332 text += preparer.format_table(table) + " " 4333 4334 create_table_suffix = self.create_table_suffix(table) 4335 if create_table_suffix: 4336 text += create_table_suffix + " " 4337 4338 text += "(" 4339 4340 separator = "\n" 4341 4342 # if only one primary key, specify it along with the column 4343 first_pk = False 4344 for create_column in create.columns: 4345 column = create_column.element 4346 try: 4347 processed = self.process( 4348 create_column, first_pk=column.primary_key and not first_pk 4349 ) 4350 if processed is not None: 4351 text += separator 4352 separator = ", \n" 4353 text += "\t" + processed 4354 if column.primary_key: 4355 first_pk = True 4356 except exc.CompileError as ce: 4357 util.raise_( 4358 exc.CompileError( 4359 util.u("(in table '%s', column '%s'): %s") 4360 % (table.description, column.name, ce.args[0]) 4361 ), 4362 from_=ce, 4363 ) 4364 4365 const = self.create_table_constraints( 4366 table, 4367 _include_foreign_key_constraints=create.include_foreign_key_constraints, # noqa 4368 ) 4369 if const: 4370 text += separator + "\t" + const 4371 4372 text += "\n)%s\n\n" % self.post_create_table(table) 4373 return text 4374 4375 def visit_create_column(self, create, first_pk=False, **kw): 4376 column = create.element 4377 4378 if column.system: 4379 return None 4380 4381 text = self.get_column_specification(column, first_pk=first_pk) 4382 const = " ".join( 4383 self.process(constraint) for constraint in column.constraints 4384 ) 4385 if const: 4386 text += " " + const 4387 4388 return text 4389 4390 def create_table_constraints( 4391 self, table, _include_foreign_key_constraints=None, **kw 4392 ): 4393 4394 # On some DB order is significant: visit PK first, then the 4395 # other constraints (engine.ReflectionTest.testbasic failed on FB2) 4396 constraints = [] 4397 if table.primary_key: 4398 constraints.append(table.primary_key) 4399 4400 all_fkcs = table.foreign_key_constraints 4401 if _include_foreign_key_constraints is not None: 4402 omit_fkcs = all_fkcs.difference(_include_foreign_key_constraints) 4403 else: 4404 omit_fkcs = set() 4405 4406 constraints.extend( 4407 [ 4408 c 4409 for c in table._sorted_constraints 4410 if c is not table.primary_key and c not in omit_fkcs 4411 ] 4412 ) 4413 4414 return ", \n\t".join( 4415 p 4416 for p in ( 4417 self.process(constraint) 4418 for constraint in constraints 4419 if ( 4420 constraint._create_rule is None 4421 or constraint._create_rule(self) 4422 ) 4423 and ( 4424 not self.dialect.supports_alter 4425 or not getattr(constraint, "use_alter", False) 4426 ) 4427 ) 4428 if p is not None 4429 ) 4430 4431 def visit_drop_table(self, drop, **kw): 4432 text = "\nDROP TABLE " 4433 if drop.if_exists: 4434 text += "IF EXISTS " 4435 return text + self.preparer.format_table(drop.element) 4436 4437 def visit_drop_view(self, drop, **kw): 4438 return "\nDROP VIEW " + self.preparer.format_table(drop.element) 4439 4440 def _verify_index_table(self, index): 4441 if index.table is None: 4442 raise exc.CompileError( 4443 "Index '%s' is not associated " "with any table." % index.name 4444 ) 4445 4446 def visit_create_index( 4447 self, create, include_schema=False, include_table_schema=True, **kw 4448 ): 4449 index = create.element 4450 self._verify_index_table(index) 4451 preparer = self.preparer 4452 text = "CREATE " 4453 if index.unique: 4454 text += "UNIQUE " 4455 if index.name is None: 4456 raise exc.CompileError( 4457 "CREATE INDEX requires that the index have a name" 4458 ) 4459 4460 text += "INDEX " 4461 if create.if_not_exists: 4462 text += "IF NOT EXISTS " 4463 4464 text += "%s ON %s (%s)" % ( 4465 self._prepared_index_name(index, include_schema=include_schema), 4466 preparer.format_table( 4467 index.table, use_schema=include_table_schema 4468 ), 4469 ", ".join( 4470 self.sql_compiler.process( 4471 expr, include_table=False, literal_binds=True 4472 ) 4473 for expr in index.expressions 4474 ), 4475 ) 4476 return text 4477 4478 def visit_drop_index(self, drop, **kw): 4479 index = drop.element 4480 4481 if index.name is None: 4482 raise exc.CompileError( 4483 "DROP INDEX requires that the index have a name" 4484 ) 4485 text = "\nDROP INDEX " 4486 if drop.if_exists: 4487 text += "IF EXISTS " 4488 4489 return text + self._prepared_index_name(index, include_schema=True) 4490 4491 def _prepared_index_name(self, index, include_schema=False): 4492 if index.table is not None: 4493 effective_schema = self.preparer.schema_for_object(index.table) 4494 else: 4495 effective_schema = None 4496 if include_schema and effective_schema: 4497 schema_name = self.preparer.quote_schema(effective_schema) 4498 else: 4499 schema_name = None 4500 4501 index_name = self.preparer.format_index(index) 4502 4503 if schema_name: 4504 index_name = schema_name + "." + index_name 4505 return index_name 4506 4507 def visit_add_constraint(self, create, **kw): 4508 return "ALTER TABLE %s ADD %s" % ( 4509 self.preparer.format_table(create.element.table), 4510 self.process(create.element), 4511 ) 4512 4513 def visit_set_table_comment(self, create, **kw): 4514 return "COMMENT ON TABLE %s IS %s" % ( 4515 self.preparer.format_table(create.element), 4516 self.sql_compiler.render_literal_value( 4517 create.element.comment, sqltypes.String() 4518 ), 4519 ) 4520 4521 def visit_drop_table_comment(self, drop, **kw): 4522 return "COMMENT ON TABLE %s IS NULL" % self.preparer.format_table( 4523 drop.element 4524 ) 4525 4526 def visit_set_column_comment(self, create, **kw): 4527 return "COMMENT ON COLUMN %s IS %s" % ( 4528 self.preparer.format_column( 4529 create.element, use_table=True, use_schema=True 4530 ), 4531 self.sql_compiler.render_literal_value( 4532 create.element.comment, sqltypes.String() 4533 ), 4534 ) 4535 4536 def visit_drop_column_comment(self, drop, **kw): 4537 return "COMMENT ON COLUMN %s IS NULL" % self.preparer.format_column( 4538 drop.element, use_table=True 4539 ) 4540 4541 def get_identity_options(self, identity_options): 4542 text = [] 4543 if identity_options.increment is not None: 4544 text.append("INCREMENT BY %d" % identity_options.increment) 4545 if identity_options.start is not None: 4546 text.append("START WITH %d" % identity_options.start) 4547 if identity_options.minvalue is not None: 4548 text.append("MINVALUE %d" % identity_options.minvalue) 4549 if identity_options.maxvalue is not None: 4550 text.append("MAXVALUE %d" % identity_options.maxvalue) 4551 if identity_options.nominvalue is not None: 4552 text.append("NO MINVALUE") 4553 if identity_options.nomaxvalue is not None: 4554 text.append("NO MAXVALUE") 4555 if identity_options.cache is not None: 4556 text.append("CACHE %d" % identity_options.cache) 4557 if identity_options.order is not None: 4558 text.append("ORDER" if identity_options.order else "NO ORDER") 4559 if identity_options.cycle is not None: 4560 text.append("CYCLE" if identity_options.cycle else "NO CYCLE") 4561 return " ".join(text) 4562 4563 def visit_create_sequence(self, create, prefix=None, **kw): 4564 text = "CREATE SEQUENCE %s" % self.preparer.format_sequence( 4565 create.element 4566 ) 4567 if prefix: 4568 text += prefix 4569 if create.element.start is None: 4570 create.element.start = self.dialect.default_sequence_base 4571 options = self.get_identity_options(create.element) 4572 if options: 4573 text += " " + options 4574 return text 4575 4576 def visit_drop_sequence(self, drop, **kw): 4577 return "DROP SEQUENCE %s" % self.preparer.format_sequence(drop.element) 4578 4579 def visit_drop_constraint(self, drop, **kw): 4580 constraint = drop.element 4581 if constraint.name is not None: 4582 formatted_name = self.preparer.format_constraint(constraint) 4583 else: 4584 formatted_name = None 4585 4586 if formatted_name is None: 4587 raise exc.CompileError( 4588 "Can't emit DROP CONSTRAINT for constraint %r; " 4589 "it has no name" % drop.element 4590 ) 4591 return "ALTER TABLE %s DROP CONSTRAINT %s%s" % ( 4592 self.preparer.format_table(drop.element.table), 4593 formatted_name, 4594 drop.cascade and " CASCADE" or "", 4595 ) 4596 4597 def get_column_specification(self, column, **kwargs): 4598 colspec = ( 4599 self.preparer.format_column(column) 4600 + " " 4601 + self.dialect.type_compiler.process( 4602 column.type, type_expression=column 4603 ) 4604 ) 4605 default = self.get_column_default_string(column) 4606 if default is not None: 4607 colspec += " DEFAULT " + default 4608 4609 if column.computed is not None: 4610 colspec += " " + self.process(column.computed) 4611 4612 if ( 4613 column.identity is not None 4614 and self.dialect.supports_identity_columns 4615 ): 4616 colspec += " " + self.process(column.identity) 4617 4618 if not column.nullable and ( 4619 not column.identity or not self.dialect.supports_identity_columns 4620 ): 4621 colspec += " NOT NULL" 4622 return colspec 4623 4624 def create_table_suffix(self, table): 4625 return "" 4626 4627 def post_create_table(self, table): 4628 return "" 4629 4630 def get_column_default_string(self, column): 4631 if isinstance(column.server_default, schema.DefaultClause): 4632 if isinstance(column.server_default.arg, util.string_types): 4633 return self.sql_compiler.render_literal_value( 4634 column.server_default.arg, sqltypes.STRINGTYPE 4635 ) 4636 else: 4637 return self.sql_compiler.process( 4638 column.server_default.arg, literal_binds=True 4639 ) 4640 else: 4641 return None 4642 4643 def visit_table_or_column_check_constraint(self, constraint, **kw): 4644 if constraint.is_column_level: 4645 return self.visit_column_check_constraint(constraint) 4646 else: 4647 return self.visit_check_constraint(constraint) 4648 4649 def visit_check_constraint(self, constraint, **kw): 4650 text = "" 4651 if constraint.name is not None: 4652 formatted_name = self.preparer.format_constraint(constraint) 4653 if formatted_name is not None: 4654 text += "CONSTRAINT %s " % formatted_name 4655 text += "CHECK (%s)" % self.sql_compiler.process( 4656 constraint.sqltext, include_table=False, literal_binds=True 4657 ) 4658 text += self.define_constraint_deferrability(constraint) 4659 return text 4660 4661 def visit_column_check_constraint(self, constraint, **kw): 4662 text = "" 4663 if constraint.name is not None: 4664 formatted_name = self.preparer.format_constraint(constraint) 4665 if formatted_name is not None: 4666 text += "CONSTRAINT %s " % formatted_name 4667 text += "CHECK (%s)" % self.sql_compiler.process( 4668 constraint.sqltext, include_table=False, literal_binds=True 4669 ) 4670 text += self.define_constraint_deferrability(constraint) 4671 return text 4672 4673 def visit_primary_key_constraint(self, constraint, **kw): 4674 if len(constraint) == 0: 4675 return "" 4676 text = "" 4677 if constraint.name is not None: 4678 formatted_name = self.preparer.format_constraint(constraint) 4679 if formatted_name is not None: 4680 text += "CONSTRAINT %s " % formatted_name 4681 text += "PRIMARY KEY " 4682 text += "(%s)" % ", ".join( 4683 self.preparer.quote(c.name) 4684 for c in ( 4685 constraint.columns_autoinc_first 4686 if constraint._implicit_generated 4687 else constraint.columns 4688 ) 4689 ) 4690 text += self.define_constraint_deferrability(constraint) 4691 return text 4692 4693 def visit_foreign_key_constraint(self, constraint, **kw): 4694 preparer = self.preparer 4695 text = "" 4696 if constraint.name is not None: 4697 formatted_name = self.preparer.format_constraint(constraint) 4698 if formatted_name is not None: 4699 text += "CONSTRAINT %s " % formatted_name 4700 remote_table = list(constraint.elements)[0].column.table 4701 text += "FOREIGN KEY(%s) REFERENCES %s (%s)" % ( 4702 ", ".join( 4703 preparer.quote(f.parent.name) for f in constraint.elements 4704 ), 4705 self.define_constraint_remote_table( 4706 constraint, remote_table, preparer 4707 ), 4708 ", ".join( 4709 preparer.quote(f.column.name) for f in constraint.elements 4710 ), 4711 ) 4712 text += self.define_constraint_match(constraint) 4713 text += self.define_constraint_cascades(constraint) 4714 text += self.define_constraint_deferrability(constraint) 4715 return text 4716 4717 def define_constraint_remote_table(self, constraint, table, preparer): 4718 """Format the remote table clause of a CREATE CONSTRAINT clause.""" 4719 4720 return preparer.format_table(table) 4721 4722 def visit_unique_constraint(self, constraint, **kw): 4723 if len(constraint) == 0: 4724 return "" 4725 text = "" 4726 if constraint.name is not None: 4727 formatted_name = self.preparer.format_constraint(constraint) 4728 if formatted_name is not None: 4729 text += "CONSTRAINT %s " % formatted_name 4730 text += "UNIQUE (%s)" % ( 4731 ", ".join(self.preparer.quote(c.name) for c in constraint) 4732 ) 4733 text += self.define_constraint_deferrability(constraint) 4734 return text 4735 4736 def define_constraint_cascades(self, constraint): 4737 text = "" 4738 if constraint.ondelete is not None: 4739 text += " ON DELETE %s" % self.preparer.validate_sql_phrase( 4740 constraint.ondelete, FK_ON_DELETE 4741 ) 4742 if constraint.onupdate is not None: 4743 text += " ON UPDATE %s" % self.preparer.validate_sql_phrase( 4744 constraint.onupdate, FK_ON_UPDATE 4745 ) 4746 return text 4747 4748 def define_constraint_deferrability(self, constraint): 4749 text = "" 4750 if constraint.deferrable is not None: 4751 if constraint.deferrable: 4752 text += " DEFERRABLE" 4753 else: 4754 text += " NOT DEFERRABLE" 4755 if constraint.initially is not None: 4756 text += " INITIALLY %s" % self.preparer.validate_sql_phrase( 4757 constraint.initially, FK_INITIALLY 4758 ) 4759 return text 4760 4761 def define_constraint_match(self, constraint): 4762 text = "" 4763 if constraint.match is not None: 4764 text += " MATCH %s" % constraint.match 4765 return text 4766 4767 def visit_computed_column(self, generated, **kw): 4768 text = "GENERATED ALWAYS AS (%s)" % self.sql_compiler.process( 4769 generated.sqltext, include_table=False, literal_binds=True 4770 ) 4771 if generated.persisted is True: 4772 text += " STORED" 4773 elif generated.persisted is False: 4774 text += " VIRTUAL" 4775 return text 4776 4777 def visit_identity_column(self, identity, **kw): 4778 text = "GENERATED %s AS IDENTITY" % ( 4779 "ALWAYS" if identity.always else "BY DEFAULT", 4780 ) 4781 options = self.get_identity_options(identity) 4782 if options: 4783 text += " (%s)" % options 4784 return text 4785 4786 4787class GenericTypeCompiler(TypeCompiler): 4788 def visit_FLOAT(self, type_, **kw): 4789 return "FLOAT" 4790 4791 def visit_REAL(self, type_, **kw): 4792 return "REAL" 4793 4794 def visit_NUMERIC(self, type_, **kw): 4795 if type_.precision is None: 4796 return "NUMERIC" 4797 elif type_.scale is None: 4798 return "NUMERIC(%(precision)s)" % {"precision": type_.precision} 4799 else: 4800 return "NUMERIC(%(precision)s, %(scale)s)" % { 4801 "precision": type_.precision, 4802 "scale": type_.scale, 4803 } 4804 4805 def visit_DECIMAL(self, type_, **kw): 4806 if type_.precision is None: 4807 return "DECIMAL" 4808 elif type_.scale is None: 4809 return "DECIMAL(%(precision)s)" % {"precision": type_.precision} 4810 else: 4811 return "DECIMAL(%(precision)s, %(scale)s)" % { 4812 "precision": type_.precision, 4813 "scale": type_.scale, 4814 } 4815 4816 def visit_INTEGER(self, type_, **kw): 4817 return "INTEGER" 4818 4819 def visit_SMALLINT(self, type_, **kw): 4820 return "SMALLINT" 4821 4822 def visit_BIGINT(self, type_, **kw): 4823 return "BIGINT" 4824 4825 def visit_TIMESTAMP(self, type_, **kw): 4826 return "TIMESTAMP" 4827 4828 def visit_DATETIME(self, type_, **kw): 4829 return "DATETIME" 4830 4831 def visit_DATE(self, type_, **kw): 4832 return "DATE" 4833 4834 def visit_TIME(self, type_, **kw): 4835 return "TIME" 4836 4837 def visit_CLOB(self, type_, **kw): 4838 return "CLOB" 4839 4840 def visit_NCLOB(self, type_, **kw): 4841 return "NCLOB" 4842 4843 def _render_string_type(self, type_, name): 4844 4845 text = name 4846 if type_.length: 4847 text += "(%d)" % type_.length 4848 if type_.collation: 4849 text += ' COLLATE "%s"' % type_.collation 4850 return text 4851 4852 def visit_CHAR(self, type_, **kw): 4853 return self._render_string_type(type_, "CHAR") 4854 4855 def visit_NCHAR(self, type_, **kw): 4856 return self._render_string_type(type_, "NCHAR") 4857 4858 def visit_VARCHAR(self, type_, **kw): 4859 return self._render_string_type(type_, "VARCHAR") 4860 4861 def visit_NVARCHAR(self, type_, **kw): 4862 return self._render_string_type(type_, "NVARCHAR") 4863 4864 def visit_TEXT(self, type_, **kw): 4865 return self._render_string_type(type_, "TEXT") 4866 4867 def visit_BLOB(self, type_, **kw): 4868 return "BLOB" 4869 4870 def visit_BINARY(self, type_, **kw): 4871 return "BINARY" + (type_.length and "(%d)" % type_.length or "") 4872 4873 def visit_VARBINARY(self, type_, **kw): 4874 return "VARBINARY" + (type_.length and "(%d)" % type_.length or "") 4875 4876 def visit_BOOLEAN(self, type_, **kw): 4877 return "BOOLEAN" 4878 4879 def visit_large_binary(self, type_, **kw): 4880 return self.visit_BLOB(type_, **kw) 4881 4882 def visit_boolean(self, type_, **kw): 4883 return self.visit_BOOLEAN(type_, **kw) 4884 4885 def visit_time(self, type_, **kw): 4886 return self.visit_TIME(type_, **kw) 4887 4888 def visit_datetime(self, type_, **kw): 4889 return self.visit_DATETIME(type_, **kw) 4890 4891 def visit_date(self, type_, **kw): 4892 return self.visit_DATE(type_, **kw) 4893 4894 def visit_big_integer(self, type_, **kw): 4895 return self.visit_BIGINT(type_, **kw) 4896 4897 def visit_small_integer(self, type_, **kw): 4898 return self.visit_SMALLINT(type_, **kw) 4899 4900 def visit_integer(self, type_, **kw): 4901 return self.visit_INTEGER(type_, **kw) 4902 4903 def visit_real(self, type_, **kw): 4904 return self.visit_REAL(type_, **kw) 4905 4906 def visit_float(self, type_, **kw): 4907 return self.visit_FLOAT(type_, **kw) 4908 4909 def visit_numeric(self, type_, **kw): 4910 return self.visit_NUMERIC(type_, **kw) 4911 4912 def visit_string(self, type_, **kw): 4913 return self.visit_VARCHAR(type_, **kw) 4914 4915 def visit_unicode(self, type_, **kw): 4916 return self.visit_VARCHAR(type_, **kw) 4917 4918 def visit_text(self, type_, **kw): 4919 return self.visit_TEXT(type_, **kw) 4920 4921 def visit_unicode_text(self, type_, **kw): 4922 return self.visit_TEXT(type_, **kw) 4923 4924 def visit_enum(self, type_, **kw): 4925 return self.visit_VARCHAR(type_, **kw) 4926 4927 def visit_null(self, type_, **kw): 4928 raise exc.CompileError( 4929 "Can't generate DDL for %r; " 4930 "did you forget to specify a " 4931 "type on this Column?" % type_ 4932 ) 4933 4934 def visit_type_decorator(self, type_, **kw): 4935 return self.process(type_.type_engine(self.dialect), **kw) 4936 4937 def visit_user_defined(self, type_, **kw): 4938 return type_.get_col_spec(**kw) 4939 4940 4941class StrSQLTypeCompiler(GenericTypeCompiler): 4942 def process(self, type_, **kw): 4943 try: 4944 _compiler_dispatch = type_._compiler_dispatch 4945 except AttributeError: 4946 return self._visit_unknown(type_, **kw) 4947 else: 4948 return _compiler_dispatch(self, **kw) 4949 4950 def __getattr__(self, key): 4951 if key.startswith("visit_"): 4952 return self._visit_unknown 4953 else: 4954 raise AttributeError(key) 4955 4956 def _visit_unknown(self, type_, **kw): 4957 if type_.__class__.__name__ == type_.__class__.__name__.upper(): 4958 return type_.__class__.__name__ 4959 else: 4960 return repr(type_) 4961 4962 def visit_null(self, type_, **kw): 4963 return "NULL" 4964 4965 def visit_user_defined(self, type_, **kw): 4966 try: 4967 get_col_spec = type_.get_col_spec 4968 except AttributeError: 4969 return repr(type_) 4970 else: 4971 return get_col_spec(**kw) 4972 4973 4974class IdentifierPreparer(object): 4975 4976 """Handle quoting and case-folding of identifiers based on options.""" 4977 4978 reserved_words = RESERVED_WORDS 4979 4980 legal_characters = LEGAL_CHARACTERS 4981 4982 illegal_initial_characters = ILLEGAL_INITIAL_CHARACTERS 4983 4984 schema_for_object = operator.attrgetter("schema") 4985 """Return the .schema attribute for an object. 4986 4987 For the default IdentifierPreparer, the schema for an object is always 4988 the value of the ".schema" attribute. if the preparer is replaced 4989 with one that has a non-empty schema_translate_map, the value of the 4990 ".schema" attribute is rendered a symbol that will be converted to a 4991 real schema name from the mapping post-compile. 4992 4993 """ 4994 4995 def __init__( 4996 self, 4997 dialect, 4998 initial_quote='"', 4999 final_quote=None, 5000 escape_quote='"', 5001 quote_case_sensitive_collations=True, 5002 omit_schema=False, 5003 ): 5004 """Construct a new ``IdentifierPreparer`` object. 5005 5006 initial_quote 5007 Character that begins a delimited identifier. 5008 5009 final_quote 5010 Character that ends a delimited identifier. Defaults to 5011 `initial_quote`. 5012 5013 omit_schema 5014 Prevent prepending schema name. Useful for databases that do 5015 not support schemae. 5016 """ 5017 5018 self.dialect = dialect 5019 self.initial_quote = initial_quote 5020 self.final_quote = final_quote or self.initial_quote 5021 self.escape_quote = escape_quote 5022 self.escape_to_quote = self.escape_quote * 2 5023 self.omit_schema = omit_schema 5024 self.quote_case_sensitive_collations = quote_case_sensitive_collations 5025 self._strings = {} 5026 self._double_percents = self.dialect.paramstyle in ( 5027 "format", 5028 "pyformat", 5029 ) 5030 5031 def _with_schema_translate(self, schema_translate_map): 5032 prep = self.__class__.__new__(self.__class__) 5033 prep.__dict__.update(self.__dict__) 5034 5035 def symbol_getter(obj): 5036 name = obj.schema 5037 if name in schema_translate_map and obj._use_schema_map: 5038 if name is not None and ("[" in name or "]" in name): 5039 raise exc.CompileError( 5040 "Square bracket characters ([]) not supported " 5041 "in schema translate name '%s'" % name 5042 ) 5043 return quoted_name( 5044 "__[SCHEMA_%s]" % (name or "_none"), quote=False 5045 ) 5046 else: 5047 return obj.schema 5048 5049 prep.schema_for_object = symbol_getter 5050 return prep 5051 5052 def _render_schema_translates(self, statement, schema_translate_map): 5053 d = schema_translate_map 5054 if None in d: 5055 d["_none"] = d[None] 5056 5057 def replace(m): 5058 name = m.group(2) 5059 effective_schema = d[name] 5060 if not effective_schema: 5061 effective_schema = self.dialect.default_schema_name 5062 if not effective_schema: 5063 # TODO: no coverage here 5064 raise exc.CompileError( 5065 "Dialect has no default schema name; can't " 5066 "use None as dynamic schema target." 5067 ) 5068 return self.quote_schema(effective_schema) 5069 5070 return re.sub(r"(__\[SCHEMA_([^\]]+)\])", replace, statement) 5071 5072 def _escape_identifier(self, value): 5073 """Escape an identifier. 5074 5075 Subclasses should override this to provide database-dependent 5076 escaping behavior. 5077 """ 5078 5079 value = value.replace(self.escape_quote, self.escape_to_quote) 5080 if self._double_percents: 5081 value = value.replace("%", "%%") 5082 return value 5083 5084 def _unescape_identifier(self, value): 5085 """Canonicalize an escaped identifier. 5086 5087 Subclasses should override this to provide database-dependent 5088 unescaping behavior that reverses _escape_identifier. 5089 """ 5090 5091 return value.replace(self.escape_to_quote, self.escape_quote) 5092 5093 def validate_sql_phrase(self, element, reg): 5094 """keyword sequence filter. 5095 5096 a filter for elements that are intended to represent keyword sequences, 5097 such as "INITIALLY", "INITIALLY DEFERRED", etc. no special characters 5098 should be present. 5099 5100 .. versionadded:: 1.3 5101 5102 """ 5103 5104 if element is not None and not reg.match(element): 5105 raise exc.CompileError( 5106 "Unexpected SQL phrase: %r (matching against %r)" 5107 % (element, reg.pattern) 5108 ) 5109 return element 5110 5111 def quote_identifier(self, value): 5112 """Quote an identifier. 5113 5114 Subclasses should override this to provide database-dependent 5115 quoting behavior. 5116 """ 5117 5118 return ( 5119 self.initial_quote 5120 + self._escape_identifier(value) 5121 + self.final_quote 5122 ) 5123 5124 def _requires_quotes(self, value): 5125 """Return True if the given identifier requires quoting.""" 5126 lc_value = value.lower() 5127 return ( 5128 lc_value in self.reserved_words 5129 or value[0] in self.illegal_initial_characters 5130 or not self.legal_characters.match(util.text_type(value)) 5131 or (lc_value != value) 5132 ) 5133 5134 def _requires_quotes_illegal_chars(self, value): 5135 """Return True if the given identifier requires quoting, but 5136 not taking case convention into account.""" 5137 return not self.legal_characters.match(util.text_type(value)) 5138 5139 def quote_schema(self, schema, force=None): 5140 """Conditionally quote a schema name. 5141 5142 5143 The name is quoted if it is a reserved word, contains quote-necessary 5144 characters, or is an instance of :class:`.quoted_name` which includes 5145 ``quote`` set to ``True``. 5146 5147 Subclasses can override this to provide database-dependent 5148 quoting behavior for schema names. 5149 5150 :param schema: string schema name 5151 :param force: unused 5152 5153 .. deprecated:: 0.9 5154 5155 The :paramref:`.IdentifierPreparer.quote_schema.force` 5156 parameter is deprecated and will be removed in a future 5157 release. This flag has no effect on the behavior of the 5158 :meth:`.IdentifierPreparer.quote` method; please refer to 5159 :class:`.quoted_name`. 5160 5161 """ 5162 if force is not None: 5163 # not using the util.deprecated_params() decorator in this 5164 # case because of the additional function call overhead on this 5165 # very performance-critical spot. 5166 util.warn_deprecated( 5167 "The IdentifierPreparer.quote_schema.force parameter is " 5168 "deprecated and will be removed in a future release. This " 5169 "flag has no effect on the behavior of the " 5170 "IdentifierPreparer.quote method; please refer to " 5171 "quoted_name().", 5172 # deprecated 0.9. warning from 1.3 5173 version="0.9", 5174 ) 5175 5176 return self.quote(schema) 5177 5178 def quote(self, ident, force=None): 5179 """Conditionally quote an identifier. 5180 5181 The identifier is quoted if it is a reserved word, contains 5182 quote-necessary characters, or is an instance of 5183 :class:`.quoted_name` which includes ``quote`` set to ``True``. 5184 5185 Subclasses can override this to provide database-dependent 5186 quoting behavior for identifier names. 5187 5188 :param ident: string identifier 5189 :param force: unused 5190 5191 .. deprecated:: 0.9 5192 5193 The :paramref:`.IdentifierPreparer.quote.force` 5194 parameter is deprecated and will be removed in a future 5195 release. This flag has no effect on the behavior of the 5196 :meth:`.IdentifierPreparer.quote` method; please refer to 5197 :class:`.quoted_name`. 5198 5199 """ 5200 if force is not None: 5201 # not using the util.deprecated_params() decorator in this 5202 # case because of the additional function call overhead on this 5203 # very performance-critical spot. 5204 util.warn_deprecated( 5205 "The IdentifierPreparer.quote.force parameter is " 5206 "deprecated and will be removed in a future release. This " 5207 "flag has no effect on the behavior of the " 5208 "IdentifierPreparer.quote method; please refer to " 5209 "quoted_name().", 5210 # deprecated 0.9. warning from 1.3 5211 version="0.9", 5212 ) 5213 5214 force = getattr(ident, "quote", None) 5215 5216 if force is None: 5217 if ident in self._strings: 5218 return self._strings[ident] 5219 else: 5220 if self._requires_quotes(ident): 5221 self._strings[ident] = self.quote_identifier(ident) 5222 else: 5223 self._strings[ident] = ident 5224 return self._strings[ident] 5225 elif force: 5226 return self.quote_identifier(ident) 5227 else: 5228 return ident 5229 5230 def format_collation(self, collation_name): 5231 if self.quote_case_sensitive_collations: 5232 return self.quote(collation_name) 5233 else: 5234 return collation_name 5235 5236 def format_sequence(self, sequence, use_schema=True): 5237 name = self.quote(sequence.name) 5238 5239 effective_schema = self.schema_for_object(sequence) 5240 5241 if ( 5242 not self.omit_schema 5243 and use_schema 5244 and effective_schema is not None 5245 ): 5246 name = self.quote_schema(effective_schema) + "." + name 5247 return name 5248 5249 def format_label(self, label, name=None): 5250 return self.quote(name or label.name) 5251 5252 def format_alias(self, alias, name=None): 5253 return self.quote(name or alias.name) 5254 5255 def format_savepoint(self, savepoint, name=None): 5256 # Running the savepoint name through quoting is unnecessary 5257 # for all known dialects. This is here to support potential 5258 # third party use cases 5259 ident = name or savepoint.ident 5260 if self._requires_quotes(ident): 5261 ident = self.quote_identifier(ident) 5262 return ident 5263 5264 @util.preload_module("sqlalchemy.sql.naming") 5265 def format_constraint(self, constraint, _alembic_quote=True): 5266 naming = util.preloaded.sql_naming 5267 5268 if constraint.name is elements._NONE_NAME: 5269 name = naming._constraint_name_for_table( 5270 constraint, constraint.table 5271 ) 5272 5273 if name is None: 5274 return None 5275 else: 5276 name = constraint.name 5277 5278 if constraint.__visit_name__ == "index": 5279 return self.truncate_and_render_index_name( 5280 name, _alembic_quote=_alembic_quote 5281 ) 5282 else: 5283 return self.truncate_and_render_constraint_name( 5284 name, _alembic_quote=_alembic_quote 5285 ) 5286 5287 def truncate_and_render_index_name(self, name, _alembic_quote=True): 5288 # calculate these at format time so that ad-hoc changes 5289 # to dialect.max_identifier_length etc. can be reflected 5290 # as IdentifierPreparer is long lived 5291 max_ = ( 5292 self.dialect.max_index_name_length 5293 or self.dialect.max_identifier_length 5294 ) 5295 return self._truncate_and_render_maxlen_name( 5296 name, max_, _alembic_quote 5297 ) 5298 5299 def truncate_and_render_constraint_name(self, name, _alembic_quote=True): 5300 # calculate these at format time so that ad-hoc changes 5301 # to dialect.max_identifier_length etc. can be reflected 5302 # as IdentifierPreparer is long lived 5303 max_ = ( 5304 self.dialect.max_constraint_name_length 5305 or self.dialect.max_identifier_length 5306 ) 5307 return self._truncate_and_render_maxlen_name( 5308 name, max_, _alembic_quote 5309 ) 5310 5311 def _truncate_and_render_maxlen_name(self, name, max_, _alembic_quote): 5312 if isinstance(name, elements._truncated_label): 5313 if len(name) > max_: 5314 name = name[0 : max_ - 8] + "_" + util.md5_hex(name)[-4:] 5315 else: 5316 self.dialect.validate_identifier(name) 5317 5318 if not _alembic_quote: 5319 return name 5320 else: 5321 return self.quote(name) 5322 5323 def format_index(self, index): 5324 return self.format_constraint(index) 5325 5326 def format_table(self, table, use_schema=True, name=None): 5327 """Prepare a quoted table and schema name.""" 5328 5329 if name is None: 5330 name = table.name 5331 5332 result = self.quote(name) 5333 5334 effective_schema = self.schema_for_object(table) 5335 5336 if not self.omit_schema and use_schema and effective_schema: 5337 result = self.quote_schema(effective_schema) + "." + result 5338 return result 5339 5340 def format_schema(self, name): 5341 """Prepare a quoted schema name.""" 5342 5343 return self.quote(name) 5344 5345 def format_label_name( 5346 self, 5347 name, 5348 anon_map=None, 5349 ): 5350 """Prepare a quoted column name.""" 5351 5352 if anon_map is not None and isinstance( 5353 name, elements._truncated_label 5354 ): 5355 name = name.apply_map(anon_map) 5356 5357 return self.quote(name) 5358 5359 def format_column( 5360 self, 5361 column, 5362 use_table=False, 5363 name=None, 5364 table_name=None, 5365 use_schema=False, 5366 anon_map=None, 5367 ): 5368 """Prepare a quoted column name.""" 5369 5370 if name is None: 5371 name = column.name 5372 5373 if anon_map is not None and isinstance( 5374 name, elements._truncated_label 5375 ): 5376 name = name.apply_map(anon_map) 5377 5378 if not getattr(column, "is_literal", False): 5379 if use_table: 5380 return ( 5381 self.format_table( 5382 column.table, use_schema=use_schema, name=table_name 5383 ) 5384 + "." 5385 + self.quote(name) 5386 ) 5387 else: 5388 return self.quote(name) 5389 else: 5390 # literal textual elements get stuck into ColumnClause a lot, 5391 # which shouldn't get quoted 5392 5393 if use_table: 5394 return ( 5395 self.format_table( 5396 column.table, use_schema=use_schema, name=table_name 5397 ) 5398 + "." 5399 + name 5400 ) 5401 else: 5402 return name 5403 5404 def format_table_seq(self, table, use_schema=True): 5405 """Format table name and schema as a tuple.""" 5406 5407 # Dialects with more levels in their fully qualified references 5408 # ('database', 'owner', etc.) could override this and return 5409 # a longer sequence. 5410 5411 effective_schema = self.schema_for_object(table) 5412 5413 if not self.omit_schema and use_schema and effective_schema: 5414 return ( 5415 self.quote_schema(effective_schema), 5416 self.format_table(table, use_schema=False), 5417 ) 5418 else: 5419 return (self.format_table(table, use_schema=False),) 5420 5421 @util.memoized_property 5422 def _r_identifiers(self): 5423 initial, final, escaped_final = [ 5424 re.escape(s) 5425 for s in ( 5426 self.initial_quote, 5427 self.final_quote, 5428 self._escape_identifier(self.final_quote), 5429 ) 5430 ] 5431 r = re.compile( 5432 r"(?:" 5433 r"(?:%(initial)s((?:%(escaped)s|[^%(final)s])+)%(final)s" 5434 r"|([^\.]+))(?=\.|$))+" 5435 % {"initial": initial, "final": final, "escaped": escaped_final} 5436 ) 5437 return r 5438 5439 def unformat_identifiers(self, identifiers): 5440 """Unpack 'schema.table.column'-like strings into components.""" 5441 5442 r = self._r_identifiers 5443 return [ 5444 self._unescape_identifier(i) 5445 for i in [a or b for a, b in r.findall(identifiers)] 5446 ] 5447