codekingpro/portable-devtools
114k
1from __future__ import annotations2 3import sys4from collections.abc import Sequence5from copy import copy6from functools import reduce7from typing import TYPE_CHECKING, Any, Generic, TypeVar8 9from pypika.enums import Dialects, JoinType, ReferenceOption, SetOperation10from pypika.terms import (11 ArithmeticExpression,12 Criterion,13 EmptyCriterion,14 Field,15 Function,16 Index,17 Node,18 PeriodCriterion,19 Rollup,20 Star,21 Term,22 Tuple,23 ValueWrapper,24)25from pypika.utils import (26 JoinException,27 QueryException,28 RollupException,29 SetOperationException,30 builder,31 format_alias_sql,32 format_quotes,33 ignore_copy,34)35 36if TYPE_CHECKING:37 if sys.version_info >= (3, 11):38 from typing import Self39 else:40 from typing_extensions import Self41 42__author__ = "Timothy Heys"43__email__ = "theys@kayak.com"44 45 46QB = TypeVar("QB", bound="QueryBuilder")47 48 49class Selectable(Node):50 def __init__(self, alias: str) -> None:51 self.alias = alias52 53 @builder54 def as_(self, alias: str) -> None:55 self.alias = alias56 57 def field(self, name: str) -> Field:58 return Field(name, table=self)59 60 @property61 def star(self) -> Star:62 return Star(self)63 64 @ignore_copy65 def __getattr__(self, name: str) -> Field:66 return self.field(name)67 68 @ignore_copy69 def __getitem__(self, name: str) -> Field:70 return self.field(name)71 72 def get_table_name(self) -> str:73 return self.alias74 75 76class AliasedQuery(Selectable):77 def __init__(self, name: str, query: Selectable | None = None) -> None:78 super().__init__(alias=name)79 self.name = name80 self.query = query81 82 def get_sql(self, **kwargs: Any) -> str:83 if self.query is None:84 return self.name85 return self.query.get_sql(**kwargs)86 87 def __eq__(self, other: AliasedQuery) -> bool:88 return isinstance(other, AliasedQuery) and self.name == other.name89 90 def __hash__(self) -> int:91 return hash(str(self.name))92 93 94class Schema:95 def __init__(self, name: str, parent: Schema | None = None) -> None:96 self._name = name97 self._parent = parent98 99 def __eq__(self, other: Schema) -> bool:100 return isinstance(other, Schema) and self._name == other._name and self._parent == other._parent101 102 def __ne__(self, other: Schema) -> bool:103 return not self.__eq__(other)104 105 @ignore_copy106 def __getattr__(self, item: str) -> Table:107 return Table(item, schema=self)108 109 def get_sql(self, quote_char: str | None = None, **kwargs: Any) -> str:110 schema_sql = format_quotes(self._name, quote_char)111 112 if self._parent is not None:113 return "{parent}.{schema}".format(114 parent=self._parent.get_sql(quote_char=quote_char, **kwargs),115 schema=schema_sql,116 )117 118 return schema_sql119 120 121class Database(Schema):122 @ignore_copy123 def __getattr__(self, item: str) -> Schema:124 return Schema(item, parent=self)125 126 127class Table(Selectable):128 @staticmethod129 def _init_schema(schema: str | list | tuple | Schema | None) -> str | list | tuple | Schema | None:130 # This is a bit complicated in order to support backwards compatibility. It should probably be cleaned up for131 # the next major release. Schema is accepted as a string, list/tuple, Schema instance, or None132 if isinstance(schema, Schema):133 return schema134 if isinstance(schema, (list, tuple)):135 return reduce(lambda obj, s: Schema(s, parent=obj), schema[1:], Schema(schema[0]))136 if schema is not None:137 return Schema(schema)138 return None139 140 def __init__(141 self,142 name: str,143 schema: Schema | str | None = None,144 alias: str | None = None,145 query_cls: type[Query] | None = None,146 ) -> None:147 super().__init__(alias)148 self._table_name = name149 self._schema = self._init_schema(schema)150 self._query_cls = query_cls or Query151 self._for = None152 self._for_portion = None153 if not issubclass(self._query_cls, Query):154 raise TypeError("Expected 'query_cls' to be subclass of Query")155 156 def get_table_name(self) -> str:157 return self.alias or self._table_name158 159 def get_sql(self, **kwargs: Any) -> str:160 quote_char = kwargs.get("quote_char")161 table_sql = format_quotes(self._table_name, quote_char)162 163 if self._schema is not None:164 table_sql = "{schema}.{table}".format(schema=self._schema.get_sql(**kwargs), table=table_sql)165 166 if self._for:167 table_sql = "{table} FOR {criterion}".format(table=table_sql, criterion=self._for.get_sql(**kwargs))168 elif self._for_portion:169 table_sql = "{table} FOR PORTION OF {criterion}".format(170 table=table_sql, criterion=self._for_portion.get_sql(**kwargs)171 )172 173 return format_alias_sql(table_sql, self.alias, **kwargs)174 175 @builder176 def for_(self, temporal_criterion: Criterion) -> None:177 if self._for:178 raise AttributeError("'Query' object already has attribute for_")179 if self._for_portion:180 raise AttributeError("'Query' object already has attribute for_portion")181 self._for = temporal_criterion182 183 @builder184 def for_portion(self, period_criterion: PeriodCriterion) -> None:185 if self._for_portion:186 raise AttributeError("'Query' object already has attribute for_portion")187 if self._for:188 raise AttributeError("'Query' object already has attribute for_")189 self._for_portion = period_criterion190 191 def __str__(self) -> str:192 return self.get_sql(quote_char='"')193 194 def __eq__(self, other) -> bool:195 if not isinstance(other, Table):196 return False197 198 if self._table_name != other._table_name:199 return False200 201 if self._schema != other._schema:202 return False203 204 if self.alias != other.alias:205 return False206 207 return True208 209 def __repr__(self) -> str:210 if self._schema:211 return "Table('{}', schema='{}')".format(self._table_name, self._schema)212 return "Table('{}')".format(self._table_name)213 214 def __ne__(self, other: Any) -> bool:215 return not self.__eq__(other)216 217 def __hash__(self) -> int:218 return hash(str(self))219 220 def select(self, *terms: int | float | str | bool | Term | Field) -> QueryBuilder:221 """222 Perform a SELECT operation on the current table223 224 :param terms:225 Type: list[expression]226 227 A list of terms to select. These can be any type of int, float, str, bool or Term or a Field.228 229 :return: QueryBuilder230 """231 return self._query_cls.from_(self).select(*terms)232 233 def update(self) -> QueryBuilder:234 """235 Perform an UPDATE operation on the current table236 237 :return: QueryBuilder238 """239 return self._query_cls.update(self)240 241 def insert(self, *terms: int | float | str | bool | Term | Field) -> QueryBuilder:242 """243 Perform an INSERT operation on the current table244 245 :param terms:246 Type: list[expression]247 248 A list of terms to select. These can be any type of int, float, str, bool or any other valid data249 250 :return: QueryBuilder251 """252 return self._query_cls.into(self).insert(*terms)253 254 255def make_tables(*names: tuple[str, str] | str, **kwargs: Any) -> list[Table]:256 """257 Shortcut to create many tables. If `names` param is a tuple, the first258 position will refer to the `_table_name` while the second will be its `alias`.259 Any other data structure will be treated as a whole as the `_table_name`.260 """261 tables = []262 for name in names:263 if isinstance(name, tuple) and len(name) == 2:264 t = Table(265 name=name[0],266 alias=name[1],267 schema=kwargs.get("schema"),268 query_cls=kwargs.get("query_cls"),269 )270 else:271 t = Table(272 name=name,273 schema=kwargs.get("schema"),274 query_cls=kwargs.get("query_cls"),275 )276 tables.append(t)277 return tables278 279 280class Column:281 """Represents a column."""282 283 def __init__(284 self,285 column_name: str,286 column_type: str | None = None,287 nullable: bool | None = None,288 default: Any | Term | None = None,289 ) -> None:290 self.name = column_name291 self.type = column_type292 self.nullable = nullable293 self.default = default if default is None or isinstance(default, Term) else ValueWrapper(default)294 295 def get_name_sql(self, **kwargs: Any) -> str:296 quote_char = kwargs.get("quote_char")297 298 column_sql = "{name}".format(299 name=format_quotes(self.name, quote_char),300 )301 302 return column_sql303 304 def get_sql(self, **kwargs: Any) -> str:305 column_sql = "{name}{type}{nullable}{default}".format(306 name=self.get_name_sql(**kwargs),307 type=" {}".format(self.type) if self.type else "",308 nullable=" {}".format("NULL" if self.nullable else "NOT NULL") if self.nullable is not None else "",309 default=" {}".format("DEFAULT " + self.default.get_sql(**kwargs)) if self.default else "",310 )311 312 return column_sql313 314 def __str__(self) -> str:315 return self.get_sql(quote_char='"')316 317 318def make_columns(*names: tuple[str, str] | str) -> list[Column]:319 """320 Shortcut to create many columns. If `names` param is a tuple, the first321 position will refer to the `name` while the second will be its `type`.322 Any other data structure will be treated as a whole as the `name`.323 """324 columns = []325 for name in names:326 if isinstance(name, tuple) and len(name) == 2:327 column = Column(column_name=name[0], column_type=name[1])328 else:329 column = Column(column_name=name)330 columns.append(column)331 332 return columns333 334 335class PeriodFor:336 def __init__(self, name: str, start_column: str | Column, end_column: str | Column) -> None:337 self.name = name338 self.start_column = start_column if isinstance(start_column, Column) else Column(start_column)339 self.end_column = end_column if isinstance(end_column, Column) else Column(end_column)340 341 def get_sql(self, **kwargs: Any) -> str:342 quote_char = kwargs.get("quote_char")343 344 period_for_sql = "PERIOD FOR {name} ({start_column_name},{end_column_name})".format(345 name=format_quotes(self.name, quote_char),346 start_column_name=self.start_column.get_name_sql(**kwargs),347 end_column_name=self.end_column.get_name_sql(**kwargs),348 )349 350 return period_for_sql351 352 353# for typing in Query's methods354_TableClass = Table355 356 357class Query:358 """359 Query is the primary class and entry point in pypika. It is used to build queries iteratively using the builder360 design361 pattern.362 363 This class is immutable.364 365 Examples366 --------367 Simple query368 369 .. code-block:: python370 371 from pypika import Query, Field372 q = Query.from_('customers').select('*').where(Field("id") == 1)373 """374 375 @classmethod376 def _builder(cls, **kwargs: Any) -> QueryBuilder:377 return QueryBuilder(**kwargs)378 379 @classmethod380 def from_(cls, table: Selectable | str, **kwargs: Any) -> QueryBuilder:381 """382 Query builder entry point. Initializes query building and sets the table to select from. When using this383 function, the query becomes a SELECT query.384 385 :param table:386 Type: Table or str387 388 An instance of a Table object or a string table name.389 390 :return: QueryBuilder391 """392 return cls._builder(**kwargs).from_(table)393 394 @classmethod395 def create_table(cls, table: str | Table) -> CreateQueryBuilder:396 """397 Query builder entry point. Initializes query building and sets the table name to be created. When using this398 function, the query becomes a CREATE statement.399 400 :param table: An instance of a Table object or a string table name.401 402 :return: CreateQueryBuilder403 """404 return CreateQueryBuilder().create_table(table)405 406 @classmethod407 def create_index(cls, index: str | Index) -> CreateIndexBuilder:408 """409 Query builder entry point. Initializes query building and sets the index name to be created. When using this410 function, the query becomes a CREATE statement.411 """412 return CreateIndexBuilder().create_index(index)413 414 @classmethod415 def drop_database(cls, database: Database | Table) -> DropQueryBuilder:416 """417 Query builder entry point. Initializes query building and sets the table name to be dropped. When using this418 function, the query becomes a DROP statement.419 420 :param database: An instance of a Database object or a string database name.421 422 :return: DropQueryBuilder423 """424 return DropQueryBuilder().drop_database(database)425 426 @classmethod427 def drop_table(cls, table: str | Table) -> DropQueryBuilder:428 """429 Query builder entry point. Initializes query building and sets the table name to be dropped. When using this430 function, the query becomes a DROP statement.431 432 :param table: An instance of a Table object or a string table name.433 434 :return: DropQueryBuilder435 """436 return DropQueryBuilder().drop_table(table)437 438 @classmethod439 def drop_user(cls, user: str) -> DropQueryBuilder:440 """441 Query builder entry point. Initializes query building and sets the table name to be dropped. When using this442 function, the query becomes a DROP statement.443 444 :param user: String user name.445 446 :return: DropQueryBuilder447 """448 return DropQueryBuilder().drop_user(user)449 450 @classmethod451 def drop_view(cls, view: str) -> DropQueryBuilder:452 """453 Query builder entry point. Initializes query building and sets the table name to be dropped. When using this454 function, the query becomes a DROP statement.455 456 :param view: String view name.457 458 :return: DropQueryBuilder459 """460 return DropQueryBuilder().drop_view(view)461 462 @classmethod463 def drop_index(cls, index: str | Index) -> DropQueryBuilder:464 """465 Query builder entry point. Initializes query building and sets the index name to be dropped. When using this466 function, the query becomes a DROP statement.467 """468 return DropQueryBuilder().drop_index(index)469 470 @classmethod471 def into(cls, table: Table | str, **kwargs: Any) -> QueryBuilder:472 """473 Query builder entry point. Initializes query building and sets the table to insert into. When using this474 function, the query becomes an INSERT query.475 476 :param table:477 Type: Table or str478 479 An instance of a Table object or a string table name.480 481 :return QueryBuilder482 """483 return cls._builder(**kwargs).into(table)484 485 @classmethod486 def with_(cls, table: str | Selectable, name: str, **kwargs: Any) -> QueryBuilder:487 return cls._builder(**kwargs).with_(table, name)488 489 @classmethod490 def select(cls, *terms: int | float | str | bool | Term, **kwargs: Any) -> QueryBuilder:491 """492 Query builder entry point. Initializes query building without a table and selects fields. Useful when testing493 SQL functions.494 495 :param terms:496 Type: list[expression]497 498 A list of terms to select. These can be any type of int, float, str, bool, or Term. They cannot be a Field499 unless the function ``Query.from_`` is called first.500 501 :return: QueryBuilder502 """503 return cls._builder(**kwargs).select(*terms)504 505 @classmethod506 def update(cls, table: str | Table, **kwargs) -> QueryBuilder:507 """508 Query builder entry point. Initializes query building and sets the table to update. When using this509 function, the query becomes an UPDATE query.510 511 :param table:512 Type: Table or str513 514 An instance of a Table object or a string table name.515 516 :return: QueryBuilder517 """518 return cls._builder(**kwargs).update(table)519 520 @classmethod521 def Table(cls, table_name: str, **kwargs) -> _TableClass:522 """523 Convenience method for creating a Table that uses this Query class.524 525 :param table_name:526 Type: str527 528 A string table name.529 530 :return: Table531 """532 kwargs["query_cls"] = cls533 return Table(table_name, **kwargs)534 535 @classmethod536 def Tables(cls, *names: tuple[str, str] | str, **kwargs: Any) -> list[_TableClass]:537 """538 Convenience method for creating many tables that uses this Query class.539 See ``Query.make_tables`` for details.540 541 :param names:542 Type: list[str or tuple]543 544 A list of string table names, or name and alias tuples.545 546 :return: Table547 """548 kwargs["query_cls"] = cls549 return make_tables(*names, **kwargs)550 551 552class _SetOperation(Selectable, Term):553 """554 A Query class wrapper for a all set operations, Union DISTINCT or ALL, Intersect, Except or Minus555 556 Created via the functions `Query.union`,`Query.union_all`,`Query.intersect`, `Query.except_of`,`Query.minus`.557 558 This class should not be instantiated directly.559 """560 561 def __init__(562 self,563 base_query: QueryBuilder,564 set_operation_query: QueryBuilder,565 set_operation: SetOperation,566 alias: str | None = None,567 wrapper_cls: type[ValueWrapper] = ValueWrapper,568 ):569 super().__init__(alias)570 self.base_query = base_query571 self._set_operation = [(set_operation, set_operation_query)]572 self._orderbys = []573 574 self._limit = None575 self._offset = None576 577 self._wrapper_cls = wrapper_cls578 579 @builder580 def orderby(self, *fields: Field, **kwargs: Any) -> None:581 for field in fields:582 field = (583 Field(field, table=self.base_query._from[0])584 if isinstance(field, str)585 else self.base_query.wrap_constant(field)586 )587 588 self._orderbys.append((field, kwargs.get("order")))589 590 @builder591 def limit(self, limit: int) -> None:592 self._limit = limit593 594 @builder595 def offset(self, offset: int) -> None:596 self._offset = offset597 598 @builder599 def union(self, other: Selectable) -> None:600 self._set_operation.append((SetOperation.union, other))601 602 @builder603 def union_all(self, other: Selectable) -> None:604 self._set_operation.append((SetOperation.union_all, other))605 606 @builder607 def intersect(self, other: Selectable) -> None:608 self._set_operation.append((SetOperation.intersect, other))609 610 @builder611 def except_of(self, other: Selectable) -> None:612 self._set_operation.append((SetOperation.except_of, other))613 614 @builder615 def minus(self, other: Selectable) -> None:616 self._set_operation.append((SetOperation.minus, other))617 618 def __add__(self, other: Selectable) -> _SetOperation:619 return self.union(other)620 621 def __mul__(self, other: Selectable) -> _SetOperation:622 return self.union_all(other)623 624 def __sub__(self, other: QueryBuilder) -> _SetOperation:625 return self.minus(other)626 627 def __str__(self) -> str:628 return self.get_sql()629 630 def get_sql(self, with_alias: bool = False, subquery: bool = False, **kwargs: Any) -> str:631 set_operation_template = " {type} {query_string}"632 633 kwargs.setdefault("dialect", self.base_query.dialect)634 # This initializes the quote char based on the base query, which could be a dialect specific query class635 # This might be overridden if quote_char is set explicitly in kwargs636 kwargs.setdefault("quote_char", self.base_query.QUOTE_CHAR)637 638 base_querystring = self.base_query.get_sql(subquery=self.base_query.wrap_set_operation_queries, **kwargs)639 640 querystring = base_querystring641 for set_operation, set_operation_query in self._set_operation:642 set_operation_querystring = set_operation_query.get_sql(643 subquery=self.base_query.wrap_set_operation_queries, **kwargs644 )645 646 if len(self.base_query._selects) != len(set_operation_query._selects):647 raise SetOperationException(648 "Queries must have an equal number of select statements in a set operation."649 "\n\nMain Query:\n{query1}\n\nSet Operations Query:\n{query2}".format(650 query1=base_querystring, query2=set_operation_querystring651 )652 )653 654 querystring += set_operation_template.format(655 type=set_operation.value, query_string=set_operation_querystring656 )657 658 if self._orderbys:659 querystring += self._orderby_sql(**kwargs)660 661 if self._limit is not None:662 querystring += self._limit_sql()663 664 if self._offset:665 querystring += self._offset_sql()666 667 if subquery:668 querystring = "({query})".format(query=querystring, **kwargs)669 670 if with_alias:671 return format_alias_sql(querystring, self.alias or self._table_name, **kwargs)672 673 return querystring674 675 def _orderby_sql(self, quote_char: str | None = None, **kwargs: Any) -> str:676 """677 Produces the ORDER BY part of the query. This is a list of fields and possibly their directionality, ASC or678 DESC. The clauses are stored in the query under self._orderbys as a list of tuples containing the field and679 directionality (which can be None).680 681 If an order by field is used in the select clause, determined by a matching , then the ORDER BY clause will use682 the alias, otherwise the field will be rendered as SQL.683 """684 clauses = []685 selected_aliases = {s.alias for s in self.base_query._selects}686 for field, directionality in self._orderbys:687 term = (688 format_quotes(field.alias, quote_char)689 if field.alias and field.alias in selected_aliases690 else field.get_sql(quote_char=quote_char, **kwargs)691 )692 693 clauses.append(694 "{term} {orient}".format(term=term, orient=directionality.value) if directionality is not None else term695 )696 697 return " ORDER BY {orderby}".format(orderby=",".join(clauses))698 699 def _offset_sql(self) -> str:700 return " OFFSET {offset}".format(offset=self._offset)701 702 def _limit_sql(self) -> str:703 return " LIMIT {limit}".format(limit=self._limit)704 705 706class QueryBuilder(Selectable, Term):707 """708 Query Builder is the main class in pypika which stores the state of a query and offers functions which allow the709 state to be branched immutably.710 """711 712 QUOTE_CHAR = '"'713 SECONDARY_QUOTE_CHAR = "'"714 ALIAS_QUOTE_CHAR = None715 QUERY_ALIAS_QUOTE_CHAR = None716 QUERY_CLS = Query717 718 def __init__(719 self,720 dialect: Dialects | None = None,721 wrap_set_operation_queries: bool = True,722 wrapper_cls: type[ValueWrapper] = ValueWrapper,723 immutable: bool = True,724 as_keyword: bool = False,725 ):726 super().__init__(None)727 728 self._from = []729 self._insert_table = None730 self._update_table = None731 self._delete_from = False732 self._replace = False733 734 self._with = []735 self._selects = []736 self._force_indexes = []737 self._use_indexes = []738 self._columns = []739 self._values = []740 self._distinct = False741 self._ignore = False742 743 self._for_update = False744 745 self._wheres = None746 self._prewheres = None747 self._groupbys = []748 self._with_totals = False749 self._havings = None750 self._qualifys = None751 self._orderbys = []752 self._joins = []753 self._unions = []754 self._using = []755 756 self._limit = None757 self._offset = None758 759 self._updates = []760 761 self._select_star = False762 self._select_star_tables = set()763 self._mysql_rollup = False764 self._select_into = False765 766 self._subquery_count = 0767 self._foreign_table = False768 769 self.dialect = dialect770 self.as_keyword = as_keyword771 self.wrap_set_operation_queries = wrap_set_operation_queries772 773 self._wrapper_cls = wrapper_cls774 775 self.immutable = immutable776 777 def __copy__(self) -> QueryBuilder:778 newone = type(self).__new__(type(self))779 newone.__dict__.update(self.__dict__)780 newone._select_star_tables = copy(self._select_star_tables)781 newone._from = copy(self._from)782 newone._with = copy(self._with)783 newone._selects = copy(self._selects)784 newone._columns = copy(self._columns)785 newone._values = copy(self._values)786 newone._groupbys = copy(self._groupbys)787 newone._orderbys = copy(self._orderbys)788 newone._joins = copy(self._joins)789 newone._unions = copy(self._unions)790 newone._updates = copy(self._updates)791 newone._force_indexes = copy(self._force_indexes)792 newone._use_indexes = copy(self._use_indexes)793 return newone794 795 @builder796 def from_(self, selectable: Selectable | Query | str) -> None:797 """798 Adds a table to the query. This function can only be called once and will raise an AttributeError if called a799 second time.800 801 :param selectable:802 Type: ``Table``, ``Query``, or ``str``803 804 When a ``str`` is passed, a table with the name matching the ``str`` value is used.805 806 :returns807 A copy of the query with the table added.808 """809 810 self._from.append(Table(selectable) if isinstance(selectable, str) else selectable)811 812 if isinstance(selectable, (QueryBuilder, _SetOperation)) and selectable.alias is None:813 if isinstance(selectable, QueryBuilder):814 sub_query_count = selectable._subquery_count815 else:816 sub_query_count = 0817 818 sub_query_count = max(self._subquery_count, sub_query_count)819 selectable.alias = "sq%d" % sub_query_count820 self._subquery_count = sub_query_count + 1821 822 @builder823 def replace_table(self, current_table: Table | None, new_table: Table | None) -> None:824 """825 Replaces all occurrences of the specified table with the new table. Useful when reusing fields across826 queries.827 828 :param current_table:829 The table instance to be replaces.830 :param new_table:831 The table instance to replace with.832 :return:833 A copy of the query with the tables replaced.834 """835 self._from = [new_table if table == current_table else table for table in self._from]836 self._insert_table = new_table if self._insert_table == current_table else self._insert_table837 self._update_table = new_table if self._update_table == current_table else self._update_table838 839 self._with = [alias_query.replace_table(current_table, new_table) for alias_query in self._with]840 self._selects = [select.replace_table(current_table, new_table) for select in self._selects]841 self._columns = [column.replace_table(current_table, new_table) for column in self._columns]842 self._values = [843 [value.replace_table(current_table, new_table) for value in value_list] for value_list in self._values844 ]845 846 self._wheres = self._wheres.replace_table(current_table, new_table) if self._wheres else None847 self._prewheres = self._prewheres.replace_table(current_table, new_table) if self._prewheres else None848 self._groupbys = [groupby.replace_table(current_table, new_table) for groupby in self._groupbys]849 self._havings = self._havings.replace_table(current_table, new_table) if self._havings else None850 self._qualifys = self._qualifys.replace_table(current_table, new_table) if self._qualifys else None851 self._orderbys = [852 (orderby[0].replace_table(current_table, new_table), orderby[1]) for orderby in self._orderbys853 ]854 self._joins = [join.replace_table(current_table, new_table) for join in self._joins]855 856 if current_table in self._select_star_tables:857 self._select_star_tables.remove(current_table)858 self._select_star_tables.add(new_table)859 860 @builder861 def with_(self, selectable: Selectable, name: str) -> None:862 t = AliasedQuery(name, selectable)863 self._with.append(t)864 865 @builder866 def into(self, table: str | Table) -> None:867 if self._insert_table is not None:868 raise AttributeError("'Query' object has no attribute '%s'" % "into")869 870 if self._selects:871 self._select_into = True872 873 self._insert_table = table if isinstance(table, Table) else Table(table)874 875 @builder876 def select(self, *terms: Any) -> None:877 for term in terms:878 if isinstance(term, Field):879 self._select_field(term)880 elif isinstance(term, str):881 self._select_field_str(term)882 elif isinstance(term, (Function, ArithmeticExpression)):883 self._select_other(term)884 else:885 self._select_other(self.wrap_constant(term, wrapper_cls=self._wrapper_cls))886 887 @builder888 def delete(self) -> None:889 if self._delete_from or self._selects or self._update_table:890 raise AttributeError("'Query' object has no attribute '%s'" % "delete")891 892 self._delete_from = True893 894 @builder895 def update(self, table: str | Table) -> None:896 if self._update_table is not None or self._selects or self._delete_from:897 raise AttributeError("'Query' object has no attribute '%s'" % "update")898 899 self._update_table = table if isinstance(table, Table) else Table(table)900 901 @builder902 def columns(self, *terms: Any) -> None:903 if self._insert_table is None:904 raise AttributeError("'Query' object has no attribute '%s'" % "insert")905 906 if terms and isinstance(terms[0], (list, tuple)):907 terms = terms[0]908 909 for term in terms:910 if isinstance(term, str):911 term = Field(term, table=self._insert_table)912 self._columns.append(term)913 914 @builder915 def insert(self, *terms: Any) -> None:916 self._apply_terms(*terms)917 self._replace = False918 919 @builder920 def replace(self, *terms: Any) -> None:921 self._apply_terms(*terms)922 self._replace = True923 924 @builder925 def force_index(self, term: str | Index, *terms: str | Index) -> None:926 for t in (term, *terms):927 if isinstance(t, Index):928 self._force_indexes.append(t)929 elif isinstance(t, str):930 self._force_indexes.append(Index(t))931 932 @builder933 def use_index(self, term: str | Index, *terms: str | Index) -> None:934 for t in (term, *terms):935 if isinstance(t, Index):936 self._use_indexes.append(t)937 elif isinstance(t, str):938 self._use_indexes.append(Index(t))939 940 @builder941 def distinct(self) -> None:942 self._distinct = True943 944 @builder945 def for_update(self) -> None:946 self._for_update = True947 948 @builder949 def ignore(self) -> None:950 self._ignore = True951 952 @builder953 def prewhere(self, criterion: Criterion) -> None:954 if not self._validate_table(criterion):955 self._foreign_table = True956 957 if self._prewheres:958 self._prewheres &= criterion959 else:960 self._prewheres = criterion961 962 @builder963 def where(self, criterion: Term | EmptyCriterion) -> None:964 if isinstance(criterion, EmptyCriterion):965 return966 967 if not self._validate_table(criterion):968 self._foreign_table = True969 970 if self._wheres:971 self._wheres &= criterion972 else:973 self._wheres = criterion974 975 @builder976 def having(self, criterion: Term | EmptyCriterion) -> None:977 if isinstance(criterion, EmptyCriterion):978 return979 980 if self._havings:981 self._havings &= criterion982 else:983 self._havings = criterion984 985 @builder986 def qualify(self, criterion: Term | EmptyCriterion) -> None:987 if isinstance(criterion, EmptyCriterion):988 return989 990 if self._qualifys:991 self._qualifys &= criterion992 else:993 self._qualifys = criterion994 995 @builder996 def groupby(self, *terms: str | int | Term) -> None:997 for term in terms:998 if isinstance(term, str):999 term = Field(term, table=self._from[0])1000 elif isinstance(term, int):1001 term = Field(str(term), table=self._from[0]).wrap_constant(term)1002 1003 self._groupbys.append(term)1004 1005 @builder1006 def with_totals(self) -> None:1007 self._with_totals = True1008 1009 @builder1010 def rollup(self, *terms: list | tuple | set | Term, **kwargs: Any) -> None:1011 for_mysql = "mysql" == kwargs.get("vendor")1012 1013 if self._mysql_rollup:1014 raise AttributeError("'Query' object has no attribute '%s'" % "rollup")1015 1016 terms = [Tuple(*term) if isinstance(term, (list, tuple, set)) else term for term in terms]1017 1018 if for_mysql:1019 # MySQL rolls up all of the dimensions always1020 if not terms and not self._groupbys:1021 raise RollupException(1022 "At least one group is required. Call Query.groupby(term) or pass" "as parameter to rollup."1023 )1024 1025 self._mysql_rollup = True1026 self._groupbys += terms1027 1028 elif 0 < len(self._groupbys) and isinstance(self._groupbys[-1], Rollup):1029 # If a rollup was added last, then append the new terms to the previous rollup1030 self._groupbys[-1].args += terms1031 1032 else:1033 self._groupbys.append(Rollup(*terms))1034 1035 @builder1036 def orderby(self, *fields: Any, **kwargs: Any) -> None:1037 for field in fields:1038 field = Field(field, table=self._from[0]) if isinstance(field, str) else self.wrap_constant(field)1039 1040 self._orderbys.append((field, kwargs.get("order")))1041 1042 @builder1043 def join(1044 self, item: Table | QueryBuilder | AliasedQuery | Selectable, how: JoinType = JoinType.inner1045 ) -> Joiner[Self]:1046 if isinstance(item, Table):1047 return Joiner(self, item, how, type_label="table")1048 1049 elif isinstance(item, QueryBuilder):1050 if item.alias is None:1051 self._tag_subquery(item)1052 return Joiner(self, item, how, type_label="subquery")1053 1054 elif isinstance(item, AliasedQuery):1055 return Joiner(self, item, how, type_label="table")1056 1057 elif isinstance(item, Selectable):1058 return Joiner(self, item, how, type_label="subquery")1059 1060 raise ValueError("Cannot join on type '%s'" % type(item))1061 1062 def inner_join(self, item: Table | QueryBuilder | AliasedQuery) -> Joiner[Self]:1063 return self.join(item, JoinType.inner)1064 1065 def left_join(self, item: Table | QueryBuilder | AliasedQuery) -> Joiner[Self]:1066 return self.join(item, JoinType.left)1067 1068 def left_outer_join(self, item: Table | QueryBuilder | AliasedQuery) -> Joiner[Self]:1069 return self.join(item, JoinType.left_outer)1070 1071 def right_join(self, item: Table | QueryBuilder | AliasedQuery) -> Joiner[Self]:1072 return self.join(item, JoinType.right)1073 1074 def right_outer_join(self, item: Table | QueryBuilder | AliasedQuery) -> Joiner[Self]:1075 return self.join(item, JoinType.right_outer)1076 1077 def outer_join(self, item: Table | QueryBuilder | AliasedQuery) -> Joiner[Self]:1078 return self.join(item, JoinType.outer)1079 1080 def full_outer_join(self, item: Table | QueryBuilder | AliasedQuery) -> Joiner[Self]:1081 return self.join(item, JoinType.full_outer)1082 1083 def cross_join(self, item: Table | QueryBuilder | AliasedQuery) -> Joiner[Self]:1084 return self.join(item, JoinType.cross)1085 1086 def hash_join(self, item: Table | QueryBuilder | AliasedQuery) -> Joiner[Self]:1087 return self.join(item, JoinType.hash)1088 1089 @builder1090 def limit(self, limit: int) -> None:1091 self._limit = limit1092 1093 @builder1094 def offset(self, offset: int) -> None:1095 self._offset = offset1096 1097 @builder1098 def union(self, other: QueryBuilder) -> _SetOperation:1099 return _SetOperation(self, other, SetOperation.union, wrapper_cls=self._wrapper_cls)1100 1101 @builder1102 def union_all(self, other: QueryBuilder) -> _SetOperation:1103 return _SetOperation(self, other, SetOperation.union_all, wrapper_cls=self._wrapper_cls)1104 1105 @builder1106 def intersect(self, other: QueryBuilder) -> _SetOperation:1107 return _SetOperation(self, other, SetOperation.intersect, wrapper_cls=self._wrapper_cls)1108 1109 @builder1110 def except_of(self, other: QueryBuilder) -> _SetOperation:1111 return _SetOperation(self, other, SetOperation.except_of, wrapper_cls=self._wrapper_cls)1112 1113 @builder1114 def minus(self, other: QueryBuilder) -> _SetOperation:1115 return _SetOperation(self, other, SetOperation.minus, wrapper_cls=self._wrapper_cls)1116 1117 @builder1118 def set(self, field: Field | str, value: Any) -> None:1119 field = Field(field) if not isinstance(field, Field) else field1120 if not isinstance(value, Term):1121 value = self.wrap_constant(value, wrapper_cls=self._wrapper_cls)1122 self._updates.append((field, value))1123 1124 def __add__(self, other: QueryBuilder) -> _SetOperation:1125 return self.union(other)1126 1127 def __mul__(self, other: QueryBuilder) -> _SetOperation:1128 return self.union_all(other)1129 1130 def __sub__(self, other: QueryBuilder) -> _SetOperation:1131 return self.minus(other)1132 1133 @builder1134 def slice(self, slice: slice) -> None:1135 self._offset = slice.start1136 self._limit = slice.stop1137 1138 def __getitem__(self, item: Any) -> QueryBuilder | Field:1139 if not isinstance(item, slice):1140 return super().__getitem__(item)1141 return self.slice(item)1142 1143 @staticmethod1144 def _list_aliases(field_set: Sequence[Field], quote_char: str | None = None) -> list[str]:1145 return [field.alias or field.get_sql(quote_char=quote_char) for field in field_set]1146 1147 def _select_field_str(self, term: str) -> None:1148 if 0 == len(self._from):1149 raise QueryException(f"Cannot select {term}, no FROM table specified.")1150 1151 if term == "*":1152 self._select_star = True1153 self._selects = [Star()]1154 return1155 1156 self._select_field(Field(term, table=self._from[0]))1157 1158 def _select_field(self, term: Field) -> None:1159 if self._select_star:1160 # Do not add select terms after a star is selected1161 return1162 1163 if term.table in self._select_star_tables:1164 # Do not add select terms for table after a table star is selected1165 return1166 1167 if isinstance(term, Star):1168 self._selects = [1169 select for select in self._selects if not hasattr(select, "table") or term.table != select.table1170 ]1171 self._select_star_tables.add(term.table)1172 1173 self._selects.append(term)1174 1175 def _select_other(self, function: Function) -> None:1176 self._selects.append(function)1177 1178 def fields_(self) -> list[Field]:1179 # Don't return anything here. Subqueries have their own fields.1180 return []1181 1182 def do_join(self, join: Join) -> None:1183 base_tables = self._from + [self._update_table] + self._with1184 join.validate(base_tables, self._joins)1185 1186 table_in_query = any(isinstance(clause, Table) and join.item in base_tables for clause in base_tables)1187 if isinstance(join.item, Table) and join.item.alias is None and table_in_query:1188 # On the odd chance that we join the same table as the FROM table and don't set an alias1189 # FIXME only works once1190 join.item.alias = join.item._table_name + "2"1191 1192 self._joins.append(join)1193 1194 def is_joined(self, table: Table) -> bool:1195 return any(table == join.item for join in self._joins)1196 1197 def _validate_table(self, term: Term) -> bool:1198 """1199 Returns False if the term references a table not already part of the1200 FROM clause or JOINS and True otherwise.