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

  Parameters:	commit – For internal use.

  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.

• batch_commit(it, n)

  Parameters:	it (iterable) – an iterable whose items will be yielded. n (int) – commit every n items.
  Returns:	an equivalent iterable to the one provided, with the addition that groups of n items will be yielded in a transaction.

  The purpose of this method is to simplify batching large operations, such as inserts, updates, etc. You pass in an iterable and the number of items-per-batch, and the items will be returned by an equivalent iterator that wraps each batch in a transaction.

  Example:

  # Some list or iterable containing data to insert.
  row_data = [{'username': 'u1'}, {'username': 'u2'}, ...]
  # Insert all data, committing every 100 rows. If, for example,
  # there are 789 items in the list, then there will be a total of
  # 8 transactions (7x100 and 1x89).
  for row in db.batch_commit(row_data, 100):
      User.create(**row)

  An alternative that may be more efficient is to batch the data into a multi-value INSERT statement (for example, using Model.insert_many()):

  with db.atomic():
      for idx in range(0, len(row_data), 100):
          # Insert 100 rows at a time.
          rows = row_data[idx:idx + 100]
          User.insert_many(rows).execute()

• 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) – One or more Model classes to bind. bind_refs (bool) – Bind related models. bind_backrefs (bool) – Bind back-reference related models.

  Bind the given list of models, and specified relations, to the database.

• bind_ctx(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.

  Example:

  ``` MODELS = (User, Account, Note)

  Bind the given models to the db for the duration of wrapped block.

  def use_test_database(fn):

  @wraps(fn)
  def inner(self):
      with test_db.bind_ctx(MODELS):
          test_db.create_tables(MODELS)
          try:
              fn(self)
          finally:
              test_db.drop_tables(MODELS)
  return inner

class TestSomething(TestCase):
    @use_test_database
    def test_something(self):
        # ... models are bound to test database ...
        pass
```

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

• attach(filename, name)

  Parameters:	filename (str) – Database to attach (or :memory: for in-memory) name (str) – Schema name for attached database.
  Returns:	boolean indicating success

  Register another database file that will be attached to every database connection. If the main database is currently connected, the new database will be attached on the open connection.

  Note

  Databases that are attached using this method will be attached every time a database connection is opened.

• detach(name)

  Parameters:	name (str) – Schema name for attached database.
  Returns:	boolean indicating success

  Unregister another database file that was attached previously with a call to attach(). If the main database is currently connected, the attached database will be detached from the open connection.

• 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 ValuesList(values[, columns=None[, alias=None]])

Represent a values list that can be used like a table.

Example:

data = [(1, 'first'), (2, 'second')]
vl = ValuesList(data, columns=('idx', 'name'))
query = (vl
         .select(vl.c.idx, vl.c.name)
         .order_by(vl.c.idx))
# Yields:
# SELECT t1.idx, t1.name
# FROM (VALUES (1, 'first'), (2, 'second')) AS t1(idx, name)
# ORDER BY t1.idx

• columns(\names*)

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

  Example:

  vl = ValuesList([(1, 'first'), (2, 'second')])
  vl = vl.columns('idx', 'name').alias('v')
  query = vl.select(vl.c.idx, vl.c.name)
  # Yields:
  # SELECT v.idx, v.name
  # FROM (VALUES (1, 'first'), (2, 'second')) AS v(idx, name)

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

Represent a common-table-expression.

• select_from(\columns*)

  Create a SELECT query that utilizes the given common table expression as 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

• union_all(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 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.

fn()

The fn() helper is actually an instance of Function that implements a __getattr__ hook to provide a nice API for calling SQL functions.

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

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

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

# Get users whose username begins with "A" or "a":
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[, 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.

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

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

class SelectQuery

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

• union_all(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 a trailing “_” 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:	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.

• select_extend(\columns*)

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

  Extend the current selection with the given columns.

  Example:

  def get_users(with_count=False):
      query = User.select()
      if with_count:
          query = (query
                   .select_extend(fn.COUNT(Tweet.id).alias('count'))
                   .join(Tweet, JOIN.LEFT_OUTER)
                   .group_by(User))
      return query

• 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([action=None[, update=None[, preserve=None[, where=None[, conflict_target=None]]]]])

  Parameters:	action (str) – Action to take when resolving conflict. If blank, action is assumed to be “update”. update – A dictionary mapping column to new value. preserve – A list of columns whose values should be preserved from the original INSERT. where – Expression to restrict the conflict resolution. conflict_target – Name of column or constraint to check.

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

  Example:

  class User(Model):
      username = TextField(unique=True)
      last_login = DateTimeField(null=True)
      login_count = IntegerField()
  def log_user_in(username):
      now = datetime.datetime.now()
      # INSERT a new row for the user with the current timestamp and
      # login count set to 1. If the user already exists, then we
      # will preserve the last_login value from the "insert()" clause
      # and atomically increment the login-count.
      userid = (User
                .insert(username=username, last_login=now, login_count=1)
                .on_conflict(
                    conflict_target=[User.username],
                    preserve=[User.last_login],
                    update={User.login_count: User.login_count + 1})
                .execute())
      return userid

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

Expressive method for declaring an index on a model.

Examples:

class Article(Model):
    name = TextField()
    timestamp = TimestampField()
    status = IntegerField()
    flags = BitField()
    is_sticky = flags.flag(1)
    is_favorite = flags.flag(2)
# CREATE INDEX ... ON "article" ("name", "timestamp")
idx = ModelIndex(Article, (Article.name, Article.timestamp))
# CREATE INDEX ... ON "article" ("name", "timestamp") WHERE "status" = 1
idx = idx.where(Article.status == 1)
# CREATE UNIQUE INDEX ... ON "article" ("timestamp" DESC, "flags" & 2) WHERE "status" = 1
idx = ModelIndex(
    Article,
    (Article.timestamp.desc(), Article.flags.bin_and(2)),
    unique = True).where(Article.status == 1)

You can also use Model.index():

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

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

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

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 BigAutoField

Field class for storing auto-incrementing primary keys using 64-bits.

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. With Postgres, the underlying column’s data-type will be UUID. Since SQLite and MySQL do not have a native UUID type, the UUID is stored as a VARCHAR instead.

class BinaryUUIDField

Field class for storing uuid.UUID objects efficiently in 16-bytes. Uses the database’s BLOB data-type (or VARBINARY in MySQL, or BYTEA in Postgres).

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.