SQLite Extensions
The default SqliteDatabase
already includes many SQLite-specific
features:
The playhouse.sqlite_ext
includes even more SQLite features, including:
Support for online backups using backup API:
backup_to_file()
Additional helpers, including bloom filter, more.
Getting started
To get started with the features described in this document, you will want to
use the SqliteExtDatabase
class from the playhouse.sqlite_ext
module. Furthermore, some features require the playhouse._sqlite_ext
C
extension – these features will be noted in the documentation.
Instantiating a SqliteExtDatabase
:
from playhouse.sqlite_ext import SqliteExtDatabase
db = SqliteExtDatabase('my_app.db', pragmas=(
('cache_size', -1024 * 64), # 64MB page-cache.
('journal_mode', 'wal'), # Use WAL-mode (you should always use this!).
('foreign_keys', 1))) # Enforce foreign-key constraints.
APIs
- class SqliteExtDatabase(database[, pragmas=None[, timeout=5[, c_extensions=None[, rank_functions=True[, hash_functions=False[, regexp_function=False[, bloomfilter=False]]]]]]])
- 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).
c_extensions (bool) – Declare that C extension speedups must/must-not be used. If set to
True
and the extension module is not available, will raise anImproperlyConfigured
exception.rank_functions (bool) – Make search result ranking functions available.
hash_functions (bool) – Make hashing functions available (md5, sha1, etc).
regexp_function (bool) – Make the REGEXP function available.
bloomfilter (bool) – Make the bloom filter available.
Extends
SqliteDatabase
and inherits methods for declaring user-defined functions, pragmas, etc.
- class CSqliteExtDatabase(database[, pragmas=None[, timeout=5[, c_extensions=None[, rank_functions=True[, hash_functions=False[, regexp_function=False[, bloomfilter=False[, replace_busy_handler=False]]]]]]]])
- 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).
c_extensions (bool) – Declare that C extension speedups must/must-not be used. If set to
True
and the extension module is not available, will raise anImproperlyConfigured
exception.rank_functions (bool) – Make search result ranking functions available.
hash_functions (bool) – Make hashing functions available (md5, sha1, etc).
regexp_function (bool) – Make the REGEXP function available.
bloomfilter (bool) – Make the bloom filter available.
replace_busy_handler (bool) – Use a smarter busy-handler implementation.
Extends
SqliteExtDatabase
and requires that theplayhouse._sqlite_ext
extension module be available.- on_commit(fn)
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 = CSqliteExtDatabase(':memory:') @db.on_commit def on_commit(): logger.info('COMMITing changes')
- on_rollback(fn)
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)
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 = CSqliteExtDatabase(':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)
- changes()
Return the number of rows modified in the currently-open transaction.
- autocommit
Property which returns a boolean indicating if autocommit is enabled. By default, this value will be
True
except when inside a transaction (oratomic()
block).Example:
>>> db = CSqliteExtDatabase(':memory:') >>> db.autocommit True >>> with db.atomic(): ... print(db.autocommit) ... False >>> db.autocommit True
- backup(destination[, pages=None, name=None, progress=None])
- Parameters
destination (SqliteDatabase) – 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 = CSqliteExtDatabase('master.db') replica = CSqliteExtDatabase('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 = CSqliteExtDatabase('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.
- Returns
Blob
instance which provides efficient access to the underlying binary data.- Return type
See
Blob
andZeroBlob
for more information.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)
- class RowIDField
Primary-key field that corresponds to the SQLite
rowid
field. For more information, see the SQLite documentation on rowid tables..Example:
class Note(Model): rowid = RowIDField() # Will be primary key. content = TextField() timestamp = TimestampField()
- class DocIDField
Subclass of
RowIDField
for use on virtual tables that specifically use the convention ofdocid
for the primary key. As far as I know this only pertains to tables using the FTS3 and FTS4 full-text search extensions.Attention
In FTS3 and FTS4, “docid” is simply an alias for “rowid”. To reduce confusion, it’s probably best to just always use
RowIDField
and never useDocIDField
.class NoteIndex(FTSModel): docid = DocIDField() # "docid" is used as an alias for "rowid". content = SearchField() class Meta: database = db
- class AutoIncrementField
SQLite, by default, may reuse primary key values after rows are deleted. To ensure that the primary key is always monotonically increasing, regardless of deletions, you should use
AutoIncrementField
. There is a small performance cost for this feature. For more information, see the SQLite docs on autoincrement.
- class JSONField(json_dumps=None, json_loads=None, ...)
Field class suitable for storing JSON data, with special methods designed to work with the json1 extension.
SQLite 3.9.0 added JSON support in the form of an extension library. The SQLite json1 extension provides a number of helper functions for working with JSON data. These APIs are exposed as methods of a special field-type,
JSONField
.To access or modify specific object keys or array indexes in a JSON structure, you can treat the
JSONField
as if it were a dictionary/list.- Parameters
json_dumps – (optional) function for serializing data to JSON strings. If not provided, will use the stdlib
json.dumps
.json_loads – (optional) function for de-serializing JSON to Python objects. If not provided, will use the stdlib
json.loads
.
Note
To customize the JSON serialization or de-serialization, you can specify a custom
json_dumps
andjson_loads
callables. These functions should accept a single paramter: the object to serialize, and the JSON string, respectively. To modify the parameters of the stdlib JSON functions, you can usefunctools.partial
:# Do not escape unicode code-points. my_json_dumps = functools.partial(json.dumps, ensure_ascii=False) class SomeModel(Model): # Specify our custom serialization function. json_data = JSONField(json_dumps=my_json_dumps)
Let’s look at some examples of using the SQLite json1 extension with Peewee. Here we’ll prepare a database and a simple model for testing the json1 extension:
>>> from playhouse.sqlite_ext import * >>> db = SqliteExtDatabase(':memory:') >>> class KV(Model): ... key = TextField() ... value = JSONField() ... class Meta: ... database = db ... >>> KV.create_table()
Storing data works as you might expect. There’s no need to serialize dictionaries or lists as JSON, as this is done automatically by Peewee:
>>> KV.create(key='a', value={'k1': 'v1'}) <KV: 1> >>> KV.get(KV.key == 'a').value {'k1': 'v1'}
We can access specific parts of the JSON data using dictionary lookups:
>>> KV.get(KV.value['k1'] == 'v1').key 'a'
It’s possible to update a JSON value in-place using the
update()
method. Note that “k1=v1” is preserved:>>> KV.update(value=KV.value.update({'k2': 'v2', 'k3': 'v3'})).execute() 1 >>> KV.get(KV.key == 'a').value {'k1': 'v1', 'k2': 'v2', 'k3': 'v3'}
We can also update existing data atomically, or remove keys by setting their value to
None
. In the following example, we’ll update the value of “k1” and remove “k3” (“k2” will not be modified):>>> KV.update(value=KV.value.update({'k1': 'v1-x', 'k3': None})).execute() 1 >>> KV.get(KV.key == 'a').value {'k1': 'v1-x', 'k2': 'v2'}
We can also set individual parts of the JSON data using the
set()
method:>>> KV.update(value=KV.value['k1'].set('v1')).execute() 1 >>> KV.get(KV.key == 'a').value {'k1': 'v1', 'k2': 'v2'}
The
set()
method can also be used with objects, in addition to scalar values:>>> KV.update(value=KV.value['k2'].set({'x2': 'y2'})).execute() 1 >>> KV.get(KV.key == 'a').value {'k1': 'v1', 'k2': {'x2': 'y2'}}
Individual parts of the JSON data can be removed atomically as well, using
remove()
:>>> KV.update(value=KV.value['k2'].remove()).execute() 1 >>> KV.get(KV.key == 'a').value {'k1': 'v1'}
We can also get the type of value stored at a specific location in the JSON data using the
json_type()
method:>>> KV.select(KV.value.json_type(), KV.value['k1'].json_type()).tuples()[:] [('object', 'text')]
Let’s add a nested value and then see how to iterate through it’s contents recursively using the
tree()
method:>>> KV.create(key='b', value={'x1': {'y1': 'z1', 'y2': 'z2'}, 'x2': [1, 2]}) <KV: 2> >>> tree = KV.value.tree().alias('tree') >>> query = KV.select(KV.key, tree.c.fullkey, tree.c.value).from_(KV, tree) >>> query.tuples()[:] [('a', '$', {'k1': 'v1'}), ('a', '$.k1', 'v1'), ('b', '$', {'x1': {'y1': 'z1', 'y2': 'z2'}, 'x2': [1, 2]}), ('b', '$.x2', [1, 2]), ('b', '$.x2[0]', 1), ('b', '$.x2[1]', 2), ('b', '$.x1', {'y1': 'z1', 'y2': 'z2'}), ('b', '$.x1.y1', 'z1'), ('b', '$.x1.y2', 'z2')]
The
tree()
andchildren()
methods are powerful. For more information on how to utilize them, see the json1 extension documentation.Also note, that
JSONField
lookups can be chained:>>> query = KV.select().where(KV.value['x1']['y1'] == 'z1') >>> for obj in query: ... print(obj.key, obj.value) ... 'b', {'x1': {'y1': 'z1', 'y2': 'z2'}, 'x2': [1, 2]}
For more information, refer to the sqlite json1 documentation.
- __getitem__(item)
- Parameters
item – Access a specific key or array index in the JSON data.
- Returns
a special object exposing access to the JSON data.
- Return type
Access a specific key or array index in the JSON data. Returns a
JSONPath
object, which exposes convenient methods for reading or modifying a particular part of a JSON object.Example:
# If metadata contains {"tags": ["list", "of", "tags"]}, we can # extract the first tag in this way: Post.select(Post, Post.metadata['tags'][0].alias('first_tag'))
For more examples see the
JSONPath
API documentation.
- extract(*paths)
- Parameters
paths – One or more JSON paths to extract.
Extract the value(s) at the specified JSON paths. If multiple paths are provided, then Sqlite will return the values as a
list
.
- extract_json(path)
- Parameters
path (str) – JSON path
Extract the value at the specified path as a JSON data-type. This corresponds to the
->
operator added in Sqlite 3.38.
- extract_text(path)
- Parameters
path (str) – JSON path
Extract the value at the specified path as a SQL data-type. This corresponds to the
->>
operator added in Sqlite 3.38.
- set(value[, as_json=None])
- Parameters
value – a scalar value, list, or dictionary.
as_json (bool) – force the value to be treated as JSON, in which case it will be serialized as JSON in Python beforehand. By default, lists and dictionaries are treated as JSON to be serialized, while strings and integers are passed as-is.
Set the value stored in a
JSONField
.Uses the json_set() function from the json1 extension.
- replace(value[, as_json=None])
- Parameters
value – a scalar value, list, or dictionary.
as_json (bool) – force the value to be treated as JSON, in which case it will be serialized as JSON in Python beforehand. By default, lists and dictionaries are treated as JSON to be serialized, while strings and integers are passed as-is.
Replace the existing value stored in a
JSONField
.Uses the json_replace() function from the json1 extension.
- insert(value[, as_json=None])
- Parameters
value – a scalar value, list, or dictionary.
as_json (bool) – force the value to be treated as JSON, in which case it will be serialized as JSON in Python beforehand. By default, lists and dictionaries are treated as JSON to be serialized, while strings and integers are passed as-is.
Insert value into
JSONField
.Uses the json_insert() function from the json1 extension.
- append(value[, as_json=None])
- Parameters
value – a scalar value, list, or dictionary.
as_json (bool) – force the value to be treated as JSON, in which case it will be serialized as JSON in Python beforehand. By default, lists and dictionaries are treated as JSON to be serialized, while strings and integers are passed as-is.
Append to the array stored in a
JSONField
.Uses the json_set() function from the json1 extension.
- update(data)
- Parameters
data – a scalar value, list or dictionary to merge with the data currently stored in a
JSONField
. To remove a particular key, set that key toNone
in the updated data.
Merge new data into the JSON value using the RFC-7396 MergePatch algorithm to apply a patch (
data
parameter) against the column data. MergePatch can add, modify, or delete elements of a JSON object, which meansupdate()
is a generalized replacement for bothset()
andremove()
. MergePatch treats JSON array objects as atomic, soupdate()
cannot append to an array, nor modify individual elements of an array.For more information as well as examples, see the SQLite json_patch() function documentation.
- remove()
Remove the data stored in the
JSONField
.Uses the json_remove function from the json1 extension.
- json_type()
Return a string identifying the type of value stored in the column.
The type returned will be one of:
object
array
integer
real
true
false
text
null <– the string “null” means an actual NULL value
NULL <– an actual NULL value means the path was not found
Uses the json_type function from the json1 extension.
- length()
Return the length of the array stored in the column.
Uses the json_array_length function from the json1 extension.
- children()
The
children
function corresponds tojson_each
, a table-valued function that walks the JSON value provided and returns the immediate children of the top-level array or object. If a path is specified, then that path is treated as the top-most element.The rows returned by calls to
children()
have the following attributes:key
: the key of the current element relative to its parent.value
: the value of the current element.type
: one of the data-types (seejson_type()
).atom
: the scalar value for primitive types,NULL
for arrays and objects.id
: a unique ID referencing the current node in the tree.parent
: the ID of the containing node.fullkey
: the full path describing the current element.path
: the path to the container of the current row.
Internally this method uses the json_each (documentation link) function from the json1 extension.
Example usage (compare to
tree()
method):class KeyData(Model): key = TextField() data = JSONField() KeyData.create(key='a', data={'k1': 'v1', 'x1': {'y1': 'z1'}}) KeyData.create(key='b', data={'x1': {'y1': 'z1', 'y2': 'z2'}}) # We will query the KeyData model for the key and all the # top-level keys and values in it's data field. kd = KeyData.data.children().alias('children') query = (KeyData .select(kd.c.key, kd.c.value, kd.c.fullkey) .from_(KeyData, kd) .order_by(kd.c.key) .tuples()) print(query[:]) # PRINTS: [('a', 'k1', 'v1', '$.k1'), ('a', 'x1', '{"y1":"z1"}', '$.x1'), ('b', 'x1', '{"y1":"z1","y2":"z2"}', '$.x1')]
- tree()
The
tree
function corresponds tojson_tree
, a table-valued function that recursively walks the JSON value provided and returns information about the keys at each level. If a path is specified, then that path is treated as the top-most element.The rows returned by calls to
tree()
have the same attributes as rows returned by calls tochildren()
:key
: the key of the current element relative to its parent.value
: the value of the current element.type
: one of the data-types (seejson_type()
).atom
: the scalar value for primitive types,NULL
for arrays and objects.id
: a unique ID referencing the current node in the tree.parent
: the ID of the containing node.fullkey
: the full path describing the current element.path
: the path to the container of the current row.
Internally this method uses the json_tree (documentation link) function from the json1 extension.
Example usage:
class KeyData(Model): key = TextField() data = JSONField() KeyData.create(key='a', data={'k1': 'v1', 'x1': {'y1': 'z1'}}) KeyData.create(key='b', data={'x1': {'y1': 'z1', 'y2': 'z2'}}) # We will query the KeyData model for the key and all the # keys and values in it's data field, recursively. kd = KeyData.data.tree().alias('tree') query = (KeyData .select(kd.c.key, kd.c.value, kd.c.fullkey) .from_(KeyData, kd) .order_by(kd.c.key) .tuples()) print(query[:]) # PRINTS: [('a', None, '{"k1":"v1","x1":{"y1":"z1"}}', '$'), ('b', None, '{"x1":{"y1":"z1","y2":"z2"}}', '$'), ('a', 'k1', 'v1', '$.k1'), ('a', 'x1', '{"y1":"z1"}', '$.x1'), ('b', 'x1', '{"y1":"z1","y2":"z2"}', '$.x1'), ('a', 'y1', 'z1', '$.x1.y1'), ('b', 'y1', 'z1', '$.x1.y1'), ('b', 'y2', 'z2', '$.x1.y2')]
- class JSONPath(field[, path=None])
- Parameters
field (JSONField) – the field object we intend to access.
path (tuple) – Components comprising the JSON path.
A convenient, Pythonic way of representing JSON paths for use with
JSONField
.The
JSONPath
object implements__getitem__
, accumulating path components, which it can turn into the corresponding json-path expression.- __getitem__(item)
- Parameters
item – Access a sub-key key or array index.
- Returns
a
JSONPath
representing the new path.
Access a sub-key or array index in the JSON data. Returns a
JSONPath
object, which exposes convenient methods for reading or modifying a particular part of a JSON object.Example:
# If metadata contains {"tags": ["list", "of", "tags"]}, we can # extract the first tag in this way: first_tag = Post.metadata['tags'][0] query = (Post .select(Post, first_tag.alias('first_tag')) .order_by(first_tag))
- set(value[, as_json=None])
- Parameters
value – a scalar value, list, or dictionary.
as_json (bool) – force the value to be treated as JSON, in which case it will be serialized as JSON in Python beforehand. By default, lists and dictionaries are treated as JSON to be serialized, while strings and integers are passed as-is.
Set the value at the given location in the JSON data.
Uses the json_set() function from the json1 extension.
- replace(value[, as_json=None])
- Parameters
value – a scalar value, list, or dictionary.
as_json (bool) – force the value to be treated as JSON, in which case it will be serialized as JSON in Python beforehand. By default, lists and dictionaries are treated as JSON to be serialized, while strings and integers are passed as-is.
Replace the existing value at the given location in the JSON data.
Uses the json_replace() function from the json1 extension.
- insert(value[, as_json=None])
- Parameters
value – a scalar value, list, or dictionary.
as_json (bool) – force the value to be treated as JSON, in which case it will be serialized as JSON in Python beforehand. By default, lists and dictionaries are treated as JSON to be serialized, while strings and integers are passed as-is.
Insert a new value at the given location in the JSON data.
Uses the json_insert() function from the json1 extension.
- append(value[, as_json=None])
- Parameters
value – a scalar value, list, or dictionary.
as_json (bool) – force the value to be treated as JSON, in which case it will be serialized as JSON in Python beforehand. By default, lists and dictionaries are treated as JSON to be serialized, while strings and integers are passed as-is.
Append to the array stored at the given location in the JSON data.
Uses the json_set() function from the json1 extension.
- update(data)
- Parameters
data – a scalar value, list or dictionary to merge with the data at the given location in the JSON data. To remove a particular key, set that key to
None
in the updated data.
Merge new data into the JSON value using the RFC-7396 MergePatch algorithm to apply a patch (
data
parameter) against the column data. MergePatch can add, modify, or delete elements of a JSON object, which meansupdate()
is a generalized replacement for bothset()
andremove()
. MergePatch treats JSON array objects as atomic, soupdate()
cannot append to an array, nor modify individual elements of an array.For more information as well as examples, see the SQLite json_patch() function documentation.
- remove()
Remove the data stored in at the given location in the JSON data.
Uses the json_type function from the json1 extension.
- json_type()
Return a string identifying the type of value stored at the given location in the JSON data.
The type returned will be one of:
object
array
integer
real
true
false
text
null <– the string “null” means an actual NULL value
NULL <– an actual NULL value means the path was not found
Uses the json_type function from the json1 extension.
- length()
Return the length of the array stored at the given location in the JSON data.
Uses the json_array_length function from the json1 extension.
- children()
Table-valued function that exposes the direct descendants of a JSON object at the given location. See also
JSONField.children()
.
- tree()
Table-valued function that exposes all descendants, recursively, of a JSON object at the given location. See also
JSONField.tree()
.
- class JSONBField(json_dumps=None, json_loads=None, ...)
Field-class suitable for use with data stored on-disk in
jsonb
format (available starting Sqlite 3.45.0). This field-class should be used with care, as the data may be returned in it’s encoded format depending on how you query it. For example:>>> KV.create(key='a', value={'k1': 'v1'}) <KV: 1> >>> KV.get(KV.key == 'a').value b"l'k1'v1"
To get the JSON value, it is necessary to use
fn.json()
or the helperJSONBField.json()
method:>>> kv = KV.select(KV.value.json()).get() >>> kv.value {'k1': 'v1'}
- class SearchField([unindexed=False[, column_name=None]])
Field-class to be used for columns on models representing full-text search virtual tables. The full-text search extensions prohibit the specification of any typing or constraints on columns. This behavior is enforced by the
SearchField
, which raises an exception if any configuration is attempted that would be incompatible with the full-text search extensions.Example model for document search index (timestamp is stored in the table but it’s data is not searchable):
class DocumentIndex(FTSModel): title = SearchField() content = SearchField() tags = SearchField() timestamp = SearchField(unindexed=True)
- match(term)
- Parameters
term (str) – full-text search query/terms
- Returns
a
Expression
corresponding to theMATCH
operator.
Sqlite’s full-text search supports searching either the full table, including all indexed columns, or searching individual columns. The
match()
method can be used to restrict search to a single column:class SearchIndex(FTSModel): title = SearchField() body = SearchField() # Search *only* the title field and return results ordered by # relevance, using bm25. query = (SearchIndex .select(SearchIndex, SearchIndex.bm25().alias('score')) .where(SearchIndex.title.match('python')) .order_by(SearchIndex.bm25()))
To instead search all indexed columns, use the
FTSModel.match()
method:# Searches *both* the title and body and return results ordered by # relevance, using bm25. query = (SearchIndex .select(SearchIndex, SearchIndex.bm25().alias('score')) .where(SearchIndex.match('python')) .order_by(SearchIndex.bm25()))
- highlight(left, right)
- Parameters
left (str) – opening tag for highlight, e.g.
'<b>'
right (str) – closing tag for highlight, e.g.
'</b>'
When performing a search using the
MATCH
operator, FTS5 can return text highlighting matches in a given column.# Search for items matching string 'python' and return the title # highlighted with square brackets. query = (SearchIndex .search('python') .select(SearchIndex.title.highlight('[', ']').alias('hi'))) for result in query: print(result.hi) # For example, might print: # Learn [python] the hard way
- snippet(left, right, over_length='...', max_tokens=16)
- Parameters
left (str) – opening tag for highlight, e.g.
'<b>'
right (str) – closing tag for highlight, e.g.
'</b>'
over_length (str) – text to prepend or append when snippet exceeds the maximum number of tokens.
max_tokens (int) – max tokens returned, must be 1 - 64.
When performing a search using the
MATCH
operator, FTS5 can return text with a snippet containing the highlighted match in a given column.# Search for items matching string 'python' and return the title # highlighted with square brackets. query = (SearchIndex .search('python') .select(SearchIndex.title.snippet('[', ']').alias('snip'))) for result in query: print(result.snip)
- class VirtualModel
Model class designed to be used to represent virtual tables. The default metadata settings are slightly different, to match those frequently used by virtual tables.
Metadata options:
arguments
- arguments passed to the virtual table constructor.extension_module
- name of extension to use for virtual table.options
- a dictionary of settings to apply in virtual tableconstructor.
primary_key
- defaults toFalse
, indicating no primary key.
These all are combined in the following way:
CREATE VIRTUAL TABLE <table_name> USING <extension_module> ([prefix_arguments, ...] fields, ... [arguments, ...], [options...])
- class FTSModel
Subclass of
VirtualModel
to be used with the FTS3 and FTS4 full-text search extensions.FTSModel subclasses should be defined normally, however there are a couple caveats:
Unique constraints, not null constraints, check constraints and foreign keys are not supported.
Indexes on fields and multi-column indexes are ignored completely
Sqlite will treat all column types as
TEXT
(although you can store other data types, Sqlite will treat them as text).FTS models contain a
rowid
field which is automatically created and managed by SQLite (unless you choose to explicitly set it during model creation). Lookups on this column are fast and efficient.
Given these constraints, it is strongly recommended that all fields declared on an
FTSModel
subclass be instances ofSearchField
(though an exception is made for explicitly declaring aRowIDField
). UsingSearchField
will help prevent you accidentally creating invalid column constraints. If you wish to store metadata in the index but would not like it to be included in the full-text index, then specifyunindexed=True
when instantiating theSearchField
.The only exception to the above is for the
rowid
primary key, which can be declared usingRowIDField
. Lookups on therowid
are very efficient. If you are using FTS4 you can also useDocIDField
, which is an alias for the rowid (though there is no benefit to doing so).Because of the lack of secondary indexes, it usually makes sense to use the
rowid
primary key as a pointer to a row in a regular table. For example:class Document(Model): # Canonical source of data, stored in a regular table. author = ForeignKeyField(User, backref='documents') title = TextField(null=False, unique=True) content = TextField(null=False) timestamp = DateTimeField() class Meta: database = db class DocumentIndex(FTSModel): # Full-text search index. rowid = RowIDField() title = SearchField() content = SearchField() class Meta: database = db # Use the porter stemming algorithm to tokenize content. options = {'tokenize': 'porter'}
To store a document in the document index, we will
INSERT
a row into theDocumentIndex
table, manually setting therowid
so that it matches the primary-key of the correspondingDocument
:def store_document(document): DocumentIndex.insert({ DocumentIndex.rowid: document.id, DocumentIndex.title: document.title, DocumentIndex.content: document.content}).execute()
To perform a search and return ranked results, we can query the
Document
table and join on theDocumentIndex
. This join will be efficient because lookups on an FTSModel’srowid
field are fast:def search(phrase): # Query the search index and join the corresponding Document # object on each search result. return (Document .select() .join( DocumentIndex, on=(Document.id == DocumentIndex.rowid)) .where(DocumentIndex.match(phrase)) .order_by(DocumentIndex.bm25()))
Warning
All SQL queries on
FTSModel
classes will be full-table scans except full-text searches androwid
lookups.If the primary source of the content you are indexing exists in a separate table, you can save some disk space by instructing SQLite to not store an additional copy of the search index content. SQLite will still create the metadata and data-structures needed to perform searches on the content, but the content itself will not be stored in the search index.
To accomplish this, you can specify a table or column using the
content
option. The FTS4 documentation has more information.Here is a short example illustrating how to implement this with peewee:
class Blog(Model): title = TextField() pub_date = DateTimeField(default=datetime.datetime.now) content = TextField() # We want to search this. class Meta: database = db class BlogIndex(FTSModel): content = SearchField() class Meta: database = db options = {'content': Blog.content} # <-- specify data source. db.create_tables([Blog, BlogIndex]) # Now, we can manage content in the BlogIndex. To populate the # search index: BlogIndex.rebuild() # Optimize the index. BlogIndex.optimize()
The
content
option accepts either a singleField
or aModel
and can reduce the amount of storage used by the database file. However, content will need to be manually moved to/from the associatedFTSModel
.- classmethod match(term)
- Parameters
term – Search term or expression.
Generate a SQL expression representing a search for the given term or expression in the table. SQLite uses the
MATCH
operator to indicate a full-text search.Example:
# Search index for "search phrase" and return results ranked # by relevancy using the BM25 algorithm. query = (DocumentIndex .select() .where(DocumentIndex.match('search phrase')) .order_by(DocumentIndex.bm25())) for result in query: print('Result: %s' % result.title)
- classmethod search(term[, weights=None[, with_score=False[, score_alias='score'[, explicit_ordering=False]]]])
- Parameters
term (str) – Search term to use.
weights – A list of weights for the columns, ordered with respect to the column’s position in the table. Or, a dictionary keyed by the field or field name and mapped to a value.
with_score – Whether the score should be returned as part of the
SELECT
statement.score_alias (str) – Alias to use for the calculated rank score. This is the attribute you will use to access the score if
with_score=True
.explicit_ordering (bool) – Order using full SQL function to calculate rank, as opposed to simply referencing the score alias in the ORDER BY clause.
Shorthand way of searching for a term and sorting results by the quality of the match.
Note
This method uses a simplified algorithm for determining the relevance rank of results. For more sophisticated result ranking, use the
search_bm25()
method.# Simple search. docs = DocumentIndex.search('search term') for result in docs: print(result.title) # More complete example. docs = DocumentIndex.search( 'search term', weights={'title': 2.0, 'content': 1.0}, with_score=True, score_alias='search_score') for result in docs: print(result.title, result.search_score)
- classmethod search_bm25(term[, weights=None[, with_score=False[, score_alias='score'[, explicit_ordering=False]]]])
- Parameters
term (str) – Search term to use.
weights – A list of weights for the columns, ordered with respect to the column’s position in the table. Or, a dictionary keyed by the field or field name and mapped to a value.
with_score – Whether the score should be returned as part of the
SELECT
statement.score_alias (str) – Alias to use for the calculated rank score. This is the attribute you will use to access the score if
with_score=True
.explicit_ordering (bool) – Order using full SQL function to calculate rank, as opposed to simply referencing the score alias in the ORDER BY clause.
Shorthand way of searching for a term and sorting results by the quality of the match using the BM25 algorithm.
Attention
The BM25 ranking algorithm is only available for FTS4. If you are using FTS3, use the
search()
method instead.
- classmethod search_bm25f(term[, weights=None[, with_score=False[, score_alias='score'[, explicit_ordering=False]]]])
Same as
FTSModel.search_bm25()
, but using the BM25f variant of the BM25 ranking algorithm.
- classmethod search_lucene(term[, weights=None[, with_score=False[, score_alias='score'[, explicit_ordering=False]]]])
Same as
FTSModel.search_bm25()
, but using the result ranking algorithm from the Lucene search engine.
- classmethod rank([col1_weight, col2_weight...coln_weight])
- Parameters
col_weight (float) – (Optional) weight to give to the ith column of the model. By default all columns have a weight of
1.0
.
Generate an expression that will calculate and return the quality of the search match. This
rank
can be used to sort the search results. A higher rank score indicates a better match.The
rank
function accepts optional parameters that allow you to specify weights for the various columns. If no weights are specified, all columns are considered of equal importance.Note
The algorithm used by
rank()
is simple and relatively quick. For more sophisticated result ranking, use:query = (DocumentIndex .select( DocumentIndex, DocumentIndex.rank().alias('score')) .where(DocumentIndex.match('search phrase')) .order_by(DocumentIndex.rank())) for search_result in query: print(search_result.title, search_result.score)
- classmethod bm25([col1_weight, col2_weight...coln_weight])
- Parameters
col_weight (float) – (Optional) weight to give to the ith column of the model. By default all columns have a weight of
1.0
.
Generate an expression that will calculate and return the quality of the search match using the BM25 algorithm. This value can be used to sort the search results, with higher scores corresponding to better matches.
Like
rank()
,bm25
function accepts optional parameters that allow you to specify weights for the various columns. If no weights are specified, all columns are considered of equal importance.Attention
The BM25 result ranking algorithm requires FTS4. If you are using FTS3, use
rank()
instead.query = (DocumentIndex .select( DocumentIndex, DocumentIndex.bm25().alias('score')) .where(DocumentIndex.match('search phrase')) .order_by(DocumentIndex.bm25())) for search_result in query: print(search_result.title, search_result.score)
Note
The above code example is equivalent to calling the
search_bm25()
method:query = DocumentIndex.search_bm25('search phrase', with_score=True) for search_result in query: print(search_result.title, search_result.score)
- classmethod bm25f([col1_weight, col2_weight...coln_weight])
Identical to
bm25()
, except that it uses the BM25f variant of the BM25 ranking algorithm.
- classmethod lucene([col1_weight, col2_weight...coln_weight])
Identical to
bm25()
, except that it uses the Lucene search result ranking algorithm.
- classmethod rebuild()
Rebuild the search index – this only works when the
content
option was specified during table creation.
- classmethod optimize()
Optimize the search index.
- class FTS5Model
Subclass of
VirtualModel
to be used with the FTS5 full-text search extensions.FTS5Model subclasses should be defined normally, however there are a couple caveats:
FTS5 explicitly disallows specification of any constraints, data-type or indexes on columns. For that reason, all columns must be instances of
SearchField
.FTS5 models contain a
rowid
field which is automatically created and managed by SQLite (unless you choose to explicitly set it during model creation). Lookups on this column are fast and efficient.Indexes on fields and multi-column indexes are not supported.
The
FTS5
extension comes with a built-in implementation of the BM25 ranking function. Therefore, thesearch
andsearch_bm25
methods have been overridden to use the builtin ranking functions rather than user-defined functions.- classmethod fts5_installed()
Return a boolean indicating whether the FTS5 extension is installed. If it is not installed, an attempt will be made to load the extension.
- classmethod search(term[, weights=None[, with_score=False[, score_alias='score']]])
- Parameters
term (str) – Search term to use.
weights – A list of weights for the columns, ordered with respect to the column’s position in the table. Or, a dictionary keyed by the field or field name and mapped to a value.
with_score – Whether the score should be returned as part of the
SELECT
statement.score_alias (str) – Alias to use for the calculated rank score. This is the attribute you will use to access the score if
with_score=True
.explicit_ordering (bool) – Order using full SQL function to calculate rank, as opposed to simply referencing the score alias in the ORDER BY clause.
Shorthand way of searching for a term and sorting results by the quality of the match. The
FTS5
extension provides a built-in implementation of the BM25 algorithm, which is used to rank the results by relevance.Higher scores correspond to better matches.
# Simple search. docs = DocumentIndex.search('search term') for result in docs: print(result.title) # More complete example. docs = DocumentIndex.search( 'search term', weights={'title': 2.0, 'content': 1.0}, with_score=True, score_alias='search_score') for result in docs: print(result.title, result.search_score)
- classmethod search_bm25(term[, weights=None[, with_score=False[, score_alias='score']]])
With FTS5,
search_bm25()
is identical to thesearch()
method.
- classmethod rank([col1_weight, col2_weight...coln_weight])
- Parameters
col_weight (float) – (Optional) weight to give to the ith column of the model. By default all columns have a weight of
1.0
.
Generate an expression that will calculate and return the quality of the search match using the BM25 algorithm. This value can be used to sort the search results, with higher scores corresponding to better matches.
The
rank()
function accepts optional parameters that allow you to specify weights for the various columns. If no weights are specified, all columns are considered of equal importance.query = (DocumentIndex .select( DocumentIndex, DocumentIndex.rank().alias('score')) .where(DocumentIndex.match('search phrase')) .order_by(DocumentIndex.rank())) for search_result in query: print(search_result.title, search_result.score)
Note
The above code example is equivalent to calling the
search()
method:query = DocumentIndex.search('search phrase', with_score=True) for search_result in query: print(search_result.title, search_result.score)
- classmethod bm25([col1_weight, col2_weight...coln_weight])
Because FTS5 provides built-in support for BM25, the
bm25()
method is identical to therank()
method.
- classmethod VocabModel([table_type='row'|'col'|'instance'[, table_name=None]])
- Parameters
table_type (str) – Either ‘row’, ‘col’ or ‘instance’.
table_name – Name for the vocab table. If not specified, will be “fts5tablename_v”.
Generate a model class suitable for accessing the vocab table corresponding to FTS5 search index.
- class TableFunction
Implement a user-defined table-valued function. Unlike a simple scalar or aggregate function, which returns a single scalar value, a table-valued function can return any number of rows of tabular data.
Simple example:
from playhouse.sqlite_ext import TableFunction class Series(TableFunction): # Name of columns in each row of generated data. columns = ['value'] # Name of parameters the function may be called with. 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,) # Register the table-function with our database, which ensures it # is declared whenever a connection is opened. db.table_function('series')(Series) # Usage: cursor = db.execute_sql('SELECT * FROM series(?, ?, ?)', (0, 5, 2)) for value, in cursor: print(value)
Note
A
TableFunction
must be registered with a database connection before it can be used. To ensure the table function is always available, you can use theSqliteDatabase.table_function()
decorator to register the function with the database.TableFunction
implementations must provide two attributes and implement two methods, described below.- columns
A list containing the names of the columns for the data returned by the function. For example, a function that is used to split a string on a delimiter might specify 3 columns:
[substring, start_idx, end_idx]
.
- params
The names of the parameters the function may be called with. All parameters, including optional parameters, should be listed. For example, a function that is used to split a string on a delimiter might specify 2 params:
[string, delimiter]
.
- name
Optional - specify the name for the table function. If not provided, name will be taken from the class name.
- print_tracebacks = True
Print a full traceback for any errors that occur in the table-function’s callback methods. When set to False, only the generic OperationalError will be visible.
- initialize(**parameter_values)
- Parameters
parameter_values – Parameters the function was called with.
- Returns
No return value.
The
initialize
method is called to initialize the table function with the parameters the user specified when calling the function.
- iterate(idx)
- Parameters
idx (int) – current iteration step
- Returns
A tuple of row data corresponding to the columns named in the
columns
attribute.- Raises
StopIteration – To signal that no more rows are available.
This function is called repeatedly and returns successive rows of data. The function may terminate before all rows are consumed (especially if the user specified a
LIMIT
on the results). Alternatively, the function can signal that no more data is available by raising aStopIteration
exception.
- classmethod register(conn)
- Parameters
conn – A
sqlite3.Connection
object.
Register the table function with a DB-API 2.0
sqlite3.Connection
object. Table-valued functions must be registered before they can be used in a query.Example:
class MyTableFunction(TableFunction): name = 'my_func' # ... other attributes and methods ... db = SqliteDatabase(':memory:') db.connect() MyTableFunction.register(db.connection())
To ensure the
TableFunction
is registered every time a connection is opened, use thetable_function()
decorator.
- ClosureTable(model_class[, foreign_key=None[, referencing_class=None[, referencing_key=None]]])
- Parameters
model_class – The model class containing the nodes in the tree.
foreign_key – The self-referential parent-node field on the model class. If not provided, peewee will introspect the model to find a suitable key.
referencing_class – Intermediate table for a many-to-many relationship.
referencing_key – For a many-to-many relationship, the originating side of the relation.
- Returns
Returns a
VirtualModel
for working with a closure table.
Factory function for creating a model class suitable for working with a transitive closure table. Closure tables are
VirtualModel
subclasses that work with the transitive closure SQLite extension. These special tables are designed to make it easy to efficiently query hierarchical data. The SQLite extension manages an AVL tree behind-the-scenes, transparently updating the tree when your table changes and making it easy to perform common queries on hierarchical data.To use the closure table extension in your project, you need:
A copy of the SQLite extension. The source code can be found in the SQLite code repository or by cloning this gist:
$ git clone https://gist.github.com/coleifer/7f3593c5c2a645913b92 closure $ cd closure/
Compile the extension as a shared library, e.g.
$ gcc -g -fPIC -shared closure.c -o closure.so
Create a model for your hierarchical data. The only requirement here is that the model has an integer primary key and a self-referential foreign key. Any additional fields are fine.
class Category(Model): name = CharField() metadata = TextField() parent = ForeignKeyField('self', index=True, null=True) # Required. # Generate a model for the closure virtual table. CategoryClosure = ClosureTable(Category)
The self-referentiality can also be achieved via an intermediate table (for a many-to-many relation).
class User(Model): name = CharField() class UserRelations(Model): user = ForeignKeyField(User) knows = ForeignKeyField(User, backref='_known_by') class Meta: primary_key = CompositeKey('user', 'knows') # Alternatively, a unique index on both columns. # Generate a model for the closure virtual table, specifying the UserRelations as the referencing table UserClosure = ClosureTable( User, referencing_class=UserRelations, foreign_key=UserRelations.knows, referencing_key=UserRelations.user)
In your application code, make sure you load the extension when you instantiate your
Database
object. This is done by passing the path to the shared library to theload_extension()
method.db = SqliteExtDatabase('my_database.db') db.load_extension('/path/to/closure')
Warning
There are two caveats you should be aware of when using the
transitive_closure
extension. First, it requires that your source model have an integer primary key. Second, it is strongly recommended that you create an index on the self-referential foreign key.Example:
class Category(Model): name = CharField() metadata = TextField() parent = ForeignKeyField('self', index=True, null=True) # Required. # Generate a model for the closure virtual table. CategoryClosure = ClosureTable(Category) # Create the tables if they do not exist. db.create_tables([Category, CategoryClosure], True)
It is now possible to perform interesting queries using the data from the closure table:
# Get all ancestors for a particular node. laptops = Category.get(Category.name == 'Laptops') for parent in Closure.ancestors(laptops): print(parent.name) # Computer Hardware # Computers # Electronics # All products # Get all descendants for a particular node. hardware = Category.get(Category.name == 'Computer Hardware') for node in Closure.descendants(hardware): print(node.name) # Laptops # Desktops # Hard-drives # Monitors # LCD Monitors # LED Monitors
API of the
VirtualModel
returned byClosureTable()
.- class BaseClosureTable
- id
A field for the primary key of the given node.
- depth
A field representing the relative depth of the given node.
- root
A field representing the relative root node.
- descendants(node[, depth=None[, include_node=False]])
Retrieve all descendants of the given node. If a depth is specified, only nodes at that depth (relative to the given node) will be returned.
node = Category.get(Category.name == 'Electronics') # Direct child categories. children = CategoryClosure.descendants(node, depth=1) # Grand-child categories. children = CategoryClosure.descendants(node, depth=2) # Descendants at all depths. all_descendants = CategoryClosure.descendants(node)
- ancestors(node[, depth=None[, include_node=False]])
Retrieve all ancestors of the given node. If a depth is specified, only nodes at that depth (relative to the given node) will be returned.
node = Category.get(Category.name == 'Laptops') # All ancestors. all_ancestors = CategoryClosure.ancestors(node) # Grand-parent category. grandparent = CategoryClosure.ancestores(node, depth=2)
- siblings(node[, include_node=False])
Retrieve all nodes that are children of the specified node’s parent.
Note
For an in-depth discussion of the SQLite transitive closure extension, check out this blog post, Querying Tree Structures in SQLite using Python and the Transitive Closure Extension.
- class LSMTable
VirtualModel
subclass suitable for working with the lsm1 extension The lsm1 extension is a virtual table that provides a SQL interface to the lsm key/value storage engine from SQLite4.Note
The LSM1 extension has not been released yet (SQLite version 3.22 at time of writing), so consider this feature experimental with potential to change in subsequent releases.
LSM tables define one primary key column and an arbitrary number of additional value columns (which are serialized and stored in a single value field in the storage engine). The primary key must be all of the same type and use one of the following field types:
Since the LSM storage engine is a key/value store, primary keys (including integers) must be specified by the application.
Attention
Secondary indexes are not supported by the LSM engine, so the only efficient queries will be lookups (or range queries) on the primary key. Other fields can be queried and filtered on, but may result in a full table-scan.
Example model declaration:
db = SqliteExtDatabase('my_app.db') db.load_extension('lsm.so') # Load shared library. class EventLog(LSMTable): timestamp = IntegerField(primary_key=True) action = TextField() sender = TextField() target = TextField() class Meta: database = db filename = 'eventlog.ldb' # LSM data is stored in separate db. # Declare virtual table. EventLog.create_table()
Example queries:
# Use dictionary operators to get, set and delete rows from the LSM # table. Slices may be passed to represent a range of key values. def get_timestamp(): # Return time as integer expressing time in microseconds. return int(time.time() * 1000000) # Create a new row, at current timestamp. ts = get_timestamp() EventLog[ts] = ('pageview', 'search', '/blog/some-post/') # Retrieve row from event log. log = EventLog[ts] print(log.action, log.sender, log.target) # Prints ("pageview", "search", "/blog/some-post/") # Delete the row. del EventLog[ts] # We can also use the "create()" method. EventLog.create( timestamp=get_timestamp(), action='signup', sender='newsletter', target='sqlite-news')
Simple key/value model declaration:
class KV(LSMTable): key = TextField(primary_key=True) value = TextField() class Meta: database = db filename = 'kv.ldb' db.create_tables([KV])
For tables consisting of a single value field, Peewee will return the value directly when getting a single item. You can also request slices of rows, in which case Peewee returns a corresponding
Select
query, which can be iterated over. Below are some examples:>>> KV['k0'] = 'v0' >>> print(KV['k0']) 'v0' >>> data = [{'key': 'k%d' % i, 'value': 'v%d' % i} for i in range(20)] >>> KV.insert_many(data).execute() >>> KV.select().count() 20 >>> KV['k8'] 'v8' >>> list(KV['k4.1':'k7.x'] [Row(key='k5', value='v5'), Row(key='k6', value='v6'), Row(key='k7', value='v7')] >>> list(KV['k6xxx':]) [Row(key='k7', value='v7'), Row(key='k8', value='v8'), Row(key='k9', value='v9')]
You can also index the
LSMTable
using expressions:>>> list(KV[KV.key > 'k6']) [Row(key='k7', value='v7'), Row(key='k8', value='v8'), Row(key='k9', value='v9')] >>> list(KV[(KV.key > 'k6') & (KV.value != 'v8')]) [Row(key='k7', value='v7'), Row(key='k9', value='v9')]
You can delete single rows using
del
or multiple rows using slices or expressions:>>> del KV['k1'] >>> del KV['k3x':'k8'] >>> del KV[KV.key.between('k10', 'k18')] >>> list(KV[:]) [Row(key='k0', value='v0'), Row(key='k19', value='v19'), Row(key='k2', value='v2'), Row(key='k3', value='v3'), Row(key='k9', value='v9')]
Attempting to get a single non-existant key will result in a
DoesNotExist
, but slices will not raise an exception:>>> KV['k1'] ... KV.DoesNotExist: <Model:KV> instance matching query does not exist: ... >>> list(KV['k1':'k1']) []
- class ZeroBlob(length)
- Parameters
length (int) – Size of blob in bytes.
ZeroBlob
is used solely to reserve space for storing a BLOB that supports incremental I/O. To use the SQLite BLOB-store it is necessary to first insert a ZeroBlob of the desired size into the row you wish to use with incremental I/O.For example, see
Blob
.
- class Blob(database, table, column, rowid[, read_only=False])
- Parameters
database –
SqliteExtDatabase
instance.table (str) – Name of table being accessed.
column (str) – Name of column being accessed.
rowid (int) – Primary-key of row being accessed.
read_only (bool) – Prevent any modifications to the blob data.
Open a blob, stored in the given table/column/row, for incremental I/O. To allocate storage for new data, you can use the
ZeroBlob
, which is very efficient.class RawData(Model): data = BlobField() # Allocate 100MB of space for writing a large file incrementally: query = RawData.insert({'data': ZeroBlob(1024 * 1024 * 100)}) rowid = query.execute() # Now we can open the row for incremental I/O: blob = Blob(db, 'rawdata', 'data', rowid) # Read from the file and write to the blob in chunks of 4096 bytes. while True: data = file_handle.read(4096) if not data: break blob.write(data) bytes_written = blob.tell() blob.close()
- read([n=None])
- Parameters
n (int) – Only read up to n bytes from current position in file.
Read up to n bytes from the current position in the blob file. If n is not specified, the entire blob will be read.
- seek(offset[, whence=0])
- Parameters
offset (int) – Seek to the given offset in the file.
whence (int) – Seek relative to the specified frame of reference.
Values for
whence
:0
: beginning of file1
: current position2
: end of file
- tell()
Return current offset within the file.
- write(data)
- Parameters
data (bytes) – Data to be written
Writes the given data, starting at the current position in the file.
- close()
Close the file and free associated resources.
Additional Features
The SqliteExtDatabase
accepts an initialization option to register
support for a simple bloom filter.
The bloom filter, once initialized, can then be used for efficient membership
queries on large set of data.
Here’s an example:
db = CSqliteExtDatabase(':memory:', bloomfilter=True)
# Create and define a table to store some data.
db.execute_sql('CREATE TABLE "register" ("data" TEXT)')
Register = Table('register', ('data',)).bind(db)
# Populate the database with a bunch of text.
with db.atomic():
for i in 'abcdefghijklmnopqrstuvwxyz':
keys = [i * j for j in range(1, 10)] # a, aa, aaa, ... aaaaaaaaa
Register.insert([{'data': key} for key in keys]).execute()
# Collect data into a 16KB bloomfilter.
query = Register.select(fn.bloomfilter(Register.data, 16 * 1024).alias('buf'))
row = query.get()
buf = row['buf']
# Use bloomfilter buf to test whether other keys are members.
test_keys = (
('aaaa', True),
('abc', False),
('zzzzzzz', True),
('zyxwvut', False))
for key, is_present in test_keys:
query = Register.select(fn.bloomfilter_contains(key, buf).alias('is_member'))
answer = query.get()['is_member']
assert answer == is_present
The SqliteExtDatabase
can also register other useful functions:
rank_functions
(enabled by default): registers functions for ranking search results, such as bm25 and lucene.hash_functions
: registers md5, sha1, sha256, adler32, crc32 and murmurhash functions.regexp_function
: registers a regexp function.
Examples:
def create_new_user(username, password):
# DO NOT DO THIS IN REAL LIFE. PLEASE.
query = User.insert({'username': username, 'password': fn.sha1(password)})
new_user_id = query.execute()
You can use the murmurhash function to hash bytes to an integer for compact storage:
>>> db = SqliteExtDatabase(':memory:', hash_functions=True)
>>> db.execute_sql('SELECT murmurhash(?)', ('abcdefg',)).fetchone()
(4188131059,)