cysqlite Extension¶

SQLite database implementation using cysqlite as the driver. The cysqlite extension also works with the following SQLite extensions, which can be imported from playhouse.cysqlite_ext:

RowIDField
DocIDField
AutoIncrementField
ISODateTimeField
JSONField and JSONPath
JSONBField and JSONBPath
SearchField
VirtualModel
FTSModel, providing full-text search.
FTS5Model, providing full-text search using FTS5.

class CySqliteDatabase(database, pragmas=None, timeout=5, rank_functions=True, regexp_function=True, **kwargs)¶

Parameters

pragmas (list) – A list of 2-tuples containing pragma key and value to set every time a connection is opened.
timeout – Set the busy-timeout on the SQLite driver (in seconds).
rank_functions (bool) – Make search result ranking functions available.
regexp_function (bool) – Make the REGEXP function available.

See also

CySqliteDatabase extends SqliteDatabase and inherits all methods for declaring user-defined functions, aggregates, window functions, collations, pragmas, etc.

Example:

db = CySqliteDatabase('app.db', pragmas={'journal_mode': 'wal'})

table_function(name=None)¶

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

See cysqlite docs for details on TableFunction API.

Example:

from cysqlite 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_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.

on_commit(fn)¶

Parameters: fn – callable or None to clear the current hook.

Register a callback to be executed whenever a transaction is committed on the current connection. The callback accepts no parameters and the return value is ignored.

However, if the callback raises a ValueError, the transaction will be aborted and rolled-back.

Example:

db = CySqliteDatabase(':memory:')

@db.on_commit
def on_commit():
    logger.info('COMMITing changes')

on_rollback(fn)¶

Parameters: fn – callable or None to clear the current hook.

Register a callback to be executed whenever a transaction is rolled back on the current connection. The callback accepts no parameters and the return value is ignored.

Example:

@db.on_rollback
def on_rollback():
    logger.info('Rolling back changes')

on_update(fn)¶

Parameters: fn – callable or None to clear the current hook.

Register a callback to be executed whenever the database is written to (via an UPDATE, INSERT or DELETE query). The callback should accept the following parameters:

query - the type of query, either INSERT, UPDATE or DELETE.
database name - the default database is named main.
table name - name of table being modified.
rowid - the rowid of the row being modified.

The callback’s return value is ignored.

Example:

db = CySqliteDatabase(':memory:')

@db.on_update
def on_update(query_type, db, table, rowid):
    # e.g. INSERT row 3 into table users.
    logger.info('%s row %s into table %s', query_type, rowid, table)

authorizer(fn)¶

Parameters: fn – callable or None to clear the current authorizer.

Register an authorizer callback. Authorizer callbacks must accept 5 parameters, which vary depending on the operation being checked.

op: operation code, e.g. cysqlite.SQLITE_INSERT.
p1: operation-specific value, e.g. table name for SQLITE_INSERT.
p2: operation-specific value.
p3: database name, e.g. "main".
p4: inner-most trigger or view responsible for the access attempt if applicable, else None.

See sqlite authorizer documentation for description of authorizer codes and values for parameters p1 and p2.

The authorizer callback must return one of:

cysqlite.SQLITE_OK: allow operation.
cysqlite.SQLITE_IGNORE: allow statement compilation but prevent the operation from occuring.
cysqlite.SQLITE_DENY: prevent statement compilation.

More details can be found in the cysqlite docs.

trace(fn, mask=2):

Parameters

fn – callable or None to clear the current trace hook.
mask (int) – mask of what types of events to trace. Default value corresponds to SQLITE_TRACE_PROFILE.

Register a trace hook (sqlite3_trace_v2). Trace callback must accept 4 parameters, which vary depending on the operation being traced.

event: type of event, e.g. SQLITE_TRACE_PROFILE.
sid: memory address of statement (only SQLITE_TRACE_CLOSE), else -1.
sql: SQL string (only SQLITE_TRACE_STMT), else None.
ns: estimated number of nanoseconds the statement took to run (only SQLITE_TRACE_PROFILE), else -1.

Any return value from callback is ignored.

More details can be found in the cysqlite docs.

progress(fn, n=1)¶

Parameters

fn – callable or None to clear the current progress handler.
n (int) – approximate number of VM instructions to execute between calls to the progress handler.

Register a progress handler (sqlite3_progress_handler). Callback takes no arguments and returns 0 to allow progress to continue or any non-zero value to interrupt progress.

More details can be found in the cysqlite docs.

autocommit¶

Property which returns a boolean indicating if autocommit is enabled. By default, this value will be True except when inside a transaction (or atomic() block).

Example:

>>> db = CySqliteDatabase(':memory:')
>>> db.autocommit
True
>>> with db.atomic():
...     print(db.autocommit)
...
False
>>> db.autocommit
True

backup(destination, pages=None, name=None, progress=None)¶

Parameters

destination (CySqliteDatabase) – Database object to serve as destination for the backup.
pages (int) – Number of pages per iteration. Default value of -1 indicates all pages should be backed-up in a single step.
name (str) – Name of source database (may differ if you used ATTACH DATABASE to load multiple databases). Defaults to “main”.
progress – Progress callback, called with three parameters: the number of pages remaining, the total page count, and whether the backup is complete.

Example:

master = CySqliteDatabase('master.db')
replica = CySqliteDatabase('replica.db')

# Backup the contents of master to replica.
master.backup(replica)

backup_to_file(filename, pages, name, progress)¶

Parameters

filename – Filename to store the database backup.
pages (int) – Number of pages per iteration. Default value of -1 indicates all pages should be backed-up in a single step.
name (str) – Name of source database (may differ if you used ATTACH DATABASE to load multiple databases). Defaults to “main”.
progress – Progress callback, called with three parameters: the number of pages remaining, the total page count, and whether the backup is complete.

Backup the current database to a file. The backed-up data is not a database dump, but an actual SQLite database file.

Example:

db = CySqliteDatabase('app.db')

def nightly_backup():
    filename = 'backup-%s.db' % (datetime.date.today())
    db.backup_to_file(filename)

blob_open(table, column, rowid, read_only=False)¶

Parameters

table (str) – Name of table containing data.
column (str) – Name of column containing data.
rowid (int) – ID of row to retrieve.
read_only (bool) – Open the blob for reading only.
dbname (str) – Database name (e.g. if multiple databases attached).

Returns

cysqlite.Blob instance which provides efficient access to the underlying binary data.

Return type

cysqlite.Blob

See cysqlite documentation for more details.

Example:

class Image(Model):
    filename = TextField()
    data = BlobField()

buf_size = 1024 * 1024 * 8  # Allocate 8MB for storing file.
rowid = Image.insert({Image.filename: 'thefile.jpg',
                      Image.data: ZeroBlob(buf_size)}).execute()

# Open the blob, returning a file-like object.
blob = db.blob_open('image', 'data', rowid)

# Write some data to the blob.
blob.write(image_data)
img_size = blob.tell()

# Read the data back out of the blob.
blob.seek(0)
image_data = blob.read(img_size)

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, deterministic=None)¶

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.
deterministic (bool) – Whether the function is deterministic for a given input (this is required to use the function in an index). Requires Sqlite 3.20 or newer, and sqlite3 driver support (added to stdlib in Python 3.8).

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, deterministic=None)¶

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.
deterministic (bool) – Whether the function is deterministic for a given input (this is required to use the function in an index). Requires Sqlite 3.20 or newer, and sqlite3 driver support (added to stdlib in Python 3.8).

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.

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

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.

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.

atomic(lock_type=None)¶

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

Create an atomic context-manager, optionally using the specified locking strategy (if unspecified, DEFERRED is used).

Note

Lock type only applies to the outermost atomic() block.

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

cysqlite Extension¶

peewee

Navigation

Related Topics