Query-builder

  • class Node

  • Base-class for all components which make up the AST for a SQL query.

    • static copy(method)
    • Decorator to use with Node methods that mutate the node’s state.This allows method-chaining, e.g.:
  1. query = MyModel.select()
  2. new_query = query.where(MyModel.field == 'value')
  • unwrap()

  • API for recursively unwrapping “wrapped” nodes. Base case is toreturn self.

  • is_alias()

  • API for determining if a node, at any point, has been explicitlyaliased by the user.
  • class Source([alias=None])
  • A source of row tuples, for example a table, join, or select query. Bydefault provides a “magic” attribute named “c” that is a factory forcolumn/attribute lookups, for example:
  1. User = Table('users')
  2. query = (User
  3.          .select(User.c.username)
  4.          .where(User.c.active == True)
  5.          .order_by(User.c.username))
  • alias(name)

  • Returns a copy of the object with the given alias applied.

  • select(*columns)

Parameters:columns – Column instances, expressions, functions,sub-queries, or anything else that you would like to select.

Create a Select query on the table. If the table explicitlydeclares columns and no columns are provided, then by default all thetable’s defined columns will be selected.

  • join(dest[, join_type='INNER'[, on=None]])

Parameters:

  1. - **dest** ([_Source_](#Source)) – Join the table with the given destination.
  2. - **join_type** (_str_) – Join type.
  3. - **on** – Expression to use as join predicate.Returns:

a Join instance.

Join type may be one of:

  1. - <code>JOIN.INNER</code>
  2. - <code>JOIN.LEFT_OUTER</code>
  3. - <code>JOIN.RIGHT_OUTER</code>
  4. - <code>JOIN.FULL</code>
  5. - <code>JOIN.FULL_OUTER</code>
  6. - <code>JOIN.CROSS</code>
  • leftouter_join(_dest[, on=None])

Parameters:

  1. - **dest** ([_Source_](#Source)) – Join the table with the given destination.
  2. - **on** – Expression to use as join predicate.Returns:

a Join instance.

Convenience method for calling join() using a LEFTOUTER join.

  • class BaseTable

  • Base class for table-like objects, which support JOINs via operatoroverloading.

    • and(dest)

    • Perform an INNER join on dest.

    • add(dest)

    • Perform a LEFT OUTER join on dest.

    • sub(dest)

    • Perform a RIGHT OUTER join on dest.

    • or(dest)

    • Perform a FULL OUTER join on dest.

    • mul(dest)

    • Perform a CROSS join on dest.
  • class Table(name[, columns=None[, primary_key=None[, schema=None[, alias=None]]]])
  • Represents a table in the database (or a table-like object such as a view).

Parameters:

  • name (str) – Database table name
  • columns (tuple) – List of column names (optional).
  • primary_key (str) – Name of primary key column.
  • schema (str) – Schema name used to access table (if necessary).
  • alias (str) – Alias to use for table in SQL queries.

Note

If columns are specified, the magic “c” attribute will be disabled.

When columns are not explicitly defined, tables have a special attribute“c” which is a factory that provides access to table columns dynamically.

Example:

  1. User = Table('users')
  2. query = (User
  3.          .select(User.c.id, User.c.username)
  4.          .order_by(User.c.username))

Equivalent example when columns are specified:

  1. User = Table('users', ('id', 'username'))
  2. query = (User
  3.          .select(User.id, User.username)
  4.          .order_by(User.username))
  • bind([database=None])

Parameters:database – Database object.

Bind this table to the given database (or unbind by leaving empty).

When a table is bound to a database, queries may be executed againstit without the need to specify the database in the query’s executemethod.

  • bindctx([_database=None])

Parameters:database – Database object.

Return a context manager that will bind the table to the given databasefor the duration of the wrapped block.

  • select(*columns)

Parameters:columns – Column instances, expressions, functions,sub-queries, or anything else that you would like to select.

Create a Select query on the table. If the table explicitlydeclares columns and no columns are provided, then by default all thetable’s defined columns will be selected.

Example:

  1. User = Table('users', ('id', 'username'))
  2.  
  3. # Because columns were defined on the Table, we will default to
  4. # selecting both of the User table's columns.
  5. # Evaluates to SELECT id, username FROM users
  6. query = User.select()
  7.  
  8. Note = Table('notes')
  9. query = (Note
  10.          .select(Note.c.content, Note.c.timestamp, User.username)
  11.          .join(User, on=(Note.c.user_id == User.id))
  12.          .where(Note.c.is_published == True)
  13.          .order_by(Note.c.timestamp.desc()))
  14.  
  15. # Using a function to select users and the number of notes they
  16. # have authored.
  17. query = (User
  18.          .select(
  19.             User.username,
  20.             fn.COUNT(Note.c.id).alias('n_notes'))
  21.          .join(
  22.             Note,
  23.             JOIN.LEFT_OUTER,
  24.             on=(User.id == Note.c.user_id))
  25.          .order_by(fn.COUNT(Note.c.id).desc()))
  • insert([insert=None[, columns=None[, **kwargs]]])

Parameters:

  1. - **insert** – A dictionary mapping column to value, an iterable thatyields dictionaries (i.e. list), or a [<code>Select</code>](#Select) query.
  2. - **columns** (_list_) – The list of columns to insert into when thedata being inserted is not a dictionary.
  3. - **kwargs** – Mapping of column-name to value.

Create a Insert query into the table.

  • replace([insert=None[, columns=None[, **kwargs]]])

Parameters:

  1. - **insert** – A dictionary mapping column to value, an iterable thatyields dictionaries (i.e. list), or a [<code>Select</code>](#Select) query.
  2. - **columns** (_list_) – The list of columns to insert into when thedata being inserted is not a dictionary.
  3. - **kwargs** – Mapping of column-name to value.

Create a Insert query into the table whose conflictresolution method is to replace.

  • update([update=None[, **kwargs]])

Parameters:

  1. - **update** – A dictionary mapping column to value.
  2. - **kwargs** – Mapping of column-name to value.

Create a Update query for the table.

  • delete()
  • Create a Delete query for the table.
  • class Join(lhs, rhs[, join_type=JOIN.INNER[, on=None[, alias=None]]])
  • Represent a JOIN between to table-like objects.

Parameters:

  • lhs – Left-hand side of the join.
  • rhs – Right-hand side of the join.
  • join_type – Type of join. e.g. JOIN.INNER, JOIN.LEFT_OUTER, etc.
  • on – Expression describing the join predicate.
  • alias (str) – Alias to apply to joined data.
  • on(predicate)

Parameters:predicate (Expression) – join predicate.

Specify the predicate expression used for this join.

  • class ValuesList(values[, columns=None[, alias=None]])
  • Represent a values list that can be used like a table.

Parameters:

  • values – a list-of-lists containing the row data to represent.
  • columns (list) – the names to give to the columns in each row.
  • alias (str) – alias to use for values-list.

Example:

  1. data = [(1, 'first'), (2, 'second')]
  2. vl = ValuesList(data, columns=('idx', 'name'))
  3.  
  4. query = (vl
  5.          .select(vl.c.idx, vl.c.name)
  6.          .order_by(vl.c.idx))
  7. # Yields:
  8. # SELECT t1.idx, t1.name
  9. # FROM (VALUES (1, 'first'), (2, 'second')) AS t1(idx, name)
  10. # ORDER BY t1.idx
  • columns(*names)

Parameters:names – names to apply to the columns of data.

Example:

  1. vl = ValuesList([(1, 'first'), (2, 'second')])
  2. vl = vl.columns('idx', 'name').alias('v')
  3.  
  4. query = vl.select(vl.c.idx, vl.c.name)
  5. # Yields:
  6. # SELECT v.idx, v.name
  7. # FROM (VALUES (1, 'first'), (2, 'second')) AS v(idx, name)
  • class CTE(name, query[, recursive=False[, columns=None]])
  • Represent a common-table-expression. For example queries, see Common Table Expressions.

Parameters:

  • name – Name for the CTE.
  • query – Select query describing CTE.
  • recursive (bool) – Whether the CTE is recursive.
  • columns (list) – Explicit list of columns produced by CTE (optional).
  • selectfrom(*columns_)
  • Create a SELECT query that utilizes the given common table expressionas the source for a new query.

Parameters:columns – One or more columns to select from the CTE.Returns:Select query utilizing the common table expression

  • unionall(_other)
  • Used on the base-case CTE to construct the recursive term of the CTE.

Parameters:other – recursive term, generally a Select query.Returns:a recursive CTE with the given recursive term.

  • class ColumnBase
  • Base-class for column-like objects, attributes or expressions.

Column-like objects can be composed using various operators and specialmethods.

  • &: Logical AND
  • |: Logical OR
  • +: Addition
  • -: Subtraction
  • *: Multiplication
  • /: Division
  • ^: Exclusive-OR
  • ==: Equality
  • !=: Inequality
  • >: Greater-than
  • <: Less-than
  • >=: Greater-than or equal
  • <=: Less-than or equal
  • <<: IN
  • >>: IS (i.e. IS NULL)
  • %: LIKE
  • **: ILIKE
  • bin_and(): Binary AND
  • bin_or(): Binary OR
  • in_(): IN
  • not_in(): NOT IN
  • regexp(): REGEXP
  • is_null(True/False): IS NULL or IS NOT NULL
  • contains(s): LIKE %s%
  • startswith(s): LIKE s%
  • endswith(s): LIKE %s
  • between(low, high): BETWEEN low AND high
  • concat(): ||
  • alias(alias)

Parameters:alias (str) – Alias for the given column-like object.Returns:a Alias object.

Indicate the alias that should be given to the specified column-likeobject.

  • cast(as_type)

Parameters:as_type (str) – Type name to cast to.Returns:a Cast object.

Create a CAST expression.

  • asc([collation=None[, nulls=None]])

Parameters:

  1. - **collation** (_str_) – Collation name to use for sorting.
  2. - **nulls** (_str_) – Sort nulls (FIRST or LAST).Returns:

an ascending Ordering object for the column.

  • desc([collation=None[, nulls=None]])

Parameters:

  1. - **collation** (_str_) – Collation name to use for sorting.
  2. - **nulls** (_str_) – Sort nulls (FIRST or LAST).Returns:

an descending Ordering object for the column.

  • invert()

Returns:a Negated wrapper for the column.

  • class Column(source, name)

Parameters:

  • source (Source) – Source for column.
  • name (str) – Column name.

Column on a table or a column returned by a sub-query.

  • class Alias(node, alias)

Parameters:

  • node (Node) – a column-like object.
  • alias (str) – alias to assign to column.

Create a named alias for the given column-like object.

  • alias([alias=None])

Parameters:alias (str) – new name (or None) for aliased column.

Create a new Alias for the aliased column-like object. Ifthe new alias is None, then the original column-like object isreturned.

  • class Negated(node)
  • Represents a negated column-like object.
  • class Value(value[, converterNone[, unpack=True]])

Parameters:

  • value – Python object or scalar value.
  • converter – Function used to convert value into type the databaseunderstands.
  • unpack (bool) – Whether lists or tuples should be unpacked into a listof values or treated as-is.

Value to be used in a parameterized query. It is the responsibility of thecaller to ensure that the value passed in can be adapted to a type thedatabase driver understands.

  • AsIs(value)
  • Represents a Value that is treated as-is, and passed directlyback to the database driver. This may be useful if you are using databaseextensions that accept native Python data-types and you do not wish Peeweeto impose any handling of the values.
  • class Cast(node, cast)

Parameters:

  • node – A column-like object.
  • cast (str) – Type to cast to.

Represents a CAST(<node> AS <cast>) expression.

  • class Ordering(node, direction[, collation=None[, nulls=None]])

Parameters:

  • node – A column-like object.
  • direction (str) – ASC or DESC
  • collation (str) – Collation name to use for sorting.
  • nulls (str) – Sort nulls (FIRST or LAST).

Represent ordering by a column-like object.

Postgresql supports a non-standard clause (“NULLS FIRST/LAST”). Peewee willautomatically use an equivalent CASE statement for databases that donot support this (Sqlite / MySQL).

  • collate([collation=None])

Parameters:collation (str) – Collation name to use for sorting.

  • Asc(node[, collation=None[, nulls=None]])
  • Short-hand for instantiating an ascending Ordering object.
  • Desc(node[, collation=None[, nulls=None]])
  • Short-hand for instantiating an descending Ordering object.
  • class Expression(lhs, op, rhs[, flat=True])

Parameters:

  • lhs – Left-hand side.
  • op – Operation.
  • rhs – Right-hand side.
  • flat (bool) – Whether to wrap expression in parentheses.

Represent a binary expression of the form (lhs op rhs), e.g. (foo + 1).

  • class Entity(*path)

Parameters:path – Components that make up the dotted-path of the entity name.

Represent a quoted entity in a query, such as a table, column, alias. Thename may consist of multiple components, e.g. “a_table”.”column_name”.

  • getattr(self, attr)
  • Factory method for creating sub-entities.
  • class SQL(sql[, params=None])

Parameters:

  • sql (str) – SQL query string.
  • params (tuple) – Parameters for query (optional).

Represent a parameterized SQL query or query-fragment.

  • Check(constraint)

Parameters:constraint (str) – Constraint SQL.

Represent a CHECK constraint.

  • class Function(name, arguments[, coerce=True[, python_value=None]])

Parameters:

  • name (str) – Function name.
  • arguments (tuple) – Arguments to function.
  • coerce (bool) – Whether to coerce the function result to a particulardata-type when reading function return values from the cursor.
  • python_value (callable) – Function to use for converting the returnvalue from the cursor.

Represent an arbitrary SQL function call.

Note

Rather than instantiating this class directly, it is recommended to usethe fn helper.

Example of using fn to call an arbitrary SQL function:

  1. # Query users and count of tweets authored.
  2. query = (User
  3.          .select(User.username, fn.COUNT(Tweet.id).alias('ct'))
  4.          .join(Tweet, JOIN.LEFT_OUTER, on=(User.id == Tweet.user_id))
  5.          .group_by(User.username)
  6.          .order_by(fn.COUNT(Tweet.id).desc()))
  • over([partition_by=None[, order_by=None[, start=None[, end=None[, window=None[, exclude=None]]]]]])

Parameters:

  1. - **partition_by** (_list_) – List of columns to partition by.
  2. - **order_by** (_list_) – List of columns / expressions to order window by.
  3. - **start** – A [<code>SQL</code>](#SQL) instance or a string expressing thestart of the window range.
  4. - **end** – A [<code>SQL</code>](#SQL) instance or a string expressing theend of the window range.
  5. - **frame_type** (_str_) – <code>Window.RANGE</code>, <code>Window.ROWS</code> or<code>Window.GROUPS</code>.
  6. - **window** ([_Window_](#Window)) – A [<code>Window</code>](#Window) instance.
  7. - **exclude** – Frame exclusion, one of <code>Window.CURRENT_ROW</code>,<code>Window.GROUP</code>, <code>Window.TIES</code> or <code>Window.NO_OTHERS</code>.

Note

For an in-depth guide to using window functions with Peewee,see the Window functions section.

Examples:

  1. # Using a simple partition on a single column.
  2. query = (Sample
  3.          .select(
  4.             Sample.counter,
  5.             Sample.value,
  6.             fn.AVG(Sample.value).over([Sample.counter]))
  7.          .order_by(Sample.counter))
  8.  
  9. # Equivalent example Using a Window() instance instead.
  10. window = Window(partition_by=[Sample.counter])
  11. query = (Sample
  12.          .select(
  13.             Sample.counter,
  14.             Sample.value,
  15.             fn.AVG(Sample.value).over(window))
  16.          .window(window)  # Note call to ".window()"
  17.          .order_by(Sample.counter))
  18.  
  19. # Example using bounded window.
  20. query = (Sample
  21.          .select(Sample.value,
  22.                  fn.SUM(Sample.value).over(
  23.                     partition_by=[Sample.counter],
  24.                     start=Window.CURRENT_ROW,  # current row
  25.                     end=Window.following()))  # unbounded following
  26.          .order_by(Sample.id))
  • filter(where)

Parameters:where – Expression for filtering aggregate.

Add a FILTER (WHERE…) clause to an aggregate function. The whereexpression is evaluated to determine which rows are fed to theaggregate function. This SQL feature is supported for Postgres andSQLite.

  • coerce([coerce=True])

Parameters:coerce (bool) – Whether to attempt to coerce function-call resultto a Python data-type.

When coerce is True, the target data-type is inferred using severalheuristics. Read the source for BaseModelCursorWrapper._initialize_columnsmethod to see how this works.

  • pythonvalue([_func=None])

Parameters:python_value (callable) – Function to use for converting the returnvalue from the cursor.

Specify a particular function to use when converting values returned bythe database cursor. For example:

  1. # Get user and a list of their tweet IDs. The tweet IDs are
  2. # returned as a comma-separated string by the db, so we'll split
  3. # the result string and convert the values to python ints.
  4. tweet_ids = (fn
  5.              .GROUP_CONCAT(Tweet.id)
  6.              .python_value(lambda idlist: [int(i) for i in idlist]))
  7.  
  8. query = (User
  9.          .select(User.username, tweet_ids.alias('tweet_ids'))
  10.          .group_by(User.username))
  11.  
  12. for user in query:
  13.     print(user.username, user.tweet_ids)
  14.  
  15. # e.g.,
  16. # huey [1, 4, 5, 7]
  17. # mickey [2, 3, 6]
  18. # zaizee []
  • fn()
  • The fn() helper is actually an instance of Functionthat implements a getattr hook to provide a nice API for callingSQL functions.

To create a node representative of a SQL function call, use the functionname as an attribute on fn and then provide the arguments as you wouldif calling a Python function:

  1. # List users and the number of tweets they have authored,
  2. # from highest-to-lowest:
  3. sql_count = fn.COUNT(Tweet.id)
  4. query = (User
  5.          .select(User, sql_count.alias('count'))
  6.          .join(Tweet, JOIN.LEFT_OUTER)
  7.          .group_by(User)
  8.          .order_by(sql_count.desc()))
  9.  
  10. # Get the timestamp of the most recent tweet:
  11. query = Tweet.select(fn.MAX(Tweet.timestamp))
  12. max_timestamp = query.scalar()  # Retrieve scalar result from query.

Function calls can, like anything else, be composed and nested:

  1. # Get users whose username begins with "A" or "a":
  2. a_users = User.select().where(fn.LOWER(fn.SUBSTR(User.username, 1, 1)) == 'a')
  • class Window([partition_by=None[, order_by=None[, start=None[, end=None[, frame_type=None[, extends=None[, exclude=None[, alias=None]]]]]]]])

Parameters:

  • partition_by (list) – List of columns to partition by.
  • order_by (list) – List of columns to order by.
  • start – A SQL instance or a string expressing the startof the window range.
  • end – A SQL instance or a string expressing the end ofthe window range.
  • frame_type (str) – Window.RANGE, Window.ROWS orWindow.GROUPS.
  • extends – A Window definition to extend. Alternately, youmay specify the window’s alias instead.
  • exclude – Frame exclusion, one of Window.CURRENT_ROW,Window.GROUP, Window.TIES or Window.NO_OTHERS.
  • alias (str) – Alias for the window.

Represent a WINDOW clause.

Note

For an in-depth guide to using window functions with Peewee,see the Window functions section.

  • RANGE
  • ROWS
  • GROUPS

  • Specify the window frame_type. See Frame types: RANGE vs ROWS vs GROUPS.

  • CURRENT_ROW

  • Reference to current row for use in start/end clause or the frameexclusion parameter.

  • NO_OTHERS

  • GROUP
  • TIES

  • Specify the window frame exclusion parameter.

  • static preceding([value=None])

Parameters:value – Number of rows preceding. If None is UNBOUNDED.

Convenience method for generating SQL suitable for passing in as thestart parameter for a window range.

  • static following([value=None])

Parameters:value – Number of rows following. If None is UNBOUNDED.

Convenience method for generating SQL suitable for passing in as theend parameter for a window range.

  • as_rows()
  • as_range()
  • as_groups()

  • Specify the frame type.

  • extends([window=None])

Parameters:window (Window) – A Window definition to extend.Alternately, you may specify the window’s alias instead.

  • exclude([frame_exclusion=None])

Parameters:frame_exclusion – Frame exclusion, one of Window.CURRENT_ROW,Window.GROUP, Window.TIES or Window.NO_OTHERS.

  • alias([alias=None])

Parameters:alias (str) – Alias to use for window.

  • Case(predicate, expression_tuples[, default=None]])

Parameters:

  • predicate – Predicate for CASE query (optional).
  • expression_tuples – One or more cases to evaluate.
  • default – Default value (optional).Returns:Representation of CASE statement.

Examples:

  1. Number = Table('numbers', ('val',))
  2.  
  3. num_as_str = Case(Number.val, (
  4.     (1, 'one'),
  5.     (2, 'two'),
  6.     (3, 'three')), 'a lot')
  7.  
  8. query = Number.select(Number.val, num_as_str.alias('num_str'))
  9.  
  10. # The above is equivalent to:
  11. # SELECT "val",
  12. #   CASE "val"
  13. #       WHEN 1 THEN 'one'
  14. #       WHEN 2 THEN 'two'
  15. #       WHEN 3 THEN 'three'
  16. #       ELSE 'a lot' END AS "num_str"
  17. # FROM "numbers"
  18.  
  19. num_as_str = Case(None, (
  20.     (Number.val == 1, 'one'),
  21.     (Number.val == 2, 'two'),
  22.     (Number.val == 3, 'three')), 'a lot')
  23. query = Number.select(Number.val, num_as_str.alias('num_str'))
  24.  
  25. # The above is equivalent to:
  26. # SELECT "val",
  27. #   CASE
  28. #       WHEN "val" = 1 THEN 'one'
  29. #       WHEN "val" = 2 THEN 'two'
  30. #       WHEN "val" = 3 THEN 'three'
  31. #       ELSE 'a lot' END AS "num_str"
  32. # FROM "numbers"
  • class NodeList(nodes[, glue=' '[, parens=False]])

Parameters:

  • nodes (list) – Zero or more nodes.
  • glue (str) – How to join the nodes when converting to SQL.
  • parens (bool) – Whether to wrap the resulting SQL in parentheses.

Represent a list of nodes, a multi-part clause, a list of parameters, etc.

  • CommaNodeList(nodes)

Parameters:nodes (list) – Zero or more nodes.Returns:a NodeList

Represent a list of nodes joined by commas.

  • EnclosedNodeList(nodes)

Parameters:nodes (list) – Zero or more nodes.Returns:a NodeList

Represent a list of nodes joined by commas and wrapped in parentheses.

  • class DQ(**query)

Parameters:query – Arbitrary filter expressions using Django-style lookups.

Represent a composable Django-style filter expression suitable for use withthe Model.filter() or ModelSelect.filter() methods.

  • class Tuple(*args)
  • Represent a SQL row value.Row-values are supported by most databases.
  • class OnConflict([action=None[, update=None[, preserve=None[, where=None[, conflict_target=None[, conflict_where=None[, conflict_constraint=None]]]]]]])

Parameters:

  • action (str) – Action to take when resolving conflict.
  • update – A dictionary mapping column to new value.
  • preserve – A list of columns whose values should be preserved from the original INSERT. See also EXCLUDED.
  • where – Expression to restrict the conflict resolution.
  • conflict_target – Column(s) that comprise the constraint.
  • conflict_where – Expressions needed to match the constraint target if it is a partial index (index with a WHERE clause).
  • conflict_constraint (str) – Name of constraint to use for conflictresolution. Currently only supported by Postgres.

Represent a conflict resolution clause for a data-modification query.

Depending on the database-driver being used, one or more of the aboveparameters may be required.

  • preserve(*columns)

Parameters:columns – Columns whose values should be preserved.

  • update([_data=None[, **kwargs]])

Parameters:

  1. - **_data** (_dict_) – Dictionary mapping column to new value.
  2. - **kwargs** – Dictionary mapping column name to new value.

The update() method supports being called with either a dictionaryof column-to-value, or keyword arguments representing the same.

  • where(*expressions)

Parameters:expressions – Expressions that restrict the action of theconflict resolution clause.

  • conflicttarget(*constraints_)

Parameters:constraints – Column(s) to use as target for conflict resolution.

  • conflictwhere(*expressions_)

Parameters:expressions – Expressions that match the conflict target index,in the case the conflict target is a partial index.

  • conflictconstraint(_constraint)

Parameters:constraint (str) – Name of constraints to use as target forconflict resolution. Currently only supported by Postgres.

  • class EXCLUDED
  • Helper object that exposes the EXCLUDED namespace that is used withINSERT … ON CONFLICT to reference values in the conflicting data.This is a “magic” helper, such that one uses it by accessing attributes onit that correspond to a particular column.

Example:

  1. class KV(Model):
  2.     key = CharField(unique=True)
  3.     value = IntegerField()
  4.  
  5. # Create one row.
  6. KV.create(key='k1', value=1)
  7.  
  8. # Demonstrate usage of EXCLUDED.
  9. # Here we will attempt to insert a new value for a given key. If that
  10. # key already exists, then we will update its value with the *sum* of its
  11. # original value and the value we attempted to insert -- provided that
  12. # the new value is larger than the original value.
  13. query = (KV.insert(key='k1', value=10)
  14.          .on_conflict(conflict_target=[KV.key],
  15.                       update={KV.value: KV.value + EXCLUDED.value},
  16.                       where=(EXCLUDED.value > KV.value)))
  17.  
  18. # Executing the above query will result in the following data being
  19. # present in the "kv" table:
  20. # (key='k1', value=11)
  21. query.execute()
  22.  
  23. # If we attempted to execute the query *again*, then nothing would be
  24. # updated, as the new value (10) is now less than the value in the
  25. # original row (11).
  • class BaseQuery

  • The parent class from which all other query classes are derived. While youwill not deal with BaseQuery directly in your code, itimplements some methods that are common across all query types.

    • default_row_type = ROW.DICT
    • bind([database=None])

Parameters:database (Database) – Database to execute query against.

Bind the query to the given database for execution.

  • dicts([as_dict=True])

Parameters:as_dict (bool) – Specify whether to return rows as dictionaries.

Return rows as dictionaries.

  • tuples([as_tuples=True])

Parameters:as_tuple (bool) – Specify whether to return rows as tuples.

Return rows as tuples.

  • namedtuples([as_namedtuple=True])

Parameters:as_namedtuple (bool) – Specify whether to return rows as namedtuples.

Return rows as named tuples.

  • objects([constructor=None])

Parameters:constructor – Function that accepts row dict and returns anarbitrary object.

Return rows as arbitrary objects using the given constructor.

  • sql()

Returns:A 2-tuple consisting of the query’s SQL and parameters.

  • execute(database)

Parameters:database (Database) – Database to execute query against. Notrequired if query was previously bound to a database.

Execute the query and return result (depends on type of query beingexecuted). For example, select queries the return result will be aniterator over the query results.

  • iterator([database=None])

Parameters:database (Database) – Database to execute query against. Notrequired if query was previously bound to a database.

Execute the query and return an iterator over the result-set. For largeresult-sets this method is preferable as rows are not cached in-memoryduring iteration.

Note

Because rows are not cached, the query may only be iterated overonce. Subsequent iterations will return empty result-sets as thecursor will have been consumed.

Example:

  1. query = StatTbl.select().order_by(StatTbl.timestamp).tuples()
  2. for row in query.iterator(db):
  3.     process_row(row)
  • iter()
  • Execute the query and return an iterator over the result-set.

Unlike iterator(), this method will cause rows tobe cached in order to allow efficient iteration, indexing and slicing.

  • getitem(value)

Parameters:value – Either an integer index or a slice.

Retrieve a row or range of rows from the result-set.

  • len()
  • Return the number of rows in the result-set.

Warning

This does not issue a COUNT() query. Instead, the result-setis loaded as it would be during normal iteration, and the lengthis determined from the size of the result set.

  • class RawQuery([sql=None[, params=None[, **kwargs]]])

Parameters:

  • sql (str) – SQL query.
  • params (tuple) – Parameters (optional).

Create a query by directly specifying the SQL to execute.

  • class Query([where=None[, order_by=None[, limit=None[, offset=None[, **kwargs]]]]])

Parameters:

  • where – Representation of WHERE clause.
  • order_by (tuple) – Columns or values to order by.
  • limit (int) – Value of LIMIT clause.
  • offset (int) – Value of OFFSET clause.

Base-class for queries that support method-chaining APIs.

  • withcte(*ctelist)

Parameters:cte_list – zero or more CTE objects.

Include the given common-table expressions in the query. Any previouslyspecified CTEs will be overwritten. For examples of common-tableexpressions, see Common Table Expressions.

  • where(*expressions)

Parameters:expressions – zero or more expressions to include in the WHEREclause.

Include the given expressions in the WHERE clause of the query. Theexpressions will be AND-ed together with any previously-specifiedWHERE expressions.

Example selection users where the username is equal to ‘somebody’:

  1. sq = User.select().where(User.username == 'somebody')

Example selecting tweets made by users who are either editors oradministrators:

  1. sq = Tweet.select().join(User).where(
  2.     (User.is_editor == True) |
  3.     (User.is_admin == True))

Example of deleting tweets by users who are no longer active:

  1. inactive_users = User.select().where(User.active == False)
  2. dq = (Tweet
  3.       .delete()
  4.       .where(Tweet.user.in_(inactive_users)))
  5. dq.execute()  # Return number of tweets deleted.

Note

where() calls are chainable. Multiple calls willbe “AND”-ed together.

  • orwhere(*expressions)

Parameters:expressions – zero or more expressions to include in the WHEREclause.

Include the given expressions in the WHERE clause of the query. Thismethod is the same as the Query.where() method, except thatthe expressions will be OR-ed together with any previously-specifiedWHERE expressions.

  • orderby(*values_)

Parameters:values – zero or more Column-like objects to order by.

Define the ORDER BY clause. Any previously-specified values will beoverwritten.

  • orderby_extend(*values_)

Parameters:values – zero or more Column-like objects to order by.

Extend any previously-specified ORDER BY clause with the given values.

  • limit([value=None])

Parameters:value (int) – specify value for LIMIT clause.

  • offset([value=None])

Parameters:value (int) – specify value for OFFSET clause.

  • paginate(page[, paginate_by=20])

Parameters:

  1. - **page** (_int_) – Page number of results (starting from 1).
  2. - **paginate_by** (_int_) – Rows-per-page.

Convenience method for specifying the LIMIT and OFFSET in a moreintuitive way.

This feature is designed with web-site pagination in mind, so the firstpage starts with page=1.

  • class SelectQuery

  • Select query helper-class that implements operator-overloads for creatingcompound queries.

    • cte(name[, recursive=False[, columns=None]])

Parameters:

  1. - **name** (_str_) – Alias for common table expression.
  2. - **recursive** (_bool_) – Will this be a recursive CTE?
  3. - **columns** (_list_) – List of column names (as strings).

Indicate that a query will be used as a common table expression. Forexample, if we are modelling a category tree and are using aparent-link foreign key, we can retrieve all categories and theirabsolute depths using a recursive CTE:

  1. class Category(Model):
  2.     name = TextField()
  3.     parent = ForeignKeyField('self', backref='children', null=True)
  4.  
  5. # The base case of our recursive CTE will be categories that are at
  6. # the root level -- in other words, categories without parents.
  7. roots = (Category
  8.          .select(Category.name, Value(0).alias('level'))
  9.          .where(Category.parent.is_null())
  10.          .cte(name='roots', recursive=True))
  11.  
  12. # The recursive term will select the category name and increment
  13. # the depth, joining on the base term so that the recursive term
  14. # consists of all children of the base category.
  15. RTerm = Category.alias()
  16. recursive = (RTerm
  17.              .select(RTerm.name, (roots.c.level + 1).alias('level'))
  18.              .join(roots, on=(RTerm.parent == roots.c.id)))
  19.  
  20. # Express <base term> UNION ALL <recursive term>.
  21. cte = roots.union_all(recursive)
  22.  
  23. # Select name and level from the recursive CTE.
  24. query = (cte
  25.          .select_from(cte.c.name, cte.c.level)
  26.          .order_by(cte.c.name))
  27.  
  28. for category in query:
  29.     print(category.name, category.level)

For more examples of CTEs, see Common Table Expressions.

  • selectfrom(*columns_)

Parameters:columns – one or more columns to select from the inner query.Returns:a new query that wraps the calling query.

Create a new query that wraps the current (calling) query. For example,suppose you have a simple UNION query, and need to apply anaggregation on the union result-set. To do this, you need to writesomething like:

  1. SELECT "u"."owner", COUNT("u"."id") AS "ct"
  2. FROM (
  3.     SELECT "id", "owner", ... FROM "cars"
  4.     UNION
  5.     SELECT "id", "owner", ... FROM "motorcycles"
  6.     UNION
  7.     SELECT "id", "owner", ... FROM "boats") AS "u"
  8. GROUP BY "u"."owner"

The select_from() method is designed to simplifyconstructing this type of query.

Example peewee code:

  1. class Car(Model):
  2.     owner = ForeignKeyField(Owner, backref='cars')
  3.     # ... car-specific fields, etc ...
  4.  
  5. class Motorcycle(Model):
  6.     owner = ForeignKeyField(Owner, backref='motorcycles')
  7.     # ... motorcycle-specific fields, etc ...
  8.  
  9. class Boat(Model):
  10.     owner = ForeignKeyField(Owner, backref='boats')
  11.     # ... boat-specific fields, etc ...
  12.  
  13. cars = Car.select(Car.owner)
  14. motorcycles = Motorcycle.select(Motorcycle.owner)
  15. boats = Boat.select(Boat.owner)
  16.  
  17. union = cars | motorcycles | boats
  18.  
  19. query = (union
  20.          .select_from(union.c.owner, fn.COUNT(union.c.id))
  21.          .group_by(union.c.owner))
  • unionall(_dest)

  • Create a UNION ALL query with dest.

  • add(dest)

  • Create a UNION ALL query with dest.

  • union(dest)

  • Create a UNION query with dest.

  • or(dest)

  • Create a UNION query with dest.

  • intersect(dest)

  • Create an INTERSECT query with dest.

  • and(dest)

  • Create an INTERSECT query with dest.

  • except(_dest)

  • Create an EXCEPT query with dest. Note that the method name has atrailing “_” character since except is a Python reserved word.

  • sub(dest)

  • Create an EXCEPT query with dest.
  • class SelectBase

  • Base-class for Select and CompoundSelect queries.

    • peek(database[, n=1])

Parameters:

  1. - **database** ([_Database_](#Database)) – database to execute query against.
  2. - **n** (_int_) – Number of rows to return.Returns:

A single row if n = 1, else a list of rows.

Execute the query and return the given number of rows from the startof the cursor. This function may be called multiple times safely, andwill always return the first N rows of results.

  • first(database[, n=1])

Parameters:

  1. - **database** ([_Database_](#Database)) – database to execute query against.
  2. - **n** (_int_) – Number of rows to return.Returns:

A single row if n = 1, else a list of rows.

Like the peek() method, except a LIMIT isapplied to the query to ensure that only n rows are returned.Multiple calls for the same value of n will not result in multipleexecutions.

  • scalar(database[, as_tuple=False])

Parameters:

  1. - **database** ([_Database_](#Database)) – database to execute query against.
  2. - **as_tuple** (_bool_) – Return the result as a tuple?Returns:

Single scalar value if as_tuple = False, else row tuple.

Return a scalar value from the first row of results. If multiplescalar values are anticipated (e.g. multiple aggregations in a singlequery) then you may specify as_tuple=True to get the row tuple.

Example:

  1. query = Note.select(fn.MAX(Note.timestamp))
  2. max_ts = query.scalar(db)
  3.  
  4. query = Note.select(fn.MAX(Note.timestamp), fn.COUNT(Note.id))
  5. max_ts, n_notes = query.scalar(db, as_tuple=True)
  • count(database[, clear_limit=False])

Parameters:

  1. - **database** ([_Database_](#Database)) – database to execute query against.
  2. - **clear_limit** (_bool_) – Clear any LIMIT clause when counting.Returns:

Number of rows in the query result-set.

Return number of rows in the query result-set.

Implemented by running SELECT COUNT(1) FROM ().

  • exists(database)

Parameters:database (Database) – database to execute query against.Returns:Whether any results exist for the current query.

Return a boolean indicating whether the current query has any results.

  • get(database)

Parameters:database (Database) – database to execute query against.Returns:A single row from the database or None.

Execute the query and return the first row, if it exists. Multiplecalls will result in multiple queries being executed.

  • class CompoundSelectQuery(lhs, op, rhs)

Parameters:

  • lhs (SelectBase) – A Select or CompoundSelect query.
  • op (str) – Operation (e.g. UNION, INTERSECT, EXCEPT).
  • rhs (SelectBase) – A Select or CompoundSelect query.

Class representing a compound SELECT query.

  • class Select([from_list=None[, columns=None[, group_by=None[, having=None[, distinct=None[, windows=None[, for_update=None[, for_update_of=None[, for_update_nowait=None[, **kwargs]]]]]]]]]])

Parameters:

  • from_list (list) – List of sources for FROM clause.
  • columns (list) – Columns or values to select.
  • group_by (list) – List of columns or values to group by.
  • having (Expression) – Expression for HAVING clause.
  • distinct – Either a boolean or a list of column-like objects.
  • windows (list) – List of Window clauses.
  • for_update – Boolean or str indicating if SELECT…FOR UPDATE.
  • for_update_of – One or more tables for FOR UPDATE OF clause.
  • for_update_nowait (bool) – Specify NOWAIT locking.

Class representing a SELECT query.

Note

Rather than instantiating this directly, most-commonly you will use afactory method like Table.select() or Model.select().

Methods on the select query can be chained together.

Example selecting some user instances from the database. Only the idand username columns are selected. When iterated, will return instancesof the User model:

  1. query = User.select(User.id, User.username)
  2. for user in query:
  3.     print(user.username)

Example selecting users and additionally the number of tweets made by theuser. The User instances returned will have an additional attribute,‘count’, that corresponds to the number of tweets made:

  1. query = (User
  2.          .select(User, fn.COUNT(Tweet.id).alias('count'))
  3.          .join(Tweet, JOIN.LEFT_OUTER)
  4.          .group_by(User))
  5. for user in query:
  6.     print(user.username, 'has tweeted', user.count, 'times')

Note

While it is possible to instantiate Select directly, morecommonly you will build the query using the method-chaining APIs.

  • columns(*columns)

Parameters:columns – Zero or more column-like objects to SELECT.

Specify which columns or column-like values to SELECT.

  • select(*columns)

Parameters:columns – Zero or more column-like objects to SELECT.

Same as Select.columns(), provided forbackwards-compatibility.

  • selectextend(*columns_)

Parameters:columns – Zero or more column-like objects to SELECT.

Extend the current selection with the given columns.

Example:

  1. def get_users(with_count=False):
  2.     query = User.select()
  3.     if with_count:
  4.         query = (query
  5.                  .select_extend(fn.COUNT(Tweet.id).alias('count'))
  6.                  .join(Tweet, JOIN.LEFT_OUTER)
  7.                  .group_by(User))
  8.     return query
  • from(*sources)[](#Select.from)

Parameters:sources – Zero or more sources for the FROM clause.

Specify which table-like objects should be used in the FROM clause.

  1. User = Table('users')
  2. Tweet = Table('tweets')
  3. query = (User
  4.          .select(User.c.username, Tweet.c.content)
  5.          .from_(User, Tweet)
  6.          .where(User.c.id == Tweet.c.user_id))
  7. for row in query.execute(db):
  8.     print(row['username'], '->', row['content'])
  • join(dest[, join_type='INNER'[, on=None]])

Parameters:

  1. - **dest** – A table or table-like object.
  2. - **join_type** (_str_) – Type of JOIN, default is “INNER”.
  3. - **on** ([_Expression_](#Expression)) – Join predicate.

Join type may be one of:

  1. - <code>JOIN.INNER</code>
  2. - <code>JOIN.LEFT_OUTER</code>
  3. - <code>JOIN.RIGHT_OUTER</code>
  4. - <code>JOIN.FULL</code>
  5. - <code>JOIN.FULL_OUTER</code>
  6. - <code>JOIN.CROSS</code>

Express a JOIN:

  1. User = Table('users', ('id', 'username'))
  2. Note = Table('notes', ('id', 'user_id', 'content'))
  3.  
  4. query = (Note
  5.          .select(Note.content, User.username)
  6.          .join(User, on=(Note.user_id == User.id)))
  • groupby(*columns_)

Parameters:values – zero or more Column-like objects to group by.

Define the GROUP BY clause. Any previously-specified values will beoverwritten.

Additionally, to specify all columns on a given table, you can pass thetable/model object in place of the individual columns.

Example:

  1. query = (User
  2.          .select(User, fn.Count(Tweet.id).alias('count'))
  3.          .join(Tweet)
  4.          .group_by(User))
  • groupby_extend(*columns_)

Parameters:values – zero or more Column-like objects to group by.

Extend the GROUP BY clause with the given columns.

  • having(*expressions)

Parameters:expressions – zero or more expressions to include in the HAVINGclause.

Include the given expressions in the HAVING clause of the query. Theexpressions will be AND-ed together with any previously-specifiedHAVING expressions.

  • distinct(*columns)

Parameters:columns – Zero or more column-like objects.

Indicate whether this query should use a DISTINCT clause. By specifyinga single value of True the query will use a simple SELECT DISTINCT.Specifying one or more columns will result in a SELECT DISTINCT ON.

  • window(*windows)

Parameters:windows – zero or more Window objects.

Define the WINDOW clause. Any previously-specified values will beoverwritten.

Example:

  1. # Equivalent example Using a Window() instance instead.
  2. window = Window(partition_by=[Sample.counter])
  3. query = (Sample
  4.          .select(
  5.             Sample.counter,
  6.             Sample.value,
  7.             fn.AVG(Sample.value).over(window))
  8.          .window(window)  # Note call to ".window()"
  9.          .order_by(Sample.counter))
  • forupdate([_for_update=True[, of=None[, nowait=None]]])

Parameters:

  1. - **for_update** – Either a boolean or a string indicating thedesired expression, e.g. “FOR SHARE”.
  2. - **of** – One or more models to restrict locking to.
  3. - **nowait** (_bool_) – Specify NOWAIT option when locking.
  • class WriteQuery(_table[, returning=None[, **kwargs]])

Parameters:

  • table (Table) – Table to write to.
  • returning (list) – List of columns for RETURNING clause.

Base-class for write queries.

  • returning(*returning)

Parameters:returning – Zero or more column-like objects for RETURNING clause

Specify the RETURNING clause of query (if supported by your database).

  1. query = (User
  2.          .insert_many([{'username': 'foo'},
  3.                        {'username': 'bar'},
  4.                        {'username': 'baz'}])
  5.          .returning(User.id, User.username)
  6.          .namedtuples())
  7. data = query.execute()
  8. for row in data:
  9.     print('added:', row.username, 'with id=', row.id)
  • class Update(table[, update=None[, **kwargs]])

Parameters:

  • table (Table) – Table to update.
  • update (dict) – Data to update.

Class representing an UPDATE query.

Example:

  1. PageView = Table('page_views')
  2. query = (PageView
  3.          .update({PageView.c.page_views: PageView.c.page_views + 1})
  4.          .where(PageView.c.url == url))
  5. query.execute(database)
  • from(*sources)[](#Update.from)

Parameters:sources (Source) – one or more Table,Model, query, or ValuesList to join with.

Specify additional tables to join with using the UPDATE … FROMsyntax, which is supported by Postgres. The Postgres documentationprovides additional detail, but to summarize:

When a FROM clause is present, what essentially happens is thatthe target table is joined to the tables mentioned in thefrom_list, and each output row of the join represents an updateoperation for the target table. When using FROM you shouldensure that the join produces at most one output row for each rowto be modified.

Example:

  1. # Update multiple users in a single query.
  2. data = [('huey', True),
  3.         ('mickey', False),
  4.         ('zaizee', True)]
  5. vl = ValuesList(data, columns=('username', 'is_admin'), alias='vl')
  6.  
  7. # Here we'll update the "is_admin" status of the above users,
  8. # "joining" the VALUES() on the "username" column.
  9. query = (User
  10.          .update(is_admin=vl.c.is_admin)
  11.          .from_(vl)
  12.          .where(User.username == vl.c.username))

The above query produces the following SQL:

  1. UPDATE "users" SET "is_admin" = "vl"."is_admin"
  2. FROM (
  3.     VALUES ('huey', t), ('mickey', f), ('zaizee', t))
  4.     AS "vl"("username", "is_admin")
  5. WHERE ("users"."username" = "vl"."username")
  • class Insert(table[, insert=None[, columns=None[, on_conflict=None[, **kwargs]]]])

Parameters:

  • table (Table) – Table to INSERT data into.
  • insert – Either a dict, a list, or a query.
  • columns (list) – List of columns when insert is a list or query.
  • on_conflict – Conflict resolution strategy.

Class representing an INSERT query.

  • onconflict_ignore([_ignore=True])

Parameters:ignore (bool) – Whether to add ON CONFLICT IGNORE clause.

Specify IGNORE conflict resolution strategy.

  • onconflict_replace([_replace=True])

Parameters:replace (bool) – Whether to add ON CONFLICT REPLACE clause.

Specify REPLACE conflict resolution strategy.

  • onconflict([_action=None[, update=None[, preserve=None[, where=None[, conflict_target=None[, conflict_where=None[, conflict_constraint=None]]]]]]])

Parameters:

  1. - **action** (_str_) – Action to take when resolving conflict. If blank,action is assumed to be “update”.
  2. - **update** – A dictionary mapping column to new value.
  3. - **preserve** – A list of columns whose values should be preserved from the original INSERT.
  4. - **where** – Expression to restrict the conflict resolution.
  5. - **conflict_target** – Column(s) that comprise the constraint.
  6. - **conflict_where** – Expressions needed to match the constraint target if it is a partial index (index with a WHERE clause).
  7. - **conflict_constraint** (_str_) – Name of constraint to use for conflictresolution. Currently only supported by Postgres.

Specify the parameters for an OnConflict clause to use forconflict resolution.

Examples:

  1. class User(Model):
  2.     username = TextField(unique=True)
  3.     last_login = DateTimeField(null=True)
  4.     login_count = IntegerField()
  5.  
  6. def log_user_in(username):
  7.     now = datetime.datetime.now()
  8.  
  9.     # INSERT a new row for the user with the current timestamp and
  10.     # login count set to 1. If the user already exists, then we
  11.     # will preserve the last_login value from the "insert()" clause
  12.     # and atomically increment the login-count.
  13.     userid = (User
  14.               .insert(username=username, last_login=now, login_count=1)
  15.               .on_conflict(
  16.                   conflict_target=[User.username],
  17.                   preserve=[User.last_login],
  18.                   update={User.login_count: User.login_count + 1})
  19.               .execute())
  20.     return userid

Example using the special EXCLUDED namespace:

  1. class KV(Model):
  2.     key = CharField(unique=True)
  3.     value = IntegerField()
  4.  
  5. # Create one row.
  6. KV.create(key='k1', value=1)
  7.  
  8. # Demonstrate usage of EXCLUDED.
  9. # Here we will attempt to insert a new value for a given key. If that
  10. # key already exists, then we will update its value with the *sum* of its
  11. # original value and the value we attempted to insert -- provided that
  12. # the new value is larger than the original value.
  13. query = (KV.insert(key='k1', value=10)
  14.          .on_conflict(conflict_target=[KV.key],
  15.                       update={KV.value: KV.value + EXCLUDED.value},
  16.                       where=(EXCLUDED.value > KV.value)))
  17.  
  18. # Executing the above query will result in the following data being
  19. # present in the "kv" table:
  20. # (key='k1', value=11)
  21. query.execute()
  22.  
  23. # If we attempted to execute the query *again*, then nothing would be
  24. # updated, as the new value (10) is now less than the value in the
  25. # original row (11).
  • class Delete
  • Class representing a DELETE query.
  • class Index(name, table, expressions[, unique=False[, safe=False[, where=None[, using=None]]]])

Parameters:

  • name (str) – Index name.
  • table (Table) – Table to create index on.
  • expressions – List of columns to index on (or expressions).
  • unique (bool) – Whether index is UNIQUE.
  • safe (bool) – Whether to add IF NOT EXISTS clause.
  • where (Expression) – Optional WHERE clause for index.
  • using (str) – Index algorithm.
  • safe([_safe=True])

Parameters:_safe (bool) – Whether to add IF NOT EXISTS clause.

  • where(*expressions)

Parameters:expressions – zero or more expressions to include in the WHEREclause.

Include the given expressions in the WHERE clause of the index. Theexpressions will be AND-ed together with any previously-specifiedWHERE expressions.

  • using([_using=None])

Parameters:_using (str) – Specify index algorithm for USING clause.

  • class ModelIndex(model, fields[, unique=False[, safe=True[, where=None[, using=None[, name=None]]]]])

Parameters:

  • model (Model) – Model class to create index on.
  • fields (list) – Fields to index.
  • unique (bool) – Whether index is UNIQUE.
  • safe (bool) – Whether to add IF NOT EXISTS clause.
  • where (Expression) – Optional WHERE clause for index.
  • using (str) – Index algorithm or type, e.g. ‘BRIN’, ‘GiST’ or ‘GIN’.
  • name (str) – Optional index name.

Expressive method for declaring an index on a model.

Examples:

  1. class Article(Model):
  2.     name = TextField()
  3.     timestamp = TimestampField()
  4.     status = IntegerField()
  5.     flags = BitField()
  6.  
  7.     is_sticky = flags.flag(1)
  8.     is_favorite = flags.flag(2)
  9.  
  10. # CREATE INDEX ... ON "article" ("name", "timestamp")
  11. idx = ModelIndex(Article, (Article.name, Article.timestamp))
  12.  
  13. # CREATE INDEX ... ON "article" ("name", "timestamp") WHERE "status" = 1
  14. idx = idx.where(Article.status == 1)
  15.  
  16. # CREATE UNIQUE INDEX ... ON "article" ("timestamp" DESC, "flags" & 2) WHERE "status" = 1
  17. idx = ModelIndex(
  18.     Article,
  19.     (Article.timestamp.desc(), Article.flags.bin_and(2)),
  20.     unique = True).where(Article.status == 1)

You can also use Model.index():

  1. idx = Article.index(Article.name, Article.timestamp).where(Article.status == 1)

To add an index to a model definition use Model.add_index():

  1. idx = Article.index(Article.name, Article.timestamp).where(Article.status == 1)
  2.  
  3. # Add above index definition to the model definition. When you call
  4. # Article.create_table() (or database.create_tables([Article])), the
  5. # index will be created.
  6. Article.add_index(idx)