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 sqlglot.expressions.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 23 24from sqlglot.errors import ParseError 25from sqlglot.helper import ( 26 AutoName, 27 camel_to_snake_case, 28 ensure_collection, 29 seq_get, 30 split_num_words, 31 subclasses, 32) 33from sqlglot.tokens import Token 34 35if t.TYPE_CHECKING: 36 from sqlglot.dialects.dialect import DialectType 37 38 39class _Expression(type): 40 def __new__(cls, clsname, bases, attrs): 41 klass = super().__new__(cls, clsname, bases, attrs) 42 43 # When an Expression class is created, its key is automatically set to be 44 # the lowercase version of the class' name. 45 klass.key = clsname.lower() 46 47 # This is so that docstrings are not inherited in pdoc 48 klass.__doc__ = klass.__doc__ or "" 49 50 return klass 51 52 53class Expression(metaclass=_Expression): 54 """ 55 The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary 56 context, such as its child expressions, their names (arg keys), and whether a given child expression 57 is optional or not. 58 59 Attributes: 60 key: a unique key for each class in the Expression hierarchy. This is useful for hashing 61 and representing expressions as strings. 62 arg_types: determines what arguments (child nodes) are supported by an expression. It 63 maps arg keys to booleans that indicate whether the corresponding args are optional. 64 65 Example: 66 >>> class Foo(Expression): 67 ... arg_types = {"this": True, "expression": False} 68 69 The above definition informs us that Foo is an Expression that requires an argument called 70 "this" and may also optionally receive an argument called "expression". 71 72 Args: 73 args: a mapping used for retrieving the arguments of an expression, given their arg keys. 74 parent: a reference to the parent expression (or None, in case of root expressions). 75 arg_key: the arg key an expression is associated with, i.e. the name its parent expression 76 uses to refer to it. 77 comments: a list of comments that are associated with a given expression. This is used in 78 order to preserve comments when transpiling SQL code. 79 _type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the 80 optimizer, in order to enable some transformations that require type information. 81 """ 82 83 key = "expression" 84 arg_types = {"this": True} 85 __slots__ = ("args", "parent", "arg_key", "comments", "_type") 86 87 def __init__(self, **args: t.Any): 88 self.args: t.Dict[str, t.Any] = args 89 self.parent: t.Optional[Expression] = None 90 self.arg_key: t.Optional[str] = None 91 self.comments: t.Optional[t.List[str]] = None 92 self._type: t.Optional[DataType] = None 93 94 for arg_key, value in self.args.items(): 95 self._set_parent(arg_key, value) 96 97 def __eq__(self, other) -> bool: 98 return type(self) is type(other) and _norm_args(self) == _norm_args(other) 99 100 def __hash__(self) -> int: 101 return hash( 102 ( 103 self.key, 104 tuple( 105 (k, tuple(v) if isinstance(v, list) else v) for k, v in _norm_args(self).items() 106 ), 107 ) 108 ) 109 110 @property 111 def this(self): 112 """ 113 Retrieves the argument with key "this". 114 """ 115 return self.args.get("this") 116 117 @property 118 def expression(self): 119 """ 120 Retrieves the argument with key "expression". 121 """ 122 return self.args.get("expression") 123 124 @property 125 def expressions(self): 126 """ 127 Retrieves the argument with key "expressions". 128 """ 129 return self.args.get("expressions") or [] 130 131 def text(self, key): 132 """ 133 Returns a textual representation of the argument corresponding to "key". This can only be used 134 for args that are strings or leaf Expression instances, such as identifiers and literals. 135 """ 136 field = self.args.get(key) 137 if isinstance(field, str): 138 return field 139 if isinstance(field, (Identifier, Literal, Var)): 140 return field.this 141 if isinstance(field, (Star, Null)): 142 return field.name 143 return "" 144 145 @property 146 def is_string(self): 147 """ 148 Checks whether a Literal expression is a string. 149 """ 150 return isinstance(self, Literal) and self.args["is_string"] 151 152 @property 153 def is_number(self): 154 """ 155 Checks whether a Literal expression is a number. 156 """ 157 return isinstance(self, Literal) and not self.args["is_string"] 158 159 @property 160 def is_int(self): 161 """ 162 Checks whether a Literal expression is an integer. 163 """ 164 if self.is_number: 165 try: 166 int(self.name) 167 return True 168 except ValueError: 169 pass 170 return False 171 172 @property 173 def alias(self): 174 """ 175 Returns the alias of the expression, or an empty string if it's not aliased. 176 """ 177 if isinstance(self.args.get("alias"), TableAlias): 178 return self.args["alias"].name 179 return self.text("alias") 180 181 @property 182 def name(self) -> str: 183 return self.text("this") 184 185 @property 186 def alias_or_name(self): 187 return self.alias or self.name 188 189 @property 190 def output_name(self): 191 """ 192 Name of the output column if this expression is a selection. 193 194 If the Expression has no output name, an empty string is returned. 195 196 Example: 197 >>> from sqlglot import parse_one 198 >>> parse_one("SELECT a").expressions[0].output_name 199 'a' 200 >>> parse_one("SELECT b AS c").expressions[0].output_name 201 'c' 202 >>> parse_one("SELECT 1 + 2").expressions[0].output_name 203 '' 204 """ 205 return "" 206 207 @property 208 def type(self) -> t.Optional[DataType]: 209 return self._type 210 211 @type.setter 212 def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None: 213 if dtype and not isinstance(dtype, DataType): 214 dtype = DataType.build(dtype) 215 self._type = dtype # type: ignore 216 217 def __deepcopy__(self, memo): 218 copy = self.__class__(**deepcopy(self.args)) 219 copy.comments = self.comments 220 copy.type = self.type 221 return copy 222 223 def copy(self): 224 """ 225 Returns a deep copy of the expression. 226 """ 227 new = deepcopy(self) 228 new.parent = self.parent 229 for item, parent, _ in new.bfs(): 230 if isinstance(item, Expression) and parent: 231 item.parent = parent 232 return new 233 234 def append(self, arg_key, value): 235 """ 236 Appends value to arg_key if it's a list or sets it as a new list. 237 238 Args: 239 arg_key (str): name of the list expression arg 240 value (Any): value to append to the list 241 """ 242 if not isinstance(self.args.get(arg_key), list): 243 self.args[arg_key] = [] 244 self.args[arg_key].append(value) 245 self._set_parent(arg_key, value) 246 247 def set(self, arg_key, value): 248 """ 249 Sets `arg_key` to `value`. 250 251 Args: 252 arg_key (str): name of the expression arg. 253 value: value to set the arg to. 254 """ 255 self.args[arg_key] = value 256 self._set_parent(arg_key, value) 257 258 def _set_parent(self, arg_key, value): 259 if isinstance(value, Expression): 260 value.parent = self 261 value.arg_key = arg_key 262 elif isinstance(value, list): 263 for v in value: 264 if isinstance(v, Expression): 265 v.parent = self 266 v.arg_key = arg_key 267 268 @property 269 def depth(self): 270 """ 271 Returns the depth of this tree. 272 """ 273 if self.parent: 274 return self.parent.depth + 1 275 return 0 276 277 def find(self, *expression_types, bfs=True): 278 """ 279 Returns the first node in this tree which matches at least one of 280 the specified types. 281 282 Args: 283 expression_types (type): the expression type(s) to match. 284 285 Returns: 286 The node which matches the criteria or None if no such node was found. 287 """ 288 return next(self.find_all(*expression_types, bfs=bfs), None) 289 290 def find_all(self, *expression_types, bfs=True): 291 """ 292 Returns a generator object which visits all nodes in this tree and only 293 yields those that match at least one of the specified expression types. 294 295 Args: 296 expression_types (type): the expression type(s) to match. 297 298 Returns: 299 The generator object. 300 """ 301 for expression, _, _ in self.walk(bfs=bfs): 302 if isinstance(expression, expression_types): 303 yield expression 304 305 def find_ancestor(self, *expression_types): 306 """ 307 Returns a nearest parent matching expression_types. 308 309 Args: 310 expression_types (type): the expression type(s) to match. 311 312 Returns: 313 The parent node. 314 """ 315 ancestor = self.parent 316 while ancestor and not isinstance(ancestor, expression_types): 317 ancestor = ancestor.parent 318 return ancestor 319 320 @property 321 def parent_select(self): 322 """ 323 Returns the parent select statement. 324 """ 325 return self.find_ancestor(Select) 326 327 def walk(self, bfs=True, prune=None): 328 """ 329 Returns a generator object which visits all nodes in this tree. 330 331 Args: 332 bfs (bool): if set to True the BFS traversal order will be applied, 333 otherwise the DFS traversal will be used instead. 334 prune ((node, parent, arg_key) -> bool): callable that returns True if 335 the generator should stop traversing this branch of the tree. 336 337 Returns: 338 the generator object. 339 """ 340 if bfs: 341 yield from self.bfs(prune=prune) 342 else: 343 yield from self.dfs(prune=prune) 344 345 def dfs(self, parent=None, key=None, prune=None): 346 """ 347 Returns a generator object which visits all nodes in this tree in 348 the DFS (Depth-first) order. 349 350 Returns: 351 The generator object. 352 """ 353 parent = parent or self.parent 354 yield self, parent, key 355 if prune and prune(self, parent, key): 356 return 357 358 for k, v in self.args.items(): 359 for node in ensure_collection(v): 360 if isinstance(node, Expression): 361 yield from node.dfs(self, k, prune) 362 363 def bfs(self, prune=None): 364 """ 365 Returns a generator object which visits all nodes in this tree in 366 the BFS (Breadth-first) order. 367 368 Returns: 369 The generator object. 370 """ 371 queue = deque([(self, self.parent, None)]) 372 373 while queue: 374 item, parent, key = queue.popleft() 375 376 yield item, parent, key 377 if prune and prune(item, parent, key): 378 continue 379 380 if isinstance(item, Expression): 381 for k, v in item.args.items(): 382 for node in ensure_collection(v): 383 if isinstance(node, Expression): 384 queue.append((node, item, k)) 385 386 def unnest(self): 387 """ 388 Returns the first non parenthesis child or self. 389 """ 390 expression = self 391 while isinstance(expression, Paren): 392 expression = expression.this 393 return expression 394 395 def unalias(self): 396 """ 397 Returns the inner expression if this is an Alias. 398 """ 399 if isinstance(self, Alias): 400 return self.this 401 return self 402 403 def unnest_operands(self): 404 """ 405 Returns unnested operands as a tuple. 406 """ 407 return tuple(arg.unnest() for arg in self.args.values() if arg) 408 409 def flatten(self, unnest=True): 410 """ 411 Returns a generator which yields child nodes who's parents are the same class. 412 413 A AND B AND C -> [A, B, C] 414 """ 415 for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not isinstance(n, self.__class__)): 416 if not isinstance(node, self.__class__): 417 yield node.unnest() if unnest else node 418 419 def __str__(self): 420 return self.sql() 421 422 def __repr__(self): 423 return self._to_s() 424 425 def sql(self, dialect: DialectType = None, **opts) -> str: 426 """ 427 Returns SQL string representation of this tree. 428 429 Args: 430 dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql"). 431 opts: other `sqlglot.generator.Generator` options. 432 433 Returns: 434 The SQL string. 435 """ 436 from sqlglot.dialects import Dialect 437 438 return Dialect.get_or_raise(dialect)().generate(self, **opts) 439 440 def _to_s(self, hide_missing: bool = True, level: int = 0) -> str: 441 indent = "" if not level else "\n" 442 indent += "".join([" "] * level) 443 left = f"({self.key.upper()} " 444 445 args: t.Dict[str, t.Any] = { 446 k: ", ".join( 447 v._to_s(hide_missing=hide_missing, level=level + 1) 448 if hasattr(v, "_to_s") 449 else str(v) 450 for v in ensure_collection(vs) 451 if v is not None 452 ) 453 for k, vs in self.args.items() 454 } 455 args["comments"] = self.comments 456 args["type"] = self.type 457 args = {k: v for k, v in args.items() if v or not hide_missing} 458 459 right = ", ".join(f"{k}: {v}" for k, v in args.items()) 460 right += ")" 461 462 return indent + left + right 463 464 def transform(self, fun, *args, copy=True, **kwargs): 465 """ 466 Recursively visits all tree nodes (excluding already transformed ones) 467 and applies the given transformation function to each node. 468 469 Args: 470 fun (function): a function which takes a node as an argument and returns a 471 new transformed node or the same node without modifications. If the function 472 returns None, then the corresponding node will be removed from the syntax tree. 473 copy (bool): if set to True a new tree instance is constructed, otherwise the tree is 474 modified in place. 475 476 Returns: 477 The transformed tree. 478 """ 479 node = self.copy() if copy else self 480 new_node = fun(node, *args, **kwargs) 481 482 if new_node is None or not isinstance(new_node, Expression): 483 return new_node 484 if new_node is not node: 485 new_node.parent = node.parent 486 return new_node 487 488 replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs)) 489 return new_node 490 491 def replace(self, expression): 492 """ 493 Swap out this expression with a new expression. 494 495 For example:: 496 497 >>> tree = Select().select("x").from_("tbl") 498 >>> tree.find(Column).replace(Column(this="y")) 499 (COLUMN this: y) 500 >>> tree.sql() 501 'SELECT y FROM tbl' 502 503 Args: 504 expression (Expression|None): new node 505 506 Returns: 507 The new expression or expressions. 508 """ 509 if not self.parent: 510 return expression 511 512 parent = self.parent 513 self.parent = None 514 515 replace_children(parent, lambda child: expression if child is self else child) 516 return expression 517 518 def pop(self): 519 """ 520 Remove this expression from its AST. 521 """ 522 self.replace(None) 523 524 def assert_is(self, type_): 525 """ 526 Assert that this `Expression` is an instance of `type_`. 527 528 If it is NOT an instance of `type_`, this raises an assertion error. 529 Otherwise, this returns this expression. 530 531 Examples: 532 This is useful for type security in chained expressions: 533 534 >>> import sqlglot 535 >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql() 536 'SELECT x, z FROM y' 537 """ 538 assert isinstance(self, type_) 539 return self 540 541 def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]: 542 """ 543 Checks if this expression is valid (e.g. all mandatory args are set). 544 545 Args: 546 args: a sequence of values that were used to instantiate a Func expression. This is used 547 to check that the provided arguments don't exceed the function argument limit. 548 549 Returns: 550 A list of error messages for all possible errors that were found. 551 """ 552 errors: t.List[str] = [] 553 554 for k in self.args: 555 if k not in self.arg_types: 556 errors.append(f"Unexpected keyword: '{k}' for {self.__class__}") 557 for k, mandatory in self.arg_types.items(): 558 v = self.args.get(k) 559 if mandatory and (v is None or (isinstance(v, list) and not v)): 560 errors.append(f"Required keyword: '{k}' missing for {self.__class__}") 561 562 if ( 563 args 564 and isinstance(self, Func) 565 and len(args) > len(self.arg_types) 566 and not self.is_var_len_args 567 ): 568 errors.append( 569 f"The number of provided arguments ({len(args)}) is greater than " 570 f"the maximum number of supported arguments ({len(self.arg_types)})" 571 ) 572 573 return errors 574 575 def dump(self): 576 """ 577 Dump this Expression to a JSON-serializable dict. 578 """ 579 from sqlglot.serde import dump 580 581 return dump(self) 582 583 @classmethod 584 def load(cls, obj): 585 """ 586 Load a dict (as returned by `Expression.dump`) into an Expression instance. 587 """ 588 from sqlglot.serde import load 589 590 return load(obj) 591 592 593IntoType = t.Union[ 594 str, 595 t.Type[Expression], 596 t.Collection[t.Union[str, t.Type[Expression]]], 597] 598 599 600class Condition(Expression): 601 def and_(self, *expressions, dialect=None, **opts): 602 """ 603 AND this condition with one or multiple expressions. 604 605 Example: 606 >>> condition("x=1").and_("y=1").sql() 607 'x = 1 AND y = 1' 608 609 Args: 610 *expressions (str | Expression): the SQL code strings to parse. 611 If an `Expression` instance is passed, it will be used as-is. 612 dialect (str): the dialect used to parse the input expression. 613 opts (kwargs): other options to use to parse the input expressions. 614 615 Returns: 616 And: the new condition. 617 """ 618 return and_(self, *expressions, dialect=dialect, **opts) 619 620 def or_(self, *expressions, dialect=None, **opts): 621 """ 622 OR this condition with one or multiple expressions. 623 624 Example: 625 >>> condition("x=1").or_("y=1").sql() 626 'x = 1 OR y = 1' 627 628 Args: 629 *expressions (str | Expression): the SQL code strings to parse. 630 If an `Expression` instance is passed, it will be used as-is. 631 dialect (str): the dialect used to parse the input expression. 632 opts (kwargs): other options to use to parse the input expressions. 633 634 Returns: 635 Or: the new condition. 636 """ 637 return or_(self, *expressions, dialect=dialect, **opts) 638 639 def not_(self): 640 """ 641 Wrap this condition with NOT. 642 643 Example: 644 >>> condition("x=1").not_().sql() 645 'NOT x = 1' 646 647 Returns: 648 Not: the new condition. 649 """ 650 return not_(self) 651 652 653class Predicate(Condition): 654 """Relationships like x = y, x > 1, x >= y.""" 655 656 657class DerivedTable(Expression): 658 @property 659 def alias_column_names(self): 660 table_alias = self.args.get("alias") 661 if not table_alias: 662 return [] 663 column_list = table_alias.assert_is(TableAlias).args.get("columns") or [] 664 return [c.name for c in column_list] 665 666 @property 667 def selects(self): 668 alias = self.args.get("alias") 669 670 if alias: 671 return alias.columns 672 return [] 673 674 @property 675 def named_selects(self): 676 return [select.output_name for select in self.selects] 677 678 679class Unionable(Expression): 680 def union(self, expression, distinct=True, dialect=None, **opts): 681 """ 682 Builds a UNION expression. 683 684 Example: 685 >>> import sqlglot 686 >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql() 687 'SELECT * FROM foo UNION SELECT * FROM bla' 688 689 Args: 690 expression (str | Expression): the SQL code string. 691 If an `Expression` instance is passed, it will be used as-is. 692 distinct (bool): set the DISTINCT flag if and only if this is true. 693 dialect (str): the dialect used to parse the input expression. 694 opts (kwargs): other options to use to parse the input expressions. 695 Returns: 696 Union: the Union expression. 697 """ 698 return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts) 699 700 def intersect(self, expression, distinct=True, dialect=None, **opts): 701 """ 702 Builds an INTERSECT expression. 703 704 Example: 705 >>> import sqlglot 706 >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql() 707 'SELECT * FROM foo INTERSECT SELECT * FROM bla' 708 709 Args: 710 expression (str | Expression): the SQL code string. 711 If an `Expression` instance is passed, it will be used as-is. 712 distinct (bool): set the DISTINCT flag if and only if this is true. 713 dialect (str): the dialect used to parse the input expression. 714 opts (kwargs): other options to use to parse the input expressions. 715 Returns: 716 Intersect: the Intersect expression 717 """ 718 return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts) 719 720 def except_(self, expression, distinct=True, dialect=None, **opts): 721 """ 722 Builds an EXCEPT expression. 723 724 Example: 725 >>> import sqlglot 726 >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql() 727 'SELECT * FROM foo EXCEPT SELECT * FROM bla' 728 729 Args: 730 expression (str | Expression): the SQL code string. 731 If an `Expression` instance is passed, it will be used as-is. 732 distinct (bool): set the DISTINCT flag if and only if this is true. 733 dialect (str): the dialect used to parse the input expression. 734 opts (kwargs): other options to use to parse the input expressions. 735 Returns: 736 Except: the Except expression 737 """ 738 return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts) 739 740 741class UDTF(DerivedTable, Unionable): 742 pass 743 744 745class Cache(Expression): 746 arg_types = { 747 "with": False, 748 "this": True, 749 "lazy": False, 750 "options": False, 751 "expression": False, 752 } 753 754 755class Uncache(Expression): 756 arg_types = {"this": True, "exists": False} 757 758 759class Create(Expression): 760 arg_types = { 761 "with": False, 762 "this": True, 763 "kind": True, 764 "expression": False, 765 "set": False, 766 "multiset": False, 767 "global_temporary": False, 768 "volatile": False, 769 "exists": False, 770 "properties": False, 771 "temporary": False, 772 "transient": False, 773 "external": False, 774 "replace": False, 775 "unique": False, 776 "materialized": False, 777 "data": False, 778 "statistics": False, 779 "no_primary_index": False, 780 "indexes": False, 781 "no_schema_binding": False, 782 "begin": False, 783 } 784 785 786class Describe(Expression): 787 arg_types = {"this": True, "kind": False} 788 789 790class Set(Expression): 791 arg_types = {"expressions": True} 792 793 794class SetItem(Expression): 795 arg_types = { 796 "this": False, 797 "expressions": False, 798 "kind": False, 799 "collate": False, # MySQL SET NAMES statement 800 "global": False, 801 } 802 803 804class Show(Expression): 805 arg_types = { 806 "this": True, 807 "target": False, 808 "offset": False, 809 "limit": False, 810 "like": False, 811 "where": False, 812 "db": False, 813 "full": False, 814 "mutex": False, 815 "query": False, 816 "channel": False, 817 "global": False, 818 "log": False, 819 "position": False, 820 "types": False, 821 } 822 823 824class UserDefinedFunction(Expression): 825 arg_types = {"this": True, "expressions": False, "wrapped": False} 826 827 828class UserDefinedFunctionKwarg(Expression): 829 arg_types = {"this": True, "kind": True, "default": False} 830 831 832class CharacterSet(Expression): 833 arg_types = {"this": True, "default": False} 834 835 836class With(Expression): 837 arg_types = {"expressions": True, "recursive": False} 838 839 @property 840 def recursive(self) -> bool: 841 return bool(self.args.get("recursive")) 842 843 844class WithinGroup(Expression): 845 arg_types = {"this": True, "expression": False} 846 847 848class CTE(DerivedTable): 849 arg_types = {"this": True, "alias": True} 850 851 852class TableAlias(Expression): 853 arg_types = {"this": False, "columns": False} 854 855 @property 856 def columns(self): 857 return self.args.get("columns") or [] 858 859 860class BitString(Condition): 861 pass 862 863 864class HexString(Condition): 865 pass 866 867 868class ByteString(Condition): 869 pass 870 871 872class Column(Condition): 873 arg_types = {"this": True, "table": False} 874 875 @property 876 def table(self): 877 return self.text("table") 878 879 @property 880 def output_name(self): 881 return self.name 882 883 884class ColumnDef(Expression): 885 arg_types = { 886 "this": True, 887 "kind": False, 888 "constraints": False, 889 "exists": False, 890 } 891 892 893class AlterColumn(Expression): 894 arg_types = { 895 "this": True, 896 "dtype": False, 897 "collate": False, 898 "using": False, 899 "default": False, 900 "drop": False, 901 } 902 903 904class RenameTable(Expression): 905 pass 906 907 908class ColumnConstraint(Expression): 909 arg_types = {"this": False, "kind": True} 910 911 912class ColumnConstraintKind(Expression): 913 pass 914 915 916class AutoIncrementColumnConstraint(ColumnConstraintKind): 917 pass 918 919 920class CheckColumnConstraint(ColumnConstraintKind): 921 pass 922 923 924class CollateColumnConstraint(ColumnConstraintKind): 925 pass 926 927 928class CommentColumnConstraint(ColumnConstraintKind): 929 pass 930 931 932class DefaultColumnConstraint(ColumnConstraintKind): 933 pass 934 935 936class EncodeColumnConstraint(ColumnConstraintKind): 937 pass 938 939 940class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind): 941 # this: True -> ALWAYS, this: False -> BY DEFAULT 942 arg_types = {"this": False, "start": False, "increment": False} 943 944 945class NotNullColumnConstraint(ColumnConstraintKind): 946 arg_types = {"allow_null": False} 947 948 949class PrimaryKeyColumnConstraint(ColumnConstraintKind): 950 arg_types = {"desc": False} 951 952 953class UniqueColumnConstraint(ColumnConstraintKind): 954 pass 955 956 957class Constraint(Expression): 958 arg_types = {"this": True, "expressions": True} 959 960 961class Delete(Expression): 962 arg_types = {"with": False, "this": False, "using": False, "where": False} 963 964 965class Drop(Expression): 966 arg_types = { 967 "this": False, 968 "kind": False, 969 "exists": False, 970 "temporary": False, 971 "materialized": False, 972 "cascade": False, 973 } 974 975 976class Filter(Expression): 977 arg_types = {"this": True, "expression": True} 978 979 980class Check(Expression): 981 pass 982 983 984class Directory(Expression): 985 # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html 986 arg_types = {"this": True, "local": False, "row_format": False} 987 988 989class ForeignKey(Expression): 990 arg_types = { 991 "expressions": True, 992 "reference": False, 993 "delete": False, 994 "update": False, 995 } 996 997 998class PrimaryKey(Expression): 999 arg_types = {"expressions": True, "options": False} 1000 1001 1002class Unique(Expression): 1003 arg_types = {"expressions": True} 1004 1005 1006# https://www.postgresql.org/docs/9.1/sql-selectinto.html 1007# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples 1008class Into(Expression): 1009 arg_types = {"this": True, "temporary": False, "unlogged": False} 1010 1011 1012class From(Expression): 1013 arg_types = {"expressions": True} 1014 1015 1016class Having(Expression): 1017 pass 1018 1019 1020class Hint(Expression): 1021 arg_types = {"expressions": True} 1022 1023 1024class JoinHint(Expression): 1025 arg_types = {"this": True, "expressions": True} 1026 1027 1028class Identifier(Expression): 1029 arg_types = {"this": True, "quoted": False} 1030 1031 @property 1032 def quoted(self): 1033 return bool(self.args.get("quoted")) 1034 1035 def __eq__(self, other): 1036 return isinstance(other, self.__class__) and _norm_arg(self.this) == _norm_arg(other.this) 1037 1038 def __hash__(self): 1039 return hash((self.key, self.this.lower())) 1040 1041 @property 1042 def output_name(self): 1043 return self.name 1044 1045 1046class Index(Expression): 1047 arg_types = { 1048 "this": False, 1049 "table": False, 1050 "where": False, 1051 "columns": False, 1052 "unique": False, 1053 "primary": False, 1054 "amp": False, # teradata 1055 } 1056 1057 1058class Insert(Expression): 1059 arg_types = { 1060 "with": False, 1061 "this": True, 1062 "expression": False, 1063 "overwrite": False, 1064 "exists": False, 1065 "partition": False, 1066 } 1067 1068 1069# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html 1070class Introducer(Expression): 1071 arg_types = {"this": True, "expression": True} 1072 1073 1074# national char, like n'utf8' 1075class National(Expression): 1076 pass 1077 1078 1079class LoadData(Expression): 1080 arg_types = { 1081 "this": True, 1082 "local": False, 1083 "overwrite": False, 1084 "inpath": True, 1085 "partition": False, 1086 "input_format": False, 1087 "serde": False, 1088 } 1089 1090 1091class Partition(Expression): 1092 arg_types = {"expressions": True} 1093 1094 1095class Fetch(Expression): 1096 arg_types = {"direction": False, "count": False} 1097 1098 1099class Group(Expression): 1100 arg_types = { 1101 "expressions": False, 1102 "grouping_sets": False, 1103 "cube": False, 1104 "rollup": False, 1105 } 1106 1107 1108class Lambda(Expression): 1109 arg_types = {"this": True, "expressions": True} 1110 1111 1112class Limit(Expression): 1113 arg_types = {"this": False, "expression": True} 1114 1115 1116class Literal(Condition): 1117 arg_types = {"this": True, "is_string": True} 1118 1119 def __eq__(self, other): 1120 return ( 1121 isinstance(other, Literal) 1122 and self.this == other.this 1123 and self.args["is_string"] == other.args["is_string"] 1124 ) 1125 1126 def __hash__(self): 1127 return hash((self.key, self.this, self.args["is_string"])) 1128 1129 @classmethod 1130 def number(cls, number) -> Literal: 1131 return cls(this=str(number), is_string=False) 1132 1133 @classmethod 1134 def string(cls, string) -> Literal: 1135 return cls(this=str(string), is_string=True) 1136 1137 @property 1138 def output_name(self): 1139 return self.name 1140 1141 1142class Join(Expression): 1143 arg_types = { 1144 "this": True, 1145 "on": False, 1146 "side": False, 1147 "kind": False, 1148 "using": False, 1149 "natural": False, 1150 } 1151 1152 @property 1153 def kind(self): 1154 return self.text("kind").upper() 1155 1156 @property 1157 def side(self): 1158 return self.text("side").upper() 1159 1160 @property 1161 def alias_or_name(self): 1162 return self.this.alias_or_name 1163 1164 def on(self, *expressions, append=True, dialect=None, copy=True, **opts): 1165 """ 1166 Append to or set the ON expressions. 1167 1168 Example: 1169 >>> import sqlglot 1170 >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql() 1171 'JOIN x ON y = 1' 1172 1173 Args: 1174 *expressions (str | Expression): the SQL code strings to parse. 1175 If an `Expression` instance is passed, it will be used as-is. 1176 Multiple expressions are combined with an AND operator. 1177 append (bool): if `True`, AND the new expressions to any existing expression. 1178 Otherwise, this resets the expression. 1179 dialect (str): the dialect used to parse the input expressions. 1180 copy (bool): if `False`, modify this expression instance in-place. 1181 opts (kwargs): other options to use to parse the input expressions. 1182 1183 Returns: 1184 Join: the modified join expression. 1185 """ 1186 join = _apply_conjunction_builder( 1187 *expressions, 1188 instance=self, 1189 arg="on", 1190 append=append, 1191 dialect=dialect, 1192 copy=copy, 1193 **opts, 1194 ) 1195 1196 if join.kind == "CROSS": 1197 join.set("kind", None) 1198 1199 return join 1200 1201 def using(self, *expressions, append=True, dialect=None, copy=True, **opts): 1202 """ 1203 Append to or set the USING expressions. 1204 1205 Example: 1206 >>> import sqlglot 1207 >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql() 1208 'JOIN x USING (foo, bla)' 1209 1210 Args: 1211 *expressions (str | Expression): the SQL code strings to parse. 1212 If an `Expression` instance is passed, it will be used as-is. 1213 append (bool): if `True`, concatenate the new expressions to the existing "using" list. 1214 Otherwise, this resets the expression. 1215 dialect (str): the dialect used to parse the input expressions. 1216 copy (bool): if `False`, modify this expression instance in-place. 1217 opts (kwargs): other options to use to parse the input expressions. 1218 1219 Returns: 1220 Join: the modified join expression. 1221 """ 1222 join = _apply_list_builder( 1223 *expressions, 1224 instance=self, 1225 arg="using", 1226 append=append, 1227 dialect=dialect, 1228 copy=copy, 1229 **opts, 1230 ) 1231 1232 if join.kind == "CROSS": 1233 join.set("kind", None) 1234 1235 return join 1236 1237 1238class Lateral(UDTF): 1239 arg_types = {"this": True, "view": False, "outer": False, "alias": False} 1240 1241 1242class MatchRecognize(Expression): 1243 arg_types = { 1244 "partition_by": False, 1245 "order": False, 1246 "measures": False, 1247 "rows": False, 1248 "after": False, 1249 "pattern": False, 1250 "define": False, 1251 } 1252 1253 1254# Clickhouse FROM FINAL modifier 1255# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier 1256class Final(Expression): 1257 pass 1258 1259 1260class Offset(Expression): 1261 arg_types = {"this": False, "expression": True} 1262 1263 1264class Order(Expression): 1265 arg_types = {"this": False, "expressions": True} 1266 1267 1268# hive specific sorts 1269# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy 1270class Cluster(Order): 1271 pass 1272 1273 1274class Distribute(Order): 1275 pass 1276 1277 1278class Sort(Order): 1279 pass 1280 1281 1282class Ordered(Expression): 1283 arg_types = {"this": True, "desc": True, "nulls_first": True} 1284 1285 1286class Property(Expression): 1287 arg_types = {"this": True, "value": True} 1288 1289 1290class AlgorithmProperty(Property): 1291 arg_types = {"this": True} 1292 1293 1294class DefinerProperty(Property): 1295 arg_types = {"this": True} 1296 1297 1298class SqlSecurityProperty(Property): 1299 arg_types = {"definer": True} 1300 1301 1302class TableFormatProperty(Property): 1303 arg_types = {"this": True} 1304 1305 1306class PartitionedByProperty(Property): 1307 arg_types = {"this": True} 1308 1309 1310class FileFormatProperty(Property): 1311 arg_types = {"this": True} 1312 1313 1314class DistKeyProperty(Property): 1315 arg_types = {"this": True} 1316 1317 1318class SortKeyProperty(Property): 1319 arg_types = {"this": True, "compound": False} 1320 1321 1322class DistStyleProperty(Property): 1323 arg_types = {"this": True} 1324 1325 1326class LikeProperty(Property): 1327 arg_types = {"this": True, "expressions": False} 1328 1329 1330class LocationProperty(Property): 1331 arg_types = {"this": True} 1332 1333 1334class EngineProperty(Property): 1335 arg_types = {"this": True} 1336 1337 1338class AutoIncrementProperty(Property): 1339 arg_types = {"this": True} 1340 1341 1342class CharacterSetProperty(Property): 1343 arg_types = {"this": True, "default": True} 1344 1345 1346class CollateProperty(Property): 1347 arg_types = {"this": True} 1348 1349 1350class SchemaCommentProperty(Property): 1351 arg_types = {"this": True} 1352 1353 1354class ReturnsProperty(Property): 1355 arg_types = {"this": True, "is_table": False, "table": False} 1356 1357 1358class LanguageProperty(Property): 1359 arg_types = {"this": True} 1360 1361 1362class ExecuteAsProperty(Property): 1363 arg_types = {"this": True} 1364 1365 1366class VolatilityProperty(Property): 1367 arg_types = {"this": True} 1368 1369 1370class RowFormatDelimitedProperty(Property): 1371 # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml 1372 arg_types = { 1373 "fields": False, 1374 "escaped": False, 1375 "collection_items": False, 1376 "map_keys": False, 1377 "lines": False, 1378 "null": False, 1379 "serde": False, 1380 } 1381 1382 1383class RowFormatSerdeProperty(Property): 1384 arg_types = {"this": True} 1385 1386 1387class SerdeProperties(Property): 1388 arg_types = {"expressions": True} 1389 1390 1391class FallbackProperty(Property): 1392 arg_types = {"no": True, "protection": False} 1393 1394 1395class WithJournalTableProperty(Property): 1396 arg_types = {"this": True} 1397 1398 1399class LogProperty(Property): 1400 arg_types = {"no": True} 1401 1402 1403class JournalProperty(Property): 1404 arg_types = {"no": True, "dual": False, "before": False} 1405 1406 1407class AfterJournalProperty(Property): 1408 arg_types = {"no": True, "dual": False, "local": False} 1409 1410 1411class ChecksumProperty(Property): 1412 arg_types = {"on": False, "default": False} 1413 1414 1415class FreespaceProperty(Property): 1416 arg_types = {"this": True, "percent": False} 1417 1418 1419class MergeBlockRatioProperty(Property): 1420 arg_types = {"this": False, "no": False, "default": False, "percent": False} 1421 1422 1423class DataBlocksizeProperty(Property): 1424 arg_types = {"size": False, "units": False, "min": False, "default": False} 1425 1426 1427class BlockCompressionProperty(Property): 1428 arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True} 1429 1430 1431class IsolatedLoadingProperty(Property): 1432 arg_types = { 1433 "no": True, 1434 "concurrent": True, 1435 "for_all": True, 1436 "for_insert": True, 1437 "for_none": True, 1438 } 1439 1440 1441class Properties(Expression): 1442 arg_types = {"expressions": True} 1443 1444 NAME_TO_PROPERTY = { 1445 "ALGORITHM": AlgorithmProperty, 1446 "AUTO_INCREMENT": AutoIncrementProperty, 1447 "CHARACTER SET": CharacterSetProperty, 1448 "COLLATE": CollateProperty, 1449 "COMMENT": SchemaCommentProperty, 1450 "DEFINER": DefinerProperty, 1451 "DISTKEY": DistKeyProperty, 1452 "DISTSTYLE": DistStyleProperty, 1453 "ENGINE": EngineProperty, 1454 "EXECUTE AS": ExecuteAsProperty, 1455 "FORMAT": FileFormatProperty, 1456 "LANGUAGE": LanguageProperty, 1457 "LOCATION": LocationProperty, 1458 "PARTITIONED_BY": PartitionedByProperty, 1459 "RETURNS": ReturnsProperty, 1460 "SORTKEY": SortKeyProperty, 1461 "TABLE_FORMAT": TableFormatProperty, 1462 } 1463 1464 PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()} 1465 1466 class Location(AutoName): 1467 POST_CREATE = auto() 1468 PRE_SCHEMA = auto() 1469 POST_INDEX = auto() 1470 POST_SCHEMA_ROOT = auto() 1471 POST_SCHEMA_WITH = auto() 1472 UNSUPPORTED = auto() 1473 1474 @classmethod 1475 def from_dict(cls, properties_dict) -> Properties: 1476 expressions = [] 1477 for key, value in properties_dict.items(): 1478 property_cls = cls.NAME_TO_PROPERTY.get(key.upper()) 1479 if property_cls: 1480 expressions.append(property_cls(this=convert(value))) 1481 else: 1482 expressions.append(Property(this=Literal.string(key), value=convert(value))) 1483 1484 return cls(expressions=expressions) 1485 1486 1487class Qualify(Expression): 1488 pass 1489 1490 1491# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql 1492class Return(Expression): 1493 pass 1494 1495 1496class Reference(Expression): 1497 arg_types = {"this": True, "expressions": False, "options": False} 1498 1499 1500class Tuple(Expression): 1501 arg_types = {"expressions": False} 1502 1503 1504class Subqueryable(Unionable): 1505 def subquery(self, alias=None, copy=True) -> Subquery: 1506 """ 1507 Convert this expression to an aliased expression that can be used as a Subquery. 1508 1509 Example: 1510 >>> subquery = Select().select("x").from_("tbl").subquery() 1511 >>> Select().select("x").from_(subquery).sql() 1512 'SELECT x FROM (SELECT x FROM tbl)' 1513 1514 Args: 1515 alias (str | Identifier): an optional alias for the subquery 1516 copy (bool): if `False`, modify this expression instance in-place. 1517 1518 Returns: 1519 Alias: the subquery 1520 """ 1521 instance = _maybe_copy(self, copy) 1522 return Subquery( 1523 this=instance, 1524 alias=TableAlias(this=to_identifier(alias)), 1525 ) 1526 1527 def limit(self, expression, dialect=None, copy=True, **opts) -> Select: 1528 raise NotImplementedError 1529 1530 @property 1531 def ctes(self): 1532 with_ = self.args.get("with") 1533 if not with_: 1534 return [] 1535 return with_.expressions 1536 1537 @property 1538 def selects(self): 1539 raise NotImplementedError("Subqueryable objects must implement `selects`") 1540 1541 @property 1542 def named_selects(self): 1543 raise NotImplementedError("Subqueryable objects must implement `named_selects`") 1544 1545 def with_( 1546 self, 1547 alias, 1548 as_, 1549 recursive=None, 1550 append=True, 1551 dialect=None, 1552 copy=True, 1553 **opts, 1554 ): 1555 """ 1556 Append to or set the common table expressions. 1557 1558 Example: 1559 >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql() 1560 'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2' 1561 1562 Args: 1563 alias (str | Expression): the SQL code string to parse as the table name. 1564 If an `Expression` instance is passed, this is used as-is. 1565 as_ (str | Expression): the SQL code string to parse as the table expression. 1566 If an `Expression` instance is passed, it will be used as-is. 1567 recursive (bool): set the RECURSIVE part of the expression. Defaults to `False`. 1568 append (bool): if `True`, add to any existing expressions. 1569 Otherwise, this resets the expressions. 1570 dialect (str): the dialect used to parse the input expression. 1571 copy (bool): if `False`, modify this expression instance in-place. 1572 opts (kwargs): other options to use to parse the input expressions. 1573 1574 Returns: 1575 Select: the modified expression. 1576 """ 1577 alias_expression = maybe_parse( 1578 alias, 1579 dialect=dialect, 1580 into=TableAlias, 1581 **opts, 1582 ) 1583 as_expression = maybe_parse( 1584 as_, 1585 dialect=dialect, 1586 **opts, 1587 ) 1588 cte = CTE( 1589 this=as_expression, 1590 alias=alias_expression, 1591 ) 1592 return _apply_child_list_builder( 1593 cte, 1594 instance=self, 1595 arg="with", 1596 append=append, 1597 copy=copy, 1598 into=With, 1599 properties={"recursive": recursive or False}, 1600 ) 1601 1602 1603QUERY_MODIFIERS = { 1604 "match": False, 1605 "laterals": False, 1606 "joins": False, 1607 "pivots": False, 1608 "where": False, 1609 "group": False, 1610 "having": False, 1611 "qualify": False, 1612 "windows": False, 1613 "distribute": False, 1614 "sort": False, 1615 "cluster": False, 1616 "order": False, 1617 "limit": False, 1618 "offset": False, 1619 "lock": False, 1620} 1621 1622 1623class Table(Expression): 1624 arg_types = { 1625 "this": True, 1626 "alias": False, 1627 "db": False, 1628 "catalog": False, 1629 "laterals": False, 1630 "joins": False, 1631 "pivots": False, 1632 "hints": False, 1633 "system_time": False, 1634 } 1635 1636 1637# See the TSQL "Querying data in a system-versioned temporal table" page 1638class SystemTime(Expression): 1639 arg_types = { 1640 "this": False, 1641 "expression": False, 1642 "kind": True, 1643 } 1644 1645 1646class Union(Subqueryable): 1647 arg_types = { 1648 "with": False, 1649 "this": True, 1650 "expression": True, 1651 "distinct": False, 1652 **QUERY_MODIFIERS, 1653 } 1654 1655 def limit(self, expression, dialect=None, copy=True, **opts) -> Select: 1656 """ 1657 Set the LIMIT expression. 1658 1659 Example: 1660 >>> select("1").union(select("1")).limit(1).sql() 1661 'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1' 1662 1663 Args: 1664 expression (str | int | Expression): the SQL code string to parse. 1665 This can also be an integer. 1666 If a `Limit` instance is passed, this is used as-is. 1667 If another `Expression` instance is passed, it will be wrapped in a `Limit`. 1668 dialect (str): the dialect used to parse the input expression. 1669 copy (bool): if `False`, modify this expression instance in-place. 1670 opts (kwargs): other options to use to parse the input expressions. 1671 1672 Returns: 1673 Select: The limited subqueryable. 1674 """ 1675 return ( 1676 select("*") 1677 .from_(self.subquery(alias="_l_0", copy=copy)) 1678 .limit(expression, dialect=dialect, copy=False, **opts) 1679 ) 1680 1681 @property 1682 def named_selects(self): 1683 return self.this.unnest().named_selects 1684 1685 @property 1686 def selects(self): 1687 return self.this.unnest().selects 1688 1689 @property 1690 def left(self): 1691 return self.this 1692 1693 @property 1694 def right(self): 1695 return self.expression 1696 1697 1698class Except(Union): 1699 pass 1700 1701 1702class Intersect(Union): 1703 pass 1704 1705 1706class Unnest(UDTF): 1707 arg_types = { 1708 "expressions": True, 1709 "ordinality": False, 1710 "alias": False, 1711 "offset": False, 1712 } 1713 1714 1715class Update(Expression): 1716 arg_types = { 1717 "with": False, 1718 "this": False, 1719 "expressions": True, 1720 "from": False, 1721 "where": False, 1722 } 1723 1724 1725class Values(UDTF): 1726 arg_types = { 1727 "expressions": True, 1728 "ordinality": False, 1729 "alias": False, 1730 } 1731 1732 1733class Var(Expression): 1734 pass 1735 1736 1737class Schema(Expression): 1738 arg_types = {"this": False, "expressions": False} 1739 1740 1741# Used to represent the FOR UPDATE and FOR SHARE locking read types. 1742# https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html 1743class Lock(Expression): 1744 arg_types = {"update": True} 1745 1746 1747class Select(Subqueryable): 1748 arg_types = { 1749 "with": False, 1750 "expressions": False, 1751 "hint": False, 1752 "distinct": False, 1753 "into": False, 1754 "from": False, 1755 **QUERY_MODIFIERS, 1756 } 1757 1758 def from_(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1759 """ 1760 Set the FROM expression. 1761 1762 Example: 1763 >>> Select().from_("tbl").select("x").sql() 1764 'SELECT x FROM tbl' 1765 1766 Args: 1767 *expressions (str | Expression): the SQL code strings to parse. 1768 If a `From` instance is passed, this is used as-is. 1769 If another `Expression` instance is passed, it will be wrapped in a `From`. 1770 append (bool): if `True`, add to any existing expressions. 1771 Otherwise, this flattens all the `From` expression into a single expression. 1772 dialect (str): the dialect used to parse the input expression. 1773 copy (bool): if `False`, modify this expression instance in-place. 1774 opts (kwargs): other options to use to parse the input expressions. 1775 1776 Returns: 1777 Select: the modified expression. 1778 """ 1779 return _apply_child_list_builder( 1780 *expressions, 1781 instance=self, 1782 arg="from", 1783 append=append, 1784 copy=copy, 1785 prefix="FROM", 1786 into=From, 1787 dialect=dialect, 1788 **opts, 1789 ) 1790 1791 def group_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1792 """ 1793 Set the GROUP BY expression. 1794 1795 Example: 1796 >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql() 1797 'SELECT x, COUNT(1) FROM tbl GROUP BY x' 1798 1799 Args: 1800 *expressions (str | Expression): the SQL code strings to parse. 1801 If a `Group` instance is passed, this is used as-is. 1802 If another `Expression` instance is passed, it will be wrapped in a `Group`. 1803 If nothing is passed in then a group by is not applied to the expression 1804 append (bool): if `True`, add to any existing expressions. 1805 Otherwise, this flattens all the `Group` expression into a single expression. 1806 dialect (str): the dialect used to parse the input expression. 1807 copy (bool): if `False`, modify this expression instance in-place. 1808 opts (kwargs): other options to use to parse the input expressions. 1809 1810 Returns: 1811 Select: the modified expression. 1812 """ 1813 if not expressions: 1814 return self if not copy else self.copy() 1815 return _apply_child_list_builder( 1816 *expressions, 1817 instance=self, 1818 arg="group", 1819 append=append, 1820 copy=copy, 1821 prefix="GROUP BY", 1822 into=Group, 1823 dialect=dialect, 1824 **opts, 1825 ) 1826 1827 def order_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1828 """ 1829 Set the ORDER BY expression. 1830 1831 Example: 1832 >>> Select().from_("tbl").select("x").order_by("x DESC").sql() 1833 'SELECT x FROM tbl ORDER BY x DESC' 1834 1835 Args: 1836 *expressions (str | Expression): the SQL code strings to parse. 1837 If a `Group` instance is passed, this is used as-is. 1838 If another `Expression` instance is passed, it will be wrapped in a `Order`. 1839 append (bool): if `True`, add to any existing expressions. 1840 Otherwise, this flattens all the `Order` expression into a single expression. 1841 dialect (str): the dialect used to parse the input expression. 1842 copy (bool): if `False`, modify this expression instance in-place. 1843 opts (kwargs): other options to use to parse the input expressions. 1844 1845 Returns: 1846 Select: the modified expression. 1847 """ 1848 return _apply_child_list_builder( 1849 *expressions, 1850 instance=self, 1851 arg="order", 1852 append=append, 1853 copy=copy, 1854 prefix="ORDER BY", 1855 into=Order, 1856 dialect=dialect, 1857 **opts, 1858 ) 1859 1860 def sort_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1861 """ 1862 Set the SORT BY expression. 1863 1864 Example: 1865 >>> Select().from_("tbl").select("x").sort_by("x DESC").sql() 1866 'SELECT x FROM tbl SORT BY x DESC' 1867 1868 Args: 1869 *expressions (str | Expression): the SQL code strings to parse. 1870 If a `Group` instance is passed, this is used as-is. 1871 If another `Expression` instance is passed, it will be wrapped in a `SORT`. 1872 append (bool): if `True`, add to any existing expressions. 1873 Otherwise, this flattens all the `Order` expression into a single expression. 1874 dialect (str): the dialect used to parse the input expression. 1875 copy (bool): if `False`, modify this expression instance in-place. 1876 opts (kwargs): other options to use to parse the input expressions. 1877 1878 Returns: 1879 Select: the modified expression. 1880 """ 1881 return _apply_child_list_builder( 1882 *expressions, 1883 instance=self, 1884 arg="sort", 1885 append=append, 1886 copy=copy, 1887 prefix="SORT BY", 1888 into=Sort, 1889 dialect=dialect, 1890 **opts, 1891 ) 1892 1893 def cluster_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1894 """ 1895 Set the CLUSTER BY expression. 1896 1897 Example: 1898 >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql() 1899 'SELECT x FROM tbl CLUSTER BY x DESC' 1900 1901 Args: 1902 *expressions (str | Expression): the SQL code strings to parse. 1903 If a `Group` instance is passed, this is used as-is. 1904 If another `Expression` instance is passed, it will be wrapped in a `Cluster`. 1905 append (bool): if `True`, add to any existing expressions. 1906 Otherwise, this flattens all the `Order` expression into a single expression. 1907 dialect (str): the dialect used to parse the input expression. 1908 copy (bool): if `False`, modify this expression instance in-place. 1909 opts (kwargs): other options to use to parse the input expressions. 1910 1911 Returns: 1912 Select: the modified expression. 1913 """ 1914 return _apply_child_list_builder( 1915 *expressions, 1916 instance=self, 1917 arg="cluster", 1918 append=append, 1919 copy=copy, 1920 prefix="CLUSTER BY", 1921 into=Cluster, 1922 dialect=dialect, 1923 **opts, 1924 ) 1925 1926 def limit(self, expression, dialect=None, copy=True, **opts) -> Select: 1927 """ 1928 Set the LIMIT expression. 1929 1930 Example: 1931 >>> Select().from_("tbl").select("x").limit(10).sql() 1932 'SELECT x FROM tbl LIMIT 10' 1933 1934 Args: 1935 expression (str | int | Expression): the SQL code string to parse. 1936 This can also be an integer. 1937 If a `Limit` instance is passed, this is used as-is. 1938 If another `Expression` instance is passed, it will be wrapped in a `Limit`. 1939 dialect (str): the dialect used to parse the input expression. 1940 copy (bool): if `False`, modify this expression instance in-place. 1941 opts (kwargs): other options to use to parse the input expressions. 1942 1943 Returns: 1944 Select: the modified expression. 1945 """ 1946 return _apply_builder( 1947 expression=expression, 1948 instance=self, 1949 arg="limit", 1950 into=Limit, 1951 prefix="LIMIT", 1952 dialect=dialect, 1953 copy=copy, 1954 **opts, 1955 ) 1956 1957 def offset(self, expression, dialect=None, copy=True, **opts) -> Select: 1958 """ 1959 Set the OFFSET expression. 1960 1961 Example: 1962 >>> Select().from_("tbl").select("x").offset(10).sql() 1963 'SELECT x FROM tbl OFFSET 10' 1964 1965 Args: 1966 expression (str | int | Expression): the SQL code string to parse. 1967 This can also be an integer. 1968 If a `Offset` instance is passed, this is used as-is. 1969 If another `Expression` instance is passed, it will be wrapped in a `Offset`. 1970 dialect (str): the dialect used to parse the input expression. 1971 copy (bool): if `False`, modify this expression instance in-place. 1972 opts (kwargs): other options to use to parse the input expressions. 1973 1974 Returns: 1975 Select: the modified expression. 1976 """ 1977 return _apply_builder( 1978 expression=expression, 1979 instance=self, 1980 arg="offset", 1981 into=Offset, 1982 prefix="OFFSET", 1983 dialect=dialect, 1984 copy=copy, 1985 **opts, 1986 ) 1987 1988 def select(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1989 """ 1990 Append to or set the SELECT expressions. 1991 1992 Example: 1993 >>> Select().select("x", "y").sql() 1994 'SELECT x, y' 1995 1996 Args: 1997 *expressions (str | Expression): the SQL code strings to parse. 1998 If an `Expression` instance is passed, it will be used as-is. 1999 append (bool): if `True`, add to any existing expressions. 2000 Otherwise, this resets the expressions. 2001 dialect (str): the dialect used to parse the input expressions. 2002 copy (bool): if `False`, modify this expression instance in-place. 2003 opts (kwargs): other options to use to parse the input expressions. 2004 2005 Returns: 2006 Select: the modified expression. 2007 """ 2008 return _apply_list_builder( 2009 *expressions, 2010 instance=self, 2011 arg="expressions", 2012 append=append, 2013 dialect=dialect, 2014 copy=copy, 2015 **opts, 2016 ) 2017 2018 def lateral(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 2019 """ 2020 Append to or set the LATERAL expressions. 2021 2022 Example: 2023 >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql() 2024 'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z' 2025 2026 Args: 2027 *expressions (str | Expression): the SQL code strings to parse. 2028 If an `Expression` instance is passed, it will be used as-is. 2029 append (bool): if `True`, add to any existing expressions. 2030 Otherwise, this resets the expressions. 2031 dialect (str): the dialect used to parse the input expressions. 2032 copy (bool): if `False`, modify this expression instance in-place. 2033 opts (kwargs): other options to use to parse the input expressions. 2034 2035 Returns: 2036 Select: the modified expression. 2037 """ 2038 return _apply_list_builder( 2039 *expressions, 2040 instance=self, 2041 arg="laterals", 2042 append=append, 2043 into=Lateral, 2044 prefix="LATERAL VIEW", 2045 dialect=dialect, 2046 copy=copy, 2047 **opts, 2048 ) 2049 2050 def join( 2051 self, 2052 expression, 2053 on=None, 2054 using=None, 2055 append=True, 2056 join_type=None, 2057 join_alias=None, 2058 dialect=None, 2059 copy=True, 2060 **opts, 2061 ) -> Select: 2062 """ 2063 Append to or set the JOIN expressions. 2064 2065 Example: 2066 >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql() 2067 'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y' 2068 2069 >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql() 2070 'SELECT 1 FROM a JOIN b USING (x, y, z)' 2071 2072 Use `join_type` to change the type of join: 2073 2074 >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql() 2075 'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y' 2076 2077 Args: 2078 expression (str | Expression): the SQL code string to parse. 2079 If an `Expression` instance is passed, it will be used as-is. 2080 on (str | Expression): optionally specify the join "on" criteria as a SQL string. 2081 If an `Expression` instance is passed, it will be used as-is. 2082 using (str | Expression): optionally specify the join "using" criteria as a SQL string. 2083 If an `Expression` instance is passed, it will be used as-is. 2084 append (bool): if `True`, add to any existing expressions. 2085 Otherwise, this resets the expressions. 2086 join_type (str): If set, alter the parsed join type 2087 dialect (str): the dialect used to parse the input expressions. 2088 copy (bool): if `False`, modify this expression instance in-place. 2089 opts (kwargs): other options to use to parse the input expressions. 2090 2091 Returns: 2092 Select: the modified expression. 2093 """ 2094 parse_args = {"dialect": dialect, **opts} 2095 2096 try: 2097 expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args) 2098 except ParseError: 2099 expression = maybe_parse(expression, into=(Join, Expression), **parse_args) 2100 2101 join = expression if isinstance(expression, Join) else Join(this=expression) 2102 2103 if isinstance(join.this, Select): 2104 join.this.replace(join.this.subquery()) 2105 2106 if join_type: 2107 natural: t.Optional[Token] 2108 side: t.Optional[Token] 2109 kind: t.Optional[Token] 2110 2111 natural, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args) # type: ignore 2112 2113 if natural: 2114 join.set("natural", True) 2115 if side: 2116 join.set("side", side.text) 2117 if kind: 2118 join.set("kind", kind.text) 2119 2120 if on: 2121 on = and_(*ensure_collection(on), dialect=dialect, **opts) 2122 join.set("on", on) 2123 2124 if using: 2125 join = _apply_list_builder( 2126 *ensure_collection(using), 2127 instance=join, 2128 arg="using", 2129 append=append, 2130 copy=copy, 2131 **opts, 2132 ) 2133 2134 if join_alias: 2135 join.set("this", alias_(join.this, join_alias, table=True)) 2136 return _apply_list_builder( 2137 join, 2138 instance=self, 2139 arg="joins", 2140 append=append, 2141 copy=copy, 2142 **opts, 2143 ) 2144 2145 def where(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 2146 """ 2147 Append to or set the WHERE expressions. 2148 2149 Example: 2150 >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql() 2151 "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'" 2152 2153 Args: 2154 *expressions (str | Expression): the SQL code strings to parse. 2155 If an `Expression` instance is passed, it will be used as-is. 2156 Multiple expressions are combined with an AND operator. 2157 append (bool): if `True`, AND the new expressions to any existing expression. 2158 Otherwise, this resets the expression. 2159 dialect (str): the dialect used to parse the input expressions. 2160 copy (bool): if `False`, modify this expression instance in-place. 2161 opts (kwargs): other options to use to parse the input expressions. 2162 2163 Returns: 2164 Select: the modified expression. 2165 """ 2166 return _apply_conjunction_builder( 2167 *expressions, 2168 instance=self, 2169 arg="where", 2170 append=append, 2171 into=Where, 2172 dialect=dialect, 2173 copy=copy, 2174 **opts, 2175 ) 2176 2177 def having(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 2178 """ 2179 Append to or set the HAVING expressions. 2180 2181 Example: 2182 >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql() 2183 'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3' 2184 2185 Args: 2186 *expressions (str | Expression): the SQL code strings to parse. 2187 If an `Expression` instance is passed, it will be used as-is. 2188 Multiple expressions are combined with an AND operator. 2189 append (bool): if `True`, AND the new expressions to any existing expression. 2190 Otherwise, this resets the expression. 2191 dialect (str): the dialect used to parse the input expressions. 2192 copy (bool): if `False`, modify this expression instance in-place. 2193 opts (kwargs): other options to use to parse the input expressions. 2194 2195 Returns: 2196 Select: the modified expression. 2197 """ 2198 return _apply_conjunction_builder( 2199 *expressions, 2200 instance=self, 2201 arg="having", 2202 append=append, 2203 into=Having, 2204 dialect=dialect, 2205 copy=copy, 2206 **opts, 2207 ) 2208 2209 def window(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 2210 return _apply_list_builder( 2211 *expressions, 2212 instance=self, 2213 arg="windows", 2214 append=append, 2215 into=Window, 2216 dialect=dialect, 2217 copy=copy, 2218 **opts, 2219 ) 2220 2221 def distinct(self, distinct=True, copy=True) -> Select: 2222 """ 2223 Set the OFFSET expression. 2224 2225 Example: 2226 >>> Select().from_("tbl").select("x").distinct().sql() 2227 'SELECT DISTINCT x FROM tbl' 2228 2229 Args: 2230 distinct (bool): whether the Select should be distinct 2231 copy (bool): if `False`, modify this expression instance in-place. 2232 2233 Returns: 2234 Select: the modified expression. 2235 """ 2236 instance = _maybe_copy(self, copy) 2237 instance.set("distinct", Distinct() if distinct else None) 2238 return instance 2239 2240 def ctas(self, table, properties=None, dialect=None, copy=True, **opts) -> Create: 2241 """ 2242 Convert this expression to a CREATE TABLE AS statement. 2243 2244 Example: 2245 >>> Select().select("*").from_("tbl").ctas("x").sql() 2246 'CREATE TABLE x AS SELECT * FROM tbl' 2247 2248 Args: 2249 table (str | Expression): the SQL code string to parse as the table name. 2250 If another `Expression` instance is passed, it will be used as-is. 2251 properties (dict): an optional mapping of table properties 2252 dialect (str): the dialect used to parse the input table. 2253 copy (bool): if `False`, modify this expression instance in-place. 2254 opts (kwargs): other options to use to parse the input table. 2255 2256 Returns: 2257 Create: the CREATE TABLE AS expression 2258 """ 2259 instance = _maybe_copy(self, copy) 2260 table_expression = maybe_parse( 2261 table, 2262 into=Table, 2263 dialect=dialect, 2264 **opts, 2265 ) 2266 properties_expression = None 2267 if properties: 2268 properties_expression = Properties.from_dict(properties) 2269 2270 return Create( 2271 this=table_expression, 2272 kind="table", 2273 expression=instance, 2274 properties=properties_expression, 2275 ) 2276 2277 def lock(self, update: bool = True, copy: bool = True) -> Select: 2278 """ 2279 Set the locking read mode for this expression. 2280 2281 Examples: 2282 >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql") 2283 "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE" 2284 2285 >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql") 2286 "SELECT x FROM tbl WHERE x = 'a' FOR SHARE" 2287 2288 Args: 2289 update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`. 2290 copy: if `False`, modify this expression instance in-place. 2291 2292 Returns: 2293 The modified expression. 2294 """ 2295 2296 inst = _maybe_copy(self, copy) 2297 inst.set("lock", Lock(update=update)) 2298 2299 return inst 2300 2301 @property 2302 def named_selects(self) -> t.List[str]: 2303 return [e.output_name for e in self.expressions if e.alias_or_name] 2304 2305 @property 2306 def selects(self) -> t.List[Expression]: 2307 return self.expressions 2308 2309 2310class Subquery(DerivedTable, Unionable): 2311 arg_types = { 2312 "this": True, 2313 "alias": False, 2314 "with": False, 2315 **QUERY_MODIFIERS, 2316 } 2317 2318 def unnest(self): 2319 """ 2320 Returns the first non subquery. 2321 """ 2322 expression = self 2323 while isinstance(expression, Subquery): 2324 expression = expression.this 2325 return expression 2326 2327 @property 2328 def output_name(self): 2329 return self.alias 2330 2331 2332class TableSample(Expression): 2333 arg_types = { 2334 "this": False, 2335 "method": False, 2336 "bucket_numerator": False, 2337 "bucket_denominator": False, 2338 "bucket_field": False, 2339 "percent": False, 2340 "rows": False, 2341 "size": False, 2342 "seed": False, 2343 } 2344 2345 2346class Tag(Expression): 2347 """Tags are used for generating arbitrary sql like SELECT <span>x</span>.""" 2348 2349 arg_types = { 2350 "this": False, 2351 "prefix": False, 2352 "postfix": False, 2353 } 2354 2355 2356class Pivot(Expression): 2357 arg_types = { 2358 "this": False, 2359 "expressions": True, 2360 "field": True, 2361 "unpivot": True, 2362 } 2363 2364 2365class Window(Expression): 2366 arg_types = { 2367 "this": True, 2368 "partition_by": False, 2369 "order": False, 2370 "spec": False, 2371 "alias": False, 2372 } 2373 2374 2375class WindowSpec(Expression): 2376 arg_types = { 2377 "kind": False, 2378 "start": False, 2379 "start_side": False, 2380 "end": False, 2381 "end_side": False, 2382 } 2383 2384 2385class Where(Expression): 2386 pass 2387 2388 2389class Star(Expression): 2390 arg_types = {"except": False, "replace": False} 2391 2392 @property 2393 def name(self) -> str: 2394 return "*" 2395 2396 @property 2397 def output_name(self): 2398 return self.name 2399 2400 2401class Parameter(Expression): 2402 pass 2403 2404 2405class SessionParameter(Expression): 2406 arg_types = {"this": True, "kind": False} 2407 2408 2409class Placeholder(Expression): 2410 arg_types = {"this": False} 2411 2412 2413class Null(Condition): 2414 arg_types: t.Dict[str, t.Any] = {} 2415 2416 @property 2417 def name(self) -> str: 2418 return "NULL" 2419 2420 2421class Boolean(Condition): 2422 pass 2423 2424 2425class DataType(Expression): 2426 arg_types = { 2427 "this": True, 2428 "expressions": False, 2429 "nested": False, 2430 "values": False, 2431 } 2432 2433 class Type(AutoName): 2434 CHAR = auto() 2435 NCHAR = auto() 2436 VARCHAR = auto() 2437 NVARCHAR = auto() 2438 TEXT = auto() 2439 MEDIUMTEXT = auto() 2440 LONGTEXT = auto() 2441 MEDIUMBLOB = auto() 2442 LONGBLOB = auto() 2443 BINARY = auto() 2444 VARBINARY = auto() 2445 INT = auto() 2446 TINYINT = auto() 2447 SMALLINT = auto() 2448 BIGINT = auto() 2449 FLOAT = auto() 2450 DOUBLE = auto() 2451 DECIMAL = auto() 2452 BOOLEAN = auto() 2453 JSON = auto() 2454 JSONB = auto() 2455 INTERVAL = auto() 2456 TIME = auto() 2457 TIMESTAMP = auto() 2458 TIMESTAMPTZ = auto() 2459 TIMESTAMPLTZ = auto() 2460 DATE = auto() 2461 DATETIME = auto() 2462 ARRAY = auto() 2463 MAP = auto() 2464 UUID = auto() 2465 GEOGRAPHY = auto() 2466 GEOMETRY = auto() 2467 STRUCT = auto() 2468 NULLABLE = auto() 2469 HLLSKETCH = auto() 2470 HSTORE = auto() 2471 SUPER = auto() 2472 SERIAL = auto() 2473 SMALLSERIAL = auto() 2474 BIGSERIAL = auto() 2475 XML = auto() 2476 UNIQUEIDENTIFIER = auto() 2477 MONEY = auto() 2478 SMALLMONEY = auto() 2479 ROWVERSION = auto() 2480 IMAGE = auto() 2481 VARIANT = auto() 2482 OBJECT = auto() 2483 NULL = auto() 2484 UNKNOWN = auto() # Sentinel value, useful for type annotation 2485 2486 TEXT_TYPES = { 2487 Type.CHAR, 2488 Type.NCHAR, 2489 Type.VARCHAR, 2490 Type.NVARCHAR, 2491 Type.TEXT, 2492 } 2493 2494 INTEGER_TYPES = { 2495 Type.INT, 2496 Type.TINYINT, 2497 Type.SMALLINT, 2498 Type.BIGINT, 2499 } 2500 2501 FLOAT_TYPES = { 2502 Type.FLOAT, 2503 Type.DOUBLE, 2504 } 2505 2506 NUMERIC_TYPES = {*INTEGER_TYPES, *FLOAT_TYPES} 2507 2508 TEMPORAL_TYPES = { 2509 Type.TIMESTAMP, 2510 Type.TIMESTAMPTZ, 2511 Type.TIMESTAMPLTZ, 2512 Type.DATE, 2513 Type.DATETIME, 2514 } 2515 2516 @classmethod 2517 def build( 2518 cls, dtype: str | DataType | DataType.Type, dialect: DialectType = None, **kwargs 2519 ) -> DataType: 2520 from sqlglot import parse_one 2521 2522 if isinstance(dtype, str): 2523 if dtype.upper() in cls.Type.__members__: 2524 data_type_exp: t.Optional[Expression] = DataType(this=DataType.Type[dtype.upper()]) 2525 else: 2526 data_type_exp = parse_one(dtype, read=dialect, into=DataType) 2527 if data_type_exp is None: 2528 raise ValueError(f"Unparsable data type value: {dtype}") 2529 elif isinstance(dtype, DataType.Type): 2530 data_type_exp = DataType(this=dtype) 2531 elif isinstance(dtype, DataType): 2532 return dtype 2533 else: 2534 raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type") 2535 return DataType(**{**data_type_exp.args, **kwargs}) 2536 2537 def is_type(self, dtype: DataType.Type) -> bool: 2538 return self.this == dtype 2539 2540 2541# https://www.postgresql.org/docs/15/datatype-pseudo.html 2542class PseudoType(Expression): 2543 pass 2544 2545 2546class StructKwarg(Expression): 2547 arg_types = {"this": True, "expression": True} 2548 2549 2550# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...) 2551class SubqueryPredicate(Predicate): 2552 pass 2553 2554 2555class All(SubqueryPredicate): 2556 pass 2557 2558 2559class Any(SubqueryPredicate): 2560 pass 2561 2562 2563class Exists(SubqueryPredicate): 2564 pass 2565 2566 2567# Commands to interact with the databases or engines. For most of the command 2568# expressions we parse whatever comes after the command's name as a string. 2569class Command(Expression): 2570 arg_types = {"this": True, "expression": False} 2571 2572 2573class Transaction(Expression): 2574 arg_types = {"this": False, "modes": False} 2575 2576 2577class Commit(Expression): 2578 arg_types = {"chain": False} 2579 2580 2581class Rollback(Expression): 2582 arg_types = {"savepoint": False} 2583 2584 2585class AlterTable(Expression): 2586 arg_types = {"this": True, "actions": True, "exists": False} 2587 2588 2589class AddConstraint(Expression): 2590 arg_types = {"this": False, "expression": False, "enforced": False} 2591 2592 2593class DropPartition(Expression): 2594 arg_types = {"expressions": True, "exists": False} 2595 2596 2597# Binary expressions like (ADD a b) 2598class Binary(Expression): 2599 arg_types = {"this": True, "expression": True} 2600 2601 @property 2602 def left(self): 2603 return self.this 2604 2605 @property 2606 def right(self): 2607 return self.expression 2608 2609 2610class Add(Binary): 2611 pass 2612 2613 2614class Connector(Binary, Condition): 2615 pass 2616 2617 2618class And(Connector): 2619 pass 2620 2621 2622class Or(Connector): 2623 pass 2624 2625 2626class BitwiseAnd(Binary): 2627 pass 2628 2629 2630class BitwiseLeftShift(Binary): 2631 pass 2632 2633 2634class BitwiseOr(Binary): 2635 pass 2636 2637 2638class BitwiseRightShift(Binary): 2639 pass 2640 2641 2642class BitwiseXor(Binary): 2643 pass 2644 2645 2646class Div(Binary): 2647 pass 2648 2649 2650class Dot(Binary): 2651 @property 2652 def name(self) -> str: 2653 return self.expression.name 2654 2655 2656class DPipe(Binary): 2657 pass 2658 2659 2660class EQ(Binary, Predicate): 2661 pass 2662 2663 2664class NullSafeEQ(Binary, Predicate): 2665 pass 2666 2667 2668class NullSafeNEQ(Binary, Predicate): 2669 pass 2670 2671 2672class Distance(Binary): 2673 pass 2674 2675 2676class Escape(Binary): 2677 pass 2678 2679 2680class Glob(Binary, Predicate): 2681 pass 2682 2683 2684class GT(Binary, Predicate): 2685 pass 2686 2687 2688class GTE(Binary, Predicate): 2689 pass 2690 2691 2692class ILike(Binary, Predicate): 2693 pass 2694 2695 2696class IntDiv(Binary): 2697 pass 2698 2699 2700class Is(Binary, Predicate): 2701 pass 2702 2703 2704class Kwarg(Binary): 2705 """Kwarg in special functions like func(kwarg => y).""" 2706 2707 2708class Like(Binary, Predicate): 2709 pass 2710 2711 2712class LT(Binary, Predicate): 2713 pass 2714 2715 2716class LTE(Binary, Predicate): 2717 pass 2718 2719 2720class Mod(Binary): 2721 pass 2722 2723 2724class Mul(Binary): 2725 pass 2726 2727 2728class NEQ(Binary, Predicate): 2729 pass 2730 2731 2732class SimilarTo(Binary, Predicate): 2733 pass 2734 2735 2736class Slice(Binary): 2737 arg_types = {"this": False, "expression": False} 2738 2739 2740class Sub(Binary): 2741 pass 2742 2743 2744# Unary Expressions 2745# (NOT a) 2746class Unary(Expression): 2747 pass 2748 2749 2750class BitwiseNot(Unary): 2751 pass 2752 2753 2754class Not(Unary, Condition): 2755 pass 2756 2757 2758class Paren(Unary, Condition): 2759 arg_types = {"this": True, "with": False} 2760 2761 2762class Neg(Unary): 2763 pass 2764 2765 2766# Special Functions 2767class Alias(Expression): 2768 arg_types = {"this": True, "alias": False} 2769 2770 @property 2771 def output_name(self): 2772 return self.alias 2773 2774 2775class Aliases(Expression): 2776 arg_types = {"this": True, "expressions": True} 2777 2778 @property 2779 def aliases(self): 2780 return self.expressions 2781 2782 2783class AtTimeZone(Expression): 2784 arg_types = {"this": True, "zone": True} 2785 2786 2787class Between(Predicate): 2788 arg_types = {"this": True, "low": True, "high": True} 2789 2790 2791class Bracket(Condition): 2792 arg_types = {"this": True, "expressions": True} 2793 2794 2795class Distinct(Expression): 2796 arg_types = {"expressions": False, "on": False} 2797 2798 2799class In(Predicate): 2800 arg_types = { 2801 "this": True, 2802 "expressions": False, 2803 "query": False, 2804 "unnest": False, 2805 "field": False, 2806 "is_global": False, 2807 } 2808 2809 2810class TimeUnit(Expression): 2811 """Automatically converts unit arg into a var.""" 2812 2813 arg_types = {"unit": False} 2814 2815 def __init__(self, **args): 2816 unit = args.get("unit") 2817 if isinstance(unit, Column): 2818 args["unit"] = Var(this=unit.name) 2819 elif isinstance(unit, Week): 2820 unit.set("this", Var(this=unit.this.name)) 2821 super().__init__(**args) 2822 2823 2824class Interval(TimeUnit): 2825 arg_types = {"this": False, "unit": False} 2826 2827 2828class IgnoreNulls(Expression): 2829 pass 2830 2831 2832class RespectNulls(Expression): 2833 pass 2834 2835 2836# Functions 2837class Func(Condition): 2838 """ 2839 The base class for all function expressions. 2840 2841 Attributes: 2842 is_var_len_args (bool): if set to True the last argument defined in arg_types will be 2843 treated as a variable length argument and the argument's value will be stored as a list. 2844 _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items) 2845 for this function expression. These values are used to map this node to a name during parsing 2846 as well as to provide the function's name during SQL string generation. By default the SQL 2847 name is set to the expression's class name transformed to snake case. 2848 """ 2849 2850 is_var_len_args = False 2851 2852 @classmethod 2853 def from_arg_list(cls, args): 2854 if cls.is_var_len_args: 2855 all_arg_keys = list(cls.arg_types) 2856 # If this function supports variable length argument treat the last argument as such. 2857 non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys 2858 num_non_var = len(non_var_len_arg_keys) 2859 2860 args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)} 2861 args_dict[all_arg_keys[-1]] = args[num_non_var:] 2862 else: 2863 args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)} 2864 2865 return cls(**args_dict) 2866 2867 @classmethod 2868 def sql_names(cls): 2869 if cls is Func: 2870 raise NotImplementedError( 2871 "SQL name is only supported by concrete function implementations" 2872 ) 2873 if "_sql_names" not in cls.__dict__: 2874 cls._sql_names = [camel_to_snake_case(cls.__name__)] 2875 return cls._sql_names 2876 2877 @classmethod 2878 def sql_name(cls): 2879 return cls.sql_names()[0] 2880 2881 @classmethod 2882 def default_parser_mappings(cls): 2883 return {name: cls.from_arg_list for name in cls.sql_names()} 2884 2885 2886class AggFunc(Func): 2887 pass 2888 2889 2890class Abs(Func): 2891 pass 2892 2893 2894class Anonymous(Func): 2895 arg_types = {"this": True, "expressions": False} 2896 is_var_len_args = True 2897 2898 2899class ApproxDistinct(AggFunc): 2900 arg_types = {"this": True, "accuracy": False} 2901 2902 2903class Array(Func): 2904 arg_types = {"expressions": False} 2905 is_var_len_args = True 2906 2907 2908class GenerateSeries(Func): 2909 arg_types = {"start": True, "end": True, "step": False} 2910 2911 2912class ArrayAgg(AggFunc): 2913 pass 2914 2915 2916class ArrayAll(Func): 2917 arg_types = {"this": True, "expression": True} 2918 2919 2920class ArrayAny(Func): 2921 arg_types = {"this": True, "expression": True} 2922 2923 2924class ArrayConcat(Func): 2925 arg_types = {"this": True, "expressions": False} 2926 is_var_len_args = True 2927 2928 2929class ArrayContains(Func): 2930 arg_types = {"this": True, "expression": True} 2931 2932 2933class ArrayFilter(Func): 2934 arg_types = {"this": True, "expression": True} 2935 _sql_names = ["FILTER", "ARRAY_FILTER"] 2936 2937 2938class ArraySize(Func): 2939 arg_types = {"this": True, "expression": False} 2940 2941 2942class ArraySort(Func): 2943 arg_types = {"this": True, "expression": False} 2944 2945 2946class ArraySum(Func): 2947 pass 2948 2949 2950class ArrayUnionAgg(AggFunc): 2951 pass 2952 2953 2954class Avg(AggFunc): 2955 pass 2956 2957 2958class AnyValue(AggFunc): 2959 pass 2960 2961 2962class Case(Func): 2963 arg_types = {"this": False, "ifs": True, "default": False} 2964 2965 2966class Cast(Func): 2967 arg_types = {"this": True, "to": True} 2968 2969 @property 2970 def name(self) -> str: 2971 return self.this.name 2972 2973 @property 2974 def to(self): 2975 return self.args["to"] 2976 2977 @property 2978 def output_name(self): 2979 return self.name 2980 2981 def is_type(self, dtype: DataType.Type) -> bool: 2982 return self.to.is_type(dtype) 2983 2984 2985class Collate(Binary): 2986 pass 2987 2988 2989class TryCast(Cast): 2990 pass 2991 2992 2993class Ceil(Func): 2994 arg_types = {"this": True, "decimals": False} 2995 _sql_names = ["CEIL", "CEILING"] 2996 2997 2998class Coalesce(Func): 2999 arg_types = {"this": True, "expressions": False} 3000 is_var_len_args = True 3001 3002 3003class Concat(Func): 3004 arg_types = {"expressions": True} 3005 is_var_len_args = True 3006 3007 3008class ConcatWs(Concat): 3009 _sql_names = ["CONCAT_WS"] 3010 3011 3012class Count(AggFunc): 3013 arg_types = {"this": False} 3014 3015 3016class CurrentDate(Func): 3017 arg_types = {"this": False} 3018 3019 3020class CurrentDatetime(Func): 3021 arg_types = {"this": False} 3022 3023 3024class CurrentTime(Func): 3025 arg_types = {"this": False} 3026 3027 3028class CurrentTimestamp(Func): 3029 arg_types = {"this": False} 3030 3031 3032class DateAdd(Func, TimeUnit): 3033 arg_types = {"this": True, "expression": True, "unit": False} 3034 3035 3036class DateSub(Func, TimeUnit): 3037 arg_types = {"this": True, "expression": True, "unit": False} 3038 3039 3040class DateDiff(Func, TimeUnit): 3041 arg_types = {"this": True, "expression": True, "unit": False} 3042 3043 3044class DateTrunc(Func): 3045 arg_types = {"this": True, "expression": True, "zone": False} 3046 3047 3048class DatetimeAdd(Func, TimeUnit): 3049 arg_types = {"this": True, "expression": True, "unit": False} 3050 3051 3052class DatetimeSub(Func, TimeUnit): 3053 arg_types = {"this": True, "expression": True, "unit": False} 3054 3055 3056class DatetimeDiff(Func, TimeUnit): 3057 arg_types = {"this": True, "expression": True, "unit": False} 3058 3059 3060class DatetimeTrunc(Func, TimeUnit): 3061 arg_types = {"this": True, "unit": True, "zone": False} 3062 3063 3064class DayOfWeek(Func): 3065 _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"] 3066 3067 3068class DayOfMonth(Func): 3069 _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"] 3070 3071 3072class DayOfYear(Func): 3073 _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"] 3074 3075 3076class WeekOfYear(Func): 3077 _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"] 3078 3079 3080class LastDateOfMonth(Func): 3081 pass 3082 3083 3084class Extract(Func): 3085 arg_types = {"this": True, "expression": True} 3086 3087 3088class TimestampAdd(Func, TimeUnit): 3089 arg_types = {"this": True, "expression": True, "unit": False} 3090 3091 3092class TimestampSub(Func, TimeUnit): 3093 arg_types = {"this": True, "expression": True, "unit": False} 3094 3095 3096class TimestampDiff(Func, TimeUnit): 3097 arg_types = {"this": True, "expression": True, "unit": False} 3098 3099 3100class TimestampTrunc(Func, TimeUnit): 3101 arg_types = {"this": True, "unit": True, "zone": False} 3102 3103 3104class TimeAdd(Func, TimeUnit): 3105 arg_types = {"this": True, "expression": True, "unit": False} 3106 3107 3108class TimeSub(Func, TimeUnit): 3109 arg_types = {"this": True, "expression": True, "unit": False} 3110 3111 3112class TimeDiff(Func, TimeUnit): 3113 arg_types = {"this": True, "expression": True, "unit": False} 3114 3115 3116class TimeTrunc(Func, TimeUnit): 3117 arg_types = {"this": True, "unit": True, "zone": False} 3118 3119 3120class DateFromParts(Func): 3121 _sql_names = ["DATEFROMPARTS"] 3122 arg_types = {"year": True, "month": True, "day": True} 3123 3124 3125class DateStrToDate(Func): 3126 pass 3127 3128 3129class DateToDateStr(Func): 3130 pass 3131 3132 3133class DateToDi(Func): 3134 pass 3135 3136 3137class Day(Func): 3138 pass 3139 3140 3141class Decode(Func): 3142 arg_types = {"this": True, "charset": True, "replace": False} 3143 3144 3145class DiToDate(Func): 3146 pass 3147 3148 3149class Encode(Func): 3150 arg_types = {"this": True, "charset": True} 3151 3152 3153class Exp(Func): 3154 pass 3155 3156 3157class Explode(Func): 3158 pass 3159 3160 3161class Floor(Func): 3162 arg_types = {"this": True, "decimals": False} 3163 3164 3165class Greatest(Func): 3166 arg_types = {"this": True, "expressions": False} 3167 is_var_len_args = True 3168 3169 3170class GroupConcat(Func): 3171 arg_types = {"this": True, "separator": False} 3172 3173 3174class Hex(Func): 3175 pass 3176 3177 3178class If(Func): 3179 arg_types = {"this": True, "true": True, "false": False} 3180 3181 3182class IfNull(Func): 3183 arg_types = {"this": True, "expression": False} 3184 _sql_names = ["IFNULL", "NVL"] 3185 3186 3187class Initcap(Func): 3188 pass 3189 3190 3191class JSONBContains(Binary): 3192 _sql_names = ["JSONB_CONTAINS"] 3193 3194 3195class JSONExtract(Binary, Func): 3196 _sql_names = ["JSON_EXTRACT"] 3197 3198 3199class JSONExtractScalar(JSONExtract): 3200 _sql_names = ["JSON_EXTRACT_SCALAR"] 3201 3202 3203class JSONBExtract(JSONExtract): 3204 _sql_names = ["JSONB_EXTRACT"] 3205 3206 3207class JSONBExtractScalar(JSONExtract): 3208 _sql_names = ["JSONB_EXTRACT_SCALAR"] 3209 3210 3211class Least(Func): 3212 arg_types = {"this": True, "expressions": False} 3213 is_var_len_args = True 3214 3215 3216class Length(Func): 3217 pass 3218 3219 3220class Levenshtein(Func): 3221 arg_types = { 3222 "this": True, 3223 "expression": False, 3224 "ins_cost": False, 3225 "del_cost": False, 3226 "sub_cost": False, 3227 } 3228 3229 3230class Ln(Func): 3231 pass 3232 3233 3234class Log(Func): 3235 arg_types = {"this": True, "expression": False} 3236 3237 3238class Log2(Func): 3239 pass 3240 3241 3242class Log10(Func): 3243 pass 3244 3245 3246class LogicalOr(AggFunc): 3247 _sql_names = ["LOGICAL_OR", "BOOL_OR"] 3248 3249 3250class Lower(Func): 3251 _sql_names = ["LOWER", "LCASE"] 3252 3253 3254class Map(Func): 3255 arg_types = {"keys": False, "values": False} 3256 3257 3258class VarMap(Func): 3259 arg_types = {"keys": True, "values": True} 3260 is_var_len_args = True 3261 3262 3263class Matches(Func): 3264 """Oracle/Snowflake decode. 3265 https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm 3266 Pattern matching MATCHES(value, search1, result1, ...searchN, resultN, else) 3267 """ 3268 3269 arg_types = {"this": True, "expressions": True} 3270 is_var_len_args = True 3271 3272 3273class Max(AggFunc): 3274 arg_types = {"this": True, "expression": False} 3275 3276 3277class Min(AggFunc): 3278 arg_types = {"this": True, "expression": False} 3279 3280 3281class Month(Func): 3282 pass 3283 3284 3285class Nvl2(Func): 3286 arg_types = {"this": True, "true": True, "false": False} 3287 3288 3289class Posexplode(Func): 3290 pass 3291 3292 3293class Pow(Binary, Func): 3294 _sql_names = ["POWER", "POW"] 3295 3296 3297class PercentileCont(AggFunc): 3298 pass 3299 3300 3301class PercentileDisc(AggFunc): 3302 pass 3303 3304 3305class Quantile(AggFunc): 3306 arg_types = {"this": True, "quantile": True} 3307 3308 3309# Clickhouse-specific: 3310# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/quantiles/#quantiles 3311class Quantiles(AggFunc): 3312 arg_types = {"parameters": True, "expressions": True} 3313 3314 3315class QuantileIf(AggFunc): 3316 arg_types = {"parameters": True, "expressions": True} 3317 3318 3319class ApproxQuantile(Quantile): 3320 arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False} 3321 3322 3323class ReadCSV(Func): 3324 _sql_names = ["READ_CSV"] 3325 is_var_len_args = True 3326 arg_types = {"this": True, "expressions": False} 3327 3328 3329class Reduce(Func): 3330 arg_types = {"this": True, "initial": True, "merge": True, "finish": False} 3331 3332 3333class RegexpLike(Func): 3334 arg_types = {"this": True, "expression": True, "flag": False} 3335 3336 3337class RegexpILike(Func): 3338 arg_types = {"this": True, "expression": True, "flag": False} 3339 3340 3341class RegexpSplit(Func): 3342 arg_types = {"this": True, "expression": True} 3343 3344 3345class Repeat(Func): 3346 arg_types = {"this": True, "times": True} 3347 3348 3349class Round(Func): 3350 arg_types = {"this": True, "decimals": False} 3351 3352 3353class RowNumber(Func): 3354 arg_types: t.Dict[str, t.Any] = {} 3355 3356 3357class SafeDivide(Func): 3358 arg_types = {"this": True, "expression": True} 3359 3360 3361class SetAgg(AggFunc): 3362 pass 3363 3364 3365class SortArray(Func): 3366 arg_types = {"this": True, "asc": False} 3367 3368 3369class Split(Func): 3370 arg_types = {"this": True, "expression": True, "limit": False} 3371 3372 3373# Start may be omitted in the case of postgres 3374# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6 3375class Substring(Func): 3376 arg_types = {"this": True, "start": False, "length": False} 3377 3378 3379class StrPosition(Func): 3380 arg_types = { 3381 "this": True, 3382 "substr": True, 3383 "position": False, 3384 "instance": False, 3385 } 3386 3387 3388class StrToDate(Func): 3389 arg_types = {"this": True, "format": True} 3390 3391 3392class StrToTime(Func): 3393 arg_types = {"this": True, "format": True} 3394 3395 3396# Spark allows unix_timestamp() 3397# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html 3398class StrToUnix(Func): 3399 arg_types = {"this": False, "format": False} 3400 3401 3402class NumberToStr(Func): 3403 arg_types = {"this": True, "format": True} 3404 3405 3406class Struct(Func): 3407 arg_types = {"expressions": True} 3408 is_var_len_args = True 3409 3410 3411class StructExtract(Func): 3412 arg_types = {"this": True, "expression": True} 3413 3414 3415class Sum(AggFunc): 3416 pass 3417 3418 3419class Sqrt(Func): 3420 pass 3421 3422 3423class Stddev(AggFunc): 3424 pass 3425 3426 3427class StddevPop(AggFunc): 3428 pass 3429 3430 3431class StddevSamp(AggFunc): 3432 pass 3433 3434 3435class TimeToStr(Func): 3436 arg_types = {"this": True, "format": True} 3437 3438 3439class TimeToTimeStr(Func): 3440 pass 3441 3442 3443class TimeToUnix(Func): 3444 pass 3445 3446 3447class TimeStrToDate(Func): 3448 pass 3449 3450 3451class TimeStrToTime(Func): 3452 pass 3453 3454 3455class TimeStrToUnix(Func): 3456 pass 3457 3458 3459class Trim(Func): 3460 arg_types = { 3461 "this": True, 3462 "expression": False, 3463 "position": False, 3464 "collation": False, 3465 } 3466 3467 3468class TsOrDsAdd(Func, TimeUnit): 3469 arg_types = {"this": True, "expression": True, "unit": False} 3470 3471 3472class TsOrDsToDateStr(Func): 3473 pass 3474 3475 3476class TsOrDsToDate(Func): 3477 arg_types = {"this": True, "format": False} 3478 3479 3480class TsOrDiToDi(Func): 3481 pass 3482 3483 3484class Unhex(Func): 3485 pass 3486 3487 3488class UnixToStr(Func): 3489 arg_types = {"this": True, "format": False} 3490 3491 3492# https://prestodb.io/docs/current/functions/datetime.html 3493# presto has weird zone/hours/minutes 3494class UnixToTime(Func): 3495 arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False} 3496 3497 SECONDS = Literal.string("seconds") 3498 MILLIS = Literal.string("millis") 3499 MICROS = Literal.string("micros") 3500 3501 3502class UnixToTimeStr(Func): 3503 pass 3504 3505 3506class Upper(Func): 3507 _sql_names = ["UPPER", "UCASE"] 3508 3509 3510class Variance(AggFunc): 3511 _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"] 3512 3513 3514class VariancePop(AggFunc): 3515 _sql_names = ["VARIANCE_POP", "VAR_POP"] 3516 3517 3518class Week(Func): 3519 arg_types = {"this": True, "mode": False} 3520 3521 3522class Year(Func): 3523 pass 3524 3525 3526class Use(Expression): 3527 arg_types = {"this": True, "kind": False} 3528 3529 3530class Merge(Expression): 3531 arg_types = {"this": True, "using": True, "on": True, "expressions": True} 3532 3533 3534class When(Func): 3535 arg_types = {"this": True, "then": True} 3536 3537 3538def _norm_args(expression): 3539 args = {} 3540 3541 for k, arg in expression.args.items(): 3542 if isinstance(arg, list): 3543 arg = [_norm_arg(a) for a in arg] 3544 if not arg: 3545 arg = None 3546 else: 3547 arg = _norm_arg(arg) 3548 3549 if arg is not None and arg is not False: 3550 args[k] = arg 3551 3552 return args 3553 3554 3555def _norm_arg(arg): 3556 return arg.lower() if isinstance(arg, str) else arg 3557 3558 3559ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func)) 3560 3561 3562# Helpers 3563def maybe_parse( 3564 sql_or_expression: str | Expression, 3565 *, 3566 into: t.Optional[IntoType] = None, 3567 dialect: DialectType = None, 3568 prefix: t.Optional[str] = None, 3569 **opts, 3570) -> Expression: 3571 """Gracefully handle a possible string or expression. 3572 3573 Example: 3574 >>> maybe_parse("1") 3575 (LITERAL this: 1, is_string: False) 3576 >>> maybe_parse(to_identifier("x")) 3577 (IDENTIFIER this: x, quoted: False) 3578 3579 Args: 3580 sql_or_expression: the SQL code string or an expression 3581 into: the SQLGlot Expression to parse into 3582 dialect: the dialect used to parse the input expressions (in the case that an 3583 input expression is a SQL string). 3584 prefix: a string to prefix the sql with before it gets parsed 3585 (automatically includes a space) 3586 **opts: other options to use to parse the input expressions (again, in the case 3587 that an input expression is a SQL string). 3588 3589 Returns: 3590 Expression: the parsed or given expression. 3591 """ 3592 if isinstance(sql_or_expression, Expression): 3593 return sql_or_expression 3594 3595 import sqlglot 3596 3597 sql = str(sql_or_expression) 3598 if prefix: 3599 sql = f"{prefix} {sql}" 3600 return sqlglot.parse_one(sql, read=dialect, into=into, **opts) 3601 3602 3603def _maybe_copy(instance, copy=True): 3604 return instance.copy() if copy else instance 3605 3606 3607def _is_wrong_expression(expression, into): 3608 return isinstance(expression, Expression) and not isinstance(expression, into) 3609 3610 3611def _apply_builder( 3612 expression, 3613 instance, 3614 arg, 3615 copy=True, 3616 prefix=None, 3617 into=None, 3618 dialect=None, 3619 **opts, 3620): 3621 if _is_wrong_expression(expression, into): 3622 expression = into(this=expression) 3623 instance = _maybe_copy(instance, copy) 3624 expression = maybe_parse( 3625 sql_or_expression=expression, 3626 prefix=prefix, 3627 into=into, 3628 dialect=dialect, 3629 **opts, 3630 ) 3631 instance.set(arg, expression) 3632 return instance 3633 3634 3635def _apply_child_list_builder( 3636 *expressions, 3637 instance, 3638 arg, 3639 append=True, 3640 copy=True, 3641 prefix=None, 3642 into=None, 3643 dialect=None, 3644 properties=None, 3645 **opts, 3646): 3647 instance = _maybe_copy(instance, copy) 3648 parsed = [] 3649 for expression in expressions: 3650 if _is_wrong_expression(expression, into): 3651 expression = into(expressions=[expression]) 3652 expression = maybe_parse( 3653 expression, 3654 into=into, 3655 dialect=dialect, 3656 prefix=prefix, 3657 **opts, 3658 ) 3659 parsed.extend(expression.expressions) 3660 3661 existing = instance.args.get(arg) 3662 if append and existing: 3663 parsed = existing.expressions + parsed 3664 3665 child = into(expressions=parsed) 3666 for k, v in (properties or {}).items(): 3667 child.set(k, v) 3668 instance.set(arg, child) 3669 return instance 3670 3671 3672def _apply_list_builder( 3673 *expressions, 3674 instance, 3675 arg, 3676 append=True, 3677 copy=True, 3678 prefix=None, 3679 into=None, 3680 dialect=None, 3681 **opts, 3682): 3683 inst = _maybe_copy(instance, copy) 3684 3685 expressions = [ 3686 maybe_parse( 3687 sql_or_expression=expression, 3688 into=into, 3689 prefix=prefix, 3690 dialect=dialect, 3691 **opts, 3692 ) 3693 for expression in expressions 3694 ] 3695 3696 existing_expressions = inst.args.get(arg) 3697 if append and existing_expressions: 3698 expressions = existing_expressions + expressions 3699 3700 inst.set(arg, expressions) 3701 return inst 3702 3703 3704def _apply_conjunction_builder( 3705 *expressions, 3706 instance, 3707 arg, 3708 into=None, 3709 append=True, 3710 copy=True, 3711 dialect=None, 3712 **opts, 3713): 3714 expressions = [exp for exp in expressions if exp is not None and exp != ""] 3715 if not expressions: 3716 return instance 3717 3718 inst = _maybe_copy(instance, copy) 3719 3720 existing = inst.args.get(arg) 3721 if append and existing is not None: 3722 expressions = [existing.this if into else existing] + list(expressions) 3723 3724 node = and_(*expressions, dialect=dialect, **opts) 3725 3726 inst.set(arg, into(this=node) if into else node) 3727 return inst 3728 3729 3730def _combine(expressions, operator, dialect=None, **opts): 3731 expressions = [condition(expression, dialect=dialect, **opts) for expression in expressions] 3732 this = expressions[0] 3733 if expressions[1:]: 3734 this = _wrap_operator(this) 3735 for expression in expressions[1:]: 3736 this = operator(this=this, expression=_wrap_operator(expression)) 3737 return this 3738 3739 3740def _wrap_operator(expression): 3741 if isinstance(expression, (And, Or, Not)): 3742 expression = Paren(this=expression) 3743 return expression 3744 3745 3746def union(left, right, distinct=True, dialect=None, **opts): 3747 """ 3748 Initializes a syntax tree from one UNION expression. 3749 3750 Example: 3751 >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql() 3752 'SELECT * FROM foo UNION SELECT * FROM bla' 3753 3754 Args: 3755 left (str | Expression): the SQL code string corresponding to the left-hand side. 3756 If an `Expression` instance is passed, it will be used as-is. 3757 right (str | Expression): the SQL code string corresponding to the right-hand side. 3758 If an `Expression` instance is passed, it will be used as-is. 3759 distinct (bool): set the DISTINCT flag if and only if this is true. 3760 dialect (str): the dialect used to parse the input expression. 3761 opts (kwargs): other options to use to parse the input expressions. 3762 Returns: 3763 Union: the syntax tree for the UNION expression. 3764 """ 3765 left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts) 3766 right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts) 3767 3768 return Union(this=left, expression=right, distinct=distinct) 3769 3770 3771def intersect(left, right, distinct=True, dialect=None, **opts): 3772 """ 3773 Initializes a syntax tree from one INTERSECT expression. 3774 3775 Example: 3776 >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql() 3777 'SELECT * FROM foo INTERSECT SELECT * FROM bla' 3778 3779 Args: 3780 left (str | Expression): the SQL code string corresponding to the left-hand side. 3781 If an `Expression` instance is passed, it will be used as-is. 3782 right (str | Expression): the SQL code string corresponding to the right-hand side. 3783 If an `Expression` instance is passed, it will be used as-is. 3784 distinct (bool): set the DISTINCT flag if and only if this is true. 3785 dialect (str): the dialect used to parse the input expression. 3786 opts (kwargs): other options to use to parse the input expressions. 3787 Returns: 3788 Intersect: the syntax tree for the INTERSECT expression. 3789 """ 3790 left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts) 3791 right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts) 3792 3793 return Intersect(this=left, expression=right, distinct=distinct) 3794 3795 3796def except_(left, right, distinct=True, dialect=None, **opts): 3797 """ 3798 Initializes a syntax tree from one EXCEPT expression. 3799 3800 Example: 3801 >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql() 3802 'SELECT * FROM foo EXCEPT SELECT * FROM bla' 3803 3804 Args: 3805 left (str | Expression): the SQL code string corresponding to the left-hand side. 3806 If an `Expression` instance is passed, it will be used as-is. 3807 right (str | Expression): the SQL code string corresponding to the right-hand side. 3808 If an `Expression` instance is passed, it will be used as-is. 3809 distinct (bool): set the DISTINCT flag if and only if this is true. 3810 dialect (str): the dialect used to parse the input expression. 3811 opts (kwargs): other options to use to parse the input expressions. 3812 Returns: 3813 Except: the syntax tree for the EXCEPT statement. 3814 """ 3815 left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts) 3816 right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts) 3817 3818 return Except(this=left, expression=right, distinct=distinct) 3819 3820 3821def select(*expressions, dialect=None, **opts) -> Select: 3822 """ 3823 Initializes a syntax tree from one or multiple SELECT expressions. 3824 3825 Example: 3826 >>> select("col1", "col2").from_("tbl").sql() 3827 'SELECT col1, col2 FROM tbl' 3828 3829 Args: 3830 *expressions (str | Expression): the SQL code string to parse as the expressions of a 3831 SELECT statement. If an Expression instance is passed, this is used as-is. 3832 dialect (str): the dialect used to parse the input expressions (in the case that an 3833 input expression is a SQL string). 3834 **opts: other options to use to parse the input expressions (again, in the case 3835 that an input expression is a SQL string). 3836 3837 Returns: 3838 Select: the syntax tree for the SELECT statement. 3839 """ 3840 return Select().select(*expressions, dialect=dialect, **opts) 3841 3842 3843def from_(*expressions, dialect=None, **opts) -> Select: 3844 """ 3845 Initializes a syntax tree from a FROM expression. 3846 3847 Example: 3848 >>> from_("tbl").select("col1", "col2").sql() 3849 'SELECT col1, col2 FROM tbl' 3850 3851 Args: 3852 *expressions (str | Expression): the SQL code string to parse as the FROM expressions of a 3853 SELECT statement. If an Expression instance is passed, this is used as-is. 3854 dialect (str): the dialect used to parse the input expression (in the case that the 3855 input expression is a SQL string). 3856 **opts: other options to use to parse the input expressions (again, in the case 3857 that the input expression is a SQL string). 3858 3859 Returns: 3860 Select: the syntax tree for the SELECT statement. 3861 """ 3862 return Select().from_(*expressions, dialect=dialect, **opts) 3863 3864 3865def update(table, properties, where=None, from_=None, dialect=None, **opts) -> Update: 3866 """ 3867 Creates an update statement. 3868 3869 Example: 3870 >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql() 3871 "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1" 3872 3873 Args: 3874 *properties (Dict[str, Any]): dictionary of properties to set which are 3875 auto converted to sql objects eg None -> NULL 3876 where (str): sql conditional parsed into a WHERE statement 3877 from_ (str): sql statement parsed into a FROM statement 3878 dialect (str): the dialect used to parse the input expressions. 3879 **opts: other options to use to parse the input expressions. 3880 3881 Returns: 3882 Update: the syntax tree for the UPDATE statement. 3883 """ 3884 update = Update(this=maybe_parse(table, into=Table, dialect=dialect)) 3885 update.set( 3886 "expressions", 3887 [ 3888 EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v)) 3889 for k, v in properties.items() 3890 ], 3891 ) 3892 if from_: 3893 update.set( 3894 "from", 3895 maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts), 3896 ) 3897 if isinstance(where, Condition): 3898 where = Where(this=where) 3899 if where: 3900 update.set( 3901 "where", 3902 maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts), 3903 ) 3904 return update 3905 3906 3907def delete(table, where=None, dialect=None, **opts) -> Delete: 3908 """ 3909 Builds a delete statement. 3910 3911 Example: 3912 >>> delete("my_table", where="id > 1").sql() 3913 'DELETE FROM my_table WHERE id > 1' 3914 3915 Args: 3916 where (str|Condition): sql conditional parsed into a WHERE statement 3917 dialect (str): the dialect used to parse the input expressions. 3918 **opts: other options to use to parse the input expressions. 3919 3920 Returns: 3921 Delete: the syntax tree for the DELETE statement. 3922 """ 3923 return Delete( 3924 this=maybe_parse(table, into=Table, dialect=dialect, **opts), 3925 where=Where(this=where) 3926 if isinstance(where, Condition) 3927 else maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts), 3928 ) 3929 3930 3931def condition(expression, dialect=None, **opts) -> Condition: 3932 """ 3933 Initialize a logical condition expression. 3934 3935 Example: 3936 >>> condition("x=1").sql() 3937 'x = 1' 3938 3939 This is helpful for composing larger logical syntax trees: 3940 >>> where = condition("x=1") 3941 >>> where = where.and_("y=1") 3942 >>> Select().from_("tbl").select("*").where(where).sql() 3943 'SELECT * FROM tbl WHERE x = 1 AND y = 1' 3944 3945 Args: 3946 *expression (str | Expression): the SQL code string to parse. 3947 If an Expression instance is passed, this is used as-is. 3948 dialect (str): the dialect used to parse the input expression (in the case that the 3949 input expression is a SQL string). 3950 **opts: other options to use to parse the input expressions (again, in the case 3951 that the input expression is a SQL string). 3952 3953 Returns: 3954 Condition: the expression 3955 """ 3956 return maybe_parse( # type: ignore 3957 expression, 3958 into=Condition, 3959 dialect=dialect, 3960 **opts, 3961 ) 3962 3963 3964def and_(*expressions, dialect=None, **opts) -> And: 3965 """ 3966 Combine multiple conditions with an AND logical operator. 3967 3968 Example: 3969 >>> and_("x=1", and_("y=1", "z=1")).sql() 3970 'x = 1 AND (y = 1 AND z = 1)' 3971 3972 Args: 3973 *expressions (str | Expression): the SQL code strings to parse. 3974 If an Expression instance is passed, this is used as-is. 3975 dialect (str): the dialect used to parse the input expression. 3976 **opts: other options to use to parse the input expressions. 3977 3978 Returns: 3979 And: the new condition 3980 """ 3981 return _combine(expressions, And, dialect, **opts) 3982 3983 3984def or_(*expressions, dialect=None, **opts) -> Or: 3985 """ 3986 Combine multiple conditions with an OR logical operator. 3987 3988 Example: 3989 >>> or_("x=1", or_("y=1", "z=1")).sql() 3990 'x = 1 OR (y = 1 OR z = 1)' 3991 3992 Args: 3993 *expressions (str | Expression): the SQL code strings to parse. 3994 If an Expression instance is passed, this is used as-is. 3995 dialect (str): the dialect used to parse the input expression. 3996 **opts: other options to use to parse the input expressions. 3997 3998 Returns: 3999 Or: the new condition 4000 """ 4001 return _combine(expressions, Or, dialect, **opts) 4002 4003 4004def not_(expression, dialect=None, **opts) -> Not: 4005 """ 4006 Wrap a condition with a NOT operator. 4007 4008 Example: 4009 >>> not_("this_suit='black'").sql() 4010 "NOT this_suit = 'black'" 4011 4012 Args: 4013 expression (str | Expression): the SQL code strings to parse. 4014 If an Expression instance is passed, this is used as-is. 4015 dialect (str): the dialect used to parse the input expression. 4016 **opts: other options to use to parse the input expressions. 4017 4018 Returns: 4019 Not: the new condition 4020 """ 4021 this = condition( 4022 expression, 4023 dialect=dialect, 4024 **opts, 4025 ) 4026 return Not(this=_wrap_operator(this)) 4027 4028 4029def paren(expression) -> Paren: 4030 return Paren(this=expression) 4031 4032 4033SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$") 4034 4035 4036@t.overload 4037def to_identifier(name: None, quoted: t.Optional[bool] = None) -> None: 4038 ... 4039 4040 4041@t.overload 4042def to_identifier(name: str | Identifier, quoted: t.Optional[bool] = None) -> Identifier: 4043 ... 4044 4045 4046def to_identifier(name, quoted=None): 4047 """Builds an identifier. 4048 4049 Args: 4050 name: The name to turn into an identifier. 4051 quoted: Whether or not force quote the identifier. 4052 4053 Returns: 4054 The identifier ast node. 4055 """ 4056 4057 if name is None: 4058 return None 4059 4060 if isinstance(name, Identifier): 4061 identifier = name 4062 elif isinstance(name, str): 4063 identifier = Identifier( 4064 this=name, 4065 quoted=not re.match(SAFE_IDENTIFIER_RE, name) if quoted is None else quoted, 4066 ) 4067 else: 4068 raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}") 4069 return identifier 4070 4071 4072INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*") 4073 4074 4075def to_interval(interval: str | Literal) -> Interval: 4076 """Builds an interval expression from a string like '1 day' or '5 months'.""" 4077 if isinstance(interval, Literal): 4078 if not interval.is_string: 4079 raise ValueError("Invalid interval string.") 4080 4081 interval = interval.this 4082 4083 interval_parts = INTERVAL_STRING_RE.match(interval) # type: ignore 4084 4085 if not interval_parts: 4086 raise ValueError("Invalid interval string.") 4087 4088 return Interval( 4089 this=Literal.string(interval_parts.group(1)), 4090 unit=Var(this=interval_parts.group(2)), 4091 ) 4092 4093 4094@t.overload 4095def to_table(sql_path: str | Table, **kwargs) -> Table: 4096 ... 4097 4098 4099@t.overload 4100def to_table(sql_path: None, **kwargs) -> None: 4101 ... 4102 4103 4104def to_table(sql_path: t.Optional[str | Table], **kwargs) -> t.Optional[Table]: 4105 """ 4106 Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional. 4107 If a table is passed in then that table is returned. 4108 4109 Args: 4110 sql_path: a `[catalog].[schema].[table]` string. 4111 4112 Returns: 4113 A table expression. 4114 """ 4115 if sql_path is None or isinstance(sql_path, Table): 4116 return sql_path 4117 if not isinstance(sql_path, str): 4118 raise ValueError(f"Invalid type provided for a table: {type(sql_path)}") 4119 4120 catalog, db, table_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 3)) 4121 return Table(this=table_name, db=db, catalog=catalog, **kwargs) 4122 4123 4124def to_column(sql_path: str | Column, **kwargs) -> Column: 4125 """ 4126 Create a column from a `[table].[column]` sql path. Schema is optional. 4127 4128 If a column is passed in then that column is returned. 4129 4130 Args: 4131 sql_path: `[table].[column]` string 4132 Returns: 4133 Table: A column expression 4134 """ 4135 if sql_path is None or isinstance(sql_path, Column): 4136 return sql_path 4137 if not isinstance(sql_path, str): 4138 raise ValueError(f"Invalid type provided for column: {type(sql_path)}") 4139 table_name, column_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 2)) 4140 return Column(this=column_name, table=table_name, **kwargs) 4141 4142 4143def alias_( 4144 expression: str | Expression, 4145 alias: str | Identifier, 4146 table: bool | t.Sequence[str | Identifier] = False, 4147 quoted: t.Optional[bool] = None, 4148 dialect: DialectType = None, 4149 **opts, 4150): 4151 """Create an Alias expression. 4152 4153 Example: 4154 >>> alias_('foo', 'bar').sql() 4155 'foo AS bar' 4156 4157 >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql() 4158 '(SELECT 1, 2) AS bar(a, b)' 4159 4160 Args: 4161 expression: the SQL code strings to parse. 4162 If an Expression instance is passed, this is used as-is. 4163 alias: the alias name to use. If the name has 4164 special characters it is quoted. 4165 table: Whether or not to create a table alias, can also be a list of columns. 4166 quoted: whether or not to quote the alias 4167 dialect: the dialect used to parse the input expression. 4168 **opts: other options to use to parse the input expressions. 4169 4170 Returns: 4171 Alias: the aliased expression 4172 """ 4173 exp = maybe_parse(expression, dialect=dialect, **opts) 4174 alias = to_identifier(alias, quoted=quoted) 4175 4176 if table: 4177 table_alias = TableAlias(this=alias) 4178 exp.set("alias", table_alias) 4179 4180 if not isinstance(table, bool): 4181 for column in table: 4182 table_alias.append("columns", to_identifier(column, quoted=quoted)) 4183 4184 return exp 4185 4186 # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in 4187 # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node 4188 # for the complete Window expression. 4189 # 4190 # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls 4191 4192 if "alias" in exp.arg_types and not isinstance(exp, Window): 4193 exp = exp.copy() 4194 exp.set("alias", alias) 4195 return exp 4196 return Alias(this=exp, alias=alias) 4197 4198 4199def subquery(expression, alias=None, dialect=None, **opts): 4200 """ 4201 Build a subquery expression. 4202 4203 Example: 4204 >>> subquery('select x from tbl', 'bar').select('x').sql() 4205 'SELECT x FROM (SELECT x FROM tbl) AS bar' 4206 4207 Args: 4208 expression (str | Expression): the SQL code strings to parse. 4209 If an Expression instance is passed, this is used as-is. 4210 alias (str | Expression): the alias name to use. 4211 dialect (str): the dialect used to parse the input expression. 4212 **opts: other options to use to parse the input expressions. 4213 4214 Returns: 4215 Select: a new select with the subquery expression included 4216 """ 4217 4218 expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias) 4219 return Select().from_(expression, dialect=dialect, **opts) 4220 4221 4222def column(col, table=None, quoted=None) -> Column: 4223 """ 4224 Build a Column. 4225 4226 Args: 4227 col (str | Expression): column name 4228 table (str | Expression): table name 4229 Returns: 4230 Column: column instance 4231 """ 4232 return Column( 4233 this=to_identifier(col, quoted=quoted), 4234 table=to_identifier(table, quoted=quoted), 4235 ) 4236 4237 4238def cast(expression: str | Expression, to: str | DataType | DataType.Type, **opts) -> Cast: 4239 """Cast an expression to a data type. 4240 4241 Example: 4242 >>> cast('x + 1', 'int').sql() 4243 'CAST(x + 1 AS INT)' 4244 4245 Args: 4246 expression: The expression to cast. 4247 to: The datatype to cast to. 4248 4249 Returns: 4250 A cast node. 4251 """ 4252 expression = maybe_parse(expression, **opts) 4253 return Cast(this=expression, to=DataType.build(to, **opts)) 4254 4255 4256def table_(table, db=None, catalog=None, quoted=None, alias=None) -> Table: 4257 """Build a Table. 4258 4259 Args: 4260 table (str | Expression): column name 4261 db (str | Expression): db name 4262 catalog (str | Expression): catalog name 4263 4264 Returns: 4265 Table: table instance 4266 """ 4267 return Table( 4268 this=to_identifier(table, quoted=quoted), 4269 db=to_identifier(db, quoted=quoted), 4270 catalog=to_identifier(catalog, quoted=quoted), 4271 alias=TableAlias(this=to_identifier(alias)) if alias else None, 4272 ) 4273 4274 4275def values( 4276 values: t.Iterable[t.Tuple[t.Any, ...]], 4277 alias: t.Optional[str] = None, 4278 columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None, 4279) -> Values: 4280 """Build VALUES statement. 4281 4282 Example: 4283 >>> values([(1, '2')]).sql() 4284 "VALUES (1, '2')" 4285 4286 Args: 4287 values: values statements that will be converted to SQL 4288 alias: optional alias 4289 columns: Optional list of ordered column names or ordered dictionary of column names to types. 4290 If either are provided then an alias is also required. 4291 If a dictionary is provided then the first column of the values will be casted to the expected type 4292 in order to help with type inference. 4293 4294 Returns: 4295 Values: the Values expression object 4296 """ 4297 if columns and not alias: 4298 raise ValueError("Alias is required when providing columns") 4299 table_alias = ( 4300 TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns]) 4301 if columns 4302 else TableAlias(this=to_identifier(alias) if alias else None) 4303 ) 4304 expressions = [convert(tup) for tup in values] 4305 if columns and isinstance(columns, dict): 4306 types = list(columns.values()) 4307 expressions[0].set( 4308 "expressions", 4309 [cast(x, types[i]) for i, x in enumerate(expressions[0].expressions)], 4310 ) 4311 return Values( 4312 expressions=expressions, 4313 alias=table_alias, 4314 ) 4315 4316 4317def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable: 4318 """Build ALTER TABLE... RENAME... expression 4319 4320 Args: 4321 old_name: The old name of the table 4322 new_name: The new name of the table 4323 4324 Returns: 4325 Alter table expression 4326 """ 4327 old_table = to_table(old_name) 4328 new_table = to_table(new_name) 4329 return AlterTable( 4330 this=old_table, 4331 actions=[ 4332 RenameTable(this=new_table), 4333 ], 4334 ) 4335 4336 4337def convert(value) -> Expression: 4338 """Convert a python value into an expression object. 4339 4340 Raises an error if a conversion is not possible. 4341 4342 Args: 4343 value (Any): a python object 4344 4345 Returns: 4346 Expression: the equivalent expression object 4347 """ 4348 if isinstance(value, Expression): 4349 return value 4350 if value is None: 4351 return NULL 4352 if isinstance(value, bool): 4353 return Boolean(this=value) 4354 if isinstance(value, str): 4355 return Literal.string(value) 4356 if isinstance(value, float) and math.isnan(value): 4357 return NULL 4358 if isinstance(value, numbers.Number): 4359 return Literal.number(value) 4360 if isinstance(value, tuple): 4361 return Tuple(expressions=[convert(v) for v in value]) 4362 if isinstance(value, list): 4363 return Array(expressions=[convert(v) for v in value]) 4364 if isinstance(value, dict): 4365 return Map( 4366 keys=[convert(k) for k in value], 4367 values=[convert(v) for v in value.values()], 4368 ) 4369 if isinstance(value, datetime.datetime): 4370 datetime_literal = Literal.string( 4371 (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat() 4372 ) 4373 return TimeStrToTime(this=datetime_literal) 4374 if isinstance(value, datetime.date): 4375 date_literal = Literal.string(value.strftime("%Y-%m-%d")) 4376 return DateStrToDate(this=date_literal) 4377 raise ValueError(f"Cannot convert {value}") 4378 4379 4380def replace_children(expression, fun): 4381 """ 4382 Replace children of an expression with the result of a lambda fun(child) -> exp. 4383 """ 4384 for k, v in expression.args.items(): 4385 is_list_arg = isinstance(v, list) 4386 4387 child_nodes = v if is_list_arg else [v] 4388 new_child_nodes = [] 4389 4390 for cn in child_nodes: 4391 if isinstance(cn, Expression): 4392 for child_node in ensure_collection(fun(cn)): 4393 new_child_nodes.append(child_node) 4394 child_node.parent = expression 4395 child_node.arg_key = k 4396 else: 4397 new_child_nodes.append(cn) 4398 4399 expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0) 4400 4401 4402def column_table_names(expression): 4403 """ 4404 Return all table names referenced through columns in an expression. 4405 4406 Example: 4407 >>> import sqlglot 4408 >>> column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")) 4409 ['c', 'a'] 4410 4411 Args: 4412 expression (sqlglot.Expression): expression to find table names 4413 4414 Returns: 4415 list: A list of unique names 4416 """ 4417 return list(dict.fromkeys(column.table for column in expression.find_all(Column))) 4418 4419 4420def table_name(table) -> str: 4421 """Get the full name of a table as a string. 4422 4423 Args: 4424 table (exp.Table | str): table expression node or string. 4425 4426 Examples: 4427 >>> from sqlglot import exp, parse_one 4428 >>> table_name(parse_one("select * from a.b.c").find(exp.Table)) 4429 'a.b.c' 4430 4431 Returns: 4432 The table name. 4433 """ 4434 4435 table = maybe_parse(table, into=Table) 4436 4437 if not table: 4438 raise ValueError(f"Cannot parse {table}") 4439 4440 return ".".join( 4441 part 4442 for part in ( 4443 table.text("catalog"), 4444 table.text("db"), 4445 table.name, 4446 ) 4447 if part 4448 ) 4449 4450 4451def replace_tables(expression, mapping): 4452 """Replace all tables in expression according to the mapping. 4453 4454 Args: 4455 expression (sqlglot.Expression): expression node to be transformed and replaced. 4456 mapping (Dict[str, str]): mapping of table names. 4457 4458 Examples: 4459 >>> from sqlglot import exp, parse_one 4460 >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql() 4461 'SELECT * FROM c' 4462 4463 Returns: 4464 The mapped expression. 4465 """ 4466 4467 def _replace_tables(node): 4468 if isinstance(node, Table): 4469 new_name = mapping.get(table_name(node)) 4470 if new_name: 4471 return to_table( 4472 new_name, 4473 **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")}, 4474 ) 4475 return node 4476 4477 return expression.transform(_replace_tables) 4478 4479 4480def replace_placeholders(expression, *args, **kwargs): 4481 """Replace placeholders in an expression. 4482 4483 Args: 4484 expression (sqlglot.Expression): expression node to be transformed and replaced. 4485 args: positional names that will substitute unnamed placeholders in the given order. 4486 kwargs: keyword arguments that will substitute named placeholders. 4487 4488 Examples: 4489 >>> from sqlglot import exp, parse_one 4490 >>> replace_placeholders( 4491 ... parse_one("select * from :tbl where ? = ?"), "a", "b", tbl="foo" 4492 ... ).sql() 4493 'SELECT * FROM foo WHERE a = b' 4494 4495 Returns: 4496 The mapped expression. 4497 """ 4498 4499 def _replace_placeholders(node, args, **kwargs): 4500 if isinstance(node, Placeholder): 4501 if node.name: 4502 new_name = kwargs.get(node.name) 4503 if new_name: 4504 return to_identifier(new_name) 4505 else: 4506 try: 4507 return to_identifier(next(args)) 4508 except StopIteration: 4509 pass 4510 return node 4511 4512 return expression.transform(_replace_placeholders, iter(args), **kwargs) 4513 4514 4515def expand(expression: Expression, sources: t.Dict[str, Subqueryable], copy=True) -> Expression: 4516 """Transforms an expression by expanding all referenced sources into subqueries. 4517 4518 Examples: 4519 >>> from sqlglot import parse_one 4520 >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql() 4521 'SELECT * FROM (SELECT * FROM y) AS z /* source: x */' 4522 4523 Args: 4524 expression: The expression to expand. 4525 sources: A dictionary of name to Subqueryables. 4526 copy: Whether or not to copy the expression during transformation. Defaults to True. 4527 4528 Returns: 4529 The transformed expression. 4530 """ 4531 4532 def _expand(node: Expression): 4533 if isinstance(node, Table): 4534 name = table_name(node) 4535 source = sources.get(name) 4536 if source: 4537 subquery = source.subquery(node.alias or name) 4538 subquery.comments = [f"source: {name}"] 4539 return subquery 4540 return node 4541 4542 return expression.transform(_expand, copy=copy) 4543 4544 4545def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func: 4546 """ 4547 Returns a Func expression. 4548 4549 Examples: 4550 >>> func("abs", 5).sql() 4551 'ABS(5)' 4552 4553 >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql() 4554 'CAST(5 AS DOUBLE)' 4555 4556 Args: 4557 name: the name of the function to build. 4558 args: the args used to instantiate the function of interest. 4559 dialect: the source dialect. 4560 kwargs: the kwargs used to instantiate the function of interest. 4561 4562 Note: 4563 The arguments `args` and `kwargs` are mutually exclusive. 4564 4565 Returns: 4566 An instance of the function of interest, or an anonymous function, if `name` doesn't 4567 correspond to an existing `sqlglot.expressions.Func` class. 4568 """ 4569 if args and kwargs: 4570 raise ValueError("Can't use both args and kwargs to instantiate a function.") 4571 4572 from sqlglot.dialects.dialect import Dialect 4573 4574 args = tuple(convert(arg) for arg in args) 4575 kwargs = {key: convert(value) for key, value in kwargs.items()} 4576 4577 parser = Dialect.get_or_raise(dialect)().parser() 4578 from_args_list = parser.FUNCTIONS.get(name.upper()) 4579 4580 if from_args_list: 4581 function = from_args_list(args) if args else from_args_list.__self__(**kwargs) # type: ignore 4582 else: 4583 kwargs = kwargs or {"expressions": args} 4584 function = Anonymous(this=name, **kwargs) 4585 4586 for error_message in function.error_messages(args): 4587 raise ValueError(error_message) 4588 4589 return function 4590 4591 4592def true(): 4593 """ 4594 Returns a true Boolean expression. 4595 """ 4596 return Boolean(this=True) 4597 4598 4599def false(): 4600 """ 4601 Returns a false Boolean expression. 4602 """ 4603 return Boolean(this=False) 4604 4605 4606def null(): 4607 """ 4608 Returns a Null expression. 4609 """ 4610 return Null() 4611 4612 4613# TODO: deprecate this 4614TRUE = Boolean(this=True) 4615FALSE = Boolean(this=False) 4616NULL = Null()
54class Expression(metaclass=_Expression): 55 """ 56 The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary 57 context, such as its child expressions, their names (arg keys), and whether a given child expression 58 is optional or not. 59 60 Attributes: 61 key: a unique key for each class in the Expression hierarchy. This is useful for hashing 62 and representing expressions as strings. 63 arg_types: determines what arguments (child nodes) are supported by an expression. It 64 maps arg keys to booleans that indicate whether the corresponding args are optional. 65 66 Example: 67 >>> class Foo(Expression): 68 ... arg_types = {"this": True, "expression": False} 69 70 The above definition informs us that Foo is an Expression that requires an argument called 71 "this" and may also optionally receive an argument called "expression". 72 73 Args: 74 args: a mapping used for retrieving the arguments of an expression, given their arg keys. 75 parent: a reference to the parent expression (or None, in case of root expressions). 76 arg_key: the arg key an expression is associated with, i.e. the name its parent expression 77 uses to refer to it. 78 comments: a list of comments that are associated with a given expression. This is used in 79 order to preserve comments when transpiling SQL code. 80 _type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the 81 optimizer, in order to enable some transformations that require type information. 82 """ 83 84 key = "expression" 85 arg_types = {"this": True} 86 __slots__ = ("args", "parent", "arg_key", "comments", "_type") 87 88 def __init__(self, **args: t.Any): 89 self.args: t.Dict[str, t.Any] = args 90 self.parent: t.Optional[Expression] = None 91 self.arg_key: t.Optional[str] = None 92 self.comments: t.Optional[t.List[str]] = None 93 self._type: t.Optional[DataType] = None 94 95 for arg_key, value in self.args.items(): 96 self._set_parent(arg_key, value) 97 98 def __eq__(self, other) -> bool: 99 return type(self) is type(other) and _norm_args(self) == _norm_args(other) 100 101 def __hash__(self) -> int: 102 return hash( 103 ( 104 self.key, 105 tuple( 106 (k, tuple(v) if isinstance(v, list) else v) for k, v in _norm_args(self).items() 107 ), 108 ) 109 ) 110 111 @property 112 def this(self): 113 """ 114 Retrieves the argument with key "this". 115 """ 116 return self.args.get("this") 117 118 @property 119 def expression(self): 120 """ 121 Retrieves the argument with key "expression". 122 """ 123 return self.args.get("expression") 124 125 @property 126 def expressions(self): 127 """ 128 Retrieves the argument with key "expressions". 129 """ 130 return self.args.get("expressions") or [] 131 132 def text(self, key): 133 """ 134 Returns a textual representation of the argument corresponding to "key". This can only be used 135 for args that are strings or leaf Expression instances, such as identifiers and literals. 136 """ 137 field = self.args.get(key) 138 if isinstance(field, str): 139 return field 140 if isinstance(field, (Identifier, Literal, Var)): 141 return field.this 142 if isinstance(field, (Star, Null)): 143 return field.name 144 return "" 145 146 @property 147 def is_string(self): 148 """ 149 Checks whether a Literal expression is a string. 150 """ 151 return isinstance(self, Literal) and self.args["is_string"] 152 153 @property 154 def is_number(self): 155 """ 156 Checks whether a Literal expression is a number. 157 """ 158 return isinstance(self, Literal) and not self.args["is_string"] 159 160 @property 161 def is_int(self): 162 """ 163 Checks whether a Literal expression is an integer. 164 """ 165 if self.is_number: 166 try: 167 int(self.name) 168 return True 169 except ValueError: 170 pass 171 return False 172 173 @property 174 def alias(self): 175 """ 176 Returns the alias of the expression, or an empty string if it's not aliased. 177 """ 178 if isinstance(self.args.get("alias"), TableAlias): 179 return self.args["alias"].name 180 return self.text("alias") 181 182 @property 183 def name(self) -> str: 184 return self.text("this") 185 186 @property 187 def alias_or_name(self): 188 return self.alias or self.name 189 190 @property 191 def output_name(self): 192 """ 193 Name of the output column if this expression is a selection. 194 195 If the Expression has no output name, an empty string is returned. 196 197 Example: 198 >>> from sqlglot import parse_one 199 >>> parse_one("SELECT a").expressions[0].output_name 200 'a' 201 >>> parse_one("SELECT b AS c").expressions[0].output_name 202 'c' 203 >>> parse_one("SELECT 1 + 2").expressions[0].output_name 204 '' 205 """ 206 return "" 207 208 @property 209 def type(self) -> t.Optional[DataType]: 210 return self._type 211 212 @type.setter 213 def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None: 214 if dtype and not isinstance(dtype, DataType): 215 dtype = DataType.build(dtype) 216 self._type = dtype # type: ignore 217 218 def __deepcopy__(self, memo): 219 copy = self.__class__(**deepcopy(self.args)) 220 copy.comments = self.comments 221 copy.type = self.type 222 return copy 223 224 def copy(self): 225 """ 226 Returns a deep copy of the expression. 227 """ 228 new = deepcopy(self) 229 new.parent = self.parent 230 for item, parent, _ in new.bfs(): 231 if isinstance(item, Expression) and parent: 232 item.parent = parent 233 return new 234 235 def append(self, arg_key, value): 236 """ 237 Appends value to arg_key if it's a list or sets it as a new list. 238 239 Args: 240 arg_key (str): name of the list expression arg 241 value (Any): value to append to the list 242 """ 243 if not isinstance(self.args.get(arg_key), list): 244 self.args[arg_key] = [] 245 self.args[arg_key].append(value) 246 self._set_parent(arg_key, value) 247 248 def set(self, arg_key, value): 249 """ 250 Sets `arg_key` to `value`. 251 252 Args: 253 arg_key (str): name of the expression arg. 254 value: value to set the arg to. 255 """ 256 self.args[arg_key] = value 257 self._set_parent(arg_key, value) 258 259 def _set_parent(self, arg_key, value): 260 if isinstance(value, Expression): 261 value.parent = self 262 value.arg_key = arg_key 263 elif isinstance(value, list): 264 for v in value: 265 if isinstance(v, Expression): 266 v.parent = self 267 v.arg_key = arg_key 268 269 @property 270 def depth(self): 271 """ 272 Returns the depth of this tree. 273 """ 274 if self.parent: 275 return self.parent.depth + 1 276 return 0 277 278 def find(self, *expression_types, bfs=True): 279 """ 280 Returns the first node in this tree which matches at least one of 281 the specified types. 282 283 Args: 284 expression_types (type): the expression type(s) to match. 285 286 Returns: 287 The node which matches the criteria or None if no such node was found. 288 """ 289 return next(self.find_all(*expression_types, bfs=bfs), None) 290 291 def find_all(self, *expression_types, bfs=True): 292 """ 293 Returns a generator object which visits all nodes in this tree and only 294 yields those that match at least one of the specified expression types. 295 296 Args: 297 expression_types (type): the expression type(s) to match. 298 299 Returns: 300 The generator object. 301 """ 302 for expression, _, _ in self.walk(bfs=bfs): 303 if isinstance(expression, expression_types): 304 yield expression 305 306 def find_ancestor(self, *expression_types): 307 """ 308 Returns a nearest parent matching expression_types. 309 310 Args: 311 expression_types (type): the expression type(s) to match. 312 313 Returns: 314 The parent node. 315 """ 316 ancestor = self.parent 317 while ancestor and not isinstance(ancestor, expression_types): 318 ancestor = ancestor.parent 319 return ancestor 320 321 @property 322 def parent_select(self): 323 """ 324 Returns the parent select statement. 325 """ 326 return self.find_ancestor(Select) 327 328 def walk(self, bfs=True, prune=None): 329 """ 330 Returns a generator object which visits all nodes in this tree. 331 332 Args: 333 bfs (bool): if set to True the BFS traversal order will be applied, 334 otherwise the DFS traversal will be used instead. 335 prune ((node, parent, arg_key) -> bool): callable that returns True if 336 the generator should stop traversing this branch of the tree. 337 338 Returns: 339 the generator object. 340 """ 341 if bfs: 342 yield from self.bfs(prune=prune) 343 else: 344 yield from self.dfs(prune=prune) 345 346 def dfs(self, parent=None, key=None, prune=None): 347 """ 348 Returns a generator object which visits all nodes in this tree in 349 the DFS (Depth-first) order. 350 351 Returns: 352 The generator object. 353 """ 354 parent = parent or self.parent 355 yield self, parent, key 356 if prune and prune(self, parent, key): 357 return 358 359 for k, v in self.args.items(): 360 for node in ensure_collection(v): 361 if isinstance(node, Expression): 362 yield from node.dfs(self, k, prune) 363 364 def bfs(self, prune=None): 365 """ 366 Returns a generator object which visits all nodes in this tree in 367 the BFS (Breadth-first) order. 368 369 Returns: 370 The generator object. 371 """ 372 queue = deque([(self, self.parent, None)]) 373 374 while queue: 375 item, parent, key = queue.popleft() 376 377 yield item, parent, key 378 if prune and prune(item, parent, key): 379 continue 380 381 if isinstance(item, Expression): 382 for k, v in item.args.items(): 383 for node in ensure_collection(v): 384 if isinstance(node, Expression): 385 queue.append((node, item, k)) 386 387 def unnest(self): 388 """ 389 Returns the first non parenthesis child or self. 390 """ 391 expression = self 392 while isinstance(expression, Paren): 393 expression = expression.this 394 return expression 395 396 def unalias(self): 397 """ 398 Returns the inner expression if this is an Alias. 399 """ 400 if isinstance(self, Alias): 401 return self.this 402 return self 403 404 def unnest_operands(self): 405 """ 406 Returns unnested operands as a tuple. 407 """ 408 return tuple(arg.unnest() for arg in self.args.values() if arg) 409 410 def flatten(self, unnest=True): 411 """ 412 Returns a generator which yields child nodes who's parents are the same class. 413 414 A AND B AND C -> [A, B, C] 415 """ 416 for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not isinstance(n, self.__class__)): 417 if not isinstance(node, self.__class__): 418 yield node.unnest() if unnest else node 419 420 def __str__(self): 421 return self.sql() 422 423 def __repr__(self): 424 return self._to_s() 425 426 def sql(self, dialect: DialectType = None, **opts) -> str: 427 """ 428 Returns SQL string representation of this tree. 429 430 Args: 431 dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql"). 432 opts: other `sqlglot.generator.Generator` options. 433 434 Returns: 435 The SQL string. 436 """ 437 from sqlglot.dialects import Dialect 438 439 return Dialect.get_or_raise(dialect)().generate(self, **opts) 440 441 def _to_s(self, hide_missing: bool = True, level: int = 0) -> str: 442 indent = "" if not level else "\n" 443 indent += "".join([" "] * level) 444 left = f"({self.key.upper()} " 445 446 args: t.Dict[str, t.Any] = { 447 k: ", ".join( 448 v._to_s(hide_missing=hide_missing, level=level + 1) 449 if hasattr(v, "_to_s") 450 else str(v) 451 for v in ensure_collection(vs) 452 if v is not None 453 ) 454 for k, vs in self.args.items() 455 } 456 args["comments"] = self.comments 457 args["type"] = self.type 458 args = {k: v for k, v in args.items() if v or not hide_missing} 459 460 right = ", ".join(f"{k}: {v}" for k, v in args.items()) 461 right += ")" 462 463 return indent + left + right 464 465 def transform(self, fun, *args, copy=True, **kwargs): 466 """ 467 Recursively visits all tree nodes (excluding already transformed ones) 468 and applies the given transformation function to each node. 469 470 Args: 471 fun (function): a function which takes a node as an argument and returns a 472 new transformed node or the same node without modifications. If the function 473 returns None, then the corresponding node will be removed from the syntax tree. 474 copy (bool): if set to True a new tree instance is constructed, otherwise the tree is 475 modified in place. 476 477 Returns: 478 The transformed tree. 479 """ 480 node = self.copy() if copy else self 481 new_node = fun(node, *args, **kwargs) 482 483 if new_node is None or not isinstance(new_node, Expression): 484 return new_node 485 if new_node is not node: 486 new_node.parent = node.parent 487 return new_node 488 489 replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs)) 490 return new_node 491 492 def replace(self, expression): 493 """ 494 Swap out this expression with a new expression. 495 496 For example:: 497 498 >>> tree = Select().select("x").from_("tbl") 499 >>> tree.find(Column).replace(Column(this="y")) 500 (COLUMN this: y) 501 >>> tree.sql() 502 'SELECT y FROM tbl' 503 504 Args: 505 expression (Expression|None): new node 506 507 Returns: 508 The new expression or expressions. 509 """ 510 if not self.parent: 511 return expression 512 513 parent = self.parent 514 self.parent = None 515 516 replace_children(parent, lambda child: expression if child is self else child) 517 return expression 518 519 def pop(self): 520 """ 521 Remove this expression from its AST. 522 """ 523 self.replace(None) 524 525 def assert_is(self, type_): 526 """ 527 Assert that this `Expression` is an instance of `type_`. 528 529 If it is NOT an instance of `type_`, this raises an assertion error. 530 Otherwise, this returns this expression. 531 532 Examples: 533 This is useful for type security in chained expressions: 534 535 >>> import sqlglot 536 >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql() 537 'SELECT x, z FROM y' 538 """ 539 assert isinstance(self, type_) 540 return self 541 542 def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]: 543 """ 544 Checks if this expression is valid (e.g. all mandatory args are set). 545 546 Args: 547 args: a sequence of values that were used to instantiate a Func expression. This is used 548 to check that the provided arguments don't exceed the function argument limit. 549 550 Returns: 551 A list of error messages for all possible errors that were found. 552 """ 553 errors: t.List[str] = [] 554 555 for k in self.args: 556 if k not in self.arg_types: 557 errors.append(f"Unexpected keyword: '{k}' for {self.__class__}") 558 for k, mandatory in self.arg_types.items(): 559 v = self.args.get(k) 560 if mandatory and (v is None or (isinstance(v, list) and not v)): 561 errors.append(f"Required keyword: '{k}' missing for {self.__class__}") 562 563 if ( 564 args 565 and isinstance(self, Func) 566 and len(args) > len(self.arg_types) 567 and not self.is_var_len_args 568 ): 569 errors.append( 570 f"The number of provided arguments ({len(args)}) is greater than " 571 f"the maximum number of supported arguments ({len(self.arg_types)})" 572 ) 573 574 return errors 575 576 def dump(self): 577 """ 578 Dump this Expression to a JSON-serializable dict. 579 """ 580 from sqlglot.serde import dump 581 582 return dump(self) 583 584 @classmethod 585 def load(cls, obj): 586 """ 587 Load a dict (as returned by `Expression.dump`) into an Expression instance. 588 """ 589 from sqlglot.serde import load 590 591 return load(obj)
The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary context, such as its child expressions, their names (arg keys), and whether a given child expression is optional or not.
Attributes:
- key: a unique key for each class in the Expression hierarchy. This is useful for hashing and representing expressions as strings.
- arg_types: determines what arguments (child nodes) are supported by an expression. It maps arg keys to booleans that indicate whether the corresponding args are optional.
Example:
>>> class Foo(Expression): ... arg_types = {"this": True, "expression": False}
The above definition informs us that Foo is an Expression that requires an argument called "this" and may also optionally receive an argument called "expression".
Arguments:
- args: a mapping used for retrieving the arguments of an expression, given their arg keys.
- parent: a reference to the parent expression (or None, in case of root expressions).
- arg_key: the arg key an expression is associated with, i.e. the name its parent expression uses to refer to it.
- comments: a list of comments that are associated with a given expression. This is used in order to preserve comments when transpiling SQL code.
- _type: the
sqlglot.expressions.DataType
type of an expression. This is inferred by the optimizer, in order to enable some transformations that require type information.
88 def __init__(self, **args: t.Any): 89 self.args: t.Dict[str, t.Any] = args 90 self.parent: t.Optional[Expression] = None 91 self.arg_key: t.Optional[str] = None 92 self.comments: t.Optional[t.List[str]] = None 93 self._type: t.Optional[DataType] = None 94 95 for arg_key, value in self.args.items(): 96 self._set_parent(arg_key, value)
132 def text(self, key): 133 """ 134 Returns a textual representation of the argument corresponding to "key". This can only be used 135 for args that are strings or leaf Expression instances, such as identifiers and literals. 136 """ 137 field = self.args.get(key) 138 if isinstance(field, str): 139 return field 140 if isinstance(field, (Identifier, Literal, Var)): 141 return field.this 142 if isinstance(field, (Star, Null)): 143 return field.name 144 return ""
Returns a textual representation of the argument corresponding to "key". This can only be used for args that are strings or leaf Expression instances, such as identifiers and literals.
Name of the output column if this expression is a selection.
If the Expression has no output name, an empty string is returned.
Example:
>>> from sqlglot import parse_one >>> parse_one("SELECT a").expressions[0].output_name 'a' >>> parse_one("SELECT b AS c").expressions[0].output_name 'c' >>> parse_one("SELECT 1 + 2").expressions[0].output_name ''
224 def copy(self): 225 """ 226 Returns a deep copy of the expression. 227 """ 228 new = deepcopy(self) 229 new.parent = self.parent 230 for item, parent, _ in new.bfs(): 231 if isinstance(item, Expression) and parent: 232 item.parent = parent 233 return new
Returns a deep copy of the expression.
235 def append(self, arg_key, value): 236 """ 237 Appends value to arg_key if it's a list or sets it as a new list. 238 239 Args: 240 arg_key (str): name of the list expression arg 241 value (Any): value to append to the list 242 """ 243 if not isinstance(self.args.get(arg_key), list): 244 self.args[arg_key] = [] 245 self.args[arg_key].append(value) 246 self._set_parent(arg_key, value)
Appends value to arg_key if it's a list or sets it as a new list.
Arguments:
- arg_key (str): name of the list expression arg
- value (Any): value to append to the list
248 def set(self, arg_key, value): 249 """ 250 Sets `arg_key` to `value`. 251 252 Args: 253 arg_key (str): name of the expression arg. 254 value: value to set the arg to. 255 """ 256 self.args[arg_key] = value 257 self._set_parent(arg_key, value)
Sets arg_key
to value
.
Arguments:
- arg_key (str): name of the expression arg.
- value: value to set the arg to.
278 def find(self, *expression_types, bfs=True): 279 """ 280 Returns the first node in this tree which matches at least one of 281 the specified types. 282 283 Args: 284 expression_types (type): the expression type(s) to match. 285 286 Returns: 287 The node which matches the criteria or None if no such node was found. 288 """ 289 return next(self.find_all(*expression_types, bfs=bfs), None)
Returns the first node in this tree which matches at least one of the specified types.
Arguments:
- expression_types (type): the expression type(s) to match.
Returns:
The node which matches the criteria or None if no such node was found.
291 def find_all(self, *expression_types, bfs=True): 292 """ 293 Returns a generator object which visits all nodes in this tree and only 294 yields those that match at least one of the specified expression types. 295 296 Args: 297 expression_types (type): the expression type(s) to match. 298 299 Returns: 300 The generator object. 301 """ 302 for expression, _, _ in self.walk(bfs=bfs): 303 if isinstance(expression, expression_types): 304 yield expression
Returns a generator object which visits all nodes in this tree and only yields those that match at least one of the specified expression types.
Arguments:
- expression_types (type): the expression type(s) to match.
Returns:
The generator object.
306 def find_ancestor(self, *expression_types): 307 """ 308 Returns a nearest parent matching expression_types. 309 310 Args: 311 expression_types (type): the expression type(s) to match. 312 313 Returns: 314 The parent node. 315 """ 316 ancestor = self.parent 317 while ancestor and not isinstance(ancestor, expression_types): 318 ancestor = ancestor.parent 319 return ancestor
Returns a nearest parent matching expression_types.
Arguments:
- expression_types (type): the expression type(s) to match.
Returns:
The parent node.
328 def walk(self, bfs=True, prune=None): 329 """ 330 Returns a generator object which visits all nodes in this tree. 331 332 Args: 333 bfs (bool): if set to True the BFS traversal order will be applied, 334 otherwise the DFS traversal will be used instead. 335 prune ((node, parent, arg_key) -> bool): callable that returns True if 336 the generator should stop traversing this branch of the tree. 337 338 Returns: 339 the generator object. 340 """ 341 if bfs: 342 yield from self.bfs(prune=prune) 343 else: 344 yield from self.dfs(prune=prune)
Returns a generator object which visits all nodes in this tree.
Arguments:
- bfs (bool): if set to True the BFS traversal order will be applied, otherwise the DFS traversal will be used instead.
- prune ((node, parent, arg_key) -> bool): callable that returns True if the generator should stop traversing this branch of the tree.
Returns:
the generator object.
346 def dfs(self, parent=None, key=None, prune=None): 347 """ 348 Returns a generator object which visits all nodes in this tree in 349 the DFS (Depth-first) order. 350 351 Returns: 352 The generator object. 353 """ 354 parent = parent or self.parent 355 yield self, parent, key 356 if prune and prune(self, parent, key): 357 return 358 359 for k, v in self.args.items(): 360 for node in ensure_collection(v): 361 if isinstance(node, Expression): 362 yield from node.dfs(self, k, prune)
Returns a generator object which visits all nodes in this tree in the DFS (Depth-first) order.
Returns:
The generator object.
364 def bfs(self, prune=None): 365 """ 366 Returns a generator object which visits all nodes in this tree in 367 the BFS (Breadth-first) order. 368 369 Returns: 370 The generator object. 371 """ 372 queue = deque([(self, self.parent, None)]) 373 374 while queue: 375 item, parent, key = queue.popleft() 376 377 yield item, parent, key 378 if prune and prune(item, parent, key): 379 continue 380 381 if isinstance(item, Expression): 382 for k, v in item.args.items(): 383 for node in ensure_collection(v): 384 if isinstance(node, Expression): 385 queue.append((node, item, k))
Returns a generator object which visits all nodes in this tree in the BFS (Breadth-first) order.
Returns:
The generator object.
387 def unnest(self): 388 """ 389 Returns the first non parenthesis child or self. 390 """ 391 expression = self 392 while isinstance(expression, Paren): 393 expression = expression.this 394 return expression
Returns the first non parenthesis child or self.
396 def unalias(self): 397 """ 398 Returns the inner expression if this is an Alias. 399 """ 400 if isinstance(self, Alias): 401 return self.this 402 return self
Returns the inner expression if this is an Alias.
404 def unnest_operands(self): 405 """ 406 Returns unnested operands as a tuple. 407 """ 408 return tuple(arg.unnest() for arg in self.args.values() if arg)
Returns unnested operands as a tuple.
410 def flatten(self, unnest=True): 411 """ 412 Returns a generator which yields child nodes who's parents are the same class. 413 414 A AND B AND C -> [A, B, C] 415 """ 416 for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not isinstance(n, self.__class__)): 417 if not isinstance(node, self.__class__): 418 yield node.unnest() if unnest else node
Returns a generator which yields child nodes who's parents are the same class.
A AND B AND C -> [A, B, C]
426 def sql(self, dialect: DialectType = None, **opts) -> str: 427 """ 428 Returns SQL string representation of this tree. 429 430 Args: 431 dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql"). 432 opts: other `sqlglot.generator.Generator` options. 433 434 Returns: 435 The SQL string. 436 """ 437 from sqlglot.dialects import Dialect 438 439 return Dialect.get_or_raise(dialect)().generate(self, **opts)
Returns SQL string representation of this tree.
Arguments:
- dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
- opts: other
sqlglot.generator.Generator
options.
Returns:
The SQL string.
465 def transform(self, fun, *args, copy=True, **kwargs): 466 """ 467 Recursively visits all tree nodes (excluding already transformed ones) 468 and applies the given transformation function to each node. 469 470 Args: 471 fun (function): a function which takes a node as an argument and returns a 472 new transformed node or the same node without modifications. If the function 473 returns None, then the corresponding node will be removed from the syntax tree. 474 copy (bool): if set to True a new tree instance is constructed, otherwise the tree is 475 modified in place. 476 477 Returns: 478 The transformed tree. 479 """ 480 node = self.copy() if copy else self 481 new_node = fun(node, *args, **kwargs) 482 483 if new_node is None or not isinstance(new_node, Expression): 484 return new_node 485 if new_node is not node: 486 new_node.parent = node.parent 487 return new_node 488 489 replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs)) 490 return new_node
Recursively visits all tree nodes (excluding already transformed ones) and applies the given transformation function to each node.
Arguments:
- fun (function): a function which takes a node as an argument and returns a new transformed node or the same node without modifications. If the function returns None, then the corresponding node will be removed from the syntax tree.
- copy (bool): if set to True a new tree instance is constructed, otherwise the tree is modified in place.
Returns:
The transformed tree.
492 def replace(self, expression): 493 """ 494 Swap out this expression with a new expression. 495 496 For example:: 497 498 >>> tree = Select().select("x").from_("tbl") 499 >>> tree.find(Column).replace(Column(this="y")) 500 (COLUMN this: y) 501 >>> tree.sql() 502 'SELECT y FROM tbl' 503 504 Args: 505 expression (Expression|None): new node 506 507 Returns: 508 The new expression or expressions. 509 """ 510 if not self.parent: 511 return expression 512 513 parent = self.parent 514 self.parent = None 515 516 replace_children(parent, lambda child: expression if child is self else child) 517 return expression
Swap out this expression with a new expression.
For example::
>>> tree = Select().select("x").from_("tbl")
>>> tree.find(Column).replace(Column(this="y"))
(COLUMN this: y)
>>> tree.sql()
'SELECT y FROM tbl'
Arguments:
- expression (Expression|None): new node
Returns:
The new expression or expressions.
525 def assert_is(self, type_): 526 """ 527 Assert that this `Expression` is an instance of `type_`. 528 529 If it is NOT an instance of `type_`, this raises an assertion error. 530 Otherwise, this returns this expression. 531 532 Examples: 533 This is useful for type security in chained expressions: 534 535 >>> import sqlglot 536 >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql() 537 'SELECT x, z FROM y' 538 """ 539 assert isinstance(self, type_) 540 return self
Assert that this Expression
is an instance of type_
.
If it is NOT an instance of type_
, this raises an assertion error.
Otherwise, this returns this expression.
Examples:
This is useful for type security in chained expressions:
>>> import sqlglot >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql() 'SELECT x, z FROM y'
542 def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]: 543 """ 544 Checks if this expression is valid (e.g. all mandatory args are set). 545 546 Args: 547 args: a sequence of values that were used to instantiate a Func expression. This is used 548 to check that the provided arguments don't exceed the function argument limit. 549 550 Returns: 551 A list of error messages for all possible errors that were found. 552 """ 553 errors: t.List[str] = [] 554 555 for k in self.args: 556 if k not in self.arg_types: 557 errors.append(f"Unexpected keyword: '{k}' for {self.__class__}") 558 for k, mandatory in self.arg_types.items(): 559 v = self.args.get(k) 560 if mandatory and (v is None or (isinstance(v, list) and not v)): 561 errors.append(f"Required keyword: '{k}' missing for {self.__class__}") 562 563 if ( 564 args 565 and isinstance(self, Func) 566 and len(args) > len(self.arg_types) 567 and not self.is_var_len_args 568 ): 569 errors.append( 570 f"The number of provided arguments ({len(args)}) is greater than " 571 f"the maximum number of supported arguments ({len(self.arg_types)})" 572 ) 573 574 return errors
Checks if this expression is valid (e.g. all mandatory args are set).
Arguments:
- args: a sequence of values that were used to instantiate a Func expression. This is used to check that the provided arguments don't exceed the function argument limit.
Returns:
A list of error messages for all possible errors that were found.
576 def dump(self): 577 """ 578 Dump this Expression to a JSON-serializable dict. 579 """ 580 from sqlglot.serde import dump 581 582 return dump(self)
Dump this Expression to a JSON-serializable dict.
584 @classmethod 585 def load(cls, obj): 586 """ 587 Load a dict (as returned by `Expression.dump`) into an Expression instance. 588 """ 589 from sqlglot.serde import load 590 591 return load(obj)
Load a dict (as returned by Expression.dump
) into an Expression instance.
601class Condition(Expression): 602 def and_(self, *expressions, dialect=None, **opts): 603 """ 604 AND this condition with one or multiple expressions. 605 606 Example: 607 >>> condition("x=1").and_("y=1").sql() 608 'x = 1 AND y = 1' 609 610 Args: 611 *expressions (str | Expression): the SQL code strings to parse. 612 If an `Expression` instance is passed, it will be used as-is. 613 dialect (str): the dialect used to parse the input expression. 614 opts (kwargs): other options to use to parse the input expressions. 615 616 Returns: 617 And: the new condition. 618 """ 619 return and_(self, *expressions, dialect=dialect, **opts) 620 621 def or_(self, *expressions, dialect=None, **opts): 622 """ 623 OR this condition with one or multiple expressions. 624 625 Example: 626 >>> condition("x=1").or_("y=1").sql() 627 'x = 1 OR y = 1' 628 629 Args: 630 *expressions (str | Expression): the SQL code strings to parse. 631 If an `Expression` instance is passed, it will be used as-is. 632 dialect (str): the dialect used to parse the input expression. 633 opts (kwargs): other options to use to parse the input expressions. 634 635 Returns: 636 Or: the new condition. 637 """ 638 return or_(self, *expressions, dialect=dialect, **opts) 639 640 def not_(self): 641 """ 642 Wrap this condition with NOT. 643 644 Example: 645 >>> condition("x=1").not_().sql() 646 'NOT x = 1' 647 648 Returns: 649 Not: the new condition. 650 """ 651 return not_(self)
602 def and_(self, *expressions, dialect=None, **opts): 603 """ 604 AND this condition with one or multiple expressions. 605 606 Example: 607 >>> condition("x=1").and_("y=1").sql() 608 'x = 1 AND y = 1' 609 610 Args: 611 *expressions (str | Expression): the SQL code strings to parse. 612 If an `Expression` instance is passed, it will be used as-is. 613 dialect (str): the dialect used to parse the input expression. 614 opts (kwargs): other options to use to parse the input expressions. 615 616 Returns: 617 And: the new condition. 618 """ 619 return and_(self, *expressions, dialect=dialect, **opts)
AND this condition with one or multiple expressions.
Example:
>>> condition("x=1").and_("y=1").sql() 'x = 1 AND y = 1'
Arguments:
- *expressions (str | Expression): the SQL code strings to parse.
If an
Expression
instance is passed, it will be used as-is. - dialect (str): the dialect used to parse the input expression.
- opts (kwargs): other options to use to parse the input expressions.
Returns:
And: the new condition.
621 def or_(self, *expressions, dialect=None, **opts): 622 """ 623 OR this condition with one or multiple expressions. 624 625 Example: 626 >>> condition("x=1").or_("y=1").sql() 627 'x = 1 OR y = 1' 628 629 Args: 630 *expressions (str | Expression): the SQL code strings to parse. 631 If an `Expression` instance is passed, it will be used as-is. 632 dialect (str): the dialect used to parse the input expression. 633 opts (kwargs): other options to use to parse the input expressions. 634 635 Returns: 636 Or: the new condition. 637 """ 638 return or_(self, *expressions, dialect=dialect, **opts)
OR this condition with one or multiple expressions.
Example:
>>> condition("x=1").or_("y=1").sql() 'x = 1 OR y = 1'
Arguments:
- *expressions (str | Expression): the SQL code strings to parse.
If an
Expression
instance is passed, it will be used as-is. - dialect (str): the dialect used to parse the input expression.
- opts (kwargs): other options to use to parse the input expressions.
Returns:
Or: the new condition.
640 def not_(self): 641 """ 642 Wrap this condition with NOT. 643 644 Example: 645 >>> condition("x=1").not_().sql() 646 'NOT x = 1' 647 648 Returns: 649 Not: the new condition. 650 """ 651 return not_(self)
Wrap this condition with NOT.
Example:
>>> condition("x=1").not_().sql() 'NOT x = 1'
Returns:
Not: the new condition.
Relationships like x = y, x > 1, x >= y.
Inherited Members
658class DerivedTable(Expression): 659 @property 660 def alias_column_names(self): 661 table_alias = self.args.get("alias") 662 if not table_alias: 663 return [] 664 column_list = table_alias.assert_is(TableAlias).args.get("columns") or [] 665 return [c.name for c in column_list] 666 667 @property 668 def selects(self): 669 alias = self.args.get("alias") 670 671 if alias: 672 return alias.columns 673 return [] 674 675 @property 676 def named_selects(self): 677 return [select.output_name for select in self.selects]
680class Unionable(Expression): 681 def union(self, expression, distinct=True, dialect=None, **opts): 682 """ 683 Builds a UNION expression. 684 685 Example: 686 >>> import sqlglot 687 >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql() 688 'SELECT * FROM foo UNION SELECT * FROM bla' 689 690 Args: 691 expression (str | Expression): the SQL code string. 692 If an `Expression` instance is passed, it will be used as-is. 693 distinct (bool): set the DISTINCT flag if and only if this is true. 694 dialect (str): the dialect used to parse the input expression. 695 opts (kwargs): other options to use to parse the input expressions. 696 Returns: 697 Union: the Union expression. 698 """ 699 return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts) 700 701 def intersect(self, expression, distinct=True, dialect=None, **opts): 702 """ 703 Builds an INTERSECT expression. 704 705 Example: 706 >>> import sqlglot 707 >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql() 708 'SELECT * FROM foo INTERSECT SELECT * FROM bla' 709 710 Args: 711 expression (str | Expression): the SQL code string. 712 If an `Expression` instance is passed, it will be used as-is. 713 distinct (bool): set the DISTINCT flag if and only if this is true. 714 dialect (str): the dialect used to parse the input expression. 715 opts (kwargs): other options to use to parse the input expressions. 716 Returns: 717 Intersect: the Intersect expression 718 """ 719 return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts) 720 721 def except_(self, expression, distinct=True, dialect=None, **opts): 722 """ 723 Builds an EXCEPT expression. 724 725 Example: 726 >>> import sqlglot 727 >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql() 728 'SELECT * FROM foo EXCEPT SELECT * FROM bla' 729 730 Args: 731 expression (str | Expression): the SQL code string. 732 If an `Expression` instance is passed, it will be used as-is. 733 distinct (bool): set the DISTINCT flag if and only if this is true. 734 dialect (str): the dialect used to parse the input expression. 735 opts (kwargs): other options to use to parse the input expressions. 736 Returns: 737 Except: the Except expression 738 """ 739 return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
681 def union(self, expression, distinct=True, dialect=None, **opts): 682 """ 683 Builds a UNION expression. 684 685 Example: 686 >>> import sqlglot 687 >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql() 688 'SELECT * FROM foo UNION SELECT * FROM bla' 689 690 Args: 691 expression (str | Expression): the SQL code string. 692 If an `Expression` instance is passed, it will be used as-is. 693 distinct (bool): set the DISTINCT flag if and only if this is true. 694 dialect (str): the dialect used to parse the input expression. 695 opts (kwargs): other options to use to parse the input expressions. 696 Returns: 697 Union: the Union expression. 698 """ 699 return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
Builds a UNION expression.
Example:
>>> import sqlglot >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql() 'SELECT * FROM foo UNION SELECT * FROM bla'
Arguments:
- expression (str | Expression): the SQL code string.
If an
Expression
instance is passed, it will be used as-is. - distinct (bool): set the DISTINCT flag if and only if this is true.
- dialect (str): the dialect used to parse the input expression.
- opts (kwargs): other options to use to parse the input expressions.
Returns:
Union: the Union expression.
701 def intersect(self, expression, distinct=True, dialect=None, **opts): 702 """ 703 Builds an INTERSECT expression. 704 705 Example: 706 >>> import sqlglot 707 >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql() 708 'SELECT * FROM foo INTERSECT SELECT * FROM bla' 709 710 Args: 711 expression (str | Expression): the SQL code string. 712 If an `Expression` instance is passed, it will be used as-is. 713 distinct (bool): set the DISTINCT flag if and only if this is true. 714 dialect (str): the dialect used to parse the input expression. 715 opts (kwargs): other options to use to parse the input expressions. 716 Returns: 717 Intersect: the Intersect expression 718 """ 719 return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
Builds an INTERSECT expression.
Example:
>>> import sqlglot >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql() 'SELECT * FROM foo INTERSECT SELECT * FROM bla'
Arguments:
- expression (str | Expression): the SQL code string.
If an
Expression
instance is passed, it will be used as-is. - distinct (bool): set the DISTINCT flag if and only if this is true.
- dialect (str): the dialect used to parse the input expression.
- opts (kwargs): other options to use to parse the input expressions.
Returns:
Intersect: the Intersect expression
721 def except_(self, expression, distinct=True, dialect=None, **opts): 722 """ 723 Builds an EXCEPT expression. 724 725 Example: 726 >>> import sqlglot 727 >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql() 728 'SELECT * FROM foo EXCEPT SELECT * FROM bla' 729 730 Args: 731 expression (str | Expression): the SQL code string. 732 If an `Expression` instance is passed, it will be used as-is. 733 distinct (bool): set the DISTINCT flag if and only if this is true. 734 dialect (str): the dialect used to parse the input expression. 735 opts (kwargs): other options to use to parse the input expressions. 736 Returns: 737 Except: the Except expression 738 """ 739 return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
Builds an EXCEPT expression.
Example:
>>> import sqlglot >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql() 'SELECT * FROM foo EXCEPT SELECT * FROM bla'
Arguments:
- expression (str | Expression): the SQL code string.
If an
Expression
instance is passed, it will be used as-is. - distinct (bool): set the DISTINCT flag if and only if this is true.
- dialect (str): the dialect used to parse the input expression.
- opts (kwargs): other options to use to parse the input expressions.
Returns:
Except: the Except expression
Inherited Members
760class Create(Expression): 761 arg_types = { 762 "with": False, 763 "this": True, 764 "kind": True, 765 "expression": False, 766 "set": False, 767 "multiset": False, 768 "global_temporary": False, 769 "volatile": False, 770 "exists": False, 771 "properties": False, 772 "temporary": False, 773 "transient": False, 774 "external": False, 775 "replace": False, 776 "unique": False, 777 "materialized": False, 778 "data": False, 779 "statistics": False, 780 "no_primary_index": False, 781 "indexes": False, 782 "no_schema_binding": False, 783 "begin": False, 784 }
805class Show(Expression): 806 arg_types = { 807 "this": True, 808 "target": False, 809 "offset": False, 810 "limit": False, 811 "like": False, 812 "where": False, 813 "db": False, 814 "full": False, 815 "mutex": False, 816 "query": False, 817 "channel": False, 818 "global": False, 819 "log": False, 820 "position": False, 821 "types": False, 822 }
Inherited Members
Inherited Members
Inherited Members
873class Column(Condition): 874 arg_types = {"this": True, "table": False} 875 876 @property 877 def table(self): 878 return self.text("table") 879 880 @property 881 def output_name(self): 882 return self.name
Name of the output column if this expression is a selection.
If the Expression has no output name, an empty string is returned.
Example:
>>> from sqlglot import parse_one >>> parse_one("SELECT a").expressions[0].output_name 'a' >>> parse_one("SELECT b AS c").expressions[0].output_name 'c' >>> parse_one("SELECT 1 + 2").expressions[0].output_name ''
Inherited Members
1029class Identifier(Expression): 1030 arg_types = {"this": True, "quoted": False} 1031 1032 @property 1033 def quoted(self): 1034 return bool(self.args.get("quoted")) 1035 1036 def __eq__(self, other): 1037 return isinstance(other, self.__class__) and _norm_arg(self.this) == _norm_arg(other.this) 1038 1039 def __hash__(self): 1040 return hash((self.key, self.this.lower())) 1041 1042 @property 1043 def output_name(self): 1044 return self.name
Name of the output column if this expression is a selection.
If the Expression has no output name, an empty string is returned.
Example:
>>> from sqlglot import parse_one >>> parse_one("SELECT a").expressions[0].output_name 'a' >>> parse_one("SELECT b AS c").expressions[0].output_name 'c' >>> parse_one("SELECT 1 + 2").expressions[0].output_name ''
1117class Literal(Condition): 1118 arg_types = {"this": True, "is_string": True} 1119 1120 def __eq__(self, other): 1121 return ( 1122 isinstance(other, Literal) 1123 and self.this == other.this 1124 and self.args["is_string"] == other.args["is_string"] 1125 ) 1126 1127 def __hash__(self): 1128 return hash((self.key, self.this, self.args["is_string"])) 1129 1130 @classmethod 1131 def number(cls, number) -> Literal: 1132 return cls(this=str(number), is_string=False) 1133 1134 @classmethod 1135 def string(cls, string) -> Literal: 1136 return cls(this=str(string), is_string=True) 1137 1138 @property 1139 def output_name(self): 1140 return self.name
Name of the output column if this expression is a selection.
If the Expression has no output name, an empty string is returned.
Example:
>>> from sqlglot import parse_one >>> parse_one("SELECT a").expressions[0].output_name 'a' >>> parse_one("SELECT b AS c").expressions[0].output_name 'c' >>> parse_one("SELECT 1 + 2").expressions[0].output_name ''
Inherited Members
1143class Join(Expression): 1144 arg_types = { 1145 "this": True, 1146 "on": False, 1147 "side": False, 1148 "kind": False, 1149 "using": False, 1150 "natural": False, 1151 } 1152 1153 @property 1154 def kind(self): 1155 return self.text("kind").upper() 1156 1157 @property 1158 def side(self): 1159 return self.text("side").upper() 1160 1161 @property 1162 def alias_or_name(self): 1163 return self.this.alias_or_name 1164 1165 def on(self, *expressions, append=True, dialect=None, copy=True, **opts): 1166 """ 1167 Append to or set the ON expressions. 1168 1169 Example: 1170 >>> import sqlglot 1171 >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql() 1172 'JOIN x ON y = 1' 1173 1174 Args: 1175 *expressions (str | Expression): the SQL code strings to parse. 1176 If an `Expression` instance is passed, it will be used as-is. 1177 Multiple expressions are combined with an AND operator. 1178 append (bool): if `True`, AND the new expressions to any existing expression. 1179 Otherwise, this resets the expression. 1180 dialect (str): the dialect used to parse the input expressions. 1181 copy (bool): if `False`, modify this expression instance in-place. 1182 opts (kwargs): other options to use to parse the input expressions. 1183 1184 Returns: 1185 Join: the modified join expression. 1186 """ 1187 join = _apply_conjunction_builder( 1188 *expressions, 1189 instance=self, 1190 arg="on", 1191 append=append, 1192 dialect=dialect, 1193 copy=copy, 1194 **opts, 1195 ) 1196 1197 if join.kind == "CROSS": 1198 join.set("kind", None) 1199 1200 return join 1201 1202 def using(self, *expressions, append=True, dialect=None, copy=True, **opts): 1203 """ 1204 Append to or set the USING expressions. 1205 1206 Example: 1207 >>> import sqlglot 1208 >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql() 1209 'JOIN x USING (foo, bla)' 1210 1211 Args: 1212 *expressions (str | Expression): the SQL code strings to parse. 1213 If an `Expression` instance is passed, it will be used as-is. 1214 append (bool): if `True`, concatenate the new expressions to the existing "using" list. 1215 Otherwise, this resets the expression. 1216 dialect (str): the dialect used to parse the input expressions. 1217 copy (bool): if `False`, modify this expression instance in-place. 1218 opts (kwargs): other options to use to parse the input expressions. 1219 1220 Returns: 1221 Join: the modified join expression. 1222 """ 1223 join = _apply_list_builder( 1224 *expressions, 1225 instance=self, 1226 arg="using", 1227 append=append, 1228 dialect=dialect, 1229 copy=copy, 1230 **opts, 1231 ) 1232 1233 if join.kind == "CROSS": 1234 join.set("kind", None) 1235 1236 return join
1165 def on(self, *expressions, append=True, dialect=None, copy=True, **opts): 1166 """ 1167 Append to or set the ON expressions. 1168 1169 Example: 1170 >>> import sqlglot 1171 >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql() 1172 'JOIN x ON y = 1' 1173 1174 Args: 1175 *expressions (str | Expression): the SQL code strings to parse. 1176 If an `Expression` instance is passed, it will be used as-is. 1177 Multiple expressions are combined with an AND operator. 1178 append (bool): if `True`, AND the new expressions to any existing expression. 1179 Otherwise, this resets the expression. 1180 dialect (str): the dialect used to parse the input expressions. 1181 copy (bool): if `False`, modify this expression instance in-place. 1182 opts (kwargs): other options to use to parse the input expressions. 1183 1184 Returns: 1185 Join: the modified join expression. 1186 """ 1187 join = _apply_conjunction_builder( 1188 *expressions, 1189 instance=self, 1190 arg="on", 1191 append=append, 1192 dialect=dialect, 1193 copy=copy, 1194 **opts, 1195 ) 1196 1197 if join.kind == "CROSS": 1198 join.set("kind", None) 1199 1200 return join
Append to or set the ON expressions.
Example:
>>> import sqlglot >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql() 'JOIN x ON y = 1'
Arguments:
- *expressions (str | Expression): the SQL code strings to parse.
If an
Expression
instance is passed, it will be used as-is. Multiple expressions are combined with an AND operator. - append (bool): if
True
, AND the new expressions to any existing expression. Otherwise, this resets the expression. - dialect (str): the dialect used to parse the input expressions.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Join: the modified join expression.
1202 def using(self, *expressions, append=True, dialect=None, copy=True, **opts): 1203 """ 1204 Append to or set the USING expressions. 1205 1206 Example: 1207 >>> import sqlglot 1208 >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql() 1209 'JOIN x USING (foo, bla)' 1210 1211 Args: 1212 *expressions (str | Expression): the SQL code strings to parse. 1213 If an `Expression` instance is passed, it will be used as-is. 1214 append (bool): if `True`, concatenate the new expressions to the existing "using" list. 1215 Otherwise, this resets the expression. 1216 dialect (str): the dialect used to parse the input expressions. 1217 copy (bool): if `False`, modify this expression instance in-place. 1218 opts (kwargs): other options to use to parse the input expressions. 1219 1220 Returns: 1221 Join: the modified join expression. 1222 """ 1223 join = _apply_list_builder( 1224 *expressions, 1225 instance=self, 1226 arg="using", 1227 append=append, 1228 dialect=dialect, 1229 copy=copy, 1230 **opts, 1231 ) 1232 1233 if join.kind == "CROSS": 1234 join.set("kind", None) 1235 1236 return join
Append to or set the USING expressions.
Example:
>>> import sqlglot >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql() 'JOIN x USING (foo, bla)'
Arguments:
- *expressions (str | Expression): the SQL code strings to parse.
If an
Expression
instance is passed, it will be used as-is. - append (bool): if
True
, concatenate the new expressions to the existing "using" list. Otherwise, this resets the expression. - dialect (str): the dialect used to parse the input expressions.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Join: the modified join expression.
1239class Lateral(UDTF): 1240 arg_types = {"this": True, "view": False, "outer": False, "alias": False}
Inherited Members
1371class RowFormatDelimitedProperty(Property): 1372 # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml 1373 arg_types = { 1374 "fields": False, 1375 "escaped": False, 1376 "collection_items": False, 1377 "map_keys": False, 1378 "lines": False, 1379 "null": False, 1380 "serde": False, 1381 }
1442class Properties(Expression): 1443 arg_types = {"expressions": True} 1444 1445 NAME_TO_PROPERTY = { 1446 "ALGORITHM": AlgorithmProperty, 1447 "AUTO_INCREMENT": AutoIncrementProperty, 1448 "CHARACTER SET": CharacterSetProperty, 1449 "COLLATE": CollateProperty, 1450 "COMMENT": SchemaCommentProperty, 1451 "DEFINER": DefinerProperty, 1452 "DISTKEY": DistKeyProperty, 1453 "DISTSTYLE": DistStyleProperty, 1454 "ENGINE": EngineProperty, 1455 "EXECUTE AS": ExecuteAsProperty, 1456 "FORMAT": FileFormatProperty, 1457 "LANGUAGE": LanguageProperty, 1458 "LOCATION": LocationProperty, 1459 "PARTITIONED_BY": PartitionedByProperty, 1460 "RETURNS": ReturnsProperty, 1461 "SORTKEY": SortKeyProperty, 1462 "TABLE_FORMAT": TableFormatProperty, 1463 } 1464 1465 PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()} 1466 1467 class Location(AutoName): 1468 POST_CREATE = auto() 1469 PRE_SCHEMA = auto() 1470 POST_INDEX = auto() 1471 POST_SCHEMA_ROOT = auto() 1472 POST_SCHEMA_WITH = auto() 1473 UNSUPPORTED = auto() 1474 1475 @classmethod 1476 def from_dict(cls, properties_dict) -> Properties: 1477 expressions = [] 1478 for key, value in properties_dict.items(): 1479 property_cls = cls.NAME_TO_PROPERTY.get(key.upper()) 1480 if property_cls: 1481 expressions.append(property_cls(this=convert(value))) 1482 else: 1483 expressions.append(Property(this=Literal.string(key), value=convert(value))) 1484 1485 return cls(expressions=expressions)
1475 @classmethod 1476 def from_dict(cls, properties_dict) -> Properties: 1477 expressions = [] 1478 for key, value in properties_dict.items(): 1479 property_cls = cls.NAME_TO_PROPERTY.get(key.upper()) 1480 if property_cls: 1481 expressions.append(property_cls(this=convert(value))) 1482 else: 1483 expressions.append(Property(this=Literal.string(key), value=convert(value))) 1484 1485 return cls(expressions=expressions)
1467 class Location(AutoName): 1468 POST_CREATE = auto() 1469 PRE_SCHEMA = auto() 1470 POST_INDEX = auto() 1471 POST_SCHEMA_ROOT = auto() 1472 POST_SCHEMA_WITH = auto() 1473 UNSUPPORTED = auto()
An enumeration.
Inherited Members
- enum.Enum
- name
- value
1505class Subqueryable(Unionable): 1506 def subquery(self, alias=None, copy=True) -> Subquery: 1507 """ 1508 Convert this expression to an aliased expression that can be used as a Subquery. 1509 1510 Example: 1511 >>> subquery = Select().select("x").from_("tbl").subquery() 1512 >>> Select().select("x").from_(subquery).sql() 1513 'SELECT x FROM (SELECT x FROM tbl)' 1514 1515 Args: 1516 alias (str | Identifier): an optional alias for the subquery 1517 copy (bool): if `False`, modify this expression instance in-place. 1518 1519 Returns: 1520 Alias: the subquery 1521 """ 1522 instance = _maybe_copy(self, copy) 1523 return Subquery( 1524 this=instance, 1525 alias=TableAlias(this=to_identifier(alias)), 1526 ) 1527 1528 def limit(self, expression, dialect=None, copy=True, **opts) -> Select: 1529 raise NotImplementedError 1530 1531 @property 1532 def ctes(self): 1533 with_ = self.args.get("with") 1534 if not with_: 1535 return [] 1536 return with_.expressions 1537 1538 @property 1539 def selects(self): 1540 raise NotImplementedError("Subqueryable objects must implement `selects`") 1541 1542 @property 1543 def named_selects(self): 1544 raise NotImplementedError("Subqueryable objects must implement `named_selects`") 1545 1546 def with_( 1547 self, 1548 alias, 1549 as_, 1550 recursive=None, 1551 append=True, 1552 dialect=None, 1553 copy=True, 1554 **opts, 1555 ): 1556 """ 1557 Append to or set the common table expressions. 1558 1559 Example: 1560 >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql() 1561 'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2' 1562 1563 Args: 1564 alias (str | Expression): the SQL code string to parse as the table name. 1565 If an `Expression` instance is passed, this is used as-is. 1566 as_ (str | Expression): the SQL code string to parse as the table expression. 1567 If an `Expression` instance is passed, it will be used as-is. 1568 recursive (bool): set the RECURSIVE part of the expression. Defaults to `False`. 1569 append (bool): if `True`, add to any existing expressions. 1570 Otherwise, this resets the expressions. 1571 dialect (str): the dialect used to parse the input expression. 1572 copy (bool): if `False`, modify this expression instance in-place. 1573 opts (kwargs): other options to use to parse the input expressions. 1574 1575 Returns: 1576 Select: the modified expression. 1577 """ 1578 alias_expression = maybe_parse( 1579 alias, 1580 dialect=dialect, 1581 into=TableAlias, 1582 **opts, 1583 ) 1584 as_expression = maybe_parse( 1585 as_, 1586 dialect=dialect, 1587 **opts, 1588 ) 1589 cte = CTE( 1590 this=as_expression, 1591 alias=alias_expression, 1592 ) 1593 return _apply_child_list_builder( 1594 cte, 1595 instance=self, 1596 arg="with", 1597 append=append, 1598 copy=copy, 1599 into=With, 1600 properties={"recursive": recursive or False}, 1601 )
1506 def subquery(self, alias=None, copy=True) -> Subquery: 1507 """ 1508 Convert this expression to an aliased expression that can be used as a Subquery. 1509 1510 Example: 1511 >>> subquery = Select().select("x").from_("tbl").subquery() 1512 >>> Select().select("x").from_(subquery).sql() 1513 'SELECT x FROM (SELECT x FROM tbl)' 1514 1515 Args: 1516 alias (str | Identifier): an optional alias for the subquery 1517 copy (bool): if `False`, modify this expression instance in-place. 1518 1519 Returns: 1520 Alias: the subquery 1521 """ 1522 instance = _maybe_copy(self, copy) 1523 return Subquery( 1524 this=instance, 1525 alias=TableAlias(this=to_identifier(alias)), 1526 )
Convert this expression to an aliased expression that can be used as a Subquery.
Example:
>>> subquery = Select().select("x").from_("tbl").subquery() >>> Select().select("x").from_(subquery).sql() 'SELECT x FROM (SELECT x FROM tbl)'
Arguments:
- alias (str | Identifier): an optional alias for the subquery
- copy (bool): if
False
, modify this expression instance in-place.
Returns:
Alias: the subquery
1546 def with_( 1547 self, 1548 alias, 1549 as_, 1550 recursive=None, 1551 append=True, 1552 dialect=None, 1553 copy=True, 1554 **opts, 1555 ): 1556 """ 1557 Append to or set the common table expressions. 1558 1559 Example: 1560 >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql() 1561 'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2' 1562 1563 Args: 1564 alias (str | Expression): the SQL code string to parse as the table name. 1565 If an `Expression` instance is passed, this is used as-is. 1566 as_ (str | Expression): the SQL code string to parse as the table expression. 1567 If an `Expression` instance is passed, it will be used as-is. 1568 recursive (bool): set the RECURSIVE part of the expression. Defaults to `False`. 1569 append (bool): if `True`, add to any existing expressions. 1570 Otherwise, this resets the expressions. 1571 dialect (str): the dialect used to parse the input expression. 1572 copy (bool): if `False`, modify this expression instance in-place. 1573 opts (kwargs): other options to use to parse the input expressions. 1574 1575 Returns: 1576 Select: the modified expression. 1577 """ 1578 alias_expression = maybe_parse( 1579 alias, 1580 dialect=dialect, 1581 into=TableAlias, 1582 **opts, 1583 ) 1584 as_expression = maybe_parse( 1585 as_, 1586 dialect=dialect, 1587 **opts, 1588 ) 1589 cte = CTE( 1590 this=as_expression, 1591 alias=alias_expression, 1592 ) 1593 return _apply_child_list_builder( 1594 cte, 1595 instance=self, 1596 arg="with", 1597 append=append, 1598 copy=copy, 1599 into=With, 1600 properties={"recursive": recursive or False}, 1601 )
Append to or set the common table expressions.
Example:
>>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql() 'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
Arguments:
- alias (str | Expression): the SQL code string to parse as the table name.
If an
Expression
instance is passed, this is used as-is. - as_ (str | Expression): the SQL code string to parse as the table expression.
If an
Expression
instance is passed, it will be used as-is. - recursive (bool): set the RECURSIVE part of the expression. Defaults to
False
. - append (bool): if
True
, add to any existing expressions. Otherwise, this resets the expressions. - dialect (str): the dialect used to parse the input expression.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Select: the modified expression.
Inherited Members
1647class Union(Subqueryable): 1648 arg_types = { 1649 "with": False, 1650 "this": True, 1651 "expression": True, 1652 "distinct": False, 1653 **QUERY_MODIFIERS, 1654 } 1655 1656 def limit(self, expression, dialect=None, copy=True, **opts) -> Select: 1657 """ 1658 Set the LIMIT expression. 1659 1660 Example: 1661 >>> select("1").union(select("1")).limit(1).sql() 1662 'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1' 1663 1664 Args: 1665 expression (str | int | Expression): the SQL code string to parse. 1666 This can also be an integer. 1667 If a `Limit` instance is passed, this is used as-is. 1668 If another `Expression` instance is passed, it will be wrapped in a `Limit`. 1669 dialect (str): the dialect used to parse the input expression. 1670 copy (bool): if `False`, modify this expression instance in-place. 1671 opts (kwargs): other options to use to parse the input expressions. 1672 1673 Returns: 1674 Select: The limited subqueryable. 1675 """ 1676 return ( 1677 select("*") 1678 .from_(self.subquery(alias="_l_0", copy=copy)) 1679 .limit(expression, dialect=dialect, copy=False, **opts) 1680 ) 1681 1682 @property 1683 def named_selects(self): 1684 return self.this.unnest().named_selects 1685 1686 @property 1687 def selects(self): 1688 return self.this.unnest().selects 1689 1690 @property 1691 def left(self): 1692 return self.this 1693 1694 @property 1695 def right(self): 1696 return self.expression
1656 def limit(self, expression, dialect=None, copy=True, **opts) -> Select: 1657 """ 1658 Set the LIMIT expression. 1659 1660 Example: 1661 >>> select("1").union(select("1")).limit(1).sql() 1662 'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1' 1663 1664 Args: 1665 expression (str | int | Expression): the SQL code string to parse. 1666 This can also be an integer. 1667 If a `Limit` instance is passed, this is used as-is. 1668 If another `Expression` instance is passed, it will be wrapped in a `Limit`. 1669 dialect (str): the dialect used to parse the input expression. 1670 copy (bool): if `False`, modify this expression instance in-place. 1671 opts (kwargs): other options to use to parse the input expressions. 1672 1673 Returns: 1674 Select: The limited subqueryable. 1675 """ 1676 return ( 1677 select("*") 1678 .from_(self.subquery(alias="_l_0", copy=copy)) 1679 .limit(expression, dialect=dialect, copy=False, **opts) 1680 )
Set the LIMIT expression.
Example:
>>> select("1").union(select("1")).limit(1).sql() 'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
Arguments:
- expression (str | int | Expression): the SQL code string to parse.
This can also be an integer.
If a
Limit
instance is passed, this is used as-is. If anotherExpression
instance is passed, it will be wrapped in aLimit
. - dialect (str): the dialect used to parse the input expression.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Select: The limited subqueryable.
Inherited Members
Inherited Members
Inherited Members
1707class Unnest(UDTF): 1708 arg_types = { 1709 "expressions": True, 1710 "ordinality": False, 1711 "alias": False, 1712 "offset": False, 1713 }
Inherited Members
1726class Values(UDTF): 1727 arg_types = { 1728 "expressions": True, 1729 "ordinality": False, 1730 "alias": False, 1731 }
Inherited Members
1748class Select(Subqueryable): 1749 arg_types = { 1750 "with": False, 1751 "expressions": False, 1752 "hint": False, 1753 "distinct": False, 1754 "into": False, 1755 "from": False, 1756 **QUERY_MODIFIERS, 1757 } 1758 1759 def from_(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1760 """ 1761 Set the FROM expression. 1762 1763 Example: 1764 >>> Select().from_("tbl").select("x").sql() 1765 'SELECT x FROM tbl' 1766 1767 Args: 1768 *expressions (str | Expression): the SQL code strings to parse. 1769 If a `From` instance is passed, this is used as-is. 1770 If another `Expression` instance is passed, it will be wrapped in a `From`. 1771 append (bool): if `True`, add to any existing expressions. 1772 Otherwise, this flattens all the `From` expression into a single expression. 1773 dialect (str): the dialect used to parse the input expression. 1774 copy (bool): if `False`, modify this expression instance in-place. 1775 opts (kwargs): other options to use to parse the input expressions. 1776 1777 Returns: 1778 Select: the modified expression. 1779 """ 1780 return _apply_child_list_builder( 1781 *expressions, 1782 instance=self, 1783 arg="from", 1784 append=append, 1785 copy=copy, 1786 prefix="FROM", 1787 into=From, 1788 dialect=dialect, 1789 **opts, 1790 ) 1791 1792 def group_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1793 """ 1794 Set the GROUP BY expression. 1795 1796 Example: 1797 >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql() 1798 'SELECT x, COUNT(1) FROM tbl GROUP BY x' 1799 1800 Args: 1801 *expressions (str | Expression): the SQL code strings to parse. 1802 If a `Group` instance is passed, this is used as-is. 1803 If another `Expression` instance is passed, it will be wrapped in a `Group`. 1804 If nothing is passed in then a group by is not applied to the expression 1805 append (bool): if `True`, add to any existing expressions. 1806 Otherwise, this flattens all the `Group` expression into a single expression. 1807 dialect (str): the dialect used to parse the input expression. 1808 copy (bool): if `False`, modify this expression instance in-place. 1809 opts (kwargs): other options to use to parse the input expressions. 1810 1811 Returns: 1812 Select: the modified expression. 1813 """ 1814 if not expressions: 1815 return self if not copy else self.copy() 1816 return _apply_child_list_builder( 1817 *expressions, 1818 instance=self, 1819 arg="group", 1820 append=append, 1821 copy=copy, 1822 prefix="GROUP BY", 1823 into=Group, 1824 dialect=dialect, 1825 **opts, 1826 ) 1827 1828 def order_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1829 """ 1830 Set the ORDER BY expression. 1831 1832 Example: 1833 >>> Select().from_("tbl").select("x").order_by("x DESC").sql() 1834 'SELECT x FROM tbl ORDER BY x DESC' 1835 1836 Args: 1837 *expressions (str | Expression): the SQL code strings to parse. 1838 If a `Group` instance is passed, this is used as-is. 1839 If another `Expression` instance is passed, it will be wrapped in a `Order`. 1840 append (bool): if `True`, add to any existing expressions. 1841 Otherwise, this flattens all the `Order` expression into a single expression. 1842 dialect (str): the dialect used to parse the input expression. 1843 copy (bool): if `False`, modify this expression instance in-place. 1844 opts (kwargs): other options to use to parse the input expressions. 1845 1846 Returns: 1847 Select: the modified expression. 1848 """ 1849 return _apply_child_list_builder( 1850 *expressions, 1851 instance=self, 1852 arg="order", 1853 append=append, 1854 copy=copy, 1855 prefix="ORDER BY", 1856 into=Order, 1857 dialect=dialect, 1858 **opts, 1859 ) 1860 1861 def sort_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1862 """ 1863 Set the SORT BY expression. 1864 1865 Example: 1866 >>> Select().from_("tbl").select("x").sort_by("x DESC").sql() 1867 'SELECT x FROM tbl SORT BY x DESC' 1868 1869 Args: 1870 *expressions (str | Expression): the SQL code strings to parse. 1871 If a `Group` instance is passed, this is used as-is. 1872 If another `Expression` instance is passed, it will be wrapped in a `SORT`. 1873 append (bool): if `True`, add to any existing expressions. 1874 Otherwise, this flattens all the `Order` expression into a single expression. 1875 dialect (str): the dialect used to parse the input expression. 1876 copy (bool): if `False`, modify this expression instance in-place. 1877 opts (kwargs): other options to use to parse the input expressions. 1878 1879 Returns: 1880 Select: the modified expression. 1881 """ 1882 return _apply_child_list_builder( 1883 *expressions, 1884 instance=self, 1885 arg="sort", 1886 append=append, 1887 copy=copy, 1888 prefix="SORT BY", 1889 into=Sort, 1890 dialect=dialect, 1891 **opts, 1892 ) 1893 1894 def cluster_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1895 """ 1896 Set the CLUSTER BY expression. 1897 1898 Example: 1899 >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql() 1900 'SELECT x FROM tbl CLUSTER BY x DESC' 1901 1902 Args: 1903 *expressions (str | Expression): the SQL code strings to parse. 1904 If a `Group` instance is passed, this is used as-is. 1905 If another `Expression` instance is passed, it will be wrapped in a `Cluster`. 1906 append (bool): if `True`, add to any existing expressions. 1907 Otherwise, this flattens all the `Order` expression into a single expression. 1908 dialect (str): the dialect used to parse the input expression. 1909 copy (bool): if `False`, modify this expression instance in-place. 1910 opts (kwargs): other options to use to parse the input expressions. 1911 1912 Returns: 1913 Select: the modified expression. 1914 """ 1915 return _apply_child_list_builder( 1916 *expressions, 1917 instance=self, 1918 arg="cluster", 1919 append=append, 1920 copy=copy, 1921 prefix="CLUSTER BY", 1922 into=Cluster, 1923 dialect=dialect, 1924 **opts, 1925 ) 1926 1927 def limit(self, expression, dialect=None, copy=True, **opts) -> Select: 1928 """ 1929 Set the LIMIT expression. 1930 1931 Example: 1932 >>> Select().from_("tbl").select("x").limit(10).sql() 1933 'SELECT x FROM tbl LIMIT 10' 1934 1935 Args: 1936 expression (str | int | Expression): the SQL code string to parse. 1937 This can also be an integer. 1938 If a `Limit` instance is passed, this is used as-is. 1939 If another `Expression` instance is passed, it will be wrapped in a `Limit`. 1940 dialect (str): the dialect used to parse the input expression. 1941 copy (bool): if `False`, modify this expression instance in-place. 1942 opts (kwargs): other options to use to parse the input expressions. 1943 1944 Returns: 1945 Select: the modified expression. 1946 """ 1947 return _apply_builder( 1948 expression=expression, 1949 instance=self, 1950 arg="limit", 1951 into=Limit, 1952 prefix="LIMIT", 1953 dialect=dialect, 1954 copy=copy, 1955 **opts, 1956 ) 1957 1958 def offset(self, expression, dialect=None, copy=True, **opts) -> Select: 1959 """ 1960 Set the OFFSET expression. 1961 1962 Example: 1963 >>> Select().from_("tbl").select("x").offset(10).sql() 1964 'SELECT x FROM tbl OFFSET 10' 1965 1966 Args: 1967 expression (str | int | Expression): the SQL code string to parse. 1968 This can also be an integer. 1969 If a `Offset` instance is passed, this is used as-is. 1970 If another `Expression` instance is passed, it will be wrapped in a `Offset`. 1971 dialect (str): the dialect used to parse the input expression. 1972 copy (bool): if `False`, modify this expression instance in-place. 1973 opts (kwargs): other options to use to parse the input expressions. 1974 1975 Returns: 1976 Select: the modified expression. 1977 """ 1978 return _apply_builder( 1979 expression=expression, 1980 instance=self, 1981 arg="offset", 1982 into=Offset, 1983 prefix="OFFSET", 1984 dialect=dialect, 1985 copy=copy, 1986 **opts, 1987 ) 1988 1989 def select(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1990 """ 1991 Append to or set the SELECT expressions. 1992 1993 Example: 1994 >>> Select().select("x", "y").sql() 1995 'SELECT x, y' 1996 1997 Args: 1998 *expressions (str | Expression): the SQL code strings to parse. 1999 If an `Expression` instance is passed, it will be used as-is. 2000 append (bool): if `True`, add to any existing expressions. 2001 Otherwise, this resets the expressions. 2002 dialect (str): the dialect used to parse the input expressions. 2003 copy (bool): if `False`, modify this expression instance in-place. 2004 opts (kwargs): other options to use to parse the input expressions. 2005 2006 Returns: 2007 Select: the modified expression. 2008 """ 2009 return _apply_list_builder( 2010 *expressions, 2011 instance=self, 2012 arg="expressions", 2013 append=append, 2014 dialect=dialect, 2015 copy=copy, 2016 **opts, 2017 ) 2018 2019 def lateral(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 2020 """ 2021 Append to or set the LATERAL expressions. 2022 2023 Example: 2024 >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql() 2025 'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z' 2026 2027 Args: 2028 *expressions (str | Expression): the SQL code strings to parse. 2029 If an `Expression` instance is passed, it will be used as-is. 2030 append (bool): if `True`, add to any existing expressions. 2031 Otherwise, this resets the expressions. 2032 dialect (str): the dialect used to parse the input expressions. 2033 copy (bool): if `False`, modify this expression instance in-place. 2034 opts (kwargs): other options to use to parse the input expressions. 2035 2036 Returns: 2037 Select: the modified expression. 2038 """ 2039 return _apply_list_builder( 2040 *expressions, 2041 instance=self, 2042 arg="laterals", 2043 append=append, 2044 into=Lateral, 2045 prefix="LATERAL VIEW", 2046 dialect=dialect, 2047 copy=copy, 2048 **opts, 2049 ) 2050 2051 def join( 2052 self, 2053 expression, 2054 on=None, 2055 using=None, 2056 append=True, 2057 join_type=None, 2058 join_alias=None, 2059 dialect=None, 2060 copy=True, 2061 **opts, 2062 ) -> Select: 2063 """ 2064 Append to or set the JOIN expressions. 2065 2066 Example: 2067 >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql() 2068 'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y' 2069 2070 >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql() 2071 'SELECT 1 FROM a JOIN b USING (x, y, z)' 2072 2073 Use `join_type` to change the type of join: 2074 2075 >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql() 2076 'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y' 2077 2078 Args: 2079 expression (str | Expression): the SQL code string to parse. 2080 If an `Expression` instance is passed, it will be used as-is. 2081 on (str | Expression): optionally specify the join "on" criteria as a SQL string. 2082 If an `Expression` instance is passed, it will be used as-is. 2083 using (str | Expression): optionally specify the join "using" criteria as a SQL string. 2084 If an `Expression` instance is passed, it will be used as-is. 2085 append (bool): if `True`, add to any existing expressions. 2086 Otherwise, this resets the expressions. 2087 join_type (str): If set, alter the parsed join type 2088 dialect (str): the dialect used to parse the input expressions. 2089 copy (bool): if `False`, modify this expression instance in-place. 2090 opts (kwargs): other options to use to parse the input expressions. 2091 2092 Returns: 2093 Select: the modified expression. 2094 """ 2095 parse_args = {"dialect": dialect, **opts} 2096 2097 try: 2098 expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args) 2099 except ParseError: 2100 expression = maybe_parse(expression, into=(Join, Expression), **parse_args) 2101 2102 join = expression if isinstance(expression, Join) else Join(this=expression) 2103 2104 if isinstance(join.this, Select): 2105 join.this.replace(join.this.subquery()) 2106 2107 if join_type: 2108 natural: t.Optional[Token] 2109 side: t.Optional[Token] 2110 kind: t.Optional[Token] 2111 2112 natural, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args) # type: ignore 2113 2114 if natural: 2115 join.set("natural", True) 2116 if side: 2117 join.set("side", side.text) 2118 if kind: 2119 join.set("kind", kind.text) 2120 2121 if on: 2122 on = and_(*ensure_collection(on), dialect=dialect, **opts) 2123 join.set("on", on) 2124 2125 if using: 2126 join = _apply_list_builder( 2127 *ensure_collection(using), 2128 instance=join, 2129 arg="using", 2130 append=append, 2131 copy=copy, 2132 **opts, 2133 ) 2134 2135 if join_alias: 2136 join.set("this", alias_(join.this, join_alias, table=True)) 2137 return _apply_list_builder( 2138 join, 2139 instance=self, 2140 arg="joins", 2141 append=append, 2142 copy=copy, 2143 **opts, 2144 ) 2145 2146 def where(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 2147 """ 2148 Append to or set the WHERE expressions. 2149 2150 Example: 2151 >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql() 2152 "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'" 2153 2154 Args: 2155 *expressions (str | Expression): the SQL code strings to parse. 2156 If an `Expression` instance is passed, it will be used as-is. 2157 Multiple expressions are combined with an AND operator. 2158 append (bool): if `True`, AND the new expressions to any existing expression. 2159 Otherwise, this resets the expression. 2160 dialect (str): the dialect used to parse the input expressions. 2161 copy (bool): if `False`, modify this expression instance in-place. 2162 opts (kwargs): other options to use to parse the input expressions. 2163 2164 Returns: 2165 Select: the modified expression. 2166 """ 2167 return _apply_conjunction_builder( 2168 *expressions, 2169 instance=self, 2170 arg="where", 2171 append=append, 2172 into=Where, 2173 dialect=dialect, 2174 copy=copy, 2175 **opts, 2176 ) 2177 2178 def having(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 2179 """ 2180 Append to or set the HAVING expressions. 2181 2182 Example: 2183 >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql() 2184 'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3' 2185 2186 Args: 2187 *expressions (str | Expression): the SQL code strings to parse. 2188 If an `Expression` instance is passed, it will be used as-is. 2189 Multiple expressions are combined with an AND operator. 2190 append (bool): if `True`, AND the new expressions to any existing expression. 2191 Otherwise, this resets the expression. 2192 dialect (str): the dialect used to parse the input expressions. 2193 copy (bool): if `False`, modify this expression instance in-place. 2194 opts (kwargs): other options to use to parse the input expressions. 2195 2196 Returns: 2197 Select: the modified expression. 2198 """ 2199 return _apply_conjunction_builder( 2200 *expressions, 2201 instance=self, 2202 arg="having", 2203 append=append, 2204 into=Having, 2205 dialect=dialect, 2206 copy=copy, 2207 **opts, 2208 ) 2209 2210 def window(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 2211 return _apply_list_builder( 2212 *expressions, 2213 instance=self, 2214 arg="windows", 2215 append=append, 2216 into=Window, 2217 dialect=dialect, 2218 copy=copy, 2219 **opts, 2220 ) 2221 2222 def distinct(self, distinct=True, copy=True) -> Select: 2223 """ 2224 Set the OFFSET expression. 2225 2226 Example: 2227 >>> Select().from_("tbl").select("x").distinct().sql() 2228 'SELECT DISTINCT x FROM tbl' 2229 2230 Args: 2231 distinct (bool): whether the Select should be distinct 2232 copy (bool): if `False`, modify this expression instance in-place. 2233 2234 Returns: 2235 Select: the modified expression. 2236 """ 2237 instance = _maybe_copy(self, copy) 2238 instance.set("distinct", Distinct() if distinct else None) 2239 return instance 2240 2241 def ctas(self, table, properties=None, dialect=None, copy=True, **opts) -> Create: 2242 """ 2243 Convert this expression to a CREATE TABLE AS statement. 2244 2245 Example: 2246 >>> Select().select("*").from_("tbl").ctas("x").sql() 2247 'CREATE TABLE x AS SELECT * FROM tbl' 2248 2249 Args: 2250 table (str | Expression): the SQL code string to parse as the table name. 2251 If another `Expression` instance is passed, it will be used as-is. 2252 properties (dict): an optional mapping of table properties 2253 dialect (str): the dialect used to parse the input table. 2254 copy (bool): if `False`, modify this expression instance in-place. 2255 opts (kwargs): other options to use to parse the input table. 2256 2257 Returns: 2258 Create: the CREATE TABLE AS expression 2259 """ 2260 instance = _maybe_copy(self, copy) 2261 table_expression = maybe_parse( 2262 table, 2263 into=Table, 2264 dialect=dialect, 2265 **opts, 2266 ) 2267 properties_expression = None 2268 if properties: 2269 properties_expression = Properties.from_dict(properties) 2270 2271 return Create( 2272 this=table_expression, 2273 kind="table", 2274 expression=instance, 2275 properties=properties_expression, 2276 ) 2277 2278 def lock(self, update: bool = True, copy: bool = True) -> Select: 2279 """ 2280 Set the locking read mode for this expression. 2281 2282 Examples: 2283 >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql") 2284 "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE" 2285 2286 >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql") 2287 "SELECT x FROM tbl WHERE x = 'a' FOR SHARE" 2288 2289 Args: 2290 update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`. 2291 copy: if `False`, modify this expression instance in-place. 2292 2293 Returns: 2294 The modified expression. 2295 """ 2296 2297 inst = _maybe_copy(self, copy) 2298 inst.set("lock", Lock(update=update)) 2299 2300 return inst 2301 2302 @property 2303 def named_selects(self) -> t.List[str]: 2304 return [e.output_name for e in self.expressions if e.alias_or_name] 2305 2306 @property 2307 def selects(self) -> t.List[Expression]: 2308 return self.expressions
1759 def from_(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1760 """ 1761 Set the FROM expression. 1762 1763 Example: 1764 >>> Select().from_("tbl").select("x").sql() 1765 'SELECT x FROM tbl' 1766 1767 Args: 1768 *expressions (str | Expression): the SQL code strings to parse. 1769 If a `From` instance is passed, this is used as-is. 1770 If another `Expression` instance is passed, it will be wrapped in a `From`. 1771 append (bool): if `True`, add to any existing expressions. 1772 Otherwise, this flattens all the `From` expression into a single expression. 1773 dialect (str): the dialect used to parse the input expression. 1774 copy (bool): if `False`, modify this expression instance in-place. 1775 opts (kwargs): other options to use to parse the input expressions. 1776 1777 Returns: 1778 Select: the modified expression. 1779 """ 1780 return _apply_child_list_builder( 1781 *expressions, 1782 instance=self, 1783 arg="from", 1784 append=append, 1785 copy=copy, 1786 prefix="FROM", 1787 into=From, 1788 dialect=dialect, 1789 **opts, 1790 )
Set the FROM expression.
Example:
>>> Select().from_("tbl").select("x").sql() 'SELECT x FROM tbl'
Arguments:
- *expressions (str | Expression): the SQL code strings to parse.
If a
From
instance is passed, this is used as-is. If anotherExpression
instance is passed, it will be wrapped in aFrom
. - append (bool): if
True
, add to any existing expressions. Otherwise, this flattens all theFrom
expression into a single expression. - dialect (str): the dialect used to parse the input expression.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Select: the modified expression.
1792 def group_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1793 """ 1794 Set the GROUP BY expression. 1795 1796 Example: 1797 >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql() 1798 'SELECT x, COUNT(1) FROM tbl GROUP BY x' 1799 1800 Args: 1801 *expressions (str | Expression): the SQL code strings to parse. 1802 If a `Group` instance is passed, this is used as-is. 1803 If another `Expression` instance is passed, it will be wrapped in a `Group`. 1804 If nothing is passed in then a group by is not applied to the expression 1805 append (bool): if `True`, add to any existing expressions. 1806 Otherwise, this flattens all the `Group` expression into a single expression. 1807 dialect (str): the dialect used to parse the input expression. 1808 copy (bool): if `False`, modify this expression instance in-place. 1809 opts (kwargs): other options to use to parse the input expressions. 1810 1811 Returns: 1812 Select: the modified expression. 1813 """ 1814 if not expressions: 1815 return self if not copy else self.copy() 1816 return _apply_child_list_builder( 1817 *expressions, 1818 instance=self, 1819 arg="group", 1820 append=append, 1821 copy=copy, 1822 prefix="GROUP BY", 1823 into=Group, 1824 dialect=dialect, 1825 **opts, 1826 )
Set the GROUP BY expression.
Example:
>>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql() 'SELECT x, COUNT(1) FROM tbl GROUP BY x'
Arguments:
- *expressions (str | Expression): the SQL code strings to parse.
If a
Group
instance is passed, this is used as-is. If anotherExpression
instance is passed, it will be wrapped in aGroup
. If nothing is passed in then a group by is not applied to the expression - append (bool): if
True
, add to any existing expressions. Otherwise, this flattens all theGroup
expression into a single expression. - dialect (str): the dialect used to parse the input expression.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Select: the modified expression.
1828 def order_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1829 """ 1830 Set the ORDER BY expression. 1831 1832 Example: 1833 >>> Select().from_("tbl").select("x").order_by("x DESC").sql() 1834 'SELECT x FROM tbl ORDER BY x DESC' 1835 1836 Args: 1837 *expressions (str | Expression): the SQL code strings to parse. 1838 If a `Group` instance is passed, this is used as-is. 1839 If another `Expression` instance is passed, it will be wrapped in a `Order`. 1840 append (bool): if `True`, add to any existing expressions. 1841 Otherwise, this flattens all the `Order` expression into a single expression. 1842 dialect (str): the dialect used to parse the input expression. 1843 copy (bool): if `False`, modify this expression instance in-place. 1844 opts (kwargs): other options to use to parse the input expressions. 1845 1846 Returns: 1847 Select: the modified expression. 1848 """ 1849 return _apply_child_list_builder( 1850 *expressions, 1851 instance=self, 1852 arg="order", 1853 append=append, 1854 copy=copy, 1855 prefix="ORDER BY", 1856 into=Order, 1857 dialect=dialect, 1858 **opts, 1859 )
Set the ORDER BY expression.
Example:
>>> Select().from_("tbl").select("x").order_by("x DESC").sql() 'SELECT x FROM tbl ORDER BY x DESC'
Arguments:
- *expressions (str | Expression): the SQL code strings to parse.
If a
Group
instance is passed, this is used as-is. If anotherExpression
instance is passed, it will be wrapped in aOrder
. - append (bool): if
True
, add to any existing expressions. Otherwise, this flattens all theOrder
expression into a single expression. - dialect (str): the dialect used to parse the input expression.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Select: the modified expression.
1861 def sort_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1862 """ 1863 Set the SORT BY expression. 1864 1865 Example: 1866 >>> Select().from_("tbl").select("x").sort_by("x DESC").sql() 1867 'SELECT x FROM tbl SORT BY x DESC' 1868 1869 Args: 1870 *expressions (str | Expression): the SQL code strings to parse. 1871 If a `Group` instance is passed, this is used as-is. 1872 If another `Expression` instance is passed, it will be wrapped in a `SORT`. 1873 append (bool): if `True`, add to any existing expressions. 1874 Otherwise, this flattens all the `Order` expression into a single expression. 1875 dialect (str): the dialect used to parse the input expression. 1876 copy (bool): if `False`, modify this expression instance in-place. 1877 opts (kwargs): other options to use to parse the input expressions. 1878 1879 Returns: 1880 Select: the modified expression. 1881 """ 1882 return _apply_child_list_builder( 1883 *expressions, 1884 instance=self, 1885 arg="sort", 1886 append=append, 1887 copy=copy, 1888 prefix="SORT BY", 1889 into=Sort, 1890 dialect=dialect, 1891 **opts, 1892 )
Set the SORT BY expression.
Example:
>>> Select().from_("tbl").select("x").sort_by("x DESC").sql() 'SELECT x FROM tbl SORT BY x DESC'
Arguments:
- *expressions (str | Expression): the SQL code strings to parse.
If a
Group
instance is passed, this is used as-is. If anotherExpression
instance is passed, it will be wrapped in aSORT
. - append (bool): if
True
, add to any existing expressions. Otherwise, this flattens all theOrder
expression into a single expression. - dialect (str): the dialect used to parse the input expression.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Select: the modified expression.
1894 def cluster_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1895 """ 1896 Set the CLUSTER BY expression. 1897 1898 Example: 1899 >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql() 1900 'SELECT x FROM tbl CLUSTER BY x DESC' 1901 1902 Args: 1903 *expressions (str | Expression): the SQL code strings to parse. 1904 If a `Group` instance is passed, this is used as-is. 1905 If another `Expression` instance is passed, it will be wrapped in a `Cluster`. 1906 append (bool): if `True`, add to any existing expressions. 1907 Otherwise, this flattens all the `Order` expression into a single expression. 1908 dialect (str): the dialect used to parse the input expression. 1909 copy (bool): if `False`, modify this expression instance in-place. 1910 opts (kwargs): other options to use to parse the input expressions. 1911 1912 Returns: 1913 Select: the modified expression. 1914 """ 1915 return _apply_child_list_builder( 1916 *expressions, 1917 instance=self, 1918 arg="cluster", 1919 append=append, 1920 copy=copy, 1921 prefix="CLUSTER BY", 1922 into=Cluster, 1923 dialect=dialect, 1924 **opts, 1925 )
Set the CLUSTER BY expression.
Example:
>>> Select().from_("tbl").select("x").cluster_by("x DESC").sql() 'SELECT x FROM tbl CLUSTER BY x DESC'
Arguments:
- *expressions (str | Expression): the SQL code strings to parse.
If a
Group
instance is passed, this is used as-is. If anotherExpression
instance is passed, it will be wrapped in aCluster
. - append (bool): if
True
, add to any existing expressions. Otherwise, this flattens all theOrder
expression into a single expression. - dialect (str): the dialect used to parse the input expression.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Select: the modified expression.
1927 def limit(self, expression, dialect=None, copy=True, **opts) -> Select: 1928 """ 1929 Set the LIMIT expression. 1930 1931 Example: 1932 >>> Select().from_("tbl").select("x").limit(10).sql() 1933 'SELECT x FROM tbl LIMIT 10' 1934 1935 Args: 1936 expression (str | int | Expression): the SQL code string to parse. 1937 This can also be an integer. 1938 If a `Limit` instance is passed, this is used as-is. 1939 If another `Expression` instance is passed, it will be wrapped in a `Limit`. 1940 dialect (str): the dialect used to parse the input expression. 1941 copy (bool): if `False`, modify this expression instance in-place. 1942 opts (kwargs): other options to use to parse the input expressions. 1943 1944 Returns: 1945 Select: the modified expression. 1946 """ 1947 return _apply_builder( 1948 expression=expression, 1949 instance=self, 1950 arg="limit", 1951 into=Limit, 1952 prefix="LIMIT", 1953 dialect=dialect, 1954 copy=copy, 1955 **opts, 1956 )
Set the LIMIT expression.
Example:
>>> Select().from_("tbl").select("x").limit(10).sql() 'SELECT x FROM tbl LIMIT 10'
Arguments:
- expression (str | int | Expression): the SQL code string to parse.
This can also be an integer.
If a
Limit
instance is passed, this is used as-is. If anotherExpression
instance is passed, it will be wrapped in aLimit
. - dialect (str): the dialect used to parse the input expression.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Select: the modified expression.
1958 def offset(self, expression, dialect=None, copy=True, **opts) -> Select: 1959 """ 1960 Set the OFFSET expression. 1961 1962 Example: 1963 >>> Select().from_("tbl").select("x").offset(10).sql() 1964 'SELECT x FROM tbl OFFSET 10' 1965 1966 Args: 1967 expression (str | int | Expression): the SQL code string to parse. 1968 This can also be an integer. 1969 If a `Offset` instance is passed, this is used as-is. 1970 If another `Expression` instance is passed, it will be wrapped in a `Offset`. 1971 dialect (str): the dialect used to parse the input expression. 1972 copy (bool): if `False`, modify this expression instance in-place. 1973 opts (kwargs): other options to use to parse the input expressions. 1974 1975 Returns: 1976 Select: the modified expression. 1977 """ 1978 return _apply_builder( 1979 expression=expression, 1980 instance=self, 1981 arg="offset", 1982 into=Offset, 1983 prefix="OFFSET", 1984 dialect=dialect, 1985 copy=copy, 1986 **opts, 1987 )
Set the OFFSET expression.
Example:
>>> Select().from_("tbl").select("x").offset(10).sql() 'SELECT x FROM tbl OFFSET 10'
Arguments:
- expression (str | int | Expression): the SQL code string to parse.
This can also be an integer.
If a
Offset
instance is passed, this is used as-is. If anotherExpression
instance is passed, it will be wrapped in aOffset
. - dialect (str): the dialect used to parse the input expression.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Select: the modified expression.
1989 def select(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 1990 """ 1991 Append to or set the SELECT expressions. 1992 1993 Example: 1994 >>> Select().select("x", "y").sql() 1995 'SELECT x, y' 1996 1997 Args: 1998 *expressions (str | Expression): the SQL code strings to parse. 1999 If an `Expression` instance is passed, it will be used as-is. 2000 append (bool): if `True`, add to any existing expressions. 2001 Otherwise, this resets the expressions. 2002 dialect (str): the dialect used to parse the input expressions. 2003 copy (bool): if `False`, modify this expression instance in-place. 2004 opts (kwargs): other options to use to parse the input expressions. 2005 2006 Returns: 2007 Select: the modified expression. 2008 """ 2009 return _apply_list_builder( 2010 *expressions, 2011 instance=self, 2012 arg="expressions", 2013 append=append, 2014 dialect=dialect, 2015 copy=copy, 2016 **opts, 2017 )
Append to or set the SELECT expressions.
Example:
>>> Select().select("x", "y").sql() 'SELECT x, y'
Arguments:
- *expressions (str | Expression): the SQL code strings to parse.
If an
Expression
instance is passed, it will be used as-is. - append (bool): if
True
, add to any existing expressions. Otherwise, this resets the expressions. - dialect (str): the dialect used to parse the input expressions.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Select: the modified expression.
2019 def lateral(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 2020 """ 2021 Append to or set the LATERAL expressions. 2022 2023 Example: 2024 >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql() 2025 'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z' 2026 2027 Args: 2028 *expressions (str | Expression): the SQL code strings to parse. 2029 If an `Expression` instance is passed, it will be used as-is. 2030 append (bool): if `True`, add to any existing expressions. 2031 Otherwise, this resets the expressions. 2032 dialect (str): the dialect used to parse the input expressions. 2033 copy (bool): if `False`, modify this expression instance in-place. 2034 opts (kwargs): other options to use to parse the input expressions. 2035 2036 Returns: 2037 Select: the modified expression. 2038 """ 2039 return _apply_list_builder( 2040 *expressions, 2041 instance=self, 2042 arg="laterals", 2043 append=append, 2044 into=Lateral, 2045 prefix="LATERAL VIEW", 2046 dialect=dialect, 2047 copy=copy, 2048 **opts, 2049 )
Append to or set the LATERAL expressions.
Example:
>>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql() 'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
Arguments:
- *expressions (str | Expression): the SQL code strings to parse.
If an
Expression
instance is passed, it will be used as-is. - append (bool): if
True
, add to any existing expressions. Otherwise, this resets the expressions. - dialect (str): the dialect used to parse the input expressions.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Select: the modified expression.
2051 def join( 2052 self, 2053 expression, 2054 on=None, 2055 using=None, 2056 append=True, 2057 join_type=None, 2058 join_alias=None, 2059 dialect=None, 2060 copy=True, 2061 **opts, 2062 ) -> Select: 2063 """ 2064 Append to or set the JOIN expressions. 2065 2066 Example: 2067 >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql() 2068 'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y' 2069 2070 >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql() 2071 'SELECT 1 FROM a JOIN b USING (x, y, z)' 2072 2073 Use `join_type` to change the type of join: 2074 2075 >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql() 2076 'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y' 2077 2078 Args: 2079 expression (str | Expression): the SQL code string to parse. 2080 If an `Expression` instance is passed, it will be used as-is. 2081 on (str | Expression): optionally specify the join "on" criteria as a SQL string. 2082 If an `Expression` instance is passed, it will be used as-is. 2083 using (str | Expression): optionally specify the join "using" criteria as a SQL string. 2084 If an `Expression` instance is passed, it will be used as-is. 2085 append (bool): if `True`, add to any existing expressions. 2086 Otherwise, this resets the expressions. 2087 join_type (str): If set, alter the parsed join type 2088 dialect (str): the dialect used to parse the input expressions. 2089 copy (bool): if `False`, modify this expression instance in-place. 2090 opts (kwargs): other options to use to parse the input expressions. 2091 2092 Returns: 2093 Select: the modified expression. 2094 """ 2095 parse_args = {"dialect": dialect, **opts} 2096 2097 try: 2098 expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args) 2099 except ParseError: 2100 expression = maybe_parse(expression, into=(Join, Expression), **parse_args) 2101 2102 join = expression if isinstance(expression, Join) else Join(this=expression) 2103 2104 if isinstance(join.this, Select): 2105 join.this.replace(join.this.subquery()) 2106 2107 if join_type: 2108 natural: t.Optional[Token] 2109 side: t.Optional[Token] 2110 kind: t.Optional[Token] 2111 2112 natural, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args) # type: ignore 2113 2114 if natural: 2115 join.set("natural", True) 2116 if side: 2117 join.set("side", side.text) 2118 if kind: 2119 join.set("kind", kind.text) 2120 2121 if on: 2122 on = and_(*ensure_collection(on), dialect=dialect, **opts) 2123 join.set("on", on) 2124 2125 if using: 2126 join = _apply_list_builder( 2127 *ensure_collection(using), 2128 instance=join, 2129 arg="using", 2130 append=append, 2131 copy=copy, 2132 **opts, 2133 ) 2134 2135 if join_alias: 2136 join.set("this", alias_(join.this, join_alias, table=True)) 2137 return _apply_list_builder( 2138 join, 2139 instance=self, 2140 arg="joins", 2141 append=append, 2142 copy=copy, 2143 **opts, 2144 )
Append to or set the JOIN expressions.
Example:
>>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql() 'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
>>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql() 'SELECT 1 FROM a JOIN b USING (x, y, z)'
Use
join_type
to change the type of join:>>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql() 'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
Arguments:
- expression (str | Expression): the SQL code string to parse.
If an
Expression
instance is passed, it will be used as-is. - on (str | Expression): optionally specify the join "on" criteria as a SQL string.
If an
Expression
instance is passed, it will be used as-is. - using (str | Expression): optionally specify the join "using" criteria as a SQL string.
If an
Expression
instance is passed, it will be used as-is. - append (bool): if
True
, add to any existing expressions. Otherwise, this resets the expressions. - join_type (str): If set, alter the parsed join type
- dialect (str): the dialect used to parse the input expressions.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Select: the modified expression.
2146 def where(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 2147 """ 2148 Append to or set the WHERE expressions. 2149 2150 Example: 2151 >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql() 2152 "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'" 2153 2154 Args: 2155 *expressions (str | Expression): the SQL code strings to parse. 2156 If an `Expression` instance is passed, it will be used as-is. 2157 Multiple expressions are combined with an AND operator. 2158 append (bool): if `True`, AND the new expressions to any existing expression. 2159 Otherwise, this resets the expression. 2160 dialect (str): the dialect used to parse the input expressions. 2161 copy (bool): if `False`, modify this expression instance in-place. 2162 opts (kwargs): other options to use to parse the input expressions. 2163 2164 Returns: 2165 Select: the modified expression. 2166 """ 2167 return _apply_conjunction_builder( 2168 *expressions, 2169 instance=self, 2170 arg="where", 2171 append=append, 2172 into=Where, 2173 dialect=dialect, 2174 copy=copy, 2175 **opts, 2176 )
Append to or set the WHERE expressions.
Example:
>>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql() "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
Arguments:
- *expressions (str | Expression): the SQL code strings to parse.
If an
Expression
instance is passed, it will be used as-is. Multiple expressions are combined with an AND operator. - append (bool): if
True
, AND the new expressions to any existing expression. Otherwise, this resets the expression. - dialect (str): the dialect used to parse the input expressions.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Select: the modified expression.
2178 def having(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select: 2179 """ 2180 Append to or set the HAVING expressions. 2181 2182 Example: 2183 >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql() 2184 'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3' 2185 2186 Args: 2187 *expressions (str | Expression): the SQL code strings to parse. 2188 If an `Expression` instance is passed, it will be used as-is. 2189 Multiple expressions are combined with an AND operator. 2190 append (bool): if `True`, AND the new expressions to any existing expression. 2191 Otherwise, this resets the expression. 2192 dialect (str): the dialect used to parse the input expressions. 2193 copy (bool): if `False`, modify this expression instance in-place. 2194 opts (kwargs): other options to use to parse the input expressions. 2195 2196 Returns: 2197 Select: the modified expression. 2198 """ 2199 return _apply_conjunction_builder( 2200 *expressions, 2201 instance=self, 2202 arg="having", 2203 append=append, 2204 into=Having, 2205 dialect=dialect, 2206 copy=copy, 2207 **opts, 2208 )
Append to or set the HAVING expressions.
Example:
>>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql() 'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
Arguments:
- *expressions (str | Expression): the SQL code strings to parse.
If an
Expression
instance is passed, it will be used as-is. Multiple expressions are combined with an AND operator. - append (bool): if
True
, AND the new expressions to any existing expression. Otherwise, this resets the expression. - dialect (str): the dialect used to parse the input expressions.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input expressions.
Returns:
Select: the modified expression.
2222 def distinct(self, distinct=True, copy=True) -> Select: 2223 """ 2224 Set the OFFSET expression. 2225 2226 Example: 2227 >>> Select().from_("tbl").select("x").distinct().sql() 2228 'SELECT DISTINCT x FROM tbl' 2229 2230 Args: 2231 distinct (bool): whether the Select should be distinct 2232 copy (bool): if `False`, modify this expression instance in-place. 2233 2234 Returns: 2235 Select: the modified expression. 2236 """ 2237 instance = _maybe_copy(self, copy) 2238 instance.set("distinct", Distinct() if distinct else None) 2239 return instance
Set the OFFSET expression.
Example:
>>> Select().from_("tbl").select("x").distinct().sql() 'SELECT DISTINCT x FROM tbl'
Arguments:
- distinct (bool): whether the Select should be distinct
- copy (bool): if
False
, modify this expression instance in-place.
Returns:
Select: the modified expression.
2241 def ctas(self, table, properties=None, dialect=None, copy=True, **opts) -> Create: 2242 """ 2243 Convert this expression to a CREATE TABLE AS statement. 2244 2245 Example: 2246 >>> Select().select("*").from_("tbl").ctas("x").sql() 2247 'CREATE TABLE x AS SELECT * FROM tbl' 2248 2249 Args: 2250 table (str | Expression): the SQL code string to parse as the table name. 2251 If another `Expression` instance is passed, it will be used as-is. 2252 properties (dict): an optional mapping of table properties 2253 dialect (str): the dialect used to parse the input table. 2254 copy (bool): if `False`, modify this expression instance in-place. 2255 opts (kwargs): other options to use to parse the input table. 2256 2257 Returns: 2258 Create: the CREATE TABLE AS expression 2259 """ 2260 instance = _maybe_copy(self, copy) 2261 table_expression = maybe_parse( 2262 table, 2263 into=Table, 2264 dialect=dialect, 2265 **opts, 2266 ) 2267 properties_expression = None 2268 if properties: 2269 properties_expression = Properties.from_dict(properties) 2270 2271 return Create( 2272 this=table_expression, 2273 kind="table", 2274 expression=instance, 2275 properties=properties_expression, 2276 )
Convert this expression to a CREATE TABLE AS statement.
Example:
>>> Select().select("*").from_("tbl").ctas("x").sql() 'CREATE TABLE x AS SELECT * FROM tbl'
Arguments:
- table (str | Expression): the SQL code string to parse as the table name.
If another
Expression
instance is passed, it will be used as-is. - properties (dict): an optional mapping of table properties
- dialect (str): the dialect used to parse the input table.
- copy (bool): if
False
, modify this expression instance in-place. - opts (kwargs): other options to use to parse the input table.
Returns:
Create: the CREATE TABLE AS expression
2278 def lock(self, update: bool = True, copy: bool = True) -> Select: 2279 """ 2280 Set the locking read mode for this expression. 2281 2282 Examples: 2283 >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql") 2284 "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE" 2285 2286 >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql") 2287 "SELECT x FROM tbl WHERE x = 'a' FOR SHARE" 2288 2289 Args: 2290 update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`. 2291 copy: if `False`, modify this expression instance in-place. 2292 2293 Returns: 2294 The modified expression. 2295 """ 2296 2297 inst = _maybe_copy(self, copy) 2298 inst.set("lock", Lock(update=update)) 2299 2300 return inst
Set the locking read mode for this expression.
Examples:
>>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql") "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
>>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql") "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
Arguments:
- update: if
True
, the locking type will beFOR UPDATE
, else it will beFOR SHARE
. - copy: if
False
, modify this expression instance in-place.
Returns:
The modified expression.
Inherited Members
2311class Subquery(DerivedTable, Unionable): 2312 arg_types = { 2313 "this": True, 2314 "alias": False, 2315 "with": False, 2316 **QUERY_MODIFIERS, 2317 } 2318 2319 def unnest(self): 2320 """ 2321 Returns the first non subquery. 2322 """ 2323 expression = self 2324 while isinstance(expression, Subquery): 2325 expression = expression.this 2326 return expression 2327 2328 @property 2329 def output_name(self): 2330 return self.alias
2319 def unnest(self): 2320 """ 2321 Returns the first non subquery. 2322 """ 2323 expression = self 2324 while isinstance(expression, Subquery): 2325 expression = expression.this 2326 return expression
Returns the first non subquery.
Name of the output column if this expression is a selection.
If the Expression has no output name, an empty string is returned.
Example:
>>> from sqlglot import parse_one >>> parse_one("SELECT a").expressions[0].output_name 'a' >>> parse_one("SELECT b AS c").expressions[0].output_name 'c' >>> parse_one("SELECT 1 + 2").expressions[0].output_name ''
Inherited Members
2347class Tag(Expression): 2348 """Tags are used for generating arbitrary sql like SELECT <span>x</span>.""" 2349 2350 arg_types = { 2351 "this": False, 2352 "prefix": False, 2353 "postfix": False, 2354 }
Tags are used for generating arbitrary sql like SELECT x.
2390class Star(Expression): 2391 arg_types = {"except": False, "replace": False} 2392 2393 @property 2394 def name(self) -> str: 2395 return "*" 2396 2397 @property 2398 def output_name(self): 2399 return self.name
Name of the output column if this expression is a selection.
If the Expression has no output name, an empty string is returned.
Example:
>>> from sqlglot import parse_one >>> parse_one("SELECT a").expressions[0].output_name 'a' >>> parse_one("SELECT b AS c").expressions[0].output_name 'c' >>> parse_one("SELECT 1 + 2").expressions[0].output_name ''
2414class Null(Condition): 2415 arg_types: t.Dict[str, t.Any] = {} 2416 2417 @property 2418 def name(self) -> str: 2419 return "NULL"
Inherited Members
Inherited Members
2426class DataType(Expression): 2427 arg_types = { 2428 "this": True, 2429 "expressions": False, 2430 "nested": False, 2431 "values": False, 2432 } 2433 2434 class Type(AutoName): 2435 CHAR = auto() 2436 NCHAR = auto() 2437 VARCHAR = auto() 2438 NVARCHAR = auto() 2439 TEXT = auto() 2440 MEDIUMTEXT = auto() 2441 LONGTEXT = auto() 2442 MEDIUMBLOB = auto() 2443 LONGBLOB = auto() 2444 BINARY = auto() 2445 VARBINARY = auto() 2446 INT = auto() 2447 TINYINT = auto() 2448 SMALLINT = auto() 2449 BIGINT = auto() 2450 FLOAT = auto() 2451 DOUBLE = auto() 2452 DECIMAL = auto() 2453 BOOLEAN = auto() 2454 JSON = auto() 2455 JSONB = auto() 2456 INTERVAL = auto() 2457 TIME = auto() 2458 TIMESTAMP = auto() 2459 TIMESTAMPTZ = auto() 2460 TIMESTAMPLTZ = auto() 2461 DATE = auto() 2462 DATETIME = auto() 2463 ARRAY = auto() 2464 MAP = auto() 2465 UUID = auto() 2466 GEOGRAPHY = auto() 2467 GEOMETRY = auto() 2468 STRUCT = auto() 2469 NULLABLE = auto() 2470 HLLSKETCH = auto() 2471 HSTORE = auto() 2472 SUPER = auto() 2473 SERIAL = auto() 2474 SMALLSERIAL = auto() 2475 BIGSERIAL = auto() 2476 XML = auto() 2477 UNIQUEIDENTIFIER = auto() 2478 MONEY = auto() 2479 SMALLMONEY = auto() 2480 ROWVERSION = auto() 2481 IMAGE = auto() 2482 VARIANT = auto() 2483 OBJECT = auto() 2484 NULL = auto() 2485 UNKNOWN = auto() # Sentinel value, useful for type annotation 2486 2487 TEXT_TYPES = { 2488 Type.CHAR, 2489 Type.NCHAR, 2490 Type.VARCHAR, 2491 Type.NVARCHAR, 2492 Type.TEXT, 2493 } 2494 2495 INTEGER_TYPES = { 2496 Type.INT, 2497 Type.TINYINT, 2498 Type.SMALLINT, 2499 Type.BIGINT, 2500 } 2501 2502 FLOAT_TYPES = { 2503 Type.FLOAT, 2504 Type.DOUBLE, 2505 } 2506 2507 NUMERIC_TYPES = {*INTEGER_TYPES, *FLOAT_TYPES} 2508 2509 TEMPORAL_TYPES = { 2510 Type.TIMESTAMP, 2511 Type.TIMESTAMPTZ, 2512 Type.TIMESTAMPLTZ, 2513 Type.DATE, 2514 Type.DATETIME, 2515 } 2516 2517 @classmethod 2518 def build( 2519 cls, dtype: str | DataType | DataType.Type, dialect: DialectType = None, **kwargs 2520 ) -> DataType: 2521 from sqlglot import parse_one 2522 2523 if isinstance(dtype, str): 2524 if dtype.upper() in cls.Type.__members__: 2525 data_type_exp: t.Optional[Expression] = DataType(this=DataType.Type[dtype.upper()]) 2526 else: 2527 data_type_exp = parse_one(dtype, read=dialect, into=DataType) 2528 if data_type_exp is None: 2529 raise ValueError(f"Unparsable data type value: {dtype}") 2530 elif isinstance(dtype, DataType.Type): 2531 data_type_exp = DataType(this=dtype) 2532 elif isinstance(dtype, DataType): 2533 return dtype 2534 else: 2535 raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type") 2536 return DataType(**{**data_type_exp.args, **kwargs}) 2537 2538 def is_type(self, dtype: DataType.Type) -> bool: 2539 return self.this == dtype
2517 @classmethod 2518 def build( 2519 cls, dtype: str | DataType | DataType.Type, dialect: DialectType = None, **kwargs 2520 ) -> DataType: 2521 from sqlglot import parse_one 2522 2523 if isinstance(dtype, str): 2524 if dtype.upper() in cls.Type.__members__: 2525 data_type_exp: t.Optional[Expression] = DataType(this=DataType.Type[dtype.upper()]) 2526 else: 2527 data_type_exp = parse_one(dtype, read=dialect, into=DataType) 2528 if data_type_exp is None: 2529 raise ValueError(f"Unparsable data type value: {dtype}") 2530 elif isinstance(dtype, DataType.Type): 2531 data_type_exp = DataType(this=dtype) 2532 elif isinstance(dtype, DataType): 2533 return dtype 2534 else: 2535 raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type") 2536 return DataType(**{**data_type_exp.args, **kwargs})
2434 class Type(AutoName): 2435 CHAR = auto() 2436 NCHAR = auto() 2437 VARCHAR = auto() 2438 NVARCHAR = auto() 2439 TEXT = auto() 2440 MEDIUMTEXT = auto() 2441 LONGTEXT = auto() 2442 MEDIUMBLOB = auto() 2443 LONGBLOB = auto() 2444 BINARY = auto() 2445 VARBINARY = auto() 2446 INT = auto() 2447 TINYINT = auto() 2448 SMALLINT = auto() 2449 BIGINT = auto() 2450 FLOAT = auto() 2451 DOUBLE = auto() 2452 DECIMAL = auto() 2453 BOOLEAN = auto() 2454 JSON = auto() 2455 JSONB = auto() 2456 INTERVAL = auto() 2457 TIME = auto() 2458 TIMESTAMP = auto() 2459 TIMESTAMPTZ = auto() 2460 TIMESTAMPLTZ = auto() 2461 DATE = auto() 2462 DATETIME = auto() 2463 ARRAY = auto() 2464 MAP = auto() 2465 UUID = auto() 2466 GEOGRAPHY = auto() 2467 GEOMETRY = auto() 2468 STRUCT = auto() 2469 NULLABLE = auto() 2470 HLLSKETCH = auto() 2471 HSTORE = auto() 2472 SUPER = auto() 2473 SERIAL = auto() 2474 SMALLSERIAL = auto() 2475 BIGSERIAL = auto() 2476 XML = auto() 2477 UNIQUEIDENTIFIER = auto() 2478 MONEY = auto() 2479 SMALLMONEY = auto() 2480 ROWVERSION = auto() 2481 IMAGE = auto() 2482 VARIANT = auto() 2483 OBJECT = auto() 2484 NULL = auto() 2485 UNKNOWN = auto() # Sentinel value, useful for type annotation
An enumeration.
Inherited Members
- enum.Enum
- name
- value
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Kwarg in special functions like func(kwarg => y).
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
2768class Alias(Expression): 2769 arg_types = {"this": True, "alias": False} 2770 2771 @property 2772 def output_name(self): 2773 return self.alias
Name of the output column if this expression is a selection.
If the Expression has no output name, an empty string is returned.
Example:
>>> from sqlglot import parse_one >>> parse_one("SELECT a").expressions[0].output_name 'a' >>> parse_one("SELECT b AS c").expressions[0].output_name 'c' >>> parse_one("SELECT 1 + 2").expressions[0].output_name ''
Inherited Members
Inherited Members
2800class In(Predicate): 2801 arg_types = { 2802 "this": True, 2803 "expressions": False, 2804 "query": False, 2805 "unnest": False, 2806 "field": False, 2807 "is_global": False, 2808 }
Inherited Members
2811class TimeUnit(Expression): 2812 """Automatically converts unit arg into a var.""" 2813 2814 arg_types = {"unit": False} 2815 2816 def __init__(self, **args): 2817 unit = args.get("unit") 2818 if isinstance(unit, Column): 2819 args["unit"] = Var(this=unit.name) 2820 elif isinstance(unit, Week): 2821 unit.set("this", Var(this=unit.this.name)) 2822 super().__init__(**args)
Automatically converts unit arg into a var.
Inherited Members
2838class Func(Condition): 2839 """ 2840 The base class for all function expressions. 2841 2842 Attributes: 2843 is_var_len_args (bool): if set to True the last argument defined in arg_types will be 2844 treated as a variable length argument and the argument's value will be stored as a list. 2845 _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items) 2846 for this function expression. These values are used to map this node to a name during parsing 2847 as well as to provide the function's name during SQL string generation. By default the SQL 2848 name is set to the expression's class name transformed to snake case. 2849 """ 2850 2851 is_var_len_args = False 2852 2853 @classmethod 2854 def from_arg_list(cls, args): 2855 if cls.is_var_len_args: 2856 all_arg_keys = list(cls.arg_types) 2857 # If this function supports variable length argument treat the last argument as such. 2858 non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys 2859 num_non_var = len(non_var_len_arg_keys) 2860 2861 args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)} 2862 args_dict[all_arg_keys[-1]] = args[num_non_var:] 2863 else: 2864 args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)} 2865 2866 return cls(**args_dict) 2867 2868 @classmethod 2869 def sql_names(cls): 2870 if cls is Func: 2871 raise NotImplementedError( 2872 "SQL name is only supported by concrete function implementations" 2873 ) 2874 if "_sql_names" not in cls.__dict__: 2875 cls._sql_names = [camel_to_snake_case(cls.__name__)] 2876 return cls._sql_names 2877 2878 @classmethod 2879 def sql_name(cls): 2880 return cls.sql_names()[0] 2881 2882 @classmethod 2883 def default_parser_mappings(cls): 2884 return {name: cls.from_arg_list for name in cls.sql_names()}
The base class for all function expressions.
Attributes:
- is_var_len_args (bool): if set to True the last argument defined in arg_types will be treated as a variable length argument and the argument's value will be stored as a list.
- _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items) for this function expression. These values are used to map this node to a name during parsing as well as to provide the function's name during SQL string generation. By default the SQL name is set to the expression's class name transformed to snake case.
2853 @classmethod 2854 def from_arg_list(cls, args): 2855 if cls.is_var_len_args: 2856 all_arg_keys = list(cls.arg_types) 2857 # If this function supports variable length argument treat the last argument as such. 2858 non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys 2859 num_non_var = len(non_var_len_arg_keys) 2860 2861 args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)} 2862 args_dict[all_arg_keys[-1]] = args[num_non_var:] 2863 else: 2864 args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)} 2865 2866 return cls(**args_dict)
2868 @classmethod 2869 def sql_names(cls): 2870 if cls is Func: 2871 raise NotImplementedError( 2872 "SQL name is only supported by concrete function implementations" 2873 ) 2874 if "_sql_names" not in cls.__dict__: 2875 cls._sql_names = [camel_to_snake_case(cls.__name__)] 2876 return cls._sql_names
Inherited Members
Inherited Members
Inherited Members
2895class Anonymous(Func): 2896 arg_types = {"this": True, "expressions": False} 2897 is_var_len_args = True
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
2925class ArrayConcat(Func): 2926 arg_types = {"this": True, "expressions": False} 2927 is_var_len_args = True
Inherited Members
Inherited Members
2934class ArrayFilter(Func): 2935 arg_types = {"this": True, "expression": True} 2936 _sql_names = ["FILTER", "ARRAY_FILTER"]
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
2967class Cast(Func): 2968 arg_types = {"this": True, "to": True} 2969 2970 @property 2971 def name(self) -> str: 2972 return self.this.name 2973 2974 @property 2975 def to(self): 2976 return self.args["to"] 2977 2978 @property 2979 def output_name(self): 2980 return self.name 2981 2982 def is_type(self, dtype: DataType.Type) -> bool: 2983 return self.to.is_type(dtype)
Name of the output column if this expression is a selection.
If the Expression has no output name, an empty string is returned.
Example:
>>> from sqlglot import parse_one >>> parse_one("SELECT a").expressions[0].output_name 'a' >>> parse_one("SELECT b AS c").expressions[0].output_name 'c' >>> parse_one("SELECT 1 + 2").expressions[0].output_name ''
Inherited Members
Inherited Members
2994class Ceil(Func): 2995 arg_types = {"this": True, "decimals": False} 2996 _sql_names = ["CEIL", "CEILING"]
Inherited Members
2999class Coalesce(Func): 3000 arg_types = {"this": True, "expressions": False} 3001 is_var_len_args = True
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
3033class DateAdd(Func, TimeUnit): 3034 arg_types = {"this": True, "expression": True, "unit": False}
Inherited Members
3037class DateSub(Func, TimeUnit): 3038 arg_types = {"this": True, "expression": True, "unit": False}
Inherited Members
3041class DateDiff(Func, TimeUnit): 3042 arg_types = {"this": True, "expression": True, "unit": False}
Inherited Members
Inherited Members
3049class DatetimeAdd(Func, TimeUnit): 3050 arg_types = {"this": True, "expression": True, "unit": False}
Inherited Members
3053class DatetimeSub(Func, TimeUnit): 3054 arg_types = {"this": True, "expression": True, "unit": False}
Inherited Members
3057class DatetimeDiff(Func, TimeUnit): 3058 arg_types = {"this": True, "expression": True, "unit": False}
Inherited Members
3061class DatetimeTrunc(Func, TimeUnit): 3062 arg_types = {"this": True, "unit": True, "zone": False}
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
3089class TimestampAdd(Func, TimeUnit): 3090 arg_types = {"this": True, "expression": True, "unit": False}
Inherited Members
3093class TimestampSub(Func, TimeUnit): 3094 arg_types = {"this": True, "expression": True, "unit": False}
Inherited Members
3097class TimestampDiff(Func, TimeUnit): 3098 arg_types = {"this": True, "expression": True, "unit": False}
Inherited Members
3101class TimestampTrunc(Func, TimeUnit): 3102 arg_types = {"this": True, "unit": True, "zone": False}
Inherited Members
3105class TimeAdd(Func, TimeUnit): 3106 arg_types = {"this": True, "expression": True, "unit": False}
Inherited Members
3109class TimeSub(Func, TimeUnit): 3110 arg_types = {"this": True, "expression": True, "unit": False}
Inherited Members
3113class TimeDiff(Func, TimeUnit): 3114 arg_types = {"this": True, "expression": True, "unit": False}
Inherited Members
Inherited Members
3121class DateFromParts(Func): 3122 _sql_names = ["DATEFROMPARTS"] 3123 arg_types = {"year": True, "month": True, "day": True}
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
3166class Greatest(Func): 3167 arg_types = {"this": True, "expressions": False} 3168 is_var_len_args = True
Inherited Members
Inherited Members
Inherited Members
Inherited Members
3183class IfNull(Func): 3184 arg_types = {"this": True, "expression": False} 3185 _sql_names = ["IFNULL", "NVL"]
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
3212class Least(Func): 3213 arg_types = {"this": True, "expressions": False} 3214 is_var_len_args = True
Inherited Members
Inherited Members
3221class Levenshtein(Func): 3222 arg_types = { 3223 "this": True, 3224 "expression": False, 3225 "ins_cost": False, 3226 "del_cost": False, 3227 "sub_cost": False, 3228 }
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
3264class Matches(Func): 3265 """Oracle/Snowflake decode. 3266 https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm 3267 Pattern matching MATCHES(value, search1, result1, ...searchN, resultN, else) 3268 """ 3269 3270 arg_types = {"this": True, "expressions": True} 3271 is_var_len_args = True
Oracle/Snowflake decode. https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm Pattern matching MATCHES(value, search1, result1, ...searchN, resultN, else)
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
3320class ApproxQuantile(Quantile): 3321 arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
Inherited Members
3324class ReadCSV(Func): 3325 _sql_names = ["READ_CSV"] 3326 is_var_len_args = True 3327 arg_types = {"this": True, "expressions": False}
Inherited Members
3330class Reduce(Func): 3331 arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
3380class StrPosition(Func): 3381 arg_types = { 3382 "this": True, 3383 "substr": True, 3384 "position": False, 3385 "instance": False, 3386 }
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
3460class Trim(Func): 3461 arg_types = { 3462 "this": True, 3463 "expression": False, 3464 "position": False, 3465 "collation": False, 3466 }
Inherited Members
3469class TsOrDsAdd(Func, TimeUnit): 3470 arg_types = {"this": True, "expression": True, "unit": False}
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
3495class UnixToTime(Func): 3496 arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False} 3497 3498 SECONDS = Literal.string("seconds") 3499 MILLIS = Literal.string("millis") 3500 MICROS = Literal.string("micros")
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
Inherited Members
3564def maybe_parse( 3565 sql_or_expression: str | Expression, 3566 *, 3567 into: t.Optional[IntoType] = None, 3568 dialect: DialectType = None, 3569 prefix: t.Optional[str] = None, 3570 **opts, 3571) -> Expression: 3572 """Gracefully handle a possible string or expression. 3573 3574 Example: 3575 >>> maybe_parse("1") 3576 (LITERAL this: 1, is_string: False) 3577 >>> maybe_parse(to_identifier("x")) 3578 (IDENTIFIER this: x, quoted: False) 3579 3580 Args: 3581 sql_or_expression: the SQL code string or an expression 3582 into: the SQLGlot Expression to parse into 3583 dialect: the dialect used to parse the input expressions (in the case that an 3584 input expression is a SQL string). 3585 prefix: a string to prefix the sql with before it gets parsed 3586 (automatically includes a space) 3587 **opts: other options to use to parse the input expressions (again, in the case 3588 that an input expression is a SQL string). 3589 3590 Returns: 3591 Expression: the parsed or given expression. 3592 """ 3593 if isinstance(sql_or_expression, Expression): 3594 return sql_or_expression 3595 3596 import sqlglot 3597 3598 sql = str(sql_or_expression) 3599 if prefix: 3600 sql = f"{prefix} {sql}" 3601 return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
Gracefully handle a possible string or expression.
Example:
>>> maybe_parse("1") (LITERAL this: 1, is_string: False) >>> maybe_parse(to_identifier("x")) (IDENTIFIER this: x, quoted: False)
Arguments:
- sql_or_expression: the SQL code string or an expression
- into: the SQLGlot Expression to parse into
- dialect: the dialect used to parse the input expressions (in the case that an input expression is a SQL string).
- prefix: a string to prefix the sql with before it gets parsed (automatically includes a space)
- **opts: other options to use to parse the input expressions (again, in the case that an input expression is a SQL string).
Returns:
Expression: the parsed or given expression.
3747def union(left, right, distinct=True, dialect=None, **opts): 3748 """ 3749 Initializes a syntax tree from one UNION expression. 3750 3751 Example: 3752 >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql() 3753 'SELECT * FROM foo UNION SELECT * FROM bla' 3754 3755 Args: 3756 left (str | Expression): the SQL code string corresponding to the left-hand side. 3757 If an `Expression` instance is passed, it will be used as-is. 3758 right (str | Expression): the SQL code string corresponding to the right-hand side. 3759 If an `Expression` instance is passed, it will be used as-is. 3760 distinct (bool): set the DISTINCT flag if and only if this is true. 3761 dialect (str): the dialect used to parse the input expression. 3762 opts (kwargs): other options to use to parse the input expressions. 3763 Returns: 3764 Union: the syntax tree for the UNION expression. 3765 """ 3766 left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts) 3767 right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts) 3768 3769 return Union(this=left, expression=right, distinct=distinct)
Initializes a syntax tree from one UNION expression.
Example:
>>> union("SELECT * FROM foo", "SELECT * FROM bla").sql() 'SELECT * FROM foo UNION SELECT * FROM bla'
Arguments:
- left (str | Expression): the SQL code string corresponding to the left-hand side.
If an
Expression
instance is passed, it will be used as-is. - right (str | Expression): the SQL code string corresponding to the right-hand side.
If an
Expression
instance is passed, it will be used as-is. - distinct (bool): set the DISTINCT flag if and only if this is true.
- dialect (str): the dialect used to parse the input expression.
- opts (kwargs): other options to use to parse the input expressions.
Returns:
Union: the syntax tree for the UNION expression.
3772def intersect(left, right, distinct=True, dialect=None, **opts): 3773 """ 3774 Initializes a syntax tree from one INTERSECT expression. 3775 3776 Example: 3777 >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql() 3778 'SELECT * FROM foo INTERSECT SELECT * FROM bla' 3779 3780 Args: 3781 left (str | Expression): the SQL code string corresponding to the left-hand side. 3782 If an `Expression` instance is passed, it will be used as-is. 3783 right (str | Expression): the SQL code string corresponding to the right-hand side. 3784 If an `Expression` instance is passed, it will be used as-is. 3785 distinct (bool): set the DISTINCT flag if and only if this is true. 3786 dialect (str): the dialect used to parse the input expression. 3787 opts (kwargs): other options to use to parse the input expressions. 3788 Returns: 3789 Intersect: the syntax tree for the INTERSECT expression. 3790 """ 3791 left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts) 3792 right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts) 3793 3794 return Intersect(this=left, expression=right, distinct=distinct)
Initializes a syntax tree from one INTERSECT expression.
Example:
>>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql() 'SELECT * FROM foo INTERSECT SELECT * FROM bla'
Arguments:
- left (str | Expression): the SQL code string corresponding to the left-hand side.
If an
Expression
instance is passed, it will be used as-is. - right (str | Expression): the SQL code string corresponding to the right-hand side.
If an
Expression
instance is passed, it will be used as-is. - distinct (bool): set the DISTINCT flag if and only if this is true.
- dialect (str): the dialect used to parse the input expression.
- opts (kwargs): other options to use to parse the input expressions.
Returns:
Intersect: the syntax tree for the INTERSECT expression.
3797def except_(left, right, distinct=True, dialect=None, **opts): 3798 """ 3799 Initializes a syntax tree from one EXCEPT expression. 3800 3801 Example: 3802 >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql() 3803 'SELECT * FROM foo EXCEPT SELECT * FROM bla' 3804 3805 Args: 3806 left (str | Expression): the SQL code string corresponding to the left-hand side. 3807 If an `Expression` instance is passed, it will be used as-is. 3808 right (str | Expression): the SQL code string corresponding to the right-hand side. 3809 If an `Expression` instance is passed, it will be used as-is. 3810 distinct (bool): set the DISTINCT flag if and only if this is true. 3811 dialect (str): the dialect used to parse the input expression. 3812 opts (kwargs): other options to use to parse the input expressions. 3813 Returns: 3814 Except: the syntax tree for the EXCEPT statement. 3815 """ 3816 left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts) 3817 right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts) 3818 3819 return Except(this=left, expression=right, distinct=distinct)
Initializes a syntax tree from one EXCEPT expression.
Example:
>>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql() 'SELECT * FROM foo EXCEPT SELECT * FROM bla'
Arguments:
- left (str | Expression): the SQL code string corresponding to the left-hand side.
If an
Expression
instance is passed, it will be used as-is. - right (str | Expression): the SQL code string corresponding to the right-hand side.
If an
Expression
instance is passed, it will be used as-is. - distinct (bool): set the DISTINCT flag if and only if this is true.
- dialect (str): the dialect used to parse the input expression.
- opts (kwargs): other options to use to parse the input expressions.
Returns:
Except: the syntax tree for the EXCEPT statement.
3822def select(*expressions, dialect=None, **opts) -> Select: 3823 """ 3824 Initializes a syntax tree from one or multiple SELECT expressions. 3825 3826 Example: 3827 >>> select("col1", "col2").from_("tbl").sql() 3828 'SELECT col1, col2 FROM tbl' 3829 3830 Args: 3831 *expressions (str | Expression): the SQL code string to parse as the expressions of a 3832 SELECT statement. If an Expression instance is passed, this is used as-is. 3833 dialect (str): the dialect used to parse the input expressions (in the case that an 3834 input expression is a SQL string). 3835 **opts: other options to use to parse the input expressions (again, in the case 3836 that an input expression is a SQL string). 3837 3838 Returns: 3839 Select: the syntax tree for the SELECT statement. 3840 """ 3841 return Select().select(*expressions, dialect=dialect, **opts)
Initializes a syntax tree from one or multiple SELECT expressions.
Example:
>>> select("col1", "col2").from_("tbl").sql() 'SELECT col1, col2 FROM tbl'
Arguments:
- *expressions (str | Expression): the SQL code string to parse as the expressions of a SELECT statement. If an Expression instance is passed, this is used as-is.
- dialect (str): the dialect used to parse the input expressions (in the case that an input expression is a SQL string).
- **opts: other options to use to parse the input expressions (again, in the case that an input expression is a SQL string).
Returns:
Select: the syntax tree for the SELECT statement.
3844def from_(*expressions, dialect=None, **opts) -> Select: 3845 """ 3846 Initializes a syntax tree from a FROM expression. 3847 3848 Example: 3849 >>> from_("tbl").select("col1", "col2").sql() 3850 'SELECT col1, col2 FROM tbl' 3851 3852 Args: 3853 *expressions (str | Expression): the SQL code string to parse as the FROM expressions of a 3854 SELECT statement. If an Expression instance is passed, this is used as-is. 3855 dialect (str): the dialect used to parse the input expression (in the case that the 3856 input expression is a SQL string). 3857 **opts: other options to use to parse the input expressions (again, in the case 3858 that the input expression is a SQL string). 3859 3860 Returns: 3861 Select: the syntax tree for the SELECT statement. 3862 """ 3863 return Select().from_(*expressions, dialect=dialect, **opts)
Initializes a syntax tree from a FROM expression.
Example:
>>> from_("tbl").select("col1", "col2").sql() 'SELECT col1, col2 FROM tbl'
Arguments:
- *expressions (str | Expression): the SQL code string to parse as the FROM expressions of a SELECT statement. If an Expression instance is passed, this is used as-is.
- dialect (str): the dialect used to parse the input expression (in the case that the input expression is a SQL string).
- **opts: other options to use to parse the input expressions (again, in the case that the input expression is a SQL string).
Returns:
Select: the syntax tree for the SELECT statement.
3866def update(table, properties, where=None, from_=None, dialect=None, **opts) -> Update: 3867 """ 3868 Creates an update statement. 3869 3870 Example: 3871 >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql() 3872 "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1" 3873 3874 Args: 3875 *properties (Dict[str, Any]): dictionary of properties to set which are 3876 auto converted to sql objects eg None -> NULL 3877 where (str): sql conditional parsed into a WHERE statement 3878 from_ (str): sql statement parsed into a FROM statement 3879 dialect (str): the dialect used to parse the input expressions. 3880 **opts: other options to use to parse the input expressions. 3881 3882 Returns: 3883 Update: the syntax tree for the UPDATE statement. 3884 """ 3885 update = Update(this=maybe_parse(table, into=Table, dialect=dialect)) 3886 update.set( 3887 "expressions", 3888 [ 3889 EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v)) 3890 for k, v in properties.items() 3891 ], 3892 ) 3893 if from_: 3894 update.set( 3895 "from", 3896 maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts), 3897 ) 3898 if isinstance(where, Condition): 3899 where = Where(this=where) 3900 if where: 3901 update.set( 3902 "where", 3903 maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts), 3904 ) 3905 return update
Creates an update statement.
Example:
>>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql() "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
Arguments:
- *properties (Dict[str, Any]): dictionary of properties to set which are auto converted to sql objects eg None -> NULL
- where (str): sql conditional parsed into a WHERE statement
- from_ (str): sql statement parsed into a FROM statement
- dialect (str): the dialect used to parse the input expressions.
- **opts: other options to use to parse the input expressions.
Returns:
Update: the syntax tree for the UPDATE statement.
3908def delete(table, where=None, dialect=None, **opts) -> Delete: 3909 """ 3910 Builds a delete statement. 3911 3912 Example: 3913 >>> delete("my_table", where="id > 1").sql() 3914 'DELETE FROM my_table WHERE id > 1' 3915 3916 Args: 3917 where (str|Condition): sql conditional parsed into a WHERE statement 3918 dialect (str): the dialect used to parse the input expressions. 3919 **opts: other options to use to parse the input expressions. 3920 3921 Returns: 3922 Delete: the syntax tree for the DELETE statement. 3923 """ 3924 return Delete( 3925 this=maybe_parse(table, into=Table, dialect=dialect, **opts), 3926 where=Where(this=where) 3927 if isinstance(where, Condition) 3928 else maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts), 3929 )
Builds a delete statement.
Example:
>>> delete("my_table", where="id > 1").sql() 'DELETE FROM my_table WHERE id > 1'
Arguments:
- where (str|Condition): sql conditional parsed into a WHERE statement
- dialect (str): the dialect used to parse the input expressions.
- **opts: other options to use to parse the input expressions.
Returns:
Delete: the syntax tree for the DELETE statement.
3932def condition(expression, dialect=None, **opts) -> Condition: 3933 """ 3934 Initialize a logical condition expression. 3935 3936 Example: 3937 >>> condition("x=1").sql() 3938 'x = 1' 3939 3940 This is helpful for composing larger logical syntax trees: 3941 >>> where = condition("x=1") 3942 >>> where = where.and_("y=1") 3943 >>> Select().from_("tbl").select("*").where(where).sql() 3944 'SELECT * FROM tbl WHERE x = 1 AND y = 1' 3945 3946 Args: 3947 *expression (str | Expression): the SQL code string to parse. 3948 If an Expression instance is passed, this is used as-is. 3949 dialect (str): the dialect used to parse the input expression (in the case that the 3950 input expression is a SQL string). 3951 **opts: other options to use to parse the input expressions (again, in the case 3952 that the input expression is a SQL string). 3953 3954 Returns: 3955 Condition: the expression 3956 """ 3957 return maybe_parse( # type: ignore 3958 expression, 3959 into=Condition, 3960 dialect=dialect, 3961 **opts, 3962 )
Initialize a logical condition expression.
Example:
>>> condition("x=1").sql() 'x = 1'
This is helpful for composing larger logical syntax trees:
>>> where = condition("x=1") >>> where = where.and_("y=1") >>> Select().from_("tbl").select("*").where(where).sql() 'SELECT * FROM tbl WHERE x = 1 AND y = 1'
Arguments:
- *expression (str | Expression): the SQL code string to parse. If an Expression instance is passed, this is used as-is.
- dialect (str): the dialect used to parse the input expression (in the case that the input expression is a SQL string).
- **opts: other options to use to parse the input expressions (again, in the case that the input expression is a SQL string).
Returns:
Condition: the expression
3965def and_(*expressions, dialect=None, **opts) -> And: 3966 """ 3967 Combine multiple conditions with an AND logical operator. 3968 3969 Example: 3970 >>> and_("x=1", and_("y=1", "z=1")).sql() 3971 'x = 1 AND (y = 1 AND z = 1)' 3972 3973 Args: 3974 *expressions (str | Expression): the SQL code strings to parse. 3975 If an Expression instance is passed, this is used as-is. 3976 dialect (str): the dialect used to parse the input expression. 3977 **opts: other options to use to parse the input expressions. 3978 3979 Returns: 3980 And: the new condition 3981 """ 3982 return _combine(expressions, And, dialect, **opts)
Combine multiple conditions with an AND logical operator.
Example:
>>> and_("x=1", and_("y=1", "z=1")).sql() 'x = 1 AND (y = 1 AND z = 1)'
Arguments:
- *expressions (str | Expression): the SQL code strings to parse. If an Expression instance is passed, this is used as-is.
- dialect (str): the dialect used to parse the input expression.
- **opts: other options to use to parse the input expressions.
Returns:
And: the new condition
3985def or_(*expressions, dialect=None, **opts) -> Or: 3986 """ 3987 Combine multiple conditions with an OR logical operator. 3988 3989 Example: 3990 >>> or_("x=1", or_("y=1", "z=1")).sql() 3991 'x = 1 OR (y = 1 OR z = 1)' 3992 3993 Args: 3994 *expressions (str | Expression): the SQL code strings to parse. 3995 If an Expression instance is passed, this is used as-is. 3996 dialect (str): the dialect used to parse the input expression. 3997 **opts: other options to use to parse the input expressions. 3998 3999 Returns: 4000 Or: the new condition 4001 """ 4002 return _combine(expressions, Or, dialect, **opts)
Combine multiple conditions with an OR logical operator.
Example:
>>> or_("x=1", or_("y=1", "z=1")).sql() 'x = 1 OR (y = 1 OR z = 1)'
Arguments:
- *expressions (str | Expression): the SQL code strings to parse. If an Expression instance is passed, this is used as-is.
- dialect (str): the dialect used to parse the input expression.
- **opts: other options to use to parse the input expressions.
Returns:
Or: the new condition
4005def not_(expression, dialect=None, **opts) -> Not: 4006 """ 4007 Wrap a condition with a NOT operator. 4008 4009 Example: 4010 >>> not_("this_suit='black'").sql() 4011 "NOT this_suit = 'black'" 4012 4013 Args: 4014 expression (str | Expression): the SQL code strings to parse. 4015 If an Expression instance is passed, this is used as-is. 4016 dialect (str): the dialect used to parse the input expression. 4017 **opts: other options to use to parse the input expressions. 4018 4019 Returns: 4020 Not: the new condition 4021 """ 4022 this = condition( 4023 expression, 4024 dialect=dialect, 4025 **opts, 4026 ) 4027 return Not(this=_wrap_operator(this))
Wrap a condition with a NOT operator.
Example:
>>> not_("this_suit='black'").sql() "NOT this_suit = 'black'"
Arguments:
- expression (str | Expression): the SQL code strings to parse. If an Expression instance is passed, this is used as-is.
- dialect (str): the dialect used to parse the input expression.
- **opts: other options to use to parse the input expressions.
Returns:
Not: the new condition
4047def to_identifier(name, quoted=None): 4048 """Builds an identifier. 4049 4050 Args: 4051 name: The name to turn into an identifier. 4052 quoted: Whether or not force quote the identifier. 4053 4054 Returns: 4055 The identifier ast node. 4056 """ 4057 4058 if name is None: 4059 return None 4060 4061 if isinstance(name, Identifier): 4062 identifier = name 4063 elif isinstance(name, str): 4064 identifier = Identifier( 4065 this=name, 4066 quoted=not re.match(SAFE_IDENTIFIER_RE, name) if quoted is None else quoted, 4067 ) 4068 else: 4069 raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}") 4070 return identifier
Builds an identifier.
Arguments:
- name: The name to turn into an identifier.
- quoted: Whether or not force quote the identifier.
Returns:
The identifier ast node.
4076def to_interval(interval: str | Literal) -> Interval: 4077 """Builds an interval expression from a string like '1 day' or '5 months'.""" 4078 if isinstance(interval, Literal): 4079 if not interval.is_string: 4080 raise ValueError("Invalid interval string.") 4081 4082 interval = interval.this 4083 4084 interval_parts = INTERVAL_STRING_RE.match(interval) # type: ignore 4085 4086 if not interval_parts: 4087 raise ValueError("Invalid interval string.") 4088 4089 return Interval( 4090 this=Literal.string(interval_parts.group(1)), 4091 unit=Var(this=interval_parts.group(2)), 4092 )
Builds an interval expression from a string like '1 day' or '5 months'.
4105def to_table(sql_path: t.Optional[str | Table], **kwargs) -> t.Optional[Table]: 4106 """ 4107 Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional. 4108 If a table is passed in then that table is returned. 4109 4110 Args: 4111 sql_path: a `[catalog].[schema].[table]` string. 4112 4113 Returns: 4114 A table expression. 4115 """ 4116 if sql_path is None or isinstance(sql_path, Table): 4117 return sql_path 4118 if not isinstance(sql_path, str): 4119 raise ValueError(f"Invalid type provided for a table: {type(sql_path)}") 4120 4121 catalog, db, table_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 3)) 4122 return Table(this=table_name, db=db, catalog=catalog, **kwargs)
Create a table expression from a [catalog].[schema].[table]
sql path. Catalog and schema are optional.
If a table is passed in then that table is returned.
Arguments:
- sql_path: a
[catalog].[schema].[table]
string.
Returns:
A table expression.
4125def to_column(sql_path: str | Column, **kwargs) -> Column: 4126 """ 4127 Create a column from a `[table].[column]` sql path. Schema is optional. 4128 4129 If a column is passed in then that column is returned. 4130 4131 Args: 4132 sql_path: `[table].[column]` string 4133 Returns: 4134 Table: A column expression 4135 """ 4136 if sql_path is None or isinstance(sql_path, Column): 4137 return sql_path 4138 if not isinstance(sql_path, str): 4139 raise ValueError(f"Invalid type provided for column: {type(sql_path)}") 4140 table_name, column_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 2)) 4141 return Column(this=column_name, table=table_name, **kwargs)
Create a column from a [table].[column]
sql path. Schema is optional.
If a column is passed in then that column is returned.
Arguments:
- sql_path:
[table].[column]
string
Returns:
Table: A column expression
4144def alias_( 4145 expression: str | Expression, 4146 alias: str | Identifier, 4147 table: bool | t.Sequence[str | Identifier] = False, 4148 quoted: t.Optional[bool] = None, 4149 dialect: DialectType = None, 4150 **opts, 4151): 4152 """Create an Alias expression. 4153 4154 Example: 4155 >>> alias_('foo', 'bar').sql() 4156 'foo AS bar' 4157 4158 >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql() 4159 '(SELECT 1, 2) AS bar(a, b)' 4160 4161 Args: 4162 expression: the SQL code strings to parse. 4163 If an Expression instance is passed, this is used as-is. 4164 alias: the alias name to use. If the name has 4165 special characters it is quoted. 4166 table: Whether or not to create a table alias, can also be a list of columns. 4167 quoted: whether or not to quote the alias 4168 dialect: the dialect used to parse the input expression. 4169 **opts: other options to use to parse the input expressions. 4170 4171 Returns: 4172 Alias: the aliased expression 4173 """ 4174 exp = maybe_parse(expression, dialect=dialect, **opts) 4175 alias = to_identifier(alias, quoted=quoted) 4176 4177 if table: 4178 table_alias = TableAlias(this=alias) 4179 exp.set("alias", table_alias) 4180 4181 if not isinstance(table, bool): 4182 for column in table: 4183 table_alias.append("columns", to_identifier(column, quoted=quoted)) 4184 4185 return exp 4186 4187 # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in 4188 # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node 4189 # for the complete Window expression. 4190 # 4191 # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls 4192 4193 if "alias" in exp.arg_types and not isinstance(exp, Window): 4194 exp = exp.copy() 4195 exp.set("alias", alias) 4196 return exp 4197 return Alias(this=exp, alias=alias)
Create an Alias expression.
Example:
>>> alias_('foo', 'bar').sql() 'foo AS bar'
>>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql() '(SELECT 1, 2) AS bar(a, b)'
Arguments:
- expression: the SQL code strings to parse. If an Expression instance is passed, this is used as-is.
- alias: the alias name to use. If the name has special characters it is quoted.
- table: Whether or not to create a table alias, can also be a list of columns.
- quoted: whether or not to quote the alias
- dialect: the dialect used to parse the input expression.
- **opts: other options to use to parse the input expressions.
Returns:
Alias: the aliased expression
4200def subquery(expression, alias=None, dialect=None, **opts): 4201 """ 4202 Build a subquery expression. 4203 4204 Example: 4205 >>> subquery('select x from tbl', 'bar').select('x').sql() 4206 'SELECT x FROM (SELECT x FROM tbl) AS bar' 4207 4208 Args: 4209 expression (str | Expression): the SQL code strings to parse. 4210 If an Expression instance is passed, this is used as-is. 4211 alias (str | Expression): the alias name to use. 4212 dialect (str): the dialect used to parse the input expression. 4213 **opts: other options to use to parse the input expressions. 4214 4215 Returns: 4216 Select: a new select with the subquery expression included 4217 """ 4218 4219 expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias) 4220 return Select().from_(expression, dialect=dialect, **opts)
Build a subquery expression.
Example:
>>> subquery('select x from tbl', 'bar').select('x').sql() 'SELECT x FROM (SELECT x FROM tbl) AS bar'
Arguments:
- expression (str | Expression): the SQL code strings to parse. If an Expression instance is passed, this is used as-is.
- alias (str | Expression): the alias name to use.
- dialect (str): the dialect used to parse the input expression.
- **opts: other options to use to parse the input expressions.
Returns:
Select: a new select with the subquery expression included
4223def column(col, table=None, quoted=None) -> Column: 4224 """ 4225 Build a Column. 4226 4227 Args: 4228 col (str | Expression): column name 4229 table (str | Expression): table name 4230 Returns: 4231 Column: column instance 4232 """ 4233 return Column( 4234 this=to_identifier(col, quoted=quoted), 4235 table=to_identifier(table, quoted=quoted), 4236 )
Build a Column.
Arguments:
- col (str | Expression): column name
- table (str | Expression): table name
Returns:
Column: column instance
4239def cast(expression: str | Expression, to: str | DataType | DataType.Type, **opts) -> Cast: 4240 """Cast an expression to a data type. 4241 4242 Example: 4243 >>> cast('x + 1', 'int').sql() 4244 'CAST(x + 1 AS INT)' 4245 4246 Args: 4247 expression: The expression to cast. 4248 to: The datatype to cast to. 4249 4250 Returns: 4251 A cast node. 4252 """ 4253 expression = maybe_parse(expression, **opts) 4254 return Cast(this=expression, to=DataType.build(to, **opts))
Cast an expression to a data type.
Example:
>>> cast('x + 1', 'int').sql() 'CAST(x + 1 AS INT)'
Arguments:
- expression: The expression to cast.
- to: The datatype to cast to.
Returns:
A cast node.
4257def table_(table, db=None, catalog=None, quoted=None, alias=None) -> Table: 4258 """Build a Table. 4259 4260 Args: 4261 table (str | Expression): column name 4262 db (str | Expression): db name 4263 catalog (str | Expression): catalog name 4264 4265 Returns: 4266 Table: table instance 4267 """ 4268 return Table( 4269 this=to_identifier(table, quoted=quoted), 4270 db=to_identifier(db, quoted=quoted), 4271 catalog=to_identifier(catalog, quoted=quoted), 4272 alias=TableAlias(this=to_identifier(alias)) if alias else None, 4273 )
Build a Table.
Arguments:
- table (str | Expression): column name
- db (str | Expression): db name
- catalog (str | Expression): catalog name
Returns:
Table: table instance
4276def values( 4277 values: t.Iterable[t.Tuple[t.Any, ...]], 4278 alias: t.Optional[str] = None, 4279 columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None, 4280) -> Values: 4281 """Build VALUES statement. 4282 4283 Example: 4284 >>> values([(1, '2')]).sql() 4285 "VALUES (1, '2')" 4286 4287 Args: 4288 values: values statements that will be converted to SQL 4289 alias: optional alias 4290 columns: Optional list of ordered column names or ordered dictionary of column names to types. 4291 If either are provided then an alias is also required. 4292 If a dictionary is provided then the first column of the values will be casted to the expected type 4293 in order to help with type inference. 4294 4295 Returns: 4296 Values: the Values expression object 4297 """ 4298 if columns and not alias: 4299 raise ValueError("Alias is required when providing columns") 4300 table_alias = ( 4301 TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns]) 4302 if columns 4303 else TableAlias(this=to_identifier(alias) if alias else None) 4304 ) 4305 expressions = [convert(tup) for tup in values] 4306 if columns and isinstance(columns, dict): 4307 types = list(columns.values()) 4308 expressions[0].set( 4309 "expressions", 4310 [cast(x, types[i]) for i, x in enumerate(expressions[0].expressions)], 4311 ) 4312 return Values( 4313 expressions=expressions, 4314 alias=table_alias, 4315 )
Build VALUES statement.
Example:
>>> values([(1, '2')]).sql() "VALUES (1, '2')"
Arguments:
- values: values statements that will be converted to SQL
- alias: optional alias
- columns: Optional list of ordered column names or ordered dictionary of column names to types. If either are provided then an alias is also required. If a dictionary is provided then the first column of the values will be casted to the expected type in order to help with type inference.
Returns:
Values: the Values expression object
4318def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable: 4319 """Build ALTER TABLE... RENAME... expression 4320 4321 Args: 4322 old_name: The old name of the table 4323 new_name: The new name of the table 4324 4325 Returns: 4326 Alter table expression 4327 """ 4328 old_table = to_table(old_name) 4329 new_table = to_table(new_name) 4330 return AlterTable( 4331 this=old_table, 4332 actions=[ 4333 RenameTable(this=new_table), 4334 ], 4335 )
Build ALTER TABLE... RENAME... expression
Arguments:
- old_name: The old name of the table
- new_name: The new name of the table
Returns:
Alter table expression
4338def convert(value) -> Expression: 4339 """Convert a python value into an expression object. 4340 4341 Raises an error if a conversion is not possible. 4342 4343 Args: 4344 value (Any): a python object 4345 4346 Returns: 4347 Expression: the equivalent expression object 4348 """ 4349 if isinstance(value, Expression): 4350 return value 4351 if value is None: 4352 return NULL 4353 if isinstance(value, bool): 4354 return Boolean(this=value) 4355 if isinstance(value, str): 4356 return Literal.string(value) 4357 if isinstance(value, float) and math.isnan(value): 4358 return NULL 4359 if isinstance(value, numbers.Number): 4360 return Literal.number(value) 4361 if isinstance(value, tuple): 4362 return Tuple(expressions=[convert(v) for v in value]) 4363 if isinstance(value, list): 4364 return Array(expressions=[convert(v) for v in value]) 4365 if isinstance(value, dict): 4366 return Map( 4367 keys=[convert(k) for k in value], 4368 values=[convert(v) for v in value.values()], 4369 ) 4370 if isinstance(value, datetime.datetime): 4371 datetime_literal = Literal.string( 4372 (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat() 4373 ) 4374 return TimeStrToTime(this=datetime_literal) 4375 if isinstance(value, datetime.date): 4376 date_literal = Literal.string(value.strftime("%Y-%m-%d")) 4377 return DateStrToDate(this=date_literal) 4378 raise ValueError(f"Cannot convert {value}")
Convert a python value into an expression object.
Raises an error if a conversion is not possible.
Arguments:
- value (Any): a python object
Returns:
Expression: the equivalent expression object
4381def replace_children(expression, fun): 4382 """ 4383 Replace children of an expression with the result of a lambda fun(child) -> exp. 4384 """ 4385 for k, v in expression.args.items(): 4386 is_list_arg = isinstance(v, list) 4387 4388 child_nodes = v if is_list_arg else [v] 4389 new_child_nodes = [] 4390 4391 for cn in child_nodes: 4392 if isinstance(cn, Expression): 4393 for child_node in ensure_collection(fun(cn)): 4394 new_child_nodes.append(child_node) 4395 child_node.parent = expression 4396 child_node.arg_key = k 4397 else: 4398 new_child_nodes.append(cn) 4399 4400 expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
Replace children of an expression with the result of a lambda fun(child) -> exp.
4403def column_table_names(expression): 4404 """ 4405 Return all table names referenced through columns in an expression. 4406 4407 Example: 4408 >>> import sqlglot 4409 >>> column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")) 4410 ['c', 'a'] 4411 4412 Args: 4413 expression (sqlglot.Expression): expression to find table names 4414 4415 Returns: 4416 list: A list of unique names 4417 """ 4418 return list(dict.fromkeys(column.table for column in expression.find_all(Column)))
Return all table names referenced through columns in an expression.
Example:
>>> import sqlglot >>> column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")) ['c', 'a']
Arguments:
- expression (sqlglot.Expression): expression to find table names
Returns:
list: A list of unique names
4421def table_name(table) -> str: 4422 """Get the full name of a table as a string. 4423 4424 Args: 4425 table (exp.Table | str): table expression node or string. 4426 4427 Examples: 4428 >>> from sqlglot import exp, parse_one 4429 >>> table_name(parse_one("select * from a.b.c").find(exp.Table)) 4430 'a.b.c' 4431 4432 Returns: 4433 The table name. 4434 """ 4435 4436 table = maybe_parse(table, into=Table) 4437 4438 if not table: 4439 raise ValueError(f"Cannot parse {table}") 4440 4441 return ".".join( 4442 part 4443 for part in ( 4444 table.text("catalog"), 4445 table.text("db"), 4446 table.name, 4447 ) 4448 if part 4449 )
Get the full name of a table as a string.
Arguments:
- table (exp.Table | str): table expression node or string.
Examples:
>>> from sqlglot import exp, parse_one >>> table_name(parse_one("select * from a.b.c").find(exp.Table)) 'a.b.c'
Returns:
The table name.
4452def replace_tables(expression, mapping): 4453 """Replace all tables in expression according to the mapping. 4454 4455 Args: 4456 expression (sqlglot.Expression): expression node to be transformed and replaced. 4457 mapping (Dict[str, str]): mapping of table names. 4458 4459 Examples: 4460 >>> from sqlglot import exp, parse_one 4461 >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql() 4462 'SELECT * FROM c' 4463 4464 Returns: 4465 The mapped expression. 4466 """ 4467 4468 def _replace_tables(node): 4469 if isinstance(node, Table): 4470 new_name = mapping.get(table_name(node)) 4471 if new_name: 4472 return to_table( 4473 new_name, 4474 **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")}, 4475 ) 4476 return node 4477 4478 return expression.transform(_replace_tables)
Replace all tables in expression according to the mapping.
Arguments:
- expression (sqlglot.Expression): expression node to be transformed and replaced.
- mapping (Dict[str, str]): mapping of table names.
Examples:
>>> from sqlglot import exp, parse_one >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql() 'SELECT * FROM c'
Returns:
The mapped expression.
4481def replace_placeholders(expression, *args, **kwargs): 4482 """Replace placeholders in an expression. 4483 4484 Args: 4485 expression (sqlglot.Expression): expression node to be transformed and replaced. 4486 args: positional names that will substitute unnamed placeholders in the given order. 4487 kwargs: keyword arguments that will substitute named placeholders. 4488 4489 Examples: 4490 >>> from sqlglot import exp, parse_one 4491 >>> replace_placeholders( 4492 ... parse_one("select * from :tbl where ? = ?"), "a", "b", tbl="foo" 4493 ... ).sql() 4494 'SELECT * FROM foo WHERE a = b' 4495 4496 Returns: 4497 The mapped expression. 4498 """ 4499 4500 def _replace_placeholders(node, args, **kwargs): 4501 if isinstance(node, Placeholder): 4502 if node.name: 4503 new_name = kwargs.get(node.name) 4504 if new_name: 4505 return to_identifier(new_name) 4506 else: 4507 try: 4508 return to_identifier(next(args)) 4509 except StopIteration: 4510 pass 4511 return node 4512 4513 return expression.transform(_replace_placeholders, iter(args), **kwargs)
Replace placeholders in an expression.
Arguments:
- expression (sqlglot.Expression): expression node to be transformed and replaced.
- args: positional names that will substitute unnamed placeholders in the given order.
- kwargs: keyword arguments that will substitute named placeholders.
Examples:
>>> from sqlglot import exp, parse_one >>> replace_placeholders( ... parse_one("select * from :tbl where ? = ?"), "a", "b", tbl="foo" ... ).sql() 'SELECT * FROM foo WHERE a = b'
Returns:
The mapped expression.
4516def expand(expression: Expression, sources: t.Dict[str, Subqueryable], copy=True) -> Expression: 4517 """Transforms an expression by expanding all referenced sources into subqueries. 4518 4519 Examples: 4520 >>> from sqlglot import parse_one 4521 >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql() 4522 'SELECT * FROM (SELECT * FROM y) AS z /* source: x */' 4523 4524 Args: 4525 expression: The expression to expand. 4526 sources: A dictionary of name to Subqueryables. 4527 copy: Whether or not to copy the expression during transformation. Defaults to True. 4528 4529 Returns: 4530 The transformed expression. 4531 """ 4532 4533 def _expand(node: Expression): 4534 if isinstance(node, Table): 4535 name = table_name(node) 4536 source = sources.get(name) 4537 if source: 4538 subquery = source.subquery(node.alias or name) 4539 subquery.comments = [f"source: {name}"] 4540 return subquery 4541 return node 4542 4543 return expression.transform(_expand, copy=copy)
Transforms an expression by expanding all referenced sources into subqueries.
Examples:
>>> from sqlglot import parse_one >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql() 'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
Arguments:
- expression: The expression to expand.
- sources: A dictionary of name to Subqueryables.
- copy: Whether or not to copy the expression during transformation. Defaults to True.
Returns:
The transformed expression.
4546def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func: 4547 """ 4548 Returns a Func expression. 4549 4550 Examples: 4551 >>> func("abs", 5).sql() 4552 'ABS(5)' 4553 4554 >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql() 4555 'CAST(5 AS DOUBLE)' 4556 4557 Args: 4558 name: the name of the function to build. 4559 args: the args used to instantiate the function of interest. 4560 dialect: the source dialect. 4561 kwargs: the kwargs used to instantiate the function of interest. 4562 4563 Note: 4564 The arguments `args` and `kwargs` are mutually exclusive. 4565 4566 Returns: 4567 An instance of the function of interest, or an anonymous function, if `name` doesn't 4568 correspond to an existing `sqlglot.expressions.Func` class. 4569 """ 4570 if args and kwargs: 4571 raise ValueError("Can't use both args and kwargs to instantiate a function.") 4572 4573 from sqlglot.dialects.dialect import Dialect 4574 4575 args = tuple(convert(arg) for arg in args) 4576 kwargs = {key: convert(value) for key, value in kwargs.items()} 4577 4578 parser = Dialect.get_or_raise(dialect)().parser() 4579 from_args_list = parser.FUNCTIONS.get(name.upper()) 4580 4581 if from_args_list: 4582 function = from_args_list(args) if args else from_args_list.__self__(**kwargs) # type: ignore 4583 else: 4584 kwargs = kwargs or {"expressions": args} 4585 function = Anonymous(this=name, **kwargs) 4586 4587 for error_message in function.error_messages(args): 4588 raise ValueError(error_message) 4589 4590 return function
Returns a Func expression.
Examples:
>>> func("abs", 5).sql() 'ABS(5)'
>>> func("cast", this=5, to=DataType.build("DOUBLE")).sql() 'CAST(5 AS DOUBLE)'
Arguments:
- name: the name of the function to build.
- args: the args used to instantiate the function of interest.
- dialect: the source dialect.
- kwargs: the kwargs used to instantiate the function of interest.
Note:
The arguments
args
andkwargs
are mutually exclusive.
Returns:
An instance of the function of interest, or an anonymous function, if
name
doesn't correspond to an existingsqlglot.expressions.Func
class.
4593def true(): 4594 """ 4595 Returns a true Boolean expression. 4596 """ 4597 return Boolean(this=True)
Returns a true Boolean expression.
4600def false(): 4601 """ 4602 Returns a false Boolean expression. 4603 """ 4604 return Boolean(this=False)
Returns a false Boolean expression.
Returns a Null expression.