API Documentation

This document specifies Peewee’s APIs.

Database

class Database(database[, thread_safe=True[, autorollback=False[, field_types=None[, operations=None[, \*kwargs*]]]]])

The Database is responsible for:

• Executing queries
• Managing connections
• Transactions
• Introspection

Note

The database can be instantiated with None as the database name if the database is not known until run-time. In this way you can create a database instance and then configure it elsewhere when the settings are known. This is called deferred initialization.

To initialize a database that has been deferred, use the init() method.

• param = '?'

  String used as parameter placeholder in SQL queries.

• quote = '"'

  Type of quotation-mark to use to denote entities such as tables or columns.

• init(database[, \*kwargs*])

  Parameters:	database (str) – Database name or filename for SQLite. kwargs – Arbitrary keyword arguments that will be passed to the database driver when a connection is created, for example password, host, etc.

  Initialize a deferred database.

• __enter__()

  The Database instance can be used as a context-manager, in which case a connection will be held open for the duration of the wrapped block.

  Additionally, any SQL executed within the wrapped block will be executed in a transaction.

• connection_context()

  Create a context-manager that will hold open a connection for the duration of the wrapped block.

  Example:

  def on_app_startup():
      # When app starts up, create the database tables, being sure
      # the connection is closed upon completion.
      with database.connection_context():
          database.create_tables(APP_MODELS)

• connect([reuse_if_open=False])

  Parameters:	reuse_if_open (bool) – Do not raise an exception if a connection is already opened.
  Returns:	whether a new connection was opened.
  Return type:	bool
  Raises:	OperationalError if connection already open and reuse_if_open is not set to True.

  Open a connection to the database.

• close()

  Returns:	Whether a connection was closed. If the database was already closed, this returns False.
  Return type:	bool

  Close the connection to the database.

• is_closed()

  Returns:	return True if database is closed, False if open.
  Return type:	bool

• connection()

  Return the open connection. If a connection is not open, one will be opened. The connection will be whatever the underlying database-driver uses to encapsulate a database connection.

• cursor([commit=None])

  Return a cursor object on the current connection. If a connection is not open, one will be opened. The cursor will be whatever the underlying database-driver uses to encapsulate a database cursor.

• execute_sql(sql[, params=None[, commit=SENTINEL]])

  Parameters:	sql (str) – SQL string to execute. params (tuple) – Parameters for query. commit – Boolean flag to override the default commit logic.
  Returns:	cursor object.

  Execute a SQL query and return a cursor over the results.

• execute(query[, commit=SENTINEL[, \*context_options*]])

  Parameters:	query – A Query instance. commit – Boolean flag to override the default commit logic. context_options – Arbitrary options to pass to the SQL generator.
  Returns:	cursor object.

  Execute a SQL query by compiling a Query instance and executing the resulting SQL.

• last_insert_id(cursor[, query_type=None])

  Parameters:	cursor – cursor object.
  Returns:	primary key of last-inserted row.

• rows_affected(cursor)

  Parameters:	cursor – cursor object.
  Returns:	number of rows modified by query.

• in_transaction()

  Returns:	whether or not a transaction is currently open.
  Return type:	bool

• atomic()

  Create a context-manager which runs any queries in the wrapped block in a transaction (or save-point if blocks are nested).

  Calls to atomic() can be nested.

  atomic() can also be used as a decorator.

  Example code:

  with db.atomic() as txn:
      perform_operation()
      with db.atomic() as nested_txn:
          perform_another_operation()

  Transactions and save-points can be explicitly committed or rolled-back within the wrapped block. If this occurs, a new transaction or savepoint is begun after the commit/rollback.

  Example:

  with db.atomic() as txn:
      User.create(username='mickey')
      txn.commit()  # Changes are saved and a new transaction begins.
      User.create(username='huey')
      txn.rollback()  # "huey" will not be saved.
      User.create(username='zaizee')
  # Print the usernames of all users.
  print [u.username for u in User.select()]
  # Prints ["mickey", "zaizee"]

• manual_commit()

  Create a context-manager which disables all transaction management for the duration of the wrapped block.

  Example:

  with db.manual_commit():
      db.begin()  # Begin transaction explicitly.
      try:
          user.delete_instance(recursive=True)
      except:
          db.rollback()  # Rollback -- an error occurred.
          raise
      else:
          try:
              db.commit()  # Attempt to commit changes.
          except:
              db.rollback()  # Error committing, rollback.
              raise

  The above code is equivalent to the following:

  with db.atomic():
      user.delete_instance(recursive=True)

• transaction()

  Create a context-manager that runs all queries in the wrapped block in a transaction.

  Warning

  Calls to transaction cannot be nested. Only the top-most call will take effect. Rolling-back or committing a nested transaction context-manager has undefined behavior.

• savepoint()

  Create a context-manager that runs all queries in the wrapped block in a savepoint. Savepoints can be nested arbitrarily.

  Warning

  Calls to savepoint must occur inside of a transaction.

• begin()

  Begin a transaction when using manual-commit mode.

  Note

  This method should only be used in conjunction with the manual_commit() context manager.

• commit()

  Manually commit the currently-active transaction.

  Note

  This method should only be used in conjunction with the manual_commit() context manager.

• rollback()

  Manually roll-back the currently-active transaction.

  Note

  This method should only be used in conjunction with the manual_commit() context manager.

• table_exists(table[, schema=None])

  Parameters:	table (str) – Table name. schema (str) – Schema name (optional).
  Returns:	bool indicating whether table exists.

• get_tables([schema=None])

  Parameters:	schema (str) – Schema name (optional).
  Returns:	a list of table names in the database.

• get_indexes(table[, schema=None])

  Parameters:	table (str) – Table name. schema (str) – Schema name (optional).

  Return a list of IndexMetadata tuples.

  Example:

  print db.get_indexes('entry')
  [IndexMetadata(
       name='entry_public_list',
       sql='CREATE INDEX "entry_public_list" ...',
       columns=['timestamp'],
       unique=False,
       table='entry'),
   IndexMetadata(
       name='entry_slug',
       sql='CREATE UNIQUE INDEX "entry_slug" ON "entry" ("slug")',
       columns=['slug'],
       unique=True,
       table='entry')]

• get_columns(table[, schema=None])

  Parameters:	table (str) – Table name. schema (str) – Schema name (optional).

  Return a list of ColumnMetadata tuples.

  Example:

  print db.get_columns('entry')
  [ColumnMetadata(
       name='id',
       data_type='INTEGER',
       null=False,
       primary_key=True,
       table='entry'),
   ColumnMetadata(
       name='title',
       data_type='TEXT',
       null=False,
       primary_key=False,
       table='entry'),
   ...]

• get_primary_keys(table[, schema=None])

  Parameters:	table (str) – Table name. schema (str) – Schema name (optional).

  Return a list of column names that comprise the primary key.

  Example:

  print db.get_primary_keys('entry')
  ['id']

• get_foreign_keys(table[, schema=None])

  Parameters:	table (str) – Table name. schema (str) – Schema name (optional).

  Return a list of ForeignKeyMetadata tuples for keys present on the table.

  Example:

  print db.get_foreign_keys('entrytag')
  [ForeignKeyMetadata(
       column='entry_id',
       dest_table='entry',
       dest_column='id',
       table='entrytag'),
   ...]

• sequence_exists(seq)

  Parameters:	seq (str) – Name of sequence.
  Returns:	Whether sequence exists.
  Return type:	bool

• create_tables(models[, \*options*])

  Parameters:	models (list) – A list of Model classes. options – Options to specify when calling Model.create_table().

  Create tables, indexes and associated metadata for the given list of models.

  Dependencies are resolved so that tables are created in the appropriate order.

• drop_tables(models[, \*options*])

  Parameters:	models (list) – A list of Model classes. kwargs – Options to specify when calling Model.drop_table().

  Drop tables, indexes and associated metadata for the given list of models.

  Dependencies are resolved so that tables are dropped in the appropriate order.

• bind(models[, bind_refs=True[, bind_backrefs=True]])

  Parameters:	models (list) – List of models to bind to the database. bind_refs (bool) – Bind models that are referenced using foreign-keys. bind_backrefs (bool) – Bind models that reference the given model with a foreign-key.

  Create a context-manager that binds (associates) the given models with the current database for the duration of the wrapped block.

class SqliteDatabase(database[, pragmas=None[, timeout=5[, \*kwargs*]]])

Sqlite database implementation. SqliteDatabase that provides some advanced features only offered by Sqlite.

• Register custom aggregates, collations and functions
• Load C extensions
• Advanced transactions (specify isolation level)
• For even more features, see SqliteExtDatabase.

Example of using PRAGMAs:

db = SqliteDatabase('my_app.db', pragmas=(
    ('cache_size', -16000),  # 16MB
    ('journal_mode', 'wal'),  # Use write-ahead-log journal mode.
))

• pragma(key[, value=SENTINEL[, permanent=False]])

  Parameters:	key – Setting name. value – New value for the setting (optional). permanent – Apply this pragma whenever a connection is opened.

  Execute a PRAGMA query once on the active connection. If a value is not specified, then the current value will be returned.

  If permanent is specified, then the PRAGMA query will also be executed whenever a new connection is opened, ensuring it is always in-effect.

  Note

  By default this only affects the current connection. If the PRAGMA being executed is not persistent, then you must specify permanent=True to ensure the pragma is set on subsequent connections.

• cache_size

  Get or set the cache_size pragma for the current connection.

• foreign_keys

  Get or set the foreign_keys pragma for the current connection.

• journal_mode

  Get or set the journal_mode pragma.

• journal_size_limit

  Get or set the journal_size_limit pragma.

• mmap_size

  Get or set the mmap_size pragma for the current connection.

• page_size

  Get or set the page_size pragma.

• read_uncommitted

  Get or set the read_uncommitted pragma for the current connection.

• synchronous

  Get or set the synchronous pragma for the current connection.

• wal_autocheckpoint

  Get or set the wal_autocheckpoint pragma for the current connection.

• timeout

  Get or set the busy timeout (seconds).

• register_aggregate(klass[, name=None[, num_params=-1]])

  Parameters:	klass – Class implementing aggregate API. name (str) – Aggregate function name (defaults to name of class). num_params (int) – Number of parameters the aggregate accepts, or -1 for any number.

  Register a user-defined aggregate function.

  The function will be registered each time a new connection is opened. Additionally, if a connection is already open, the aggregate will be registered with the open connection.

• aggregate([name=None[, num_params=-1]])

  Parameters:	name (str) – Name of the aggregate (defaults to class name). num_params (int) – Number of parameters the aggregate accepts, or -1 for any number.

  Class decorator to register a user-defined aggregate function.

  Example:

  ``` @db.aggregate(‘md5’) class MD5(object):

  def initialize(self):
      self.md5 = hashlib.md5()
  def step(self, value):
      self.md5.update(value)
  def finalize(self):
      return self.md5.hexdigest()

@db.aggregate()
class Product(object):
    '''Like SUM() except calculates cumulative product.'''
    def __init__(self):
        self.product = 1
    def step(self, value):
        self.product *= value
    def finalize(self):
        return self.product
```

• register_collation(fn[, name=None])

  Parameters:	fn – The collation function. name (str) – Name of collation (defaults to function name)

  Register a user-defined collation. The collation will be registered each time a new connection is opened. Additionally, if a connection is already open, the collation will be registered with the open connection.

• collation([name=None])

  Parameters:	name (str) – Name of collation (defaults to function name)

  Decorator to register a user-defined collation.

  Example:

  @db.collation('reverse')
  def collate_reverse(s1, s2):
      return -cmp(s1, s2)
  # Usage:
  Book.select().order_by(collate_reverse.collation(Book.title))
  # Equivalent:
  Book.select().order_by(Book.title.asc(collation='reverse'))

  As you might have noticed, the original collate_reverse function has a special attribute called collation attached to it. This extra attribute provides a shorthand way to generate the SQL necessary to use our custom collation.

• register_function(fn[, name=None[, num_params=-1]])

  Parameters:	fn – The user-defined scalar function. name (str) – Name of function (defaults to function name) num_params (int) – Number of arguments the function accepts, or -1 for any number.

  Register a user-defined scalar function. The function will be registered each time a new connection is opened. Additionally, if a connection is already open, the function will be registered with the open connection.

• func([name=None[, num_params=-1]])

  Parameters:	name (str) – Name of the function (defaults to function name). num_params (int) – Number of parameters the function accepts, or -1 for any number.

  Decorator to register a user-defined scalar function.

  Example:

  @db.func('title_case')
  def title_case(s):
      return s.title() if s else ''
  # Usage:
  title_case_books = Book.select(fn.title_case(Book.title))

• table_function([name=None])

  Class-decorator for registering a TableFunction. Table functions are user-defined functions that, rather than returning a single, scalar value, can return any number of rows of tabular data.

  Example:

  from playhouse.sqlite_ext import TableFunction
  @db.table_function('series')
  class Series(TableFunction):
      columns = ['value']
      params = ['start', 'stop', 'step']
      def initialize(self, start=0, stop=None, step=1):
          """
          Table-functions declare an initialize() method, which is
          called with whatever arguments the user has called the
          function with.
          """
          self.start = self.current = start
          self.stop = stop or float('Inf')
          self.step = step
      def iterate(self, idx):
          """
          Iterate is called repeatedly by the SQLite database engine
          until the required number of rows has been read **or** the
          function raises a `StopIteration` signalling no more rows
          are available.
          """
          if self.current > self.stop:
              raise StopIteration
          ret, self.current = self.current, self.current + self.step
          return (ret,)
  # Usage:
  cursor = db.execute_sql('SELECT * FROM series(?, ?, ?)', (0, 5, 2))
  for value, in cursor:
      print(value)
  # Prints:
  # 0
  # 2
  # 4

• unregister_aggregate(name)

  Parameters:	name – Name of the user-defined aggregate function.

  Unregister the user-defined aggregate function.

• unregister_collation(name)

  Parameters:	name – Name of the user-defined collation.

  Unregister the user-defined collation.

• unregister_function(name)

  Parameters:	name – Name of the user-defined scalar function.

  Unregister the user-defined scalar function.

• unregister_table_function(name)

  Parameters:	name – Name of the user-defined table function.
  Returns:	True or False, depending on whether the function was removed.

  Unregister the user-defined scalar function.

• load_extension(extension_module)

  Load the given C extension. If a connection is currently open in the calling thread, then the extension will be loaded for that connection as well as all subsequent connections.

  For example, if you’ve compiled the closure table extension and wish to use it in your application, you might write:

  db = SqliteExtDatabase('my_app.db')
  db.load_extension('closure')

• transaction([lock_type=None])

  Parameters:	lock_type (str) – Locking strategy: DEFERRED, IMMEDIATE, EXCLUSIVE.

  Create a transaction context-manager using the specified locking strategy (defaults to DEFERRED).

class PostgresqlDatabase(database[, register_unicode=True[, encoding=None]])

Postgresql database implementation.

Additional optional keyword-parameters:

class MySQLDatabase(database[, \*kwargs*])

MySQL database implementation.

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.:

  query = MyModel.select() new_query = query.where(MyModel.field == ‘value’)

• unwrap()

  API for recursively unwrapping “wrapped” nodes. Base case is to return self.

class Source([alias=None])

A source of row tuples, for example a table, join, or select query. By default provides a “magic” attribute named “c” that is a factory for column/attribute lookups, for example:

User = Table('users')
query = (User
         .select(User.c.username)
         .where(User.c.active == True)
         .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 explicitly declares columns and no columns are provided, then by default all the table’s defined columns will be selected.

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

  Parameters:	dest (Source) – Join the table with the given destination. join_type (str) – Join type. on – Expression to use as join predicate.
  Returns:	a Join instance.

  Join type may be one of:

  • JOIN.INNER
  • JOIN.LEFT_OUTER
  • JOIN.RIGHT_OUTER
  • JOIN.FULL
  • JOIN.FULL_OUTER
  • JOIN.CROSS

• left_outer_join(dest[, on=None])

  Parameters:	dest (Source) – Join the table with the given destination. on – Expression to use as join predicate.
  Returns:	a Join instance.

  Convenience method for calling join() using a LEFT OUTER join.

class BaseTable

Base class for table-like objects, which support JOINs via operator overloading.

• __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).

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:

User = Table('users')
query = (User
         .select(User.c.id, User.c.username)
         .order_by(User.c.username))

Equivalent example when columns are specified:

User = Table('users', ('id', 'username'))
query = (User
         .select(User.id, User.username)
         .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 against it without the need to specify the database in the query’s execute method.

• bind_ctx([database=None])

  Parameters:	database – Database object.

  Return a context manager that will bind the table to the given database for 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 explicitly declares columns and no columns are provided, then by default all the table’s defined columns will be selected.

  Example:

  User = Table('users', ('id', 'username'))
  # Because columns were defined on the Table, we will default to
  # selecting both of the User table's columns.
  # Evaluates to SELECT id, username FROM users
  query = User.select()
  Note = Table('notes')
  query = (Note
           .select(Note.c.content, Note.c.timestamp, User.username)
           .join(User, on=(Note.c.user_id == User.id))
           .where(Note.c.is_published == True)
           .order_by(Note.c.timestamp.desc()))
  # Using a function to select users and the number of notes they
  # have authored.
  query = (User
           .select(
              User.username,
              fn.COUNT(Note.c.id).alias('n_notes'))
           .join(
              Note,
              JOIN.LEFT_OUTER,
              on=(User.id == Note.c.user_id))
           .order_by(fn.COUNT(Note.c.id).desc()))

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

  Parameters:	insert – A dictionary mapping column to value, an iterable that yields dictionaries (i.e. list), or a Select query. columns (list) – The list of columns to insert into when the data being inserted is not a dictionary. kwargs – Mapping of column-name to value.

  Create a Insert query into the table.

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

  Parameters:	insert – A dictionary mapping column to value, an iterable that yields dictionaries (i.e. list), or a Select query. columns (list) – The list of columns to insert into when the data being inserted is not a dictionary. kwargs – Mapping of column-name to value.

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

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

  Parameters:	update – A dictionary mapping column to value. 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.

• on(predicate)

  Parameters:	predicate (Expression) – join predicate.

  Specify the predicate expression used for this join.

class CTE(name, query[, recursive=False[, columns=None]])

Represent a common-table-expression.

class ColumnBase

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

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

• &: 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-like object.

• cast(as_type)

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

  Create a CAST expression.

• asc()

  Returns:	an ascending Ordering object for the column.

• desc()

  Returns:	an descending Ordering object for the column.

• __invert__()

  Returns:	a Negated wrapper for the column.

class Column(source, name)

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

class Alias(node, alias)

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. If the new alias is None, then the original column-like object is returned.

class Negated(node)

Represents a negated column-like object.

class Value(value[, converterNone[, unpack=True]])

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

AsIs(value)

Represents a Value that is treated as-is, and passed directly back to the database driver.

class Cast(node, cast)

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

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

Represent ordering by a column-like object.

• 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])

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

class Entity(\path*)

Represent a quoted entity in a query, such as a table, column, alias. The name 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])

Represent a parameterized SQL query or query-fragment.

Check(constraint)

Represent a CHECK constraint.

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

Represent an arbitrary SQL function call.

Note

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

Example of using fn to call an arbitrary SQL function:

# Query users and count of tweets authored.
query = (User
         .select(User.username, fn.COUNT(Tweet.id).alias('ct'))
         .join(Tweet, JOIN.LEFT_OUTER, on=(User.id == Tweet.user_id))
         .group_by(User.username)
         .order_by(fn.COUNT(Tweet.id).desc()))

• over([partition_by=None[, order_by=None[, start=None[, end=None[, window=None]]]]])

  Parameters:	partition_by (list) – List of columns to partition by. order_by (list) – List of columns / expressions to order window by. start – A SQL instance or a string expressing the start of the window range. end – A SQL instance or a string expressing the end of the window range. window (Window) – A Window instance.

  Note

  For simplicity, it is permissible to call over() with a Window instance as the first and only parameter.

  Examples:

  # Using a simple partition on a single column.
  query = (Sample
           .select(
              Sample.counter,
              Sample.value,
              fn.AVG(Sample.value).over([Sample.counter]))
           .order_by(Sample.counter))
  # Equivalent example Using a Window() instance instead.
  window = Window(partition_by=[Sample.counter])
  query = (Sample
           .select(
              Sample.counter,
              Sample.value,
              fn.AVG(Sample.value).over(window))
           .window(window)  # Note call to ".window()"
           .order_by(Sample.counter))
  # Example using bounded window.
  query = (Sample
           .select(Sample.value,
                   fn.SUM(Sample.value).over(
                      partition_by=[Sample.counter],
                      start=Window.preceding(),  # unbounded.
                      end=Window.following(1)))  # 1 following.
           .order_by(Sample.id))

• coerce([coerce=True])

  Parameters:	coerce (bool) – Whether to coerce function-call result.

class Window([partition_by=None[, order_by=None[, start=None[, end=None[, alias=None]]]]])

Represent a WINDOW clause.

• CURRENT_ROW

  Handy reference to current row for use in start/end clause.

• alias([alias=None])

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

• static following([value=None])

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

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

• static preceding([value=None])

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

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

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

Examples:

Number = Table('numbers', ('val',))
num_as_str = Case(Number.val, (
    (1, 'one'),
    (2, 'two'),
    (3, 'three')), 'a lot')
query = Number.select(Number.val, num_as_str.alias('num_str'))
# The above is equivalent to:
# SELECT "val",
#   CASE "val"
#       WHEN 1 THEN 'one'
#       WHEN 2 THEN 'two'
#       WHEN 3 THEN 'three'
#       ELSE 'a lot' END AS "num_str"
# FROM "numbers"
num_as_str = Case(None, (
    (Number.val == 1, 'one'),
    (Number.val == 2, 'two'),
    (Number.val == 3, 'three')), 'a lot')
query = Number.select(Number.val, num_as_str.alias('num_str'))
# The above is equivalent to:
# SELECT "val",
#   CASE
#       WHEN "val" = 1 THEN 'one'
#       WHEN "val" = 2 THEN 'two'
#       WHEN "val" = 3 THEN 'three'
#       ELSE 'a lot' END AS "num_str"
# FROM "numbers"

class NodeList(nodes[, glue=’ ‘[, parens=False]])

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

CommaNodeList(nodes)

Represent a list of nodes joined by commas.

EnclosedNodeList(nodes)

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

class DQ(\*query*)

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

class Tuple(\args*)

Represent a SQL row tuple.

class OnConflict([action=None[, update=None[, preserve=None[, where=None[, conflict_target=None]]]]])

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

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

• preserve(\columns*)

  Parameters:	columns – Columns whose values should be preserved.

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

  Parameters:	_data (dict) – Dictionary mapping column to new value. kwargs – Dictionary mapping column name to new value.

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

• where(\expressions*)

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

• conflict_target(\constraints*)

  Parameters:	constraints – Name(s) of columns/constraints that are the target of the conflict resolution.

class BaseQuery

The parent class from which all other query classes are derived. While you will not deal with BaseQuery directly in your code, it implements 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.

• as_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 named tuples.

  Return rows as named tuples.

• objects([constructor=None])

  Parameters:	constructor – Function that accepts row dict and returns an arbitrary 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. Not required if query was previously bound to a database.

  Execute the query and return result (depends on type of query being executed). For example, select queries the return result will be an iterator over the query results.

• iterator([database=None])

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

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

  Note

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

  Example:

  query = StatTbl.select().order_by(StatTbl.timestamp).tuples()
  for row in query.iterator(db):
      process_row(row)

• __iter__()

  Execute the query and return an iterator over the result-set.

  Unlike iterator(), this method will cause rows to be 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-set is loaded as it would be during normal iteration, and the length is determined from the size of the result set.

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

Create a query by directly specifying the SQL to execute.

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

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

• with_cte(\cte_list*)

  Parameters:	cte_list – zero or more CTE objects.

  Include the given common-table-expressions in the query. Any previously specified CTEs will be overwritten.

• where(\expressions*)

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

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

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

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

  Example selecting tweets made by users who are either editors or administrators:

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

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

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

  Note

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

• order_by(\values*)

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

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

• order_by_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:	page (int) – Page number of results (starting from 1). paginate_by (int) – Rows-per-page.

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

class SelectQuery

Select query helper-class that implements operator-overloads for creating compound queries.

• __add__(dest)

  Create a UNION ALL query with dest.

• __or__(dest)

  Create a UNION query with dest.

• __and__(dest)

  Create an INTERSECT query with dest.

• __sub__(dest)

  Create an EXCEPT query with dest.

class SelectBase

Base-class for Select and CompoundSelect queries.

• peek(database[, n=1])

  Parameters:	database (Database) – database to execute query against. 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 start of the cursor. This function may be called multiple times safely, and will always return the first N rows of results.

• first(database[, n=1])

  Parameters:	database (Database) – database to execute query against. 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 is applied to the query to ensure that only n rows are returned. Multiple calls for the same value of n will not result in multiple executions.

• scalar(database[, as_tuple=False])

  Parameters:	database (Database) – database to execute query against. 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 multiple scalar values are anticipated (e.g. multiple aggregations in a single query) then you may specify as_tuple=True to get the row tuple.

  Example:

  query = Note.select(fn.MAX(Note.timestamp))
  max_ts = query.scalar(db)
  query = Note.select(fn.MAX(Note.timestamp), fn.COUNT(Note.id))
  max_ts, n_notes = query.scalar(db, as_tuple=True)

• count(database[, clear_limit=False])

  Parameters:	database (Database) – database to execute query against. 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 (<current query>).

• 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. Multiple calls will result in multiple queries being executed.

class CompoundSelectQuery(lhs, op, rhs)

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[, \*kwargs*]]]]]]]])

Class representing a SELECT query.

Note

Rather than instantiating this directly, most-commonly you will use a factory 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 id and username columns are selected. When iterated, will return instances of the User model:

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

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

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

Note

While it is possible to instantiate Select directly, more commonly 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 for backwards-compatibility.

• from_(\sources*)

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

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

  User = Table('users')
  Tweet = Table('tweets')
  query = (User
           .select(User.c.username, Tweet.c.content)
           .from_(User, Tweet)
           .where(User.c.id == Tweet.c.user_id))
  for row in query.execute(db):
      print(row['username'], '->', row['content'])

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

  Parameters:	dest – A table or table-like object. join_type (str) – Type of JOIN, default is “INNER”. on (Expression) – Join predicate.

  Join type may be one of:

  • JOIN.INNER
  • JOIN.LEFT_OUTER
  • JOIN.RIGHT_OUTER
  • JOIN.FULL
  • JOIN.FULL_OUTER
  • JOIN.CROSS

  Express a JOIN:

  User = Table('users', ('id', 'username'))
  Note = Table('notes', ('id', 'user_id', 'content'))
  query = (Note
           .select(Note.content, User.username)
           .join(User, on=(Note.user_id == User.id)))

• group_by(\columns*)

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

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

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

  Example:

  query = (User
           .select(User, fn.Count(Tweet.id).alias('count'))
           .join(Tweet)
           .group_by(User))

• group_by_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 HAVING clause.

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

• distinct(\columns*)

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

  Indicate whether this query should use a DISTINCT clause. By specifying a 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 be overwritten.

  Example:

  # Equivalent example Using a Window() instance instead.
  window = Window(partition_by=[Sample.counter])
  query = (Sample
           .select(
              Sample.counter,
              Sample.value,
              fn.AVG(Sample.value).over(window))
           .window(window)  # Note call to ".window()"
           .order_by(Sample.counter))

• for_update([for_update=True])

  Parameters:	for_update – Either a boolean or a string indicating the desired expression, e.g. “FOR UPDATE NOWAIT”.

class _WriteQuery(table[, returning=None[, \*kwargs*]])

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).

  query = (User
           .insert_many([{'username': 'foo'},
                         {'username': 'bar'},
                         {'username': 'baz'}])
           .returning(User.id, User.username)
           .namedtuples())
  data = query.execute()
  for row in data:
      print('added:', row.username, 'with id=', row.id)

class Update(table[, update=None[, \*kwargs*]])

Class representing an UPDATE query.

Example:

PageView = Table('page_views')
query = (PageView
         .update({PageView.c.page_views: PageView.c.page_views + 1})
         .where(PageView.c.url == url))
query.execute(database)

class Insert(table[, insert=None[, columns=None[, on_conflict=None[, \*kwargs*]]]])

Class representing an INSERT query.

• on_conflict_ignore([ignore=True])

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

  Specify IGNORE conflict resolution strategy.

• on_conflict_replace([replace=True])

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

  Specify REPLACE conflict resolution strategy.

• on_conflict(\args, **kwargs*)

  Specify an ON CONFLICT clause by populating a OnConflict object.

class Delete

Class representing a DELETE query.

class Index(name, table, expressions[, unique=False[, safe=False[, where=None[, using=None]]]])

• 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 WHERE clause.

  Include the given expressions in the WHERE clause of the index. The expressions will be AND-ed together with any previously-specified WHERE 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]]]]])

Fields

class Field([null=False[, index=False[, unique=False[, column_name=None[, default=None[, primary_key=False[, constraints=None[, sequence=None[, collation=None[, unindexed=False[, choices=None[, help_text=None[, verbose_name=None]]]]]]]]]]]]])

Fields on a Model are analagous to columns on a table.

• field_type = '<some field type>'

  Attribute used to map this field to a column type, e.g. “INT”. See the FIELD object in the source for more information.

• column

  Retrieve a reference to the underlying Column object.

• model

  The model the field is bound to.

• name

  The name of the field.

• db_value(value)

  Coerce a Python value into a value suitable for storage in the database. Sub-classes operating on special data-types will most likely want to override this method.

• python_value(value)

  Coerce a value from the database into a Python object. Sub-classes operating on special data-types will most likely want to override this method.

• coerce(value)

  This method is a shorthand that is used, by default, by both db_value() and python_value().

  Parameters:	value – arbitrary data from app or backend
  Return type:	python data type

class IntegerField

Field class for storing integers.

class BigIntegerField

Field class for storing big integers (if supported by database).

class SmallIntegerField

Field class for storing small integers (if supported by database).

class AutoField

Field class for storing auto-incrementing primary keys.

Note

In SQLite, for performance reasons, the default primary key type simply uses the max existing value + 1 for new values, as opposed to the max ever value + 1. This means deleted records can have their primary keys reused. In conjunction with SQLite having foreign keys disabled by default (meaning ON DELETE is ignored, even if you specify it explicitly), this can lead to surprising and dangerous behaviour. To avoid this, you may want to use one or both of AutoIncrementField and pragmas=[('foreign_keys', 'on')] when you instantiate SqliteDatabase.

class FloatField

Field class for storing floating-point numbers.

class DoubleField

Field class for storing double-precision floating-point numbers.

class DecimalField([max_digits=10[, decimal_places=5[, auto_round=False[, rounding=None[, \*kwargs*]]]]])

class CharField([max_length=255])

Field class for storing strings.

Note

Values that exceed length are not truncated automatically.

class FixedCharField

Field class for storing fixed-length strings.

Note

Values that exceed length are not truncated automatically.

class TextField

Field class for storing text.

class BlobField

Field class for storing binary data.

class BitField

Field class for storing options in a 64-bit integer column.

Usage:

class Post(Model):
    content = TextField()
    flags = BitField()
    is_favorite = flags.flag(1)
    is_sticky = flags.flag(2)
    is_minimized = flags.flag(4)
    is_deleted = flags.flag(8)
>>> p = Post()
>>> p.is_sticky = True
>>> p.is_minimized = True
>>> print(p.flags)  # Prints 4 | 2 --> "6"
6
>>> p.is_favorite
False
>>> p.is_sticky
True

We can use the flags on the Post class to build expressions in queries as well:

# Generates a WHERE clause that looks like:
# WHERE (post.flags & 1 != 0)
query = Post.select().where(Post.is_favorite)
# Query for sticky + favorite posts:
query = Post.select().where(Post.is_sticky & Post.is_favorite)

• flag(value)

  Returns a descriptor that can get or set specific bits in the overall value. When accessed on the class itself, it returns a Expression object suitable for use in a query.

class BigBitField

Field class for storing arbitrarily-large bitmaps in a BLOB. The field will grow the underlying buffer as necessary, ensuring there are enough bytes of data to support the number of bits of data being stored.

Example usage:

class Bitmap(Model):
    data = BigBitField()
bitmap = Bitmap()
# Sets the ith bit, e.g. the 1st bit, the 11th bit, the 63rd, etc.
bits_to_set = (1, 11, 63, 31, 55, 48, 100, 99)
for bit_idx in bits_to_set:
    bitmap.data.set_bit(bit_idx)
# We can test whether a bit is set using "is_set":
assert bitmap.data.is_set(11)
assert not bitmap.data.is_set(12)
# We can clear a bit:
bitmap.data.clear_bit(11)
assert not bitmap.data.is_set(11)
# We can also "toggle" a bit. Recall that the 63rd bit was set earlier.
assert bitmap.data.toggle_bit(63) is False
assert bitmap.data.toggle_bit(63) is True
assert bitmap.data.is_set(63)

• set_bit(idx)

  Parameters:	idx (int) – Bit to set, indexed starting from zero.

  Sets the idx-th bit in the bitmap.

• clear_bit(idx)

  Parameters:	idx (int) – Bit to clear, indexed starting from zero.

  Clears the idx-th bit in the bitmap.

• toggle_bit(idx)

  Parameters:	idx (int) – Bit to toggle, indexed starting from zero.
  Returns:	Whether the bit is set or not.

  Toggles the idx-th bit in the bitmap and returns whether the bit is set or not.

  Example:

  >>> bitmap = Bitmap()
  >>> bitmap.data.toggle_bit(10)  # Toggle the 10th bit.
  True
  >>> bitmap.data.toggle_bit(10)  # This will clear the 10th bit.
  False

• is_set(idx)

  Parameters:	idx (int) – Bit index, indexed starting from zero.
  Returns:	Whether the bit is set or not.

  Returns boolean indicating whether the idx-th bit is set or not.

class UUIDField

Field class for storing uuid.UUID objects.

class DateTimeField([formats=None[, \*kwargs*]])

Field class for storing datetime.datetime objects.

Accepts a special parameter formats, which contains a list of formats the datetime can be encoded with (for databases that do not have support for a native datetime data-type). The default supported formats are:

Note

If the incoming value does not match a format, it is returned as-is.

'%Y-%m-%d %H:%M:%S.%f' # year-month-day hour-minute-second.microsecond
'%Y-%m-%d %H:%M:%S' # year-month-day hour-minute-second
'%Y-%m-%d' # year-month-day

• year

  Reference the year of the value stored in the column in a query.

  Blog.select().where(Blog.pub_date.year == 2018)

• month

  Reference the month of the value stored in the column in a query.

• day

  Reference the day of the value stored in the column in a query.

• hour

  Reference the hour of the value stored in the column in a query.

• minute

  Reference the minute of the value stored in the column in a query.

• second

  Reference the second of the value stored in the column in a query.

class DateField([formats=None[, \*kwargs*]])

Field class for storing datetime.date objects.

Accepts a special parameter formats, which contains a list of formats the datetime can be encoded with (for databases that do not have support for a native date data-type). The default supported formats are:

'%Y-%m-%d' # year-month-day
'%Y-%m-%d %H:%M:%S' # year-month-day hour-minute-second
'%Y-%m-%d %H:%M:%S.%f' # year-month-day hour-minute-second.microsecond

Note

If the incoming value does not match a format, it is returned as-is.

• year

  Reference the year of the value stored in the column in a query.

  Person.select().where(Person.dob.year == 1983)

• month

  Reference the month of the value stored in the column in a query.

• day

  Reference the day of the value stored in the column in a query.

class TimeField([formats=None[, \*kwargs*]])

Field class for storing datetime.time objects (not timedelta).

Accepts a special parameter formats, which contains a list of formats the datetime can be encoded with (for databases that do not have support for a native time data-type). The default supported formats are:

'%H:%M:%S.%f' # hour:minute:second.microsecond
'%H:%M:%S' # hour:minute:second
'%H:%M' # hour:minute
'%Y-%m-%d %H:%M:%S.%f' # year-month-day hour-minute-second.microsecond
'%Y-%m-%d %H:%M:%S' # year-month-day hour-minute-second

Note

If the incoming value does not match a format, it is returned as-is.

• hour

  Reference the hour of the value stored in the column in a query.

  evening_events = Event.select().where(Event.time.hour > 17)

• minute

  Reference the minute of the value stored in the column in a query.

• second

  Reference the second of the value stored in the column in a query.

class TimestampField([resolution=1[, utc=False[, \*kwargs*]]])

Field class for storing date-times as integer timestamps. Sub-second resolution is supported by multiplying by a power of 10 to get an integer.

Accepts a special parameter resolution, which is a power-of-10 up to 10^6. This allows sub-second precision while still using an IntegerField for storage. Default is 1 (second precision).

Also accepts a boolean parameter utc, used to indicate whether the timestamps should be UTC. Default is False.

Finally, the field default is the current timestamp. If you do not want this behavior, then explicitly pass in default=None.

class IPField

Field class for storing IPv4 addresses efficiently (as integers).

class BooleanField

Field class for storing boolean values.

class BareField([coerce=None[, \*kwargs*]])

Field class that does not specify a data-type (SQLite-only).

Since data-types are not enforced, you can declare fields without any data-type. It is also common for SQLite virtual tables to use meta-columns or untyped columns, so for those cases as well you may wish to use an untyped field.

Accepts a special coerce parameter, a function that takes a value coming from the database and converts it into the appropriate Python type.

class ForeignKeyField(model[, field=None[, backref=None[, on_delete=None[, on_update=None[, object_id_name=None[, \*kwargs*]]]]]])

Field class for storing a foreign key.

class User(Model):
    name = TextField()
class Tweet(Model):
    user = ForeignKeyField(User, backref='tweets')
    content = TextField()
# "user" attribute
>>> some_tweet.user
<User: charlie>
# "tweets" backref attribute
>>> for tweet in charlie.tweets:
...     print(tweet.content)
Some tweet
Another tweet
Yet another tweet

Note

Foreign keys do not have a particular field_type as they will take their field type depending on the type of primary key on the model they are related to.

Note

If you manually specify a field, that field must be either a primary key or have a unique constraint.

Note

Take care with foreign keys in SQLite. By default, ON DELETE has no effect, which can have surprising (and usually unwanted) effects on your database integrity. This can affect you even if you don’t specify on_delete, since the default ON DELETE behaviour (to fail without modifying your data) does not happen, and your data can be silently relinked. The safest thing to do is to specify pragmas=(('foreign_keys', 'on'),) when you instantiate SqliteDatabase.

class DeferredForeignKey(rel_model_name[, \*kwargs*])

Field class for representing a deferred foreign key.

class ManyToManyField(model[, backref=None[, through_model=None]])

The ManyToManyField provides a simple interface for working with many-to-many relationships, inspired by Django. A many-to-many relationship is typically implemented by creating a junction table with foreign keys to the two models being related. For instance, if you were building a syllabus manager for college students, the relationship between students and courses would be many-to-many. Here is the schema using standard APIs:

Attention

This is not a field in the sense that there is no column associated with it. Rather, it provides a convenient interface for accessing rows of data related via a through model.

Standard way of declaring a many-to-many relationship (without the use of the ManyToManyField):

class Student(Model):
    name = CharField()
class Course(Model):
    name = CharField()
class StudentCourse(Model):
    student = ForeignKeyField(Student)
    course = ForeignKeyField(Course)

To query the courses for a particular student, you would join through the junction table:

# List the courses that "Huey" is enrolled in:
courses = (Course
           .select()
           .join(StudentCourse)
           .join(Student)
           .where(Student.name == 'Huey'))
for course in courses:
    print(course.name)

The ManyToManyField is designed to simplify this use-case by providing a field-like API for querying and modifying data in the junction table. Here is how our code looks using ManyToManyField:

class Student(Model):
    name = CharField()
class Course(Model):
    name = CharField()
    students = ManyToManyField(Student, backref='courses')

Note

It does not matter from Peewee’s perspective which model the ManyToManyField goes on, since the back-reference is just the mirror image. In order to write valid Python, though, you will need to add the ManyToManyField on the second model so that the name of the first model is in the scope.

We still need a junction table to store the relationships between students and courses. This model can be accessed by calling the get_through_model() method. This is useful when creating tables.

# Create tables for the students, courses, and relationships between
# the two.
db.create_tables([
    Student,
    Course,
    Course.students.get_through_model()])

When accessed from a model instance, the ManyToManyField exposes a ModelSelect representing the set of related objects. Let’s use the interactive shell to see how all this works:

>>> huey = Student.get(Student.name == 'huey')
>>> [course.name for course in huey.courses]
['English 101', 'CS 101']
>>> engl_101 = Course.get(Course.name == 'English 101')
>>> [student.name for student in engl_101.students]
['Huey', 'Mickey', 'Zaizee']

To add new relationships between objects, you can either assign the objects directly to the ManyToManyField attribute, or call the add() method. The difference between the two is that simply assigning will clear out any existing relationships, whereas add() can preserve existing relationships.

>>> huey.courses = Course.select().where(Course.name.contains('english'))
>>> for course in huey.courses.order_by(Course.name):
...     print course.name
English 101
English 151
English 201
English 221
>>> cs_101 = Course.get(Course.name == 'CS 101')
>>> cs_151 = Course.get(Course.name == 'CS 151')
>>> huey.courses.add([cs_101, cs_151])
>>> [course.name for course in huey.courses.order_by(Course.name)]
['CS 101', 'CS151', 'English 101', 'English 151', 'English 201',
 'English 221']

This is quite a few courses, so let’s remove the 200-level english courses. To remove objects, use the remove() method.

>>> huey.courses.remove(Course.select().where(Course.name.contains('2'))
2
>>> [course.name for course in huey.courses.order_by(Course.name)]
['CS 101', 'CS151', 'English 101', 'English 151']

To remove all relationships from a collection, you can use the clear() method. Let’s say that English 101 is canceled, so we need to remove all the students from it:

>>> engl_101 = Course.get(Course.name == 'English 101')
>>> engl_101.students.clear()

Note

For an overview of implementing many-to-many relationships using standard Peewee APIs, check out the Implementing Many to Many section. For all but the most simple cases, you will be better off implementing many-to-many using the standard APIs.

• through_model

  The Model representing the many-to-many junction table. Will be auto-generated if not explicitly declared.

• add(value[, clear_existing=True])

  Parameters:	value – Either a Model instance, a list of model instances, or a SelectQuery. clear_existing (bool) – Whether to remove existing relationships.

  Associate value with the current instance. You can pass in a single model instance, a list of model instances, or even a ModelSelect.

  Example code:

  # Huey needs to enroll in a bunch of courses, including all
  # the English classes, and a couple Comp-Sci classes.
  huey = Student.get(Student.name == 'Huey')
  # We can add all the objects represented by a query.
  english_courses = Course.select().where(
      Course.name.contains('english'))
  huey.courses.add(english_courses)
  # We can also add lists of individual objects.
  cs101 = Course.get(Course.name == 'CS 101')
  cs151 = Course.get(Course.name == 'CS 151')
  huey.courses.add([cs101, cs151])

• remove(value)

  Parameters:	value – Either a Model instance, a list of model instances, or a ModelSelect.

  Disassociate value from the current instance. Like add(), you can pass in a model instance, a list of model instances, or even a ModelSelect.

  Example code:

  # Huey is currently enrolled in a lot of english classes
  # as well as some Comp-Sci. He is changing majors, so we
  # will remove all his courses.
  english_courses = Course.select().where(
      Course.name.contains('english'))
  huey.courses.remove(english_courses)
  # Remove the two Comp-Sci classes Huey is enrolled in.
  cs101 = Course.get(Course.name == 'CS 101')
  cs151 = Course.get(Course.name == 'CS 151')
  huey.courses.remove([cs101, cs151])

• clear()

  Remove all associated objects.

  Example code:

  # English 101 is canceled this semester, so remove all
  # the enrollments.
  english_101 = Course.get(Course.name == 'English 101')
  english_101.students.clear()

• get_through_model()

  Return the Model representing the many-to-many junction table. This can be specified manually when the field is being instantiated using the through_model parameter. If a through_model is not specified, one will automatically be created.

  When creating tables for an application that uses ManyToManyField, you must create the through table expicitly.

  # Get a reference to the automatically-created through table.
  StudentCourseThrough = Course.students.get_through_model()
  # Create tables for our two models as well as the through model.
  db.create_tables([
      Student,
      Course,
      StudentCourseThrough])