Expressions
Every AST node in SQLGlot is represented by a subclass of Expression
.
This module contains the implementation of all supported Expression
types. Additionally,
it exposes a number of helper functions, which are mainly used to programmatically build
SQL expressions, such as select
.
1""" 2## Expressions 3 4Every AST node in SQLGlot is represented by a subclass of `Expression`. 5 6This module contains the implementation of all supported `Expression` types. Additionally, 7it exposes a number of helper functions, which are mainly used to programmatically build 8SQL expressions, such as `sqlglot.expressions.select`. 9 10---- 11""" 12 13from __future__ import annotations 14 15import datetime 16import math 17import numbers 18import re 19import typing as t 20from collections import deque 21from copy import deepcopy 22from enum import auto 23from functools import reduce 24 25from sqlglot._typing import E 26from sqlglot.errors import ErrorLevel, ParseError 27from sqlglot.helper import ( 28 AutoName, 29 camel_to_snake_case, 30 ensure_collection, 31 ensure_list, 32 seq_get, 33 subclasses, 34) 35from sqlglot.tokens import Token 36 37if t.TYPE_CHECKING: 38 from sqlglot.dialects.dialect import DialectType 39 40 41class _Expression(type): 42 def __new__(cls, clsname, bases, attrs): 43 klass = super().__new__(cls, clsname, bases, attrs) 44 45 # When an Expression class is created, its key is automatically set to be 46 # the lowercase version of the class' name. 47 klass.key = clsname.lower() 48 49 # This is so that docstrings are not inherited in pdoc 50 klass.__doc__ = klass.__doc__ or "" 51 52 return klass 53 54 55SQLGLOT_META = "sqlglot.meta" 56TABLE_PARTS = ("this", "db", "catalog") 57 58 59class Expression(metaclass=_Expression): 60 """ 61 The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary 62 context, such as its child expressions, their names (arg keys), and whether a given child expression 63 is optional or not. 64 65 Attributes: 66 key: a unique key for each class in the Expression hierarchy. This is useful for hashing 67 and representing expressions as strings. 68 arg_types: determines what arguments (child nodes) are supported by an expression. It 69 maps arg keys to booleans that indicate whether the corresponding args are optional. 70 parent: a reference to the parent expression (or None, in case of root expressions). 71 arg_key: the arg key an expression is associated with, i.e. the name its parent expression 72 uses to refer to it. 73 comments: a list of comments that are associated with a given expression. This is used in 74 order to preserve comments when transpiling SQL code. 75 type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the 76 optimizer, in order to enable some transformations that require type information. 77 meta: a dictionary that can be used to store useful metadata for a given expression. 78 79 Example: 80 >>> class Foo(Expression): 81 ... arg_types = {"this": True, "expression": False} 82 83 The above definition informs us that Foo is an Expression that requires an argument called 84 "this" and may also optionally receive an argument called "expression". 85 86 Args: 87 args: a mapping used for retrieving the arguments of an expression, given their arg keys. 88 """ 89 90 key = "expression" 91 arg_types = {"this": True} 92 __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash") 93 94 def __init__(self, **args: t.Any): 95 self.args: t.Dict[str, t.Any] = args 96 self.parent: t.Optional[Expression] = None 97 self.arg_key: t.Optional[str] = None 98 self.comments: t.Optional[t.List[str]] = None 99 self._type: t.Optional[DataType] = None 100 self._meta: t.Optional[t.Dict[str, t.Any]] = None 101 self._hash: t.Optional[int] = None 102 103 for arg_key, value in self.args.items(): 104 self._set_parent(arg_key, value) 105 106 def __eq__(self, other) -> bool: 107 return type(self) is type(other) and hash(self) == hash(other) 108 109 @property 110 def hashable_args(self) -> t.Any: 111 return frozenset( 112 (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v)) 113 for k, v in self.args.items() 114 if not (v is None or v is False or (type(v) is list and not v)) 115 ) 116 117 def __hash__(self) -> int: 118 if self._hash is not None: 119 return self._hash 120 121 return hash((self.__class__, self.hashable_args)) 122 123 @property 124 def this(self) -> t.Any: 125 """ 126 Retrieves the argument with key "this". 127 """ 128 return self.args.get("this") 129 130 @property 131 def expression(self) -> t.Any: 132 """ 133 Retrieves the argument with key "expression". 134 """ 135 return self.args.get("expression") 136 137 @property 138 def expressions(self) -> t.List[t.Any]: 139 """ 140 Retrieves the argument with key "expressions". 141 """ 142 return self.args.get("expressions") or [] 143 144 def text(self, key) -> str: 145 """ 146 Returns a textual representation of the argument corresponding to "key". This can only be used 147 for args that are strings or leaf Expression instances, such as identifiers and literals. 148 """ 149 field = self.args.get(key) 150 if isinstance(field, str): 151 return field 152 if isinstance(field, (Identifier, Literal, Var)): 153 return field.this 154 if isinstance(field, (Star, Null)): 155 return field.name 156 return "" 157 158 @property 159 def is_string(self) -> bool: 160 """ 161 Checks whether a Literal expression is a string. 162 """ 163 return isinstance(self, Literal) and self.args["is_string"] 164 165 @property 166 def is_number(self) -> bool: 167 """ 168 Checks whether a Literal expression is a number. 169 """ 170 return isinstance(self, Literal) and not self.args["is_string"] 171 172 @property 173 def is_int(self) -> bool: 174 """ 175 Checks whether a Literal expression is an integer. 176 """ 177 if self.is_number: 178 try: 179 int(self.name) 180 return True 181 except ValueError: 182 pass 183 return False 184 185 @property 186 def is_star(self) -> bool: 187 """Checks whether an expression is a star.""" 188 return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star)) 189 190 @property 191 def alias(self) -> str: 192 """ 193 Returns the alias of the expression, or an empty string if it's not aliased. 194 """ 195 if isinstance(self.args.get("alias"), TableAlias): 196 return self.args["alias"].name 197 return self.text("alias") 198 199 @property 200 def alias_column_names(self) -> t.List[str]: 201 table_alias = self.args.get("alias") 202 if not table_alias: 203 return [] 204 return [c.name for c in table_alias.args.get("columns") or []] 205 206 @property 207 def name(self) -> str: 208 return self.text("this") 209 210 @property 211 def alias_or_name(self) -> str: 212 return self.alias or self.name 213 214 @property 215 def output_name(self) -> str: 216 """ 217 Name of the output column if this expression is a selection. 218 219 If the Expression has no output name, an empty string is returned. 220 221 Example: 222 >>> from sqlglot import parse_one 223 >>> parse_one("SELECT a").expressions[0].output_name 224 'a' 225 >>> parse_one("SELECT b AS c").expressions[0].output_name 226 'c' 227 >>> parse_one("SELECT 1 + 2").expressions[0].output_name 228 '' 229 """ 230 return "" 231 232 @property 233 def type(self) -> t.Optional[DataType]: 234 return self._type 235 236 @type.setter 237 def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None: 238 if dtype and not isinstance(dtype, DataType): 239 dtype = DataType.build(dtype) 240 self._type = dtype # type: ignore 241 242 def is_type(self, *dtypes) -> bool: 243 return self.type is not None and self.type.is_type(*dtypes) 244 245 @property 246 def meta(self) -> t.Dict[str, t.Any]: 247 if self._meta is None: 248 self._meta = {} 249 return self._meta 250 251 def __deepcopy__(self, memo): 252 copy = self.__class__(**deepcopy(self.args)) 253 if self.comments is not None: 254 copy.comments = deepcopy(self.comments) 255 256 if self._type is not None: 257 copy._type = self._type.copy() 258 259 if self._meta is not None: 260 copy._meta = deepcopy(self._meta) 261 262 return copy 263 264 def copy(self): 265 """ 266 Returns a deep copy of the expression. 267 """ 268 new = deepcopy(self) 269 new.parent = self.parent 270 return new 271 272 def add_comments(self, comments: t.Optional[t.List[str]]) -> None: 273 if self.comments is None: 274 self.comments = [] 275 if comments: 276 for comment in comments: 277 _, *meta = comment.split(SQLGLOT_META) 278 if meta: 279 for kv in "".join(meta).split(","): 280 k, *v = kv.split("=") 281 value = v[0].strip() if v else True 282 self.meta[k.strip()] = value 283 self.comments.append(comment) 284 285 def append(self, arg_key: str, value: t.Any) -> None: 286 """ 287 Appends value to arg_key if it's a list or sets it as a new list. 288 289 Args: 290 arg_key (str): name of the list expression arg 291 value (Any): value to append to the list 292 """ 293 if not isinstance(self.args.get(arg_key), list): 294 self.args[arg_key] = [] 295 self.args[arg_key].append(value) 296 self._set_parent(arg_key, value) 297 298 def set(self, arg_key: str, value: t.Any) -> None: 299 """ 300 Sets arg_key to value. 301 302 Args: 303 arg_key: name of the expression arg. 304 value: value to set the arg to. 305 """ 306 if value is None: 307 self.args.pop(arg_key, None) 308 return 309 310 self.args[arg_key] = value 311 self._set_parent(arg_key, value) 312 313 def _set_parent(self, arg_key: str, value: t.Any) -> None: 314 if hasattr(value, "parent"): 315 value.parent = self 316 value.arg_key = arg_key 317 elif type(value) is list: 318 for v in value: 319 if hasattr(v, "parent"): 320 v.parent = self 321 v.arg_key = arg_key 322 323 @property 324 def depth(self) -> int: 325 """ 326 Returns the depth of this tree. 327 """ 328 if self.parent: 329 return self.parent.depth + 1 330 return 0 331 332 def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]: 333 """Yields the key and expression for all arguments, exploding list args.""" 334 for k, vs in self.args.items(): 335 if type(vs) is list: 336 for v in vs: 337 if hasattr(v, "parent"): 338 yield k, v 339 else: 340 if hasattr(vs, "parent"): 341 yield k, vs 342 343 def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]: 344 """ 345 Returns the first node in this tree which matches at least one of 346 the specified types. 347 348 Args: 349 expression_types: the expression type(s) to match. 350 bfs: whether to search the AST using the BFS algorithm (DFS is used if false). 351 352 Returns: 353 The node which matches the criteria or None if no such node was found. 354 """ 355 return next(self.find_all(*expression_types, bfs=bfs), None) 356 357 def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]: 358 """ 359 Returns a generator object which visits all nodes in this tree and only 360 yields those that match at least one of the specified expression types. 361 362 Args: 363 expression_types: the expression type(s) to match. 364 bfs: whether to search the AST using the BFS algorithm (DFS is used if false). 365 366 Returns: 367 The generator object. 368 """ 369 for expression, *_ in self.walk(bfs=bfs): 370 if isinstance(expression, expression_types): 371 yield expression 372 373 def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]: 374 """ 375 Returns a nearest parent matching expression_types. 376 377 Args: 378 expression_types: the expression type(s) to match. 379 380 Returns: 381 The parent node. 382 """ 383 ancestor = self.parent 384 while ancestor and not isinstance(ancestor, expression_types): 385 ancestor = ancestor.parent 386 return t.cast(E, ancestor) 387 388 @property 389 def parent_select(self) -> t.Optional[Select]: 390 """ 391 Returns the parent select statement. 392 """ 393 return self.find_ancestor(Select) 394 395 @property 396 def same_parent(self) -> bool: 397 """Returns if the parent is the same class as itself.""" 398 return type(self.parent) is self.__class__ 399 400 def root(self) -> Expression: 401 """ 402 Returns the root expression of this tree. 403 """ 404 expression = self 405 while expression.parent: 406 expression = expression.parent 407 return expression 408 409 def walk(self, bfs=True, prune=None): 410 """ 411 Returns a generator object which visits all nodes in this tree. 412 413 Args: 414 bfs (bool): if set to True the BFS traversal order will be applied, 415 otherwise the DFS traversal will be used instead. 416 prune ((node, parent, arg_key) -> bool): callable that returns True if 417 the generator should stop traversing this branch of the tree. 418 419 Returns: 420 the generator object. 421 """ 422 if bfs: 423 yield from self.bfs(prune=prune) 424 else: 425 yield from self.dfs(prune=prune) 426 427 def dfs(self, parent=None, key=None, prune=None): 428 """ 429 Returns a generator object which visits all nodes in this tree in 430 the DFS (Depth-first) order. 431 432 Returns: 433 The generator object. 434 """ 435 parent = parent or self.parent 436 yield self, parent, key 437 if prune and prune(self, parent, key): 438 return 439 440 for k, v in self.iter_expressions(): 441 yield from v.dfs(self, k, prune) 442 443 def bfs(self, prune=None): 444 """ 445 Returns a generator object which visits all nodes in this tree in 446 the BFS (Breadth-first) order. 447 448 Returns: 449 The generator object. 450 """ 451 queue = deque([(self, self.parent, None)]) 452 453 while queue: 454 item, parent, key = queue.popleft() 455 456 yield item, parent, key 457 if prune and prune(item, parent, key): 458 continue 459 460 for k, v in item.iter_expressions(): 461 queue.append((v, item, k)) 462 463 def unnest(self): 464 """ 465 Returns the first non parenthesis child or self. 466 """ 467 expression = self 468 while type(expression) is Paren: 469 expression = expression.this 470 return expression 471 472 def unalias(self): 473 """ 474 Returns the inner expression if this is an Alias. 475 """ 476 if isinstance(self, Alias): 477 return self.this 478 return self 479 480 def unnest_operands(self): 481 """ 482 Returns unnested operands as a tuple. 483 """ 484 return tuple(arg.unnest() for _, arg in self.iter_expressions()) 485 486 def flatten(self, unnest=True): 487 """ 488 Returns a generator which yields child nodes who's parents are the same class. 489 490 A AND B AND C -> [A, B, C] 491 """ 492 for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__): 493 if not type(node) is self.__class__: 494 yield node.unnest() if unnest and not isinstance(node, Subquery) else node 495 496 def __str__(self) -> str: 497 return self.sql() 498 499 def __repr__(self) -> str: 500 return self._to_s() 501 502 def sql(self, dialect: DialectType = None, **opts) -> str: 503 """ 504 Returns SQL string representation of this tree. 505 506 Args: 507 dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql"). 508 opts: other `sqlglot.generator.Generator` options. 509 510 Returns: 511 The SQL string. 512 """ 513 from sqlglot.dialects import Dialect 514 515 return Dialect.get_or_raise(dialect)().generate(self, **opts) 516 517 def _to_s(self, hide_missing: bool = True, level: int = 0) -> str: 518 indent = "" if not level else "\n" 519 indent += "".join([" "] * level) 520 left = f"({self.key.upper()} " 521 522 args: t.Dict[str, t.Any] = { 523 k: ", ".join( 524 v._to_s(hide_missing=hide_missing, level=level + 1) 525 if hasattr(v, "_to_s") 526 else str(v) 527 for v in ensure_list(vs) 528 if v is not None 529 ) 530 for k, vs in self.args.items() 531 } 532 args["comments"] = self.comments 533 args["type"] = self.type 534 args = {k: v for k, v in args.items() if v or not hide_missing} 535 536 right = ", ".join(f"{k}: {v}" for k, v in args.items()) 537 right += ")" 538 539 return indent + left + right 540 541 def transform(self, fun, *args, copy=True, **kwargs): 542 """ 543 Recursively visits all tree nodes (excluding already transformed ones) 544 and applies the given transformation function to each node. 545 546 Args: 547 fun (function): a function which takes a node as an argument and returns a 548 new transformed node or the same node without modifications. If the function 549 returns None, then the corresponding node will be removed from the syntax tree. 550 copy (bool): if set to True a new tree instance is constructed, otherwise the tree is 551 modified in place. 552 553 Returns: 554 The transformed tree. 555 """ 556 node = self.copy() if copy else self 557 new_node = fun(node, *args, **kwargs) 558 559 if new_node is None or not isinstance(new_node, Expression): 560 return new_node 561 if new_node is not node: 562 new_node.parent = node.parent 563 return new_node 564 565 replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs)) 566 return new_node 567 568 @t.overload 569 def replace(self, expression: E) -> E: 570 ... 571 572 @t.overload 573 def replace(self, expression: None) -> None: 574 ... 575 576 def replace(self, expression): 577 """ 578 Swap out this expression with a new expression. 579 580 For example:: 581 582 >>> tree = Select().select("x").from_("tbl") 583 >>> tree.find(Column).replace(Column(this="y")) 584 (COLUMN this: y) 585 >>> tree.sql() 586 'SELECT y FROM tbl' 587 588 Args: 589 expression: new node 590 591 Returns: 592 The new expression or expressions. 593 """ 594 if not self.parent: 595 return expression 596 597 parent = self.parent 598 self.parent = None 599 600 replace_children(parent, lambda child: expression if child is self else child) 601 return expression 602 603 def pop(self: E) -> E: 604 """ 605 Remove this expression from its AST. 606 607 Returns: 608 The popped expression. 609 """ 610 self.replace(None) 611 return self 612 613 def assert_is(self, type_: t.Type[E]) -> E: 614 """ 615 Assert that this `Expression` is an instance of `type_`. 616 617 If it is NOT an instance of `type_`, this raises an assertion error. 618 Otherwise, this returns this expression. 619 620 Examples: 621 This is useful for type security in chained expressions: 622 623 >>> import sqlglot 624 >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql() 625 'SELECT x, z FROM y' 626 """ 627 assert isinstance(self, type_) 628 return self 629 630 def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]: 631 """ 632 Checks if this expression is valid (e.g. all mandatory args are set). 633 634 Args: 635 args: a sequence of values that were used to instantiate a Func expression. This is used 636 to check that the provided arguments don't exceed the function argument limit. 637 638 Returns: 639 A list of error messages for all possible errors that were found. 640 """ 641 errors: t.List[str] = [] 642 643 for k in self.args: 644 if k not in self.arg_types: 645 errors.append(f"Unexpected keyword: '{k}' for {self.__class__}") 646 for k, mandatory in self.arg_types.items(): 647 v = self.args.get(k) 648 if mandatory and (v is None or (isinstance(v, list) and not v)): 649 errors.append(f"Required keyword: '{k}' missing for {self.__class__}") 650 651 if ( 652 args 653 and isinstance(self, Func) 654 and len(args) > len(self.arg_types) 655 and not self.is_var_len_args 656 ): 657 errors.append( 658 f"The number of provided arguments ({len(args)}) is greater than " 659 f"the maximum number of supported arguments ({len(self.arg_types)})" 660 ) 661 662 return errors 663 664 def dump(self): 665 """ 666 Dump this Expression to a JSON-serializable dict. 667 """ 668 from sqlglot.serde import dump 669 670 return dump(self) 671 672 @classmethod 673 def load(cls, obj): 674 """ 675 Load a dict (as returned by `Expression.dump`) into an Expression instance. 676 """ 677 from sqlglot.serde import load 678 679 return load(obj) 680 681 def and_( 682 self, 683 *expressions: t.Optional[ExpOrStr], 684 dialect: DialectType = None, 685 copy: bool = True, 686 **opts, 687 ) -> Condition: 688 """ 689 AND this condition with one or multiple expressions. 690 691 Example: 692 >>> condition("x=1").and_("y=1").sql() 693 'x = 1 AND y = 1' 694 695 Args: 696 *expressions: the SQL code strings to parse. 697 If an `Expression` instance is passed, it will be used as-is. 698 dialect: the dialect used to parse the input expression. 699 copy: whether or not to copy the involved expressions (only applies to Expressions). 700 opts: other options to use to parse the input expressions. 701 702 Returns: 703 The new And condition. 704 """ 705 return and_(self, *expressions, dialect=dialect, copy=copy, **opts) 706 707 def or_( 708 self, 709 *expressions: t.Optional[ExpOrStr], 710 dialect: DialectType = None, 711 copy: bool = True, 712 **opts, 713 ) -> Condition: 714 """ 715 OR this condition with one or multiple expressions. 716 717 Example: 718 >>> condition("x=1").or_("y=1").sql() 719 'x = 1 OR y = 1' 720 721 Args: 722 *expressions: the SQL code strings to parse. 723 If an `Expression` instance is passed, it will be used as-is. 724 dialect: the dialect used to parse the input expression. 725 copy: whether or not to copy the involved expressions (only applies to Expressions). 726 opts: other options to use to parse the input expressions. 727 728 Returns: 729 The new Or condition. 730 """ 731 return or_(self, *expressions, dialect=dialect, copy=copy, **opts) 732 733 def not_(self, copy: bool = True): 734 """ 735 Wrap this condition with NOT. 736 737 Example: 738 >>> condition("x=1").not_().sql() 739 'NOT x = 1' 740 741 Args: 742 copy: whether or not to copy this object. 743 744 Returns: 745 The new Not instance. 746 """ 747 return not_(self, copy=copy) 748 749 def as_( 750 self, 751 alias: str | Identifier, 752 quoted: t.Optional[bool] = None, 753 dialect: DialectType = None, 754 copy: bool = True, 755 **opts, 756 ) -> Alias: 757 return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts) 758 759 def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E: 760 this = self.copy() 761 other = convert(other, copy=True) 762 if not isinstance(this, klass) and not isinstance(other, klass): 763 this = _wrap(this, Binary) 764 other = _wrap(other, Binary) 765 if reverse: 766 return klass(this=other, expression=this) 767 return klass(this=this, expression=other) 768 769 def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket: 770 return Bracket( 771 this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)] 772 ) 773 774 def __iter__(self) -> t.Iterator: 775 if "expressions" in self.arg_types: 776 return iter(self.args.get("expressions") or []) 777 # We define this because __getitem__ converts Expression into an iterable, which is 778 # problematic because one can hit infinite loops if they do "for x in some_expr: ..." 779 # See: https://peps.python.org/pep-0234/ 780 raise TypeError(f"'{self.__class__.__name__}' object is not iterable") 781 782 def isin( 783 self, 784 *expressions: t.Any, 785 query: t.Optional[ExpOrStr] = None, 786 unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None, 787 copy: bool = True, 788 **opts, 789 ) -> In: 790 return In( 791 this=maybe_copy(self, copy), 792 expressions=[convert(e, copy=copy) for e in expressions], 793 query=maybe_parse(query, copy=copy, **opts) if query else None, 794 unnest=Unnest( 795 expressions=[ 796 maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest) 797 ] 798 ) 799 if unnest 800 else None, 801 ) 802 803 def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between: 804 return Between( 805 this=maybe_copy(self, copy), 806 low=convert(low, copy=copy, **opts), 807 high=convert(high, copy=copy, **opts), 808 ) 809 810 def is_(self, other: ExpOrStr) -> Is: 811 return self._binop(Is, other) 812 813 def like(self, other: ExpOrStr) -> Like: 814 return self._binop(Like, other) 815 816 def ilike(self, other: ExpOrStr) -> ILike: 817 return self._binop(ILike, other) 818 819 def eq(self, other: t.Any) -> EQ: 820 return self._binop(EQ, other) 821 822 def neq(self, other: t.Any) -> NEQ: 823 return self._binop(NEQ, other) 824 825 def rlike(self, other: ExpOrStr) -> RegexpLike: 826 return self._binop(RegexpLike, other) 827 828 def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div: 829 div = self._binop(Div, other) 830 div.args["typed"] = typed 831 div.args["safe"] = safe 832 return div 833 834 def __lt__(self, other: t.Any) -> LT: 835 return self._binop(LT, other) 836 837 def __le__(self, other: t.Any) -> LTE: 838 return self._binop(LTE, other) 839 840 def __gt__(self, other: t.Any) -> GT: 841 return self._binop(GT, other) 842 843 def __ge__(self, other: t.Any) -> GTE: 844 return self._binop(GTE, other) 845 846 def __add__(self, other: t.Any) -> Add: 847 return self._binop(Add, other) 848 849 def __radd__(self, other: t.Any) -> Add: 850 return self._binop(Add, other, reverse=True) 851 852 def __sub__(self, other: t.Any) -> Sub: 853 return self._binop(Sub, other) 854 855 def __rsub__(self, other: t.Any) -> Sub: 856 return self._binop(Sub, other, reverse=True) 857 858 def __mul__(self, other: t.Any) -> Mul: 859 return self._binop(Mul, other) 860 861 def __rmul__(self, other: t.Any) -> Mul: 862 return self._binop(Mul, other, reverse=True) 863 864 def __truediv__(self, other: t.Any) -> Div: 865 return self._binop(Div, other) 866 867 def __rtruediv__(self, other: t.Any) -> Div: 868 return self._binop(Div, other, reverse=True) 869 870 def __floordiv__(self, other: t.Any) -> IntDiv: 871 return self._binop(IntDiv, other) 872 873 def __rfloordiv__(self, other: t.Any) -> IntDiv: 874 return self._binop(IntDiv, other, reverse=True) 875 876 def __mod__(self, other: t.Any) -> Mod: 877 return self._binop(Mod, other) 878 879 def __rmod__(self, other: t.Any) -> Mod: 880 return self._binop(Mod, other, reverse=True) 881 882 def __pow__(self, other: t.Any) -> Pow: 883 return self._binop(Pow, other) 884 885 def __rpow__(self, other: t.Any) -> Pow: 886 return self._binop(Pow, other, reverse=True) 887 888 def __and__(self, other: t.Any) -> And: 889 return self._binop(And, other) 890 891 def __rand__(self, other: t.Any) -> And: 892 return self._binop(And, other, reverse=True) 893 894 def __or__(self, other: t.Any) -> Or: 895 return self._binop(Or, other) 896 897 def __ror__(self, other: t.Any) -> Or: 898 return self._binop(Or, other, reverse=True) 899 900 def __neg__(self) -> Neg: 901 return Neg(this=_wrap(self.copy(), Binary)) 902 903 def __invert__(self) -> Not: 904 return not_(self.copy()) 905 906 907IntoType = t.Union[ 908 str, 909 t.Type[Expression], 910 t.Collection[t.Union[str, t.Type[Expression]]], 911] 912ExpOrStr = t.Union[str, Expression] 913 914 915class Condition(Expression): 916 """Logical conditions like x AND y, or simply x""" 917 918 919class Predicate(Condition): 920 """Relationships like x = y, x > 1, x >= y.""" 921 922 923class DerivedTable(Expression): 924 @property 925 def selects(self) -> t.List[Expression]: 926 return self.this.selects if isinstance(self.this, Subqueryable) else [] 927 928 @property 929 def named_selects(self) -> t.List[str]: 930 return [select.output_name for select in self.selects] 931 932 933class Unionable(Expression): 934 def union( 935 self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts 936 ) -> Unionable: 937 """ 938 Builds a UNION expression. 939 940 Example: 941 >>> import sqlglot 942 >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql() 943 'SELECT * FROM foo UNION SELECT * FROM bla' 944 945 Args: 946 expression: the SQL code string. 947 If an `Expression` instance is passed, it will be used as-is. 948 distinct: set the DISTINCT flag if and only if this is true. 949 dialect: the dialect used to parse the input expression. 950 opts: other options to use to parse the input expressions. 951 952 Returns: 953 The new Union expression. 954 """ 955 return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts) 956 957 def intersect( 958 self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts 959 ) -> Unionable: 960 """ 961 Builds an INTERSECT expression. 962 963 Example: 964 >>> import sqlglot 965 >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql() 966 'SELECT * FROM foo INTERSECT SELECT * FROM bla' 967 968 Args: 969 expression: the SQL code string. 970 If an `Expression` instance is passed, it will be used as-is. 971 distinct: set the DISTINCT flag if and only if this is true. 972 dialect: the dialect used to parse the input expression. 973 opts: other options to use to parse the input expressions. 974 975 Returns: 976 The new Intersect expression. 977 """ 978 return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts) 979 980 def except_( 981 self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts 982 ) -> Unionable: 983 """ 984 Builds an EXCEPT expression. 985 986 Example: 987 >>> import sqlglot 988 >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql() 989 'SELECT * FROM foo EXCEPT SELECT * FROM bla' 990 991 Args: 992 expression: the SQL code string. 993 If an `Expression` instance is passed, it will be used as-is. 994 distinct: set the DISTINCT flag if and only if this is true. 995 dialect: the dialect used to parse the input expression. 996 opts: other options to use to parse the input expressions. 997 998 Returns: 999 The new Except expression. 1000 """ 1001 return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts) 1002 1003 1004class UDTF(DerivedTable, Unionable): 1005 @property 1006 def selects(self) -> t.List[Expression]: 1007 alias = self.args.get("alias") 1008 return alias.columns if alias else [] 1009 1010 1011class Cache(Expression): 1012 arg_types = { 1013 "this": True, 1014 "lazy": False, 1015 "options": False, 1016 "expression": False, 1017 } 1018 1019 1020class Uncache(Expression): 1021 arg_types = {"this": True, "exists": False} 1022 1023 1024class Refresh(Expression): 1025 pass 1026 1027 1028class DDL(Expression): 1029 @property 1030 def ctes(self): 1031 with_ = self.args.get("with") 1032 if not with_: 1033 return [] 1034 return with_.expressions 1035 1036 @property 1037 def named_selects(self) -> t.List[str]: 1038 if isinstance(self.expression, Subqueryable): 1039 return self.expression.named_selects 1040 return [] 1041 1042 @property 1043 def selects(self) -> t.List[Expression]: 1044 if isinstance(self.expression, Subqueryable): 1045 return self.expression.selects 1046 return [] 1047 1048 1049class DML(Expression): 1050 def returning( 1051 self, 1052 expression: ExpOrStr, 1053 dialect: DialectType = None, 1054 copy: bool = True, 1055 **opts, 1056 ) -> DML: 1057 """ 1058 Set the RETURNING expression. Not supported by all dialects. 1059 1060 Example: 1061 >>> delete("tbl").returning("*", dialect="postgres").sql() 1062 'DELETE FROM tbl RETURNING *' 1063 1064 Args: 1065 expression: the SQL code strings to parse. 1066 If an `Expression` instance is passed, it will be used as-is. 1067 dialect: the dialect used to parse the input expressions. 1068 copy: if `False`, modify this expression instance in-place. 1069 opts: other options to use to parse the input expressions. 1070 1071 Returns: 1072 Delete: the modified expression. 1073 """ 1074 return _apply_builder( 1075 expression=expression, 1076 instance=self, 1077 arg="returning", 1078 prefix="RETURNING", 1079 dialect=dialect, 1080 copy=copy, 1081 into=Returning, 1082 **opts, 1083 ) 1084 1085 1086class Create(DDL): 1087 arg_types = { 1088 "with": False, 1089 "this": True, 1090 "kind": True, 1091 "expression": False, 1092 "exists": False, 1093 "properties": False, 1094 "replace": False, 1095 "unique": False, 1096 "indexes": False, 1097 "no_schema_binding": False, 1098 "begin": False, 1099 "end": False, 1100 "clone": False, 1101 } 1102 1103 1104# https://docs.snowflake.com/en/sql-reference/sql/create-clone 1105# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement 1106# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy 1107class Clone(Expression): 1108 arg_types = { 1109 "this": True, 1110 "when": False, 1111 "kind": False, 1112 "shallow": False, 1113 "expression": False, 1114 "copy": False, 1115 } 1116 1117 1118class Describe(Expression): 1119 arg_types = {"this": True, "kind": False, "expressions": False} 1120 1121 1122class Kill(Expression): 1123 arg_types = {"this": True, "kind": False} 1124 1125 1126class Pragma(Expression): 1127 pass 1128 1129 1130class Set(Expression): 1131 arg_types = {"expressions": False, "unset": False, "tag": False} 1132 1133 1134class SetItem(Expression): 1135 arg_types = { 1136 "this": False, 1137 "expressions": False, 1138 "kind": False, 1139 "collate": False, # MySQL SET NAMES statement 1140 "global": False, 1141 } 1142 1143 1144class Show(Expression): 1145 arg_types = { 1146 "this": True, 1147 "target": False, 1148 "offset": False, 1149 "limit": False, 1150 "like": False, 1151 "where": False, 1152 "db": False, 1153 "scope": False, 1154 "scope_kind": False, 1155 "full": False, 1156 "mutex": False, 1157 "query": False, 1158 "channel": False, 1159 "global": False, 1160 "log": False, 1161 "position": False, 1162 "types": False, 1163 } 1164 1165 1166class UserDefinedFunction(Expression): 1167 arg_types = {"this": True, "expressions": False, "wrapped": False} 1168 1169 1170class CharacterSet(Expression): 1171 arg_types = {"this": True, "default": False} 1172 1173 1174class With(Expression): 1175 arg_types = {"expressions": True, "recursive": False} 1176 1177 @property 1178 def recursive(self) -> bool: 1179 return bool(self.args.get("recursive")) 1180 1181 1182class WithinGroup(Expression): 1183 arg_types = {"this": True, "expression": False} 1184 1185 1186# clickhouse supports scalar ctes 1187# https://clickhouse.com/docs/en/sql-reference/statements/select/with 1188class CTE(DerivedTable): 1189 arg_types = {"this": True, "alias": True, "scalar": False} 1190 1191 1192class TableAlias(Expression): 1193 arg_types = {"this": False, "columns": False} 1194 1195 @property 1196 def columns(self): 1197 return self.args.get("columns") or [] 1198 1199 1200class BitString(Condition): 1201 pass 1202 1203 1204class HexString(Condition): 1205 pass 1206 1207 1208class ByteString(Condition): 1209 pass 1210 1211 1212class RawString(Condition): 1213 pass 1214 1215 1216class Column(Condition): 1217 arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False} 1218 1219 @property 1220 def table(self) -> str: 1221 return self.text("table") 1222 1223 @property 1224 def db(self) -> str: 1225 return self.text("db") 1226 1227 @property 1228 def catalog(self) -> str: 1229 return self.text("catalog") 1230 1231 @property 1232 def output_name(self) -> str: 1233 return self.name 1234 1235 @property 1236 def parts(self) -> t.List[Identifier]: 1237 """Return the parts of a column in order catalog, db, table, name.""" 1238 return [ 1239 t.cast(Identifier, self.args[part]) 1240 for part in ("catalog", "db", "table", "this") 1241 if self.args.get(part) 1242 ] 1243 1244 def to_dot(self) -> Dot | Identifier: 1245 """Converts the column into a dot expression.""" 1246 parts = self.parts 1247 parent = self.parent 1248 1249 while parent: 1250 if isinstance(parent, Dot): 1251 parts.append(parent.expression) 1252 parent = parent.parent 1253 1254 return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0] 1255 1256 1257class ColumnPosition(Expression): 1258 arg_types = {"this": False, "position": True} 1259 1260 1261class ColumnDef(Expression): 1262 arg_types = { 1263 "this": True, 1264 "kind": False, 1265 "constraints": False, 1266 "exists": False, 1267 "position": False, 1268 } 1269 1270 @property 1271 def constraints(self) -> t.List[ColumnConstraint]: 1272 return self.args.get("constraints") or [] 1273 1274 1275class AlterColumn(Expression): 1276 arg_types = { 1277 "this": True, 1278 "dtype": False, 1279 "collate": False, 1280 "using": False, 1281 "default": False, 1282 "drop": False, 1283 } 1284 1285 1286class RenameTable(Expression): 1287 pass 1288 1289 1290class SwapTable(Expression): 1291 pass 1292 1293 1294class Comment(Expression): 1295 arg_types = {"this": True, "kind": True, "expression": True, "exists": False} 1296 1297 1298class Comprehension(Expression): 1299 arg_types = {"this": True, "expression": True, "iterator": True, "condition": False} 1300 1301 1302# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl 1303class MergeTreeTTLAction(Expression): 1304 arg_types = { 1305 "this": True, 1306 "delete": False, 1307 "recompress": False, 1308 "to_disk": False, 1309 "to_volume": False, 1310 } 1311 1312 1313# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl 1314class MergeTreeTTL(Expression): 1315 arg_types = { 1316 "expressions": True, 1317 "where": False, 1318 "group": False, 1319 "aggregates": False, 1320 } 1321 1322 1323# https://dev.mysql.com/doc/refman/8.0/en/create-table.html 1324class IndexConstraintOption(Expression): 1325 arg_types = { 1326 "key_block_size": False, 1327 "using": False, 1328 "parser": False, 1329 "comment": False, 1330 "visible": False, 1331 "engine_attr": False, 1332 "secondary_engine_attr": False, 1333 } 1334 1335 1336class ColumnConstraint(Expression): 1337 arg_types = {"this": False, "kind": True} 1338 1339 @property 1340 def kind(self) -> ColumnConstraintKind: 1341 return self.args["kind"] 1342 1343 1344class ColumnConstraintKind(Expression): 1345 pass 1346 1347 1348class AutoIncrementColumnConstraint(ColumnConstraintKind): 1349 pass 1350 1351 1352class PeriodForSystemTimeConstraint(ColumnConstraintKind): 1353 arg_types = {"this": True, "expression": True} 1354 1355 1356class CaseSpecificColumnConstraint(ColumnConstraintKind): 1357 arg_types = {"not_": True} 1358 1359 1360class CharacterSetColumnConstraint(ColumnConstraintKind): 1361 arg_types = {"this": True} 1362 1363 1364class CheckColumnConstraint(ColumnConstraintKind): 1365 pass 1366 1367 1368class ClusteredColumnConstraint(ColumnConstraintKind): 1369 pass 1370 1371 1372class CollateColumnConstraint(ColumnConstraintKind): 1373 pass 1374 1375 1376class CommentColumnConstraint(ColumnConstraintKind): 1377 pass 1378 1379 1380class CompressColumnConstraint(ColumnConstraintKind): 1381 pass 1382 1383 1384class DateFormatColumnConstraint(ColumnConstraintKind): 1385 arg_types = {"this": True} 1386 1387 1388class DefaultColumnConstraint(ColumnConstraintKind): 1389 pass 1390 1391 1392class EncodeColumnConstraint(ColumnConstraintKind): 1393 pass 1394 1395 1396class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind): 1397 # this: True -> ALWAYS, this: False -> BY DEFAULT 1398 arg_types = { 1399 "this": False, 1400 "expression": False, 1401 "on_null": False, 1402 "start": False, 1403 "increment": False, 1404 "minvalue": False, 1405 "maxvalue": False, 1406 "cycle": False, 1407 } 1408 1409 1410class GeneratedAsRowColumnConstraint(ColumnConstraintKind): 1411 arg_types = {"start": True, "hidden": False} 1412 1413 1414# https://dev.mysql.com/doc/refman/8.0/en/create-table.html 1415class IndexColumnConstraint(ColumnConstraintKind): 1416 arg_types = { 1417 "this": False, 1418 "schema": True, 1419 "kind": False, 1420 "index_type": False, 1421 "options": False, 1422 } 1423 1424 1425class InlineLengthColumnConstraint(ColumnConstraintKind): 1426 pass 1427 1428 1429class NonClusteredColumnConstraint(ColumnConstraintKind): 1430 pass 1431 1432 1433class NotForReplicationColumnConstraint(ColumnConstraintKind): 1434 arg_types = {} 1435 1436 1437class NotNullColumnConstraint(ColumnConstraintKind): 1438 arg_types = {"allow_null": False} 1439 1440 1441# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html 1442class OnUpdateColumnConstraint(ColumnConstraintKind): 1443 pass 1444 1445 1446class PrimaryKeyColumnConstraint(ColumnConstraintKind): 1447 arg_types = {"desc": False} 1448 1449 1450class TitleColumnConstraint(ColumnConstraintKind): 1451 pass 1452 1453 1454class UniqueColumnConstraint(ColumnConstraintKind): 1455 arg_types = {"this": False, "index_type": False} 1456 1457 1458class UppercaseColumnConstraint(ColumnConstraintKind): 1459 arg_types: t.Dict[str, t.Any] = {} 1460 1461 1462class PathColumnConstraint(ColumnConstraintKind): 1463 pass 1464 1465 1466# computed column expression 1467# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16 1468class ComputedColumnConstraint(ColumnConstraintKind): 1469 arg_types = {"this": True, "persisted": False, "not_null": False} 1470 1471 1472class Constraint(Expression): 1473 arg_types = {"this": True, "expressions": True} 1474 1475 1476class Delete(DML): 1477 arg_types = { 1478 "with": False, 1479 "this": False, 1480 "using": False, 1481 "where": False, 1482 "returning": False, 1483 "limit": False, 1484 "tables": False, # Multiple-Table Syntax (MySQL) 1485 } 1486 1487 def delete( 1488 self, 1489 table: ExpOrStr, 1490 dialect: DialectType = None, 1491 copy: bool = True, 1492 **opts, 1493 ) -> Delete: 1494 """ 1495 Create a DELETE expression or replace the table on an existing DELETE expression. 1496 1497 Example: 1498 >>> delete("tbl").sql() 1499 'DELETE FROM tbl' 1500 1501 Args: 1502 table: the table from which to delete. 1503 dialect: the dialect used to parse the input expression. 1504 copy: if `False`, modify this expression instance in-place. 1505 opts: other options to use to parse the input expressions. 1506 1507 Returns: 1508 Delete: the modified expression. 1509 """ 1510 return _apply_builder( 1511 expression=table, 1512 instance=self, 1513 arg="this", 1514 dialect=dialect, 1515 into=Table, 1516 copy=copy, 1517 **opts, 1518 ) 1519 1520 def where( 1521 self, 1522 *expressions: t.Optional[ExpOrStr], 1523 append: bool = True, 1524 dialect: DialectType = None, 1525 copy: bool = True, 1526 **opts, 1527 ) -> Delete: 1528 """ 1529 Append to or set the WHERE expressions. 1530 1531 Example: 1532 >>> delete("tbl").where("x = 'a' OR x < 'b'").sql() 1533 "DELETE FROM tbl WHERE x = 'a' OR x < 'b'" 1534 1535 Args: 1536 *expressions: the SQL code strings to parse. 1537 If an `Expression` instance is passed, it will be used as-is. 1538 Multiple expressions are combined with an AND operator. 1539 append: if `True`, AND the new expressions to any existing expression. 1540 Otherwise, this resets the expression. 1541 dialect: the dialect used to parse the input expressions. 1542 copy: if `False`, modify this expression instance in-place. 1543 opts: other options to use to parse the input expressions. 1544 1545 Returns: 1546 Delete: the modified expression. 1547 """ 1548 return _apply_conjunction_builder( 1549 *expressions, 1550 instance=self, 1551 arg="where", 1552 append=append, 1553 into=Where, 1554 dialect=dialect, 1555 copy=copy, 1556 **opts, 1557 ) 1558 1559 1560class Drop(Expression): 1561 arg_types = { 1562 "this": False, 1563 "kind": False, 1564 "exists": False, 1565 "temporary": False, 1566 "materialized": False, 1567 "cascade": False, 1568 "constraints": False, 1569 "purge": False, 1570 } 1571 1572 1573class Filter(Expression): 1574 arg_types = {"this": True, "expression": True} 1575 1576 1577class Check(Expression): 1578 pass 1579 1580 1581# https://docs.snowflake.com/en/sql-reference/constructs/connect-by 1582class Connect(Expression): 1583 arg_types = {"start": False, "connect": True} 1584 1585 1586class Prior(Expression): 1587 pass 1588 1589 1590class Directory(Expression): 1591 # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html 1592 arg_types = {"this": True, "local": False, "row_format": False} 1593 1594 1595class ForeignKey(Expression): 1596 arg_types = { 1597 "expressions": True, 1598 "reference": False, 1599 "delete": False, 1600 "update": False, 1601 } 1602 1603 1604class ColumnPrefix(Expression): 1605 arg_types = {"this": True, "expression": True} 1606 1607 1608class PrimaryKey(Expression): 1609 arg_types = {"expressions": True, "options": False} 1610 1611 1612# https://www.postgresql.org/docs/9.1/sql-selectinto.html 1613# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples 1614class Into(Expression): 1615 arg_types = {"this": True, "temporary": False, "unlogged": False} 1616 1617 1618class From(Expression): 1619 @property 1620 def name(self) -> str: 1621 return self.this.name 1622 1623 @property 1624 def alias_or_name(self) -> str: 1625 return self.this.alias_or_name 1626 1627 1628class Having(Expression): 1629 pass 1630 1631 1632class Hint(Expression): 1633 arg_types = {"expressions": True} 1634 1635 1636class JoinHint(Expression): 1637 arg_types = {"this": True, "expressions": True} 1638 1639 1640class Identifier(Expression): 1641 arg_types = {"this": True, "quoted": False, "global": False, "temporary": False} 1642 1643 @property 1644 def quoted(self) -> bool: 1645 return bool(self.args.get("quoted")) 1646 1647 @property 1648 def hashable_args(self) -> t.Any: 1649 return (self.this, self.quoted) 1650 1651 @property 1652 def output_name(self) -> str: 1653 return self.name 1654 1655 1656# https://www.postgresql.org/docs/current/indexes-opclass.html 1657class Opclass(Expression): 1658 arg_types = {"this": True, "expression": True} 1659 1660 1661class Index(Expression): 1662 arg_types = { 1663 "this": False, 1664 "table": False, 1665 "using": False, 1666 "where": False, 1667 "columns": False, 1668 "unique": False, 1669 "primary": False, 1670 "amp": False, # teradata 1671 "partition_by": False, # teradata 1672 "where": False, # postgres partial indexes 1673 } 1674 1675 1676class Insert(DDL, DML): 1677 arg_types = { 1678 "with": False, 1679 "this": True, 1680 "expression": False, 1681 "conflict": False, 1682 "returning": False, 1683 "overwrite": False, 1684 "exists": False, 1685 "partition": False, 1686 "alternative": False, 1687 "where": False, 1688 "ignore": False, 1689 "by_name": False, 1690 } 1691 1692 def with_( 1693 self, 1694 alias: ExpOrStr, 1695 as_: ExpOrStr, 1696 recursive: t.Optional[bool] = None, 1697 append: bool = True, 1698 dialect: DialectType = None, 1699 copy: bool = True, 1700 **opts, 1701 ) -> Insert: 1702 """ 1703 Append to or set the common table expressions. 1704 1705 Example: 1706 >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql() 1707 'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte' 1708 1709 Args: 1710 alias: the SQL code string to parse as the table name. 1711 If an `Expression` instance is passed, this is used as-is. 1712 as_: the SQL code string to parse as the table expression. 1713 If an `Expression` instance is passed, it will be used as-is. 1714 recursive: set the RECURSIVE part of the expression. Defaults to `False`. 1715 append: if `True`, add to any existing expressions. 1716 Otherwise, this resets the expressions. 1717 dialect: the dialect used to parse the input expression. 1718 copy: if `False`, modify this expression instance in-place. 1719 opts: other options to use to parse the input expressions. 1720 1721 Returns: 1722 The modified expression. 1723 """ 1724 return _apply_cte_builder( 1725 self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts 1726 ) 1727 1728 1729class OnConflict(Expression): 1730 arg_types = { 1731 "duplicate": False, 1732 "expressions": False, 1733 "nothing": False, 1734 "key": False, 1735 "constraint": False, 1736 } 1737 1738 1739class Returning(Expression): 1740 arg_types = {"expressions": True, "into": False} 1741 1742 1743# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html 1744class Introducer(Expression): 1745 arg_types = {"this": True, "expression": True} 1746 1747 1748# national char, like n'utf8' 1749class National(Expression): 1750 pass 1751 1752 1753class LoadData(Expression): 1754 arg_types = { 1755 "this": True, 1756 "local": False, 1757 "overwrite": False, 1758 "inpath": True, 1759 "partition": False, 1760 "input_format": False, 1761 "serde": False, 1762 } 1763 1764 1765class Partition(Expression): 1766 arg_types = {"expressions": True} 1767 1768 1769class Fetch(Expression): 1770 arg_types = { 1771 "direction": False, 1772 "count": False, 1773 "percent": False, 1774 "with_ties": False, 1775 } 1776 1777 1778class Group(Expression): 1779 arg_types = { 1780 "expressions": False, 1781 "grouping_sets": False, 1782 "cube": False, 1783 "rollup": False, 1784 "totals": False, 1785 "all": False, 1786 } 1787 1788 1789class Lambda(Expression): 1790 arg_types = {"this": True, "expressions": True} 1791 1792 1793class Limit(Expression): 1794 arg_types = {"this": False, "expression": True, "offset": False} 1795 1796 1797class Literal(Condition): 1798 arg_types = {"this": True, "is_string": True} 1799 1800 @property 1801 def hashable_args(self) -> t.Any: 1802 return (self.this, self.args.get("is_string")) 1803 1804 @classmethod 1805 def number(cls, number) -> Literal: 1806 return cls(this=str(number), is_string=False) 1807 1808 @classmethod 1809 def string(cls, string) -> Literal: 1810 return cls(this=str(string), is_string=True) 1811 1812 @property 1813 def output_name(self) -> str: 1814 return self.name 1815 1816 1817class Join(Expression): 1818 arg_types = { 1819 "this": True, 1820 "on": False, 1821 "side": False, 1822 "kind": False, 1823 "using": False, 1824 "method": False, 1825 "global": False, 1826 "hint": False, 1827 } 1828 1829 @property 1830 def method(self) -> str: 1831 return self.text("method").upper() 1832 1833 @property 1834 def kind(self) -> str: 1835 return self.text("kind").upper() 1836 1837 @property 1838 def side(self) -> str: 1839 return self.text("side").upper() 1840 1841 @property 1842 def hint(self) -> str: 1843 return self.text("hint").upper() 1844 1845 @property 1846 def alias_or_name(self) -> str: 1847 return self.this.alias_or_name 1848 1849 def on( 1850 self, 1851 *expressions: t.Optional[ExpOrStr], 1852 append: bool = True, 1853 dialect: DialectType = None, 1854 copy: bool = True, 1855 **opts, 1856 ) -> Join: 1857 """ 1858 Append to or set the ON expressions. 1859 1860 Example: 1861 >>> import sqlglot 1862 >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql() 1863 'JOIN x ON y = 1' 1864 1865 Args: 1866 *expressions: the SQL code strings to parse. 1867 If an `Expression` instance is passed, it will be used as-is. 1868 Multiple expressions are combined with an AND operator. 1869 append: if `True`, AND the new expressions to any existing expression. 1870 Otherwise, this resets the expression. 1871 dialect: the dialect used to parse the input expressions. 1872 copy: if `False`, modify this expression instance in-place. 1873 opts: other options to use to parse the input expressions. 1874 1875 Returns: 1876 The modified Join expression. 1877 """ 1878 join = _apply_conjunction_builder( 1879 *expressions, 1880 instance=self, 1881 arg="on", 1882 append=append, 1883 dialect=dialect, 1884 copy=copy, 1885 **opts, 1886 ) 1887 1888 if join.kind == "CROSS": 1889 join.set("kind", None) 1890 1891 return join 1892 1893 def using( 1894 self, 1895 *expressions: t.Optional[ExpOrStr], 1896 append: bool = True, 1897 dialect: DialectType = None, 1898 copy: bool = True, 1899 **opts, 1900 ) -> Join: 1901 """ 1902 Append to or set the USING expressions. 1903 1904 Example: 1905 >>> import sqlglot 1906 >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql() 1907 'JOIN x USING (foo, bla)' 1908 1909 Args: 1910 *expressions: the SQL code strings to parse. 1911 If an `Expression` instance is passed, it will be used as-is. 1912 append: if `True`, concatenate the new expressions to the existing "using" list. 1913 Otherwise, this resets the expression. 1914 dialect: the dialect used to parse the input expressions. 1915 copy: if `False`, modify this expression instance in-place. 1916 opts: other options to use to parse the input expressions. 1917 1918 Returns: 1919 The modified Join expression. 1920 """ 1921 join = _apply_list_builder( 1922 *expressions, 1923 instance=self, 1924 arg="using", 1925 append=append, 1926 dialect=dialect, 1927 copy=copy, 1928 **opts, 1929 ) 1930 1931 if join.kind == "CROSS": 1932 join.set("kind", None) 1933 1934 return join 1935 1936 1937class Lateral(UDTF): 1938 arg_types = {"this": True, "view": False, "outer": False, "alias": False} 1939 1940 1941class MatchRecognize(Expression): 1942 arg_types = { 1943 "partition_by": False, 1944 "order": False, 1945 "measures": False, 1946 "rows": False, 1947 "after": False, 1948 "pattern": False, 1949 "define": False, 1950 "alias": False, 1951 } 1952 1953 1954# Clickhouse FROM FINAL modifier 1955# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier 1956class Final(Expression): 1957 pass 1958 1959 1960class Offset(Expression): 1961 arg_types = {"this": False, "expression": True} 1962 1963 1964class Order(Expression): 1965 arg_types = {"this": False, "expressions": True} 1966 1967 1968# hive specific sorts 1969# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy 1970class Cluster(Order): 1971 pass 1972 1973 1974class Distribute(Order): 1975 pass 1976 1977 1978class Sort(Order): 1979 pass 1980 1981 1982class Ordered(Expression): 1983 arg_types = {"this": True, "desc": False, "nulls_first": True} 1984 1985 1986class Property(Expression): 1987 arg_types = {"this": True, "value": True} 1988 1989 1990class AlgorithmProperty(Property): 1991 arg_types = {"this": True} 1992 1993 1994class AutoIncrementProperty(Property): 1995 arg_types = {"this": True} 1996 1997 1998class BlockCompressionProperty(Property): 1999 arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True} 2000 2001 2002class CharacterSetProperty(Property): 2003 arg_types = {"this": True, "default": True} 2004 2005 2006class ChecksumProperty(Property): 2007 arg_types = {"on": False, "default": False} 2008 2009 2010class CollateProperty(Property): 2011 arg_types = {"this": True, "default": False} 2012 2013 2014class CopyGrantsProperty(Property): 2015 arg_types = {} 2016 2017 2018class DataBlocksizeProperty(Property): 2019 arg_types = { 2020 "size": False, 2021 "units": False, 2022 "minimum": False, 2023 "maximum": False, 2024 "default": False, 2025 } 2026 2027 2028class DefinerProperty(Property): 2029 arg_types = {"this": True} 2030 2031 2032class DistKeyProperty(Property): 2033 arg_types = {"this": True} 2034 2035 2036class DistStyleProperty(Property): 2037 arg_types = {"this": True} 2038 2039 2040class EngineProperty(Property): 2041 arg_types = {"this": True} 2042 2043 2044class HeapProperty(Property): 2045 arg_types = {} 2046 2047 2048class ToTableProperty(Property): 2049 arg_types = {"this": True} 2050 2051 2052class ExecuteAsProperty(Property): 2053 arg_types = {"this": True} 2054 2055 2056class ExternalProperty(Property): 2057 arg_types = {"this": False} 2058 2059 2060class FallbackProperty(Property): 2061 arg_types = {"no": True, "protection": False} 2062 2063 2064class FileFormatProperty(Property): 2065 arg_types = {"this": True} 2066 2067 2068class FreespaceProperty(Property): 2069 arg_types = {"this": True, "percent": False} 2070 2071 2072class InputModelProperty(Property): 2073 arg_types = {"this": True} 2074 2075 2076class OutputModelProperty(Property): 2077 arg_types = {"this": True} 2078 2079 2080class IsolatedLoadingProperty(Property): 2081 arg_types = { 2082 "no": True, 2083 "concurrent": True, 2084 "for_all": True, 2085 "for_insert": True, 2086 "for_none": True, 2087 } 2088 2089 2090class JournalProperty(Property): 2091 arg_types = { 2092 "no": False, 2093 "dual": False, 2094 "before": False, 2095 "local": False, 2096 "after": False, 2097 } 2098 2099 2100class LanguageProperty(Property): 2101 arg_types = {"this": True} 2102 2103 2104# spark ddl 2105class ClusteredByProperty(Property): 2106 arg_types = {"expressions": True, "sorted_by": False, "buckets": True} 2107 2108 2109class DictProperty(Property): 2110 arg_types = {"this": True, "kind": True, "settings": False} 2111 2112 2113class DictSubProperty(Property): 2114 pass 2115 2116 2117class DictRange(Property): 2118 arg_types = {"this": True, "min": True, "max": True} 2119 2120 2121# Clickhouse CREATE ... ON CLUSTER modifier 2122# https://clickhouse.com/docs/en/sql-reference/distributed-ddl 2123class OnCluster(Property): 2124 arg_types = {"this": True} 2125 2126 2127class LikeProperty(Property): 2128 arg_types = {"this": True, "expressions": False} 2129 2130 2131class LocationProperty(Property): 2132 arg_types = {"this": True} 2133 2134 2135class LockingProperty(Property): 2136 arg_types = { 2137 "this": False, 2138 "kind": True, 2139 "for_or_in": False, 2140 "lock_type": True, 2141 "override": False, 2142 } 2143 2144 2145class LogProperty(Property): 2146 arg_types = {"no": True} 2147 2148 2149class MaterializedProperty(Property): 2150 arg_types = {"this": False} 2151 2152 2153class MergeBlockRatioProperty(Property): 2154 arg_types = {"this": False, "no": False, "default": False, "percent": False} 2155 2156 2157class NoPrimaryIndexProperty(Property): 2158 arg_types = {} 2159 2160 2161class OnProperty(Property): 2162 arg_types = {"this": True} 2163 2164 2165class OnCommitProperty(Property): 2166 arg_types = {"delete": False} 2167 2168 2169class PartitionedByProperty(Property): 2170 arg_types = {"this": True} 2171 2172 2173# https://www.postgresql.org/docs/current/sql-createtable.html 2174class PartitionBoundSpec(Expression): 2175 # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...) 2176 arg_types = { 2177 "this": False, 2178 "expression": False, 2179 "from_expressions": False, 2180 "to_expressions": False, 2181 } 2182 2183 2184class PartitionedOfProperty(Property): 2185 # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT 2186 arg_types = {"this": True, "expression": True} 2187 2188 2189class RemoteWithConnectionModelProperty(Property): 2190 arg_types = {"this": True} 2191 2192 2193class ReturnsProperty(Property): 2194 arg_types = {"this": True, "is_table": False, "table": False} 2195 2196 2197class RowFormatProperty(Property): 2198 arg_types = {"this": True} 2199 2200 2201class RowFormatDelimitedProperty(Property): 2202 # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml 2203 arg_types = { 2204 "fields": False, 2205 "escaped": False, 2206 "collection_items": False, 2207 "map_keys": False, 2208 "lines": False, 2209 "null": False, 2210 "serde": False, 2211 } 2212 2213 2214class RowFormatSerdeProperty(Property): 2215 arg_types = {"this": True, "serde_properties": False} 2216 2217 2218# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html 2219class QueryTransform(Expression): 2220 arg_types = { 2221 "expressions": True, 2222 "command_script": True, 2223 "schema": False, 2224 "row_format_before": False, 2225 "record_writer": False, 2226 "row_format_after": False, 2227 "record_reader": False, 2228 } 2229 2230 2231class SampleProperty(Property): 2232 arg_types = {"this": True} 2233 2234 2235class SchemaCommentProperty(Property): 2236 arg_types = {"this": True} 2237 2238 2239class SerdeProperties(Property): 2240 arg_types = {"expressions": True} 2241 2242 2243class SetProperty(Property): 2244 arg_types = {"multi": True} 2245 2246 2247class SettingsProperty(Property): 2248 arg_types = {"expressions": True} 2249 2250 2251class SortKeyProperty(Property): 2252 arg_types = {"this": True, "compound": False} 2253 2254 2255class SqlSecurityProperty(Property): 2256 arg_types = {"definer": True} 2257 2258 2259class StabilityProperty(Property): 2260 arg_types = {"this": True} 2261 2262 2263class TemporaryProperty(Property): 2264 arg_types = {} 2265 2266 2267class TransformModelProperty(Property): 2268 arg_types = {"expressions": True} 2269 2270 2271class TransientProperty(Property): 2272 arg_types = {"this": False} 2273 2274 2275class VolatileProperty(Property): 2276 arg_types = {"this": False} 2277 2278 2279class WithDataProperty(Property): 2280 arg_types = {"no": True, "statistics": False} 2281 2282 2283class WithJournalTableProperty(Property): 2284 arg_types = {"this": True} 2285 2286 2287class WithSystemVersioningProperty(Property): 2288 # this -> history table name, expression -> data consistency check 2289 arg_types = {"this": False, "expression": False} 2290 2291 2292class Properties(Expression): 2293 arg_types = {"expressions": True} 2294 2295 NAME_TO_PROPERTY = { 2296 "ALGORITHM": AlgorithmProperty, 2297 "AUTO_INCREMENT": AutoIncrementProperty, 2298 "CHARACTER SET": CharacterSetProperty, 2299 "CLUSTERED_BY": ClusteredByProperty, 2300 "COLLATE": CollateProperty, 2301 "COMMENT": SchemaCommentProperty, 2302 "DEFINER": DefinerProperty, 2303 "DISTKEY": DistKeyProperty, 2304 "DISTSTYLE": DistStyleProperty, 2305 "ENGINE": EngineProperty, 2306 "EXECUTE AS": ExecuteAsProperty, 2307 "FORMAT": FileFormatProperty, 2308 "LANGUAGE": LanguageProperty, 2309 "LOCATION": LocationProperty, 2310 "PARTITIONED_BY": PartitionedByProperty, 2311 "RETURNS": ReturnsProperty, 2312 "ROW_FORMAT": RowFormatProperty, 2313 "SORTKEY": SortKeyProperty, 2314 } 2315 2316 PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()} 2317 2318 # CREATE property locations 2319 # Form: schema specified 2320 # create [POST_CREATE] 2321 # table a [POST_NAME] 2322 # (b int) [POST_SCHEMA] 2323 # with ([POST_WITH]) 2324 # index (b) [POST_INDEX] 2325 # 2326 # Form: alias selection 2327 # create [POST_CREATE] 2328 # table a [POST_NAME] 2329 # as [POST_ALIAS] (select * from b) [POST_EXPRESSION] 2330 # index (c) [POST_INDEX] 2331 class Location(AutoName): 2332 POST_CREATE = auto() 2333 POST_NAME = auto() 2334 POST_SCHEMA = auto() 2335 POST_WITH = auto() 2336 POST_ALIAS = auto() 2337 POST_EXPRESSION = auto() 2338 POST_INDEX = auto() 2339 UNSUPPORTED = auto() 2340 2341 @classmethod 2342 def from_dict(cls, properties_dict: t.Dict) -> Properties: 2343 expressions = [] 2344 for key, value in properties_dict.items(): 2345 property_cls = cls.NAME_TO_PROPERTY.get(key.upper()) 2346 if property_cls: 2347 expressions.append(property_cls(this=convert(value))) 2348 else: 2349 expressions.append(Property(this=Literal.string(key), value=convert(value))) 2350 2351 return cls(expressions=expressions) 2352 2353 2354class Qualify(Expression): 2355 pass 2356 2357 2358class InputOutputFormat(Expression): 2359 arg_types = {"input_format": False, "output_format": False} 2360 2361 2362# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql 2363class Return(Expression): 2364 pass 2365 2366 2367class Reference(Expression): 2368 arg_types = {"this": True, "expressions": False, "options": False} 2369 2370 2371class Tuple(Expression): 2372 arg_types = {"expressions": False} 2373 2374 def isin( 2375 self, 2376 *expressions: t.Any, 2377 query: t.Optional[ExpOrStr] = None, 2378 unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None, 2379 copy: bool = True, 2380 **opts, 2381 ) -> In: 2382 return In( 2383 this=maybe_copy(self, copy), 2384 expressions=[convert(e, copy=copy) for e in expressions], 2385 query=maybe_parse(query, copy=copy, **opts) if query else None, 2386 unnest=Unnest( 2387 expressions=[ 2388 maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest) 2389 ] 2390 ) 2391 if unnest 2392 else None, 2393 ) 2394 2395 2396class Subqueryable(Unionable): 2397 def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery: 2398 """ 2399 Convert this expression to an aliased expression that can be used as a Subquery. 2400 2401 Example: 2402 >>> subquery = Select().select("x").from_("tbl").subquery() 2403 >>> Select().select("x").from_(subquery).sql() 2404 'SELECT x FROM (SELECT x FROM tbl)' 2405 2406 Args: 2407 alias (str | Identifier): an optional alias for the subquery 2408 copy (bool): if `False`, modify this expression instance in-place. 2409 2410 Returns: 2411 Alias: the subquery 2412 """ 2413 instance = maybe_copy(self, copy) 2414 if not isinstance(alias, Expression): 2415 alias = TableAlias(this=to_identifier(alias)) if alias else None 2416 2417 return Subquery(this=instance, alias=alias) 2418 2419 def limit( 2420 self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts 2421 ) -> Select: 2422 raise NotImplementedError 2423 2424 @property 2425 def ctes(self): 2426 with_ = self.args.get("with") 2427 if not with_: 2428 return [] 2429 return with_.expressions 2430 2431 @property 2432 def selects(self) -> t.List[Expression]: 2433 raise NotImplementedError("Subqueryable objects must implement `selects`") 2434 2435 @property 2436 def named_selects(self) -> t.List[str]: 2437 raise NotImplementedError("Subqueryable objects must implement `named_selects`") 2438 2439 def select( 2440 self, 2441 *expressions: t.Optional[ExpOrStr], 2442 append: bool = True, 2443 dialect: DialectType = None, 2444 copy: bool = True, 2445 **opts, 2446 ) -> Subqueryable: 2447 raise NotImplementedError("Subqueryable objects must implement `select`") 2448 2449 def with_( 2450 self, 2451 alias: ExpOrStr, 2452 as_: ExpOrStr, 2453 recursive: t.Optional[bool] = None, 2454 append: bool = True, 2455 dialect: DialectType = None, 2456 copy: bool = True, 2457 **opts, 2458 ) -> Subqueryable: 2459 """ 2460 Append to or set the common table expressions. 2461 2462 Example: 2463 >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql() 2464 'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2' 2465 2466 Args: 2467 alias: the SQL code string to parse as the table name. 2468 If an `Expression` instance is passed, this is used as-is. 2469 as_: the SQL code string to parse as the table expression. 2470 If an `Expression` instance is passed, it will be used as-is. 2471 recursive: set the RECURSIVE part of the expression. Defaults to `False`. 2472 append: if `True`, add to any existing expressions. 2473 Otherwise, this resets the expressions. 2474 dialect: the dialect used to parse the input expression. 2475 copy: if `False`, modify this expression instance in-place. 2476 opts: other options to use to parse the input expressions. 2477 2478 Returns: 2479 The modified expression. 2480 """ 2481 return _apply_cte_builder( 2482 self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts 2483 ) 2484 2485 2486QUERY_MODIFIERS = { 2487 "match": False, 2488 "laterals": False, 2489 "joins": False, 2490 "connect": False, 2491 "pivots": False, 2492 "where": False, 2493 "group": False, 2494 "having": False, 2495 "qualify": False, 2496 "windows": False, 2497 "distribute": False, 2498 "sort": False, 2499 "cluster": False, 2500 "order": False, 2501 "limit": False, 2502 "offset": False, 2503 "locks": False, 2504 "sample": False, 2505 "settings": False, 2506 "format": False, 2507} 2508 2509 2510# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16 2511class WithTableHint(Expression): 2512 arg_types = {"expressions": True} 2513 2514 2515# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html 2516class IndexTableHint(Expression): 2517 arg_types = {"this": True, "expressions": False, "target": False} 2518 2519 2520class Table(Expression): 2521 arg_types = { 2522 "this": True, 2523 "alias": False, 2524 "db": False, 2525 "catalog": False, 2526 "laterals": False, 2527 "joins": False, 2528 "pivots": False, 2529 "hints": False, 2530 "system_time": False, 2531 "version": False, 2532 "format": False, 2533 "pattern": False, 2534 "index": False, 2535 "ordinality": False, 2536 } 2537 2538 @property 2539 def name(self) -> str: 2540 if isinstance(self.this, Func): 2541 return "" 2542 return self.this.name 2543 2544 @property 2545 def db(self) -> str: 2546 return self.text("db") 2547 2548 @property 2549 def catalog(self) -> str: 2550 return self.text("catalog") 2551 2552 @property 2553 def selects(self) -> t.List[Expression]: 2554 return [] 2555 2556 @property 2557 def named_selects(self) -> t.List[str]: 2558 return [] 2559 2560 @property 2561 def parts(self) -> t.List[Expression]: 2562 """Return the parts of a table in order catalog, db, table.""" 2563 parts: t.List[Expression] = [] 2564 2565 for arg in ("catalog", "db", "this"): 2566 part = self.args.get(arg) 2567 2568 if isinstance(part, Dot): 2569 parts.extend(part.flatten()) 2570 elif isinstance(part, Expression): 2571 parts.append(part) 2572 2573 return parts 2574 2575 2576class Union(Subqueryable): 2577 arg_types = { 2578 "with": False, 2579 "this": True, 2580 "expression": True, 2581 "distinct": False, 2582 "by_name": False, 2583 **QUERY_MODIFIERS, 2584 } 2585 2586 def limit( 2587 self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts 2588 ) -> Select: 2589 """ 2590 Set the LIMIT expression. 2591 2592 Example: 2593 >>> select("1").union(select("1")).limit(1).sql() 2594 'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1' 2595 2596 Args: 2597 expression: the SQL code string to parse. 2598 This can also be an integer. 2599 If a `Limit` instance is passed, this is used as-is. 2600 If another `Expression` instance is passed, it will be wrapped in a `Limit`. 2601 dialect: the dialect used to parse the input expression. 2602 copy: if `False`, modify this expression instance in-place. 2603 opts: other options to use to parse the input expressions. 2604 2605 Returns: 2606 The limited subqueryable. 2607 """ 2608 return ( 2609 select("*") 2610 .from_(self.subquery(alias="_l_0", copy=copy)) 2611 .limit(expression, dialect=dialect, copy=False, **opts) 2612 ) 2613 2614 def select( 2615 self, 2616 *expressions: t.Optional[ExpOrStr], 2617 append: bool = True, 2618 dialect: DialectType = None, 2619 copy: bool = True, 2620 **opts, 2621 ) -> Union: 2622 """Append to or set the SELECT of the union recursively. 2623 2624 Example: 2625 >>> from sqlglot import parse_one 2626 >>> parse_one("select a from x union select a from y union select a from z").select("b").sql() 2627 'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z' 2628 2629 Args: 2630 *expressions: the SQL code strings to parse. 2631 If an `Expression` instance is passed, it will be used as-is. 2632 append: if `True`, add to any existing expressions. 2633 Otherwise, this resets the expressions. 2634 dialect: the dialect used to parse the input expressions. 2635 copy: if `False`, modify this expression instance in-place. 2636 opts: other options to use to parse the input expressions. 2637 2638 Returns: 2639 Union: the modified expression. 2640 """ 2641 this = self.copy() if copy else self 2642 this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts) 2643 this.expression.unnest().select( 2644 *expressions, append=append, dialect=dialect, copy=False, **opts 2645 ) 2646 return this 2647 2648 @property 2649 def named_selects(self) -> t.List[str]: 2650 return self.this.unnest().named_selects 2651 2652 @property 2653 def is_star(self) -> bool: 2654 return self.this.is_star or self.expression.is_star 2655 2656 @property 2657 def selects(self) -> t.List[Expression]: 2658 return self.this.unnest().selects 2659 2660 @property 2661 def left(self) -> Expression: 2662 return self.this 2663 2664 @property 2665 def right(self) -> Expression: 2666 return self.expression 2667 2668 2669class Except(Union): 2670 pass 2671 2672 2673class Intersect(Union): 2674 pass 2675 2676 2677class Unnest(UDTF): 2678 arg_types = { 2679 "expressions": True, 2680 "alias": False, 2681 "offset": False, 2682 } 2683 2684 2685class Update(Expression): 2686 arg_types = { 2687 "with": False, 2688 "this": False, 2689 "expressions": True, 2690 "from": False, 2691 "where": False, 2692 "returning": False, 2693 "order": False, 2694 "limit": False, 2695 } 2696 2697 2698class Values(UDTF): 2699 arg_types = {"expressions": True, "alias": False} 2700 2701 2702class Var(Expression): 2703 pass 2704 2705 2706class Version(Expression): 2707 """ 2708 Time travel, iceberg, bigquery etc 2709 https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots 2710 https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html 2711 https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of 2712 https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16 2713 this is either TIMESTAMP or VERSION 2714 kind is ("AS OF", "BETWEEN") 2715 """ 2716 2717 arg_types = {"this": True, "kind": True, "expression": False} 2718 2719 2720class Schema(Expression): 2721 arg_types = {"this": False, "expressions": False} 2722 2723 2724# https://dev.mysql.com/doc/refman/8.0/en/select.html 2725# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html 2726class Lock(Expression): 2727 arg_types = {"update": True, "expressions": False, "wait": False} 2728 2729 2730class Select(Subqueryable): 2731 arg_types = { 2732 "with": False, 2733 "kind": False, 2734 "expressions": False, 2735 "hint": False, 2736 "distinct": False, 2737 "into": False, 2738 "from": False, 2739 **QUERY_MODIFIERS, 2740 } 2741 2742 def from_( 2743 self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts 2744 ) -> Select: 2745 """ 2746 Set the FROM expression. 2747 2748 Example: 2749 >>> Select().from_("tbl").select("x").sql() 2750 'SELECT x FROM tbl' 2751 2752 Args: 2753 expression : the SQL code strings to parse. 2754 If a `From` instance is passed, this is used as-is. 2755 If another `Expression` instance is passed, it will be wrapped in a `From`. 2756 dialect: the dialect used to parse the input expression. 2757 copy: if `False`, modify this expression instance in-place. 2758 opts: other options to use to parse the input expressions. 2759 2760 Returns: 2761 The modified Select expression. 2762 """ 2763 return _apply_builder( 2764 expression=expression, 2765 instance=self, 2766 arg="from", 2767 into=From, 2768 prefix="FROM", 2769 dialect=dialect, 2770 copy=copy, 2771 **opts, 2772 ) 2773 2774 def group_by( 2775 self, 2776 *expressions: t.Optional[ExpOrStr], 2777 append: bool = True, 2778 dialect: DialectType = None, 2779 copy: bool = True, 2780 **opts, 2781 ) -> Select: 2782 """ 2783 Set the GROUP BY expression. 2784 2785 Example: 2786 >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql() 2787 'SELECT x, COUNT(1) FROM tbl GROUP BY x' 2788 2789 Args: 2790 *expressions: the SQL code strings to parse. 2791 If a `Group` instance is passed, this is used as-is. 2792 If another `Expression` instance is passed, it will be wrapped in a `Group`. 2793 If nothing is passed in then a group by is not applied to the expression 2794 append: if `True`, add to any existing expressions. 2795 Otherwise, this flattens all the `Group` expression into a single expression. 2796 dialect: the dialect used to parse the input expression. 2797 copy: if `False`, modify this expression instance in-place. 2798 opts: other options to use to parse the input expressions. 2799 2800 Returns: 2801 The modified Select expression. 2802 """ 2803 if not expressions: 2804 return self if not copy else self.copy() 2805 2806 return _apply_child_list_builder( 2807 *expressions, 2808 instance=self, 2809 arg="group", 2810 append=append, 2811 copy=copy, 2812 prefix="GROUP BY", 2813 into=Group, 2814 dialect=dialect, 2815 **opts, 2816 ) 2817 2818 def order_by( 2819 self, 2820 *expressions: t.Optional[ExpOrStr], 2821 append: bool = True, 2822 dialect: DialectType = None, 2823 copy: bool = True, 2824 **opts, 2825 ) -> Select: 2826 """ 2827 Set the ORDER BY expression. 2828 2829 Example: 2830 >>> Select().from_("tbl").select("x").order_by("x DESC").sql() 2831 'SELECT x FROM tbl ORDER BY x DESC' 2832 2833 Args: 2834 *expressions: the SQL code strings to parse. 2835 If a `Group` instance is passed, this is used as-is. 2836 If another `Expression` instance is passed, it will be wrapped in a `Order`. 2837 append: if `True`, add to any existing expressions. 2838 Otherwise, this flattens all the `Order` expression into a single expression. 2839 dialect: the dialect used to parse the input expression. 2840 copy: if `False`, modify this expression instance in-place. 2841 opts: other options to use to parse the input expressions. 2842 2843 Returns: 2844 The modified Select expression. 2845 """ 2846 return _apply_child_list_builder( 2847 *expressions, 2848 instance=self, 2849 arg="order", 2850 append=append, 2851 copy=copy, 2852 prefix="ORDER BY", 2853 into=Order, 2854 dialect=dialect, 2855 **opts, 2856 ) 2857 2858 def sort_by( 2859 self, 2860 *expressions: t.Optional[ExpOrStr], 2861 append: bool = True, 2862 dialect: DialectType = None, 2863 copy: bool = True, 2864 **opts, 2865 ) -> Select: 2866 """ 2867 Set the SORT BY expression. 2868 2869 Example: 2870 >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive") 2871 'SELECT x FROM tbl SORT BY x DESC' 2872 2873 Args: 2874 *expressions: the SQL code strings to parse. 2875 If a `Group` instance is passed, this is used as-is. 2876 If another `Expression` instance is passed, it will be wrapped in a `SORT`. 2877 append: if `True`, add to any existing expressions. 2878 Otherwise, this flattens all the `Order` expression into a single expression. 2879 dialect: the dialect used to parse the input expression. 2880 copy: if `False`, modify this expression instance in-place. 2881 opts: other options to use to parse the input expressions. 2882 2883 Returns: 2884 The modified Select expression. 2885 """ 2886 return _apply_child_list_builder( 2887 *expressions, 2888 instance=self, 2889 arg="sort", 2890 append=append, 2891 copy=copy, 2892 prefix="SORT BY", 2893 into=Sort, 2894 dialect=dialect, 2895 **opts, 2896 ) 2897 2898 def cluster_by( 2899 self, 2900 *expressions: t.Optional[ExpOrStr], 2901 append: bool = True, 2902 dialect: DialectType = None, 2903 copy: bool = True, 2904 **opts, 2905 ) -> Select: 2906 """ 2907 Set the CLUSTER BY expression. 2908 2909 Example: 2910 >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive") 2911 'SELECT x FROM tbl CLUSTER BY x DESC' 2912 2913 Args: 2914 *expressions: the SQL code strings to parse. 2915 If a `Group` instance is passed, this is used as-is. 2916 If another `Expression` instance is passed, it will be wrapped in a `Cluster`. 2917 append: if `True`, add to any existing expressions. 2918 Otherwise, this flattens all the `Order` expression into a single expression. 2919 dialect: the dialect used to parse the input expression. 2920 copy: if `False`, modify this expression instance in-place. 2921 opts: other options to use to parse the input expressions. 2922 2923 Returns: 2924 The modified Select expression. 2925 """ 2926 return _apply_child_list_builder( 2927 *expressions, 2928 instance=self, 2929 arg="cluster", 2930 append=append, 2931 copy=copy, 2932 prefix="CLUSTER BY", 2933 into=Cluster, 2934 dialect=dialect, 2935 **opts, 2936 ) 2937 2938 def limit( 2939 self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts 2940 ) -> Select: 2941 """ 2942 Set the LIMIT expression. 2943 2944 Example: 2945 >>> Select().from_("tbl").select("x").limit(10).sql() 2946 'SELECT x FROM tbl LIMIT 10' 2947 2948 Args: 2949 expression: the SQL code string to parse. 2950 This can also be an integer. 2951 If a `Limit` instance is passed, this is used as-is. 2952 If another `Expression` instance is passed, it will be wrapped in a `Limit`. 2953 dialect: the dialect used to parse the input expression. 2954 copy: if `False`, modify this expression instance in-place. 2955 opts: other options to use to parse the input expressions. 2956 2957 Returns: 2958 Select: the modified expression. 2959 """ 2960 return _apply_builder( 2961 expression=expression, 2962 instance=self, 2963 arg="limit", 2964 into=Limit, 2965 prefix="LIMIT", 2966 dialect=dialect, 2967 copy=copy, 2968 into_arg="expression", 2969 **opts, 2970 ) 2971 2972 def offset( 2973 self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts 2974 ) -> Select: 2975 """ 2976 Set the OFFSET expression. 2977 2978 Example: 2979 >>> Select().from_("tbl").select("x").offset(10).sql() 2980 'SELECT x FROM tbl OFFSET 10' 2981 2982 Args: 2983 expression: the SQL code string to parse. 2984 This can also be an integer. 2985 If a `Offset` instance is passed, this is used as-is. 2986 If another `Expression` instance is passed, it will be wrapped in a `Offset`. 2987 dialect: the dialect used to parse the input expression. 2988 copy: if `False`, modify this expression instance in-place. 2989 opts: other options to use to parse the input expressions. 2990 2991 Returns: 2992 The modified Select expression. 2993 """ 2994 return _apply_builder( 2995 expression=expression, 2996 instance=self, 2997 arg="offset", 2998 into=Offset, 2999 prefix="OFFSET", 3000 dialect=dialect, 3001 copy=copy, 3002 into_arg="expression", 3003 **opts, 3004 ) 3005 3006 def select( 3007 self, 3008 *expressions: t.Optional[ExpOrStr], 3009 append: bool = True, 3010 dialect: DialectType = None, 3011 copy: bool = True, 3012 **opts, 3013 ) -> Select: 3014 """ 3015 Append to or set the SELECT expressions. 3016 3017 Example: 3018 >>> Select().select("x", "y").sql() 3019 'SELECT x, y' 3020 3021 Args: 3022 *expressions: the SQL code strings to parse. 3023 If an `Expression` instance is passed, it will be used as-is. 3024 append: if `True`, add to any existing expressions. 3025 Otherwise, this resets the expressions. 3026 dialect: the dialect used to parse the input expressions. 3027 copy: if `False`, modify this expression instance in-place. 3028 opts: other options to use to parse the input expressions. 3029 3030 Returns: 3031 The modified Select expression. 3032 """ 3033 return _apply_list_builder( 3034 *expressions, 3035 instance=self, 3036 arg="expressions", 3037 append=append, 3038 dialect=dialect, 3039 copy=copy, 3040 **opts, 3041 ) 3042 3043 def lateral( 3044 self, 3045 *expressions: t.Optional[ExpOrStr], 3046 append: bool = True, 3047 dialect: DialectType = None, 3048 copy: bool = True, 3049 **opts, 3050 ) -> Select: 3051 """ 3052 Append to or set the LATERAL expressions. 3053 3054 Example: 3055 >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql() 3056 'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z' 3057 3058 Args: 3059 *expressions: the SQL code strings to parse. 3060 If an `Expression` instance is passed, it will be used as-is. 3061 append: if `True`, add to any existing expressions. 3062 Otherwise, this resets the expressions. 3063 dialect: the dialect used to parse the input expressions. 3064 copy: if `False`, modify this expression instance in-place. 3065 opts: other options to use to parse the input expressions. 3066 3067 Returns: 3068 The modified Select expression. 3069 """ 3070 return _apply_list_builder( 3071 *expressions, 3072 instance=self, 3073 arg="laterals", 3074 append=append, 3075 into=Lateral, 3076 prefix="LATERAL VIEW", 3077 dialect=dialect, 3078 copy=copy, 3079 **opts, 3080 ) 3081 3082 def join( 3083 self, 3084 expression: ExpOrStr, 3085 on: t.Optional[ExpOrStr] = None, 3086 using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None, 3087 append: bool = True, 3088 join_type: t.Optional[str] = None, 3089 join_alias: t.Optional[Identifier | str] = None, 3090 dialect: DialectType = None, 3091 copy: bool = True, 3092 **opts, 3093 ) -> Select: 3094 """ 3095 Append to or set the JOIN expressions. 3096 3097 Example: 3098 >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql() 3099 'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y' 3100 3101 >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql() 3102 'SELECT 1 FROM a JOIN b USING (x, y, z)' 3103 3104 Use `join_type` to change the type of join: 3105 3106 >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql() 3107 'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y' 3108 3109 Args: 3110 expression: the SQL code string to parse. 3111 If an `Expression` instance is passed, it will be used as-is. 3112 on: optionally specify the join "on" criteria as a SQL string. 3113 If an `Expression` instance is passed, it will be used as-is. 3114 using: optionally specify the join "using" criteria as a SQL string. 3115 If an `Expression` instance is passed, it will be used as-is. 3116 append: if `True`, add to any existing expressions. 3117 Otherwise, this resets the expressions. 3118 join_type: if set, alter the parsed join type. 3119 join_alias: an optional alias for the joined source. 3120 dialect: the dialect used to parse the input expressions. 3121 copy: if `False`, modify this expression instance in-place. 3122 opts: other options to use to parse the input expressions. 3123 3124 Returns: 3125 Select: the modified expression. 3126 """ 3127 parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts} 3128 3129 try: 3130 expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args) 3131 except ParseError: 3132 expression = maybe_parse(expression, into=(Join, Expression), **parse_args) 3133 3134 join = expression if isinstance(expression, Join) else Join(this=expression) 3135 3136 if isinstance(join.this, Select): 3137 join.this.replace(join.this.subquery()) 3138 3139 if join_type: 3140 method: t.Optional[Token] 3141 side: t.Optional[Token] 3142 kind: t.Optional[Token] 3143 3144 method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args) # type: ignore 3145 3146 if method: 3147 join.set("method", method.text) 3148 if side: 3149 join.set("side", side.text) 3150 if kind: 3151 join.set("kind", kind.text) 3152 3153 if on: 3154 on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts) 3155 join.set("on", on) 3156 3157 if using: 3158 join = _apply_list_builder( 3159 *ensure_list(using), 3160 instance=join, 3161 arg="using", 3162 append=append, 3163 copy=copy, 3164 into=Identifier, 3165 **opts, 3166 ) 3167 3168 if join_alias: 3169 join.set("this", alias_(join.this, join_alias, table=True)) 3170 3171 return _apply_list_builder( 3172 join, 3173 instance=self, 3174 arg="joins", 3175 append=append, 3176 copy=copy, 3177 **opts, 3178 ) 3179 3180 def where( 3181 self, 3182 *expressions: t.Optional[ExpOrStr], 3183 append: bool = True, 3184 dialect: DialectType = None, 3185 copy: bool = True, 3186 **opts, 3187 ) -> Select: 3188 """ 3189 Append to or set the WHERE expressions. 3190 3191 Example: 3192 >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql() 3193 "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'" 3194 3195 Args: 3196 *expressions: the SQL code strings to parse. 3197 If an `Expression` instance is passed, it will be used as-is. 3198 Multiple expressions are combined with an AND operator. 3199 append: if `True`, AND the new expressions to any existing expression. 3200 Otherwise, this resets the expression. 3201 dialect: the dialect used to parse the input expressions. 3202 copy: if `False`, modify this expression instance in-place. 3203 opts: other options to use to parse the input expressions. 3204 3205 Returns: 3206 Select: the modified expression. 3207 """ 3208 return _apply_conjunction_builder( 3209 *expressions, 3210 instance=self, 3211 arg="where", 3212 append=append, 3213 into=Where, 3214 dialect=dialect, 3215 copy=copy, 3216 **opts, 3217 ) 3218 3219 def having( 3220 self, 3221 *expressions: t.Optional[ExpOrStr], 3222 append: bool = True, 3223 dialect: DialectType = None, 3224 copy: bool = True, 3225 **opts, 3226 ) -> Select: 3227 """ 3228 Append to or set the HAVING expressions. 3229 3230 Example: 3231 >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql() 3232 'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3' 3233 3234 Args: 3235 *expressions: the SQL code strings to parse. 3236 If an `Expression` instance is passed, it will be used as-is. 3237 Multiple expressions are combined with an AND operator. 3238 append: if `True`, AND the new expressions to any existing expression. 3239 Otherwise, this resets the expression. 3240 dialect: the dialect used to parse the input expressions. 3241 copy: if `False`, modify this expression instance in-place. 3242 opts: other options to use to parse the input expressions. 3243 3244 Returns: 3245 The modified Select expression. 3246 """ 3247 return _apply_conjunction_builder( 3248 *expressions, 3249 instance=self, 3250 arg="having", 3251 append=append, 3252 into=Having, 3253 dialect=dialect, 3254 copy=copy, 3255 **opts, 3256 ) 3257 3258 def window( 3259 self, 3260 *expressions: t.Optional[ExpOrStr], 3261 append: bool = True, 3262 dialect: DialectType = None, 3263 copy: bool = True, 3264 **opts, 3265 ) -> Select: 3266 return _apply_list_builder( 3267 *expressions, 3268 instance=self, 3269 arg="windows", 3270 append=append, 3271 into=Window, 3272 dialect=dialect, 3273 copy=copy, 3274 **opts, 3275 ) 3276 3277 def qualify( 3278 self, 3279 *expressions: t.Optional[ExpOrStr], 3280 append: bool = True, 3281 dialect: DialectType = None, 3282 copy: bool = True, 3283 **opts, 3284 ) -> Select: 3285 return _apply_conjunction_builder( 3286 *expressions, 3287 instance=self, 3288 arg="qualify", 3289 append=append, 3290 into=Qualify, 3291 dialect=dialect, 3292 copy=copy, 3293 **opts, 3294 ) 3295 3296 def distinct( 3297 self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True 3298 ) -> Select: 3299 """ 3300 Set the OFFSET expression. 3301 3302 Example: 3303 >>> Select().from_("tbl").select("x").distinct().sql() 3304 'SELECT DISTINCT x FROM tbl' 3305 3306 Args: 3307 ons: the expressions to distinct on 3308 distinct: whether the Select should be distinct 3309 copy: if `False`, modify this expression instance in-place. 3310 3311 Returns: 3312 Select: the modified expression. 3313 """ 3314 instance = maybe_copy(self, copy) 3315 on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None 3316 instance.set("distinct", Distinct(on=on) if distinct else None) 3317 return instance 3318 3319 def ctas( 3320 self, 3321 table: ExpOrStr, 3322 properties: t.Optional[t.Dict] = None, 3323 dialect: DialectType = None, 3324 copy: bool = True, 3325 **opts, 3326 ) -> Create: 3327 """ 3328 Convert this expression to a CREATE TABLE AS statement. 3329 3330 Example: 3331 >>> Select().select("*").from_("tbl").ctas("x").sql() 3332 'CREATE TABLE x AS SELECT * FROM tbl' 3333 3334 Args: 3335 table: the SQL code string to parse as the table name. 3336 If another `Expression` instance is passed, it will be used as-is. 3337 properties: an optional mapping of table properties 3338 dialect: the dialect used to parse the input table. 3339 copy: if `False`, modify this expression instance in-place. 3340 opts: other options to use to parse the input table. 3341 3342 Returns: 3343 The new Create expression. 3344 """ 3345 instance = maybe_copy(self, copy) 3346 table_expression = maybe_parse( 3347 table, 3348 into=Table, 3349 dialect=dialect, 3350 **opts, 3351 ) 3352 properties_expression = None 3353 if properties: 3354 properties_expression = Properties.from_dict(properties) 3355 3356 return Create( 3357 this=table_expression, 3358 kind="table", 3359 expression=instance, 3360 properties=properties_expression, 3361 ) 3362 3363 def lock(self, update: bool = True, copy: bool = True) -> Select: 3364 """ 3365 Set the locking read mode for this expression. 3366 3367 Examples: 3368 >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql") 3369 "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE" 3370 3371 >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql") 3372 "SELECT x FROM tbl WHERE x = 'a' FOR SHARE" 3373 3374 Args: 3375 update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`. 3376 copy: if `False`, modify this expression instance in-place. 3377 3378 Returns: 3379 The modified expression. 3380 """ 3381 inst = maybe_copy(self, copy) 3382 inst.set("locks", [Lock(update=update)]) 3383 3384 return inst 3385 3386 def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select: 3387 """ 3388 Set hints for this expression. 3389 3390 Examples: 3391 >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark") 3392 'SELECT /*+ BROADCAST(y) */ x FROM tbl' 3393 3394 Args: 3395 hints: The SQL code strings to parse as the hints. 3396 If an `Expression` instance is passed, it will be used as-is. 3397 dialect: The dialect used to parse the hints. 3398 copy: If `False`, modify this expression instance in-place. 3399 3400 Returns: 3401 The modified expression. 3402 """ 3403 inst = maybe_copy(self, copy) 3404 inst.set( 3405 "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints]) 3406 ) 3407 3408 return inst 3409 3410 @property 3411 def named_selects(self) -> t.List[str]: 3412 return [e.output_name for e in self.expressions if e.alias_or_name] 3413 3414 @property 3415 def is_star(self) -> bool: 3416 return any(expression.is_star for expression in self.expressions) 3417 3418 @property 3419 def selects(self) -> t.List[Expression]: 3420 return self.expressions 3421 3422 3423class Subquery(DerivedTable, Unionable): 3424 arg_types = { 3425 "this": True, 3426 "alias": False, 3427 "with": False, 3428 **QUERY_MODIFIERS, 3429 } 3430 3431 def unnest(self): 3432 """ 3433 Returns the first non subquery. 3434 """ 3435 expression = self 3436 while isinstance(expression, Subquery): 3437 expression = expression.this 3438 return expression 3439 3440 def unwrap(self) -> Subquery: 3441 expression = self 3442 while expression.same_parent and expression.is_wrapper: 3443 expression = t.cast(Subquery, expression.parent) 3444 return expression 3445 3446 @property 3447 def is_wrapper(self) -> bool: 3448 """ 3449 Whether this Subquery acts as a simple wrapper around another expression. 3450 3451 SELECT * FROM (((SELECT * FROM t))) 3452 ^ 3453 This corresponds to a "wrapper" Subquery node 3454 """ 3455 return all(v is None for k, v in self.args.items() if k != "this") 3456 3457 @property 3458 def is_star(self) -> bool: 3459 return self.this.is_star 3460 3461 @property 3462 def output_name(self) -> str: 3463 return self.alias 3464 3465 3466class TableSample(Expression): 3467 arg_types = { 3468 "this": False, 3469 "expressions": False, 3470 "method": False, 3471 "bucket_numerator": False, 3472 "bucket_denominator": False, 3473 "bucket_field": False, 3474 "percent": False, 3475 "rows": False, 3476 "size": False, 3477 "seed": False, 3478 "kind": False, 3479 } 3480 3481 3482class Tag(Expression): 3483 """Tags are used for generating arbitrary sql like SELECT <span>x</span>.""" 3484 3485 arg_types = { 3486 "this": False, 3487 "prefix": False, 3488 "postfix": False, 3489 } 3490 3491 3492# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax 3493# https://duckdb.org/docs/sql/statements/pivot 3494class Pivot(Expression): 3495 arg_types = { 3496 "this": False, 3497 "alias": False, 3498 "expressions": False, 3499 "field": False, 3500 "unpivot": False, 3501 "using": False, 3502 "group": False, 3503 "columns": False, 3504 "include_nulls": False, 3505 } 3506 3507 3508class Window(Condition): 3509 arg_types = { 3510 "this": True, 3511 "partition_by": False, 3512 "order": False, 3513 "spec": False, 3514 "alias": False, 3515 "over": False, 3516 "first": False, 3517 } 3518 3519 3520class WindowSpec(Expression): 3521 arg_types = { 3522 "kind": False, 3523 "start": False, 3524 "start_side": False, 3525 "end": False, 3526 "end_side": False, 3527 } 3528 3529 3530class Where(Expression): 3531 pass 3532 3533 3534class Star(Expression): 3535 arg_types = {"except": False, "replace": False} 3536 3537 @property 3538 def name(self) -> str: 3539 return "*" 3540 3541 @property 3542 def output_name(self) -> str: 3543 return self.name 3544 3545 3546class Parameter(Condition): 3547 arg_types = {"this": True, "expression": False} 3548 3549 3550class SessionParameter(Condition): 3551 arg_types = {"this": True, "kind": False} 3552 3553 3554class Placeholder(Condition): 3555 arg_types = {"this": False, "kind": False} 3556 3557 3558class Null(Condition): 3559 arg_types: t.Dict[str, t.Any] = {} 3560 3561 @property 3562 def name(self) -> str: 3563 return "NULL" 3564 3565 3566class Boolean(Condition): 3567 pass 3568 3569 3570class DataTypeParam(Expression): 3571 arg_types = {"this": True, "expression": False} 3572 3573 3574class DataType(Expression): 3575 arg_types = { 3576 "this": True, 3577 "expressions": False, 3578 "nested": False, 3579 "values": False, 3580 "prefix": False, 3581 "kind": False, 3582 } 3583 3584 class Type(AutoName): 3585 ARRAY = auto() 3586 BIGDECIMAL = auto() 3587 BIGINT = auto() 3588 BIGSERIAL = auto() 3589 BINARY = auto() 3590 BIT = auto() 3591 BOOLEAN = auto() 3592 CHAR = auto() 3593 DATE = auto() 3594 DATEMULTIRANGE = auto() 3595 DATERANGE = auto() 3596 DATETIME = auto() 3597 DATETIME64 = auto() 3598 DECIMAL = auto() 3599 DOUBLE = auto() 3600 ENUM = auto() 3601 ENUM8 = auto() 3602 ENUM16 = auto() 3603 FIXEDSTRING = auto() 3604 FLOAT = auto() 3605 GEOGRAPHY = auto() 3606 GEOMETRY = auto() 3607 HLLSKETCH = auto() 3608 HSTORE = auto() 3609 IMAGE = auto() 3610 INET = auto() 3611 INT = auto() 3612 INT128 = auto() 3613 INT256 = auto() 3614 INT4MULTIRANGE = auto() 3615 INT4RANGE = auto() 3616 INT8MULTIRANGE = auto() 3617 INT8RANGE = auto() 3618 INTERVAL = auto() 3619 IPADDRESS = auto() 3620 IPPREFIX = auto() 3621 JSON = auto() 3622 JSONB = auto() 3623 LONGBLOB = auto() 3624 LONGTEXT = auto() 3625 LOWCARDINALITY = auto() 3626 MAP = auto() 3627 MEDIUMBLOB = auto() 3628 MEDIUMINT = auto() 3629 MEDIUMTEXT = auto() 3630 MONEY = auto() 3631 NCHAR = auto() 3632 NESTED = auto() 3633 NULL = auto() 3634 NULLABLE = auto() 3635 NUMMULTIRANGE = auto() 3636 NUMRANGE = auto() 3637 NVARCHAR = auto() 3638 OBJECT = auto() 3639 ROWVERSION = auto() 3640 SERIAL = auto() 3641 SET = auto() 3642 SMALLINT = auto() 3643 SMALLMONEY = auto() 3644 SMALLSERIAL = auto() 3645 STRUCT = auto() 3646 SUPER = auto() 3647 TEXT = auto() 3648 TINYBLOB = auto() 3649 TINYTEXT = auto() 3650 TIME = auto() 3651 TIMETZ = auto() 3652 TIMESTAMP = auto() 3653 TIMESTAMPLTZ = auto() 3654 TIMESTAMPTZ = auto() 3655 TIMESTAMP_S = auto() 3656 TIMESTAMP_MS = auto() 3657 TIMESTAMP_NS = auto() 3658 TINYINT = auto() 3659 TSMULTIRANGE = auto() 3660 TSRANGE = auto() 3661 TSTZMULTIRANGE = auto() 3662 TSTZRANGE = auto() 3663 UBIGINT = auto() 3664 UINT = auto() 3665 UINT128 = auto() 3666 UINT256 = auto() 3667 UMEDIUMINT = auto() 3668 UDECIMAL = auto() 3669 UNIQUEIDENTIFIER = auto() 3670 UNKNOWN = auto() # Sentinel value, useful for type annotation 3671 USERDEFINED = "USER-DEFINED" 3672 USMALLINT = auto() 3673 UTINYINT = auto() 3674 UUID = auto() 3675 VARBINARY = auto() 3676 VARCHAR = auto() 3677 VARIANT = auto() 3678 XML = auto() 3679 YEAR = auto() 3680 3681 TEXT_TYPES = { 3682 Type.CHAR, 3683 Type.NCHAR, 3684 Type.VARCHAR, 3685 Type.NVARCHAR, 3686 Type.TEXT, 3687 } 3688 3689 INTEGER_TYPES = { 3690 Type.INT, 3691 Type.TINYINT, 3692 Type.SMALLINT, 3693 Type.BIGINT, 3694 Type.INT128, 3695 Type.INT256, 3696 Type.BIT, 3697 } 3698 3699 FLOAT_TYPES = { 3700 Type.FLOAT, 3701 Type.DOUBLE, 3702 } 3703 3704 NUMERIC_TYPES = { 3705 *INTEGER_TYPES, 3706 *FLOAT_TYPES, 3707 } 3708 3709 TEMPORAL_TYPES = { 3710 Type.TIME, 3711 Type.TIMETZ, 3712 Type.TIMESTAMP, 3713 Type.TIMESTAMPTZ, 3714 Type.TIMESTAMPLTZ, 3715 Type.TIMESTAMP_S, 3716 Type.TIMESTAMP_MS, 3717 Type.TIMESTAMP_NS, 3718 Type.DATE, 3719 Type.DATETIME, 3720 Type.DATETIME64, 3721 } 3722 3723 @classmethod 3724 def build( 3725 cls, 3726 dtype: DATA_TYPE, 3727 dialect: DialectType = None, 3728 udt: bool = False, 3729 **kwargs, 3730 ) -> DataType: 3731 """ 3732 Constructs a DataType object. 3733 3734 Args: 3735 dtype: the data type of interest. 3736 dialect: the dialect to use for parsing `dtype`, in case it's a string. 3737 udt: when set to True, `dtype` will be used as-is if it can't be parsed into a 3738 DataType, thus creating a user-defined type. 3739 kawrgs: additional arguments to pass in the constructor of DataType. 3740 3741 Returns: 3742 The constructed DataType object. 3743 """ 3744 from sqlglot import parse_one 3745 3746 if isinstance(dtype, str): 3747 if dtype.upper() == "UNKNOWN": 3748 return DataType(this=DataType.Type.UNKNOWN, **kwargs) 3749 3750 try: 3751 data_type_exp = parse_one( 3752 dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE 3753 ) 3754 except ParseError: 3755 if udt: 3756 return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs) 3757 raise 3758 elif isinstance(dtype, DataType.Type): 3759 data_type_exp = DataType(this=dtype) 3760 elif isinstance(dtype, DataType): 3761 return dtype 3762 else: 3763 raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type") 3764 3765 return DataType(**{**data_type_exp.args, **kwargs}) 3766 3767 def is_type(self, *dtypes: DATA_TYPE) -> bool: 3768 """ 3769 Checks whether this DataType matches one of the provided data types. Nested types or precision 3770 will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>. 3771 3772 Args: 3773 dtypes: the data types to compare this DataType to. 3774 3775 Returns: 3776 True, if and only if there is a type in `dtypes` which is equal to this DataType. 3777 """ 3778 for dtype in dtypes: 3779 other = DataType.build(dtype, udt=True) 3780 3781 if ( 3782 other.expressions 3783 or self.this == DataType.Type.USERDEFINED 3784 or other.this == DataType.Type.USERDEFINED 3785 ): 3786 matches = self == other 3787 else: 3788 matches = self.this == other.this 3789 3790 if matches: 3791 return True 3792 return False 3793 3794 3795DATA_TYPE = t.Union[str, DataType, DataType.Type] 3796 3797 3798# https://www.postgresql.org/docs/15/datatype-pseudo.html 3799class PseudoType(DataType): 3800 arg_types = {"this": True} 3801 3802 3803# https://www.postgresql.org/docs/15/datatype-oid.html 3804class ObjectIdentifier(DataType): 3805 arg_types = {"this": True} 3806 3807 3808# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...) 3809class SubqueryPredicate(Predicate): 3810 pass 3811 3812 3813class All(SubqueryPredicate): 3814 pass 3815 3816 3817class Any(SubqueryPredicate): 3818 pass 3819 3820 3821class Exists(SubqueryPredicate): 3822 pass 3823 3824 3825# Commands to interact with the databases or engines. For most of the command 3826# expressions we parse whatever comes after the command's name as a string. 3827class Command(Expression): 3828 arg_types = {"this": True, "expression": False} 3829 3830 3831class Transaction(Expression): 3832 arg_types = {"this": False, "modes": False, "mark": False} 3833 3834 3835class Commit(Expression): 3836 arg_types = {"chain": False, "this": False, "durability": False} 3837 3838 3839class Rollback(Expression): 3840 arg_types = {"savepoint": False, "this": False} 3841 3842 3843class AlterTable(Expression): 3844 arg_types = {"this": True, "actions": True, "exists": False, "only": False} 3845 3846 3847class AddConstraint(Expression): 3848 arg_types = {"this": False, "expression": False, "enforced": False} 3849 3850 3851class DropPartition(Expression): 3852 arg_types = {"expressions": True, "exists": False} 3853 3854 3855# Binary expressions like (ADD a b) 3856class Binary(Condition): 3857 arg_types = {"this": True, "expression": True} 3858 3859 @property 3860 def left(self) -> Expression: 3861 return self.this 3862 3863 @property 3864 def right(self) -> Expression: 3865 return self.expression 3866 3867 3868class Add(Binary): 3869 pass 3870 3871 3872class Connector(Binary): 3873 pass 3874 3875 3876class And(Connector): 3877 pass 3878 3879 3880class Or(Connector): 3881 pass 3882 3883 3884class BitwiseAnd(Binary): 3885 pass 3886 3887 3888class BitwiseLeftShift(Binary): 3889 pass 3890 3891 3892class BitwiseOr(Binary): 3893 pass 3894 3895 3896class BitwiseRightShift(Binary): 3897 pass 3898 3899 3900class BitwiseXor(Binary): 3901 pass 3902 3903 3904class Div(Binary): 3905 arg_types = {"this":