API Documentation

This document specifies Peewee’s APIs.

Database

class Database(database[, thread_safe=True[, autorollback=False[, field_types=None[, operations=None[, autoconnect=True[, \*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.

Examples:

# Sqlite database using WAL-mode and 32MB page-cache.
db = SqliteDatabase('app.db', pragmas={
    'journal_mode': 'wal',
    'cache_size': -32 * 1000})
# Postgresql database on remote host.
db = PostgresqlDatabase('my_app', user='postgres', host='10.1.0.3',
                        password='secret')

Deferred initialization example:

db = PostgresqlDatabase(None)
class BaseModel(Model):
    class Meta:
        database = db
# Read database connection info from env, for example:
db_name = os.environ['DATABASE']
db_host = os.environ['PGHOST']
# Initialize database.
db.init(db_name, host=db_host, user='postgres')

• 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. See Run-time database configuration for more info.

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

• session_start()

  Begin a new transaction (without using a context-manager or decorator). This method is useful if you intend to execute a sequence of operations inside a transaction, but using a decorator or context-manager would not be appropriate.

  Note

  It is strongly advised that you use the Database.atomic() method whenever possible for managing transactions/savepoints. The atomic method correctly manages nesting, uses the appropriate construction (e.g., transaction-vs-savepoint), and always cleans up after itself.

  The session_start() method should only be used if the sequence of operations does not easily lend itself to wrapping using either a context-manager or decorator.

  Warning

  You must always call either session_commit() or session_rollback() after calling the session_start method.

• session_commit()

  Commit any changes made during a transaction begun with session_start().

• session_rollback()

  Roll back any changes made during a transaction begun with session_start().

• 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'),
   ...]

• get_views([schema=None])

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

  Return a list of ViewMetadata tuples for VIEWs present in the database.

  Example:

  print(db.get_views())
  [ViewMetadata(
       name='entries_public',
       sql='CREATE VIEW entries_public AS SELECT ... '),
   ...]

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

• extract_date(date_part, date_field)

  Parameters:	date_part (str) – date part to extract, e.g. ‘year’. date_field (Node) – a SQL node containing a date/time, for example a DateTimeField.
  Returns:	a SQL node representing a function call that will return the provided date part.

  Provides a compatible interface for extracting a portion of a datetime.

• truncate_date(date_part, date_field)

  Parameters:	date_part (str) – date part to truncate to, e.g. ‘day’. date_field (Node) – a SQL node containing a date/time, for example a DateTimeField.
  Returns:	a SQL node representing a function call that will return the truncated date part.

  Provides a compatible interface for truncating a datetime to the given resolution.

• random()

  Returns:	a SQL node representing a function call that returns a random value.

  A compatible interface for calling the appropriate random number generation function provided by the database. For Postgres and Sqlite, this is equivalent to fn.random(), for MySQL fn.rand().

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 lock type)
• For even more features, see SqliteExtDatabase.

Example of initializing a database and configuring some PRAGMAs:

db = SqliteDatabase('my_app.db', pragmas=(
    ('cache_size', -16000),  # 16MB
    ('journal_mode', 'wal'),  # Use write-ahead-log journal mode.
))
# Alternatively, pragmas can be specified using a dictionary.
db = SqliteDatabase('my_app.db', pragmas={'journal_mode': 'wal'})

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

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

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

  Register a user-defined window function.

  Attention

  This feature requires SQLite >= 3.25.0 and pysqlite3 >= 0.2.0.

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

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

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

  Class decorator to register a user-defined window function. Window functions must define the following methods:

  • step(<params>) - receive values from a row and update state.
  • inverse(<params>) - inverse of step() for the given values.
  • value() - return the current value of the window function.
  • finalize() - return the final value of the window function.

  Example:

  @db.window_function('my_sum')
  class MySum(object):
      def __init__(self):
          self._value = 0
      def step(self, value):
          self._value += value
      def inverse(self, value):
          self._value -= value
      def value(self):
          return self._value
      def finalize(self):
          return self._value

• 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[, isolation_level=None]]])

Postgresql database implementation.

Additional optional keyword-parameters:

• set_time_zone(timezone)

  Parameters:	timezone (str) – timezone name, e.g. “US/Central”.
  Returns:	no return value.

  Set the timezone on the current connection. If no connection is open, then one will be opened.

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.

• is_alias()

  API for determining if a node, at any point, has been explicitly aliased by the user.

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. For example queries, see Common Table Expressions.

• 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([collation=None[, nulls=None]])

  Parameters:	collation (str) – Collation name to use for sorting. nulls (str) – Sort nulls (FIRST or LAST).
  Returns:	an ascending Ordering object for the column.

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

  Parameters:	collation (str) – Collation name to use for sorting. nulls (str) – Sort nulls (FIRST or LAST).
  Returns:	an descending Ordering object for the column.

• __invert__()

  Returns:	a Negated wrapper for the column.

class Column(source, name)

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. This may be useful if you are using database extensions that accept native Python data-types and you do not wish Peewee to impose any handling of the values.

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.

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

• collate([collation=None])

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

Asc(node[, collation=None[, nulls=None]])

Short-hand for instantiating an ascending Ordering object.

Desc(node[, collation=None[, nulls=None]])

Short-hand for instantiating an descending Ordering object.

class Expression(lhs, op, rhs[, flat=True])

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[, name=None])

Represent a CHECK constraint.

Warning

MySQL may not support a name parameter when inlining the constraint along with the column definition. The solution is to just put the named Check constraint in the model’s Meta.constraints list instead of in the field instances constraints=[...] list.

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

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[, exclude=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. frame_type (str) – Window.RANGE, Window.ROWS or Window.GROUPS. window (Window) – A Window instance. exclude – Frame exclusion, one of Window.CURRENT_ROW, Window.GROUP, Window.TIES or Window.NO_OTHERS.

  Note

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

  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.CURRENT_ROW,  # current row
                      end=Window.following()))  # unbounded following
           .order_by(Sample.id))

• filter(where)

  Parameters:	where – Expression for filtering aggregate.

  Add a FILTER (WHERE...) clause to an aggregate function. The where expression is evaluated to determine which rows are fed to the aggregate function. This SQL feature is supported for Postgres and SQLite.

• coerce([coerce=True])

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

  When coerce is True, the target data-type is inferred using several heuristics. Read the source for BaseModelCursorWrapper._initialize_columns method to see how this works.

• python_value([func=None])

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

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

  # Get user and a list of their tweet IDs. The tweet IDs are
  # returned as a comma-separated string by the db, so we'll split
  # the result string and convert the values to python ints.
  tweet_ids = (fn
               .GROUP_CONCAT(Tweet.id)
               .python_value(lambda idlist: [int(i) for i in idlist]))
  query = (User
           .select(User.username, tweet_ids.alias('tweet_ids'))
           .group_by(User.username))
  for user in query:
      print(user.username, user.tweet_ids)
  # e.g.,
  # huey [1, 4, 5, 7]
  # mickey [2, 3, 6]
  # zaizee []

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[, frame_type=None[, extends=None[, exclude=None[, alias=None]]]]]]]])

Represent a WINDOW clause.

Note

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

• RANGE

• ROWS

• GROUPS

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

• CURRENT_ROW

  Reference to current row for use in start/end clause or the frame exclusion parameter.

• NO_OTHERS

• GROUP

• TIES

  Specify the window frame exclusion parameter.

• static preceding([value=None])

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

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

• static following([value=None])

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

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

• as_rows()

• as_range()

• as_groups()

  Specify the frame type.

• extends([window=None])

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

• exclude([frame_exclusion=None])

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

• alias([alias=None])

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

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

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 value. Row-values are supported by most databases.

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

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 – Column(s) to use as target for conflict resolution.

• conflict_where(\expressions*)

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

• conflict_constraint(constraint)

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

class EXCLUDED

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

Example:

class KV(Model):
    key = CharField(unique=True)
    value = IntegerField()
# Create one row.
KV.create(key='k1', value=1)
# Demonstrate usage of EXCLUDED.
# Here we will attempt to insert a new value for a given key. If that
# key already exists, then we will update its value with the *sum* of its
# original value and the value we attempted to insert -- provided that
# the new value is larger than the original value.
query = (KV.insert(key='k1', value=10)
         .on_conflict(conflict_target=[KV.key],
                      update={KV.value: KV.value + EXCLUDED.value},
                      where=(EXCLUDED.value > KV.value)))
# Executing the above query will result in the following data being
# present in the "kv" table:
# (key='k1', value=11)
query.execute()
# If we attempted to execute the query *again*, then nothing would be
# updated, as the new value (10) is now less than the value in the
# original row (11).

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. For examples of common-table expressions, see Common Table Expressions.

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

• orwhere(\expressions*)

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

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

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

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

  Parameters:	name (str) – Alias for common table expression. recursive (bool) – Will this be a recursive CTE? columns (list) – List of column names (as strings).

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

  class Category(Model):
      name = TextField()
      parent = ForeignKeyField('self', backref='children', null=True)
  # The base case of our recursive CTE will be categories that are at
  # the root level -- in other words, categories without parents.
  roots = (Category
           .select(Category.name, Value(0).alias('level'))
           .where(Category.parent.is_null())
           .cte(name='roots', recursive=True))
  # The recursive term will select the category name and increment
  # the depth, joining on the base term so that the recursive term
  # consists of all children of the base category.
  RTerm = Category.alias()
  recursive = (RTerm
               .select(RTerm.name, (roots.c.level + 1).alias('level'))
               .join(roots, on=(RTerm.parent == roots.c.id)))
  # Express <base term> UNION ALL <recursive term>.
  cte = roots.union_all(recursive)
  # Select name and level from the recursive CTE.
  query = (cte
           .select_from(cte.c.name, cte.c.level)
           .order_by(cte.c.name))
  for category in query:
      print(category.name, category.level)

  For more examples of CTEs, see Common Table Expressions.

• select_from(\columns*)

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

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

  SELECT "u"."owner", COUNT("u"."id") AS "ct"
  FROM (
      SELECT "id", "owner", ... FROM "cars"
      UNION
      SELECT "id", "owner", ... FROM "motorcycles"
      UNION
      SELECT "id", "owner", ... FROM "boats") AS "u"
  GROUP BY "u"."owner"

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

  Example peewee code:

  class Car(Model):
      owner = ForeignKeyField(Owner, backref='cars')
      # ... car-specific fields, etc ...
  class Motorcycle(Model):
      owner = ForeignKeyField(Owner, backref='motorcycles')
      # ... motorcycle-specific fields, etc ...
  class Boat(Model):
      owner = ForeignKeyField(Owner, backref='boats')
      # ... boat-specific fields, etc ...
  cars = Car.select(Car.owner)
  motorcycles = Motorcycle.select(Motorcycle.owner)
  boats = Boat.select(Boat.owner)
  union = cars | motorcycles | boats
  query = (union
           .select_from(union.c.owner, fn.COUNT(union.c.id))
           .group_by(union.c.owner))

• 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[, for_update_of=None[, for_update_nowait=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[, of=None[, nowait=None]]])

  Parameters:	for_update – Either a boolean or a string indicating the desired expression, e.g. “FOR SHARE”. of – One or more models to restrict locking to. nowait (bool) – Specify NOWAIT option when locking.

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