Reference

The ItemDB class

class itemdb.ItemDB(filename)

A transactional database for storage and retrieval of dict items.

Parameters:filename (str) – The file to open. Use “:memory:” for an in-memory db.

The items in the database can be any JSON serializable dictionary. Indices can be defined for specific fields to enable fast selection of items based on these fields. Indices can be marked as unique to make a field mandatory and identify items based on that field.

Transactions are done by using the with statement, and are mandatory for all operations that write to the database.

mtime

The time that the database file was last modified, as a Unix timestamp.

Is -1 if the file did not exist, or if the filename is not represented on the filesystem.

close()

Close the database connection.

This will be automatically called when the instance is deleted. But since it can be held e.g. in a traceback, consider using with closing(db):.

get_table_names()

Return a (sorted) list of table names present in the database.

get_indices(table_name)

Get a set of index names for the given table.

Parameters:table_name (str) – The name of the table to get the indices for. To avoid SQL injection, this arg should not be based on unsafe data.

Names prefixed with “!” represent fields that are required and unique. Raises KeyError if the table does not exist.

ensure_table(table_name, *indices)

Ensure that the given table exists and has the given indices.

Parameters:
  • table_name (str) – The name of the table to make sure exists. To avoid SQL injection, this arg should not be based on unsafe data.
  • indices (varargs) – A sequence of strings, representing index names. Fields that are indexed can be queried with e.g. select(). To avoid SQL injection, this arg should not be based on unsafe data.

If an index name is prefixed with “!”, it indicates a field that is mandatory and unique. Note that new unique indices cannot be added when the table already exist.

This method returns as quickly as possible when the table already exists and has the appropriate indices. Returns the ItemDB object, so calls to this method can be stacked.

Although this call may modify the database, one does not need to call this in a transaction.

delete_table(table_name)

Delete the table with the given name.

Parameters:table_name (str) – The name of the table to delete. To avoid SQL injection, this arg should not be based on unsafe data.

Be aware that this deletes the whole table, including all of its items.

This method must be called within a transaction. Can raise KeyError if an invalid table is given, or IOError if not used within a transaction

rename_table(table_name, new_table_name)

Rename a table.

Parameters:
  • table_name (str) – The current name of the table. To avoid SQL injection, this arg should not be based on unsafe data.
  • new_table_name (str) – The new name. To avoid SQL injection, this arg should not be based on unsafe data.

This method must be called within a transaction. Can raise KeyError if an invalid table is given, or IOError if not used within a transaction

count_all(table_name)

Get the total number of items in the given table.

count(table_name, query, *save_args)

Get the number of items in the given table that match the given query.

Parameters:
  • table_name (str) – The name of the table to count items in. To avoid SQL injection, this arg should not be based on unsafe data.
  • query (str) – The query to select items on. To avoid SQL injection, this arg should not be based on unsafe data; use save_args for end-user input.
  • save_args (varargs) – The values to select items on.

Examples:

# Count the persons older than 20
db.count("persons", "age > ?", 20)
# Count the persons older than a given value
db.count("persons", "age > ?", min_age)
# Use AND and OR for more precise queries
db.count("persons", "age > ? AND age < ?", min_age, max_age)

See select() for details on queries.

Can raise KeyError if an invalid table is given, IndexError if an invalid field is used in the query, or sqlite3.OperationalError for an invalid query.

select_all(table_name)

Get all items in the given table. See select() for details.

select(table_name, query, *save_args)

Get the items in the given table that match the given query.

Parameters:
  • table_name (str) – The name of the table to select items in. To avoid SQL injection, this arg should not be based on unsafe data.
  • query (str) – The query to select items on. To avoid SQL injection, this arg should not be based on unsafe data; use save_args for end-user input.
  • save_args (varargs) – The values to select items on.

The query follows SQLite syntax and can only include indexed fields. If needed, use ensure_table() to add indices. The query is always fast (which is why this method is called ‘select’, and not ‘search’).

Examples:

# Select the persons older than 20
db.select("persons", "age > ?",  20)
# Select the persons older than a given age
db.select("persons", "age > ?", min_age)
# Use AND and OR for more precise queries
db.select("persons", "age > ? AND age < ?", min_age, max_age)

There is no method to filter items bases on non-indexed fields, because this is easy using a list comprehension, e.g.:

items = db.select_all("persons")
items = [i for i in items if i["age"] > 20]

Can raise KeyError if an invalid table is given, IndexError if an invalid field is used in the query, or sqlite3.OperationalError for an invalid query.

select_one(table_name, query, *args)

Get the first item in the given table that match the given query.

Parameters:
  • table_name (str) – The name of the table to select an item in. To avoid SQL injection, this arg should not be based on unsafe data.
  • query (str) – The query to select the item on. To avoid SQL injection, this arg should not be based on unsafe data; use save_args for end-user input.
  • save_args (varargs) – The values to select the item on.

Returns None if there was no match. See select() for details.

put(table_name, *items)

Put one or more items into the given table.

Parameters:
  • table_name (str) – The name of the table to put the item(s) in. To avoid SQL injection, this arg should not be based on unsafe data.
  • items (varargs) – The dicts to add. Keys that match an index can later be used for fast querying.

This method must be called within a transaction. Can raise KeyError if an invalid table is given, IOError if not used within a transaction, TypeError if an item is not a (JSON serializable) dict, or IndexError if an item does not have a required field.

put_one(table_name, **item)

Put an item into the given table using kwargs.

Parameters:
  • table_name (str) – The name of the table to put the item(s) in. To avoid SQL injection, this arg should not be based on unsafe data.
  • item (kwargs) – The dict to add. Keys that match an index can later be used for fast querying.

This method must be called within a transaction.

delete(table_name, query, *save_args)

Delete items from the given table.

Parameters:
  • table_name (str) – The name of the table to delete items from. To avoid SQL injection, this arg should not be based on unsafe data.
  • query (str) – The query to select the items to delete. To avoid SQL injection, this arg should not be based on unsafe data; use save_args for end-user input.
  • save_args (varargs) – The values to select the item on.

Examples:

# Delete the persons older than 20
db.delete("persons", "age > ?", 20)
#  Delete the persons older than a given age
db.delete("persons", "age > ?", min_age)
# Use AND and OR for more precise queries
db.delete("persons", "age > ? AND age < ?", min_age, max_age)

See select() for details on queries.

This method must be called within a transaction. Can raise KeyError if an invalid table is given, IOError if not used within a transaction, IndexError if an invalid field is used in the query, or sqlite3.OperationalError for an invalid query.

The AsyncItemDB class

class itemdb.AsyncItemDB

An async version of ItemDB. The API is exactly the same, except that all methods are async, and one must use async with instead of the normal with.

The asyncify function

itemdb.asyncify(func)

Wrap a normal function into an awaitable co-routine. Can be used as a decorator.

The original function will be executed in a separate thread. This allows async code to execute io-bound code (like querying a sqlite database) without stalling.

Note that the code in func must be thread-safe. It’s probably best to isolate the io-bound parts of your code and only wrap these.