Database abstraction layer

Nexus uses a custom abstraction layer for SQLite database operations. It has a NoSQL-like API, meaning that objects work like dicts or lists where possible and appropriate.
The abstraction layer can be used by importing nexus.core.db.
Important: All tables that this abstraction layer is used for, must have a ROWID alias named id.

Table of contents

Database([filename])
Creates a new Database connection representing an SQLite database with the given filename. If it does not exist, it is created.
filename
Optional. Filename of the SQLite database.
Database.setup()
Attempts to set up the tables needed for Nexus in the database. If the tables already exist, nothing happens.
Database.query(query[, params=params])
Runs a custom query against the database, and returns an sqlite3.cursor object, that you can use to retrieve the results from (using .fetchone(), .fetchmany(size), or .fetchall()). The cursor will return Row objects, like other functions in the abstraction layer do.
You'll only need this if you need to run queries that aren't covered by any of the other functions; the functions specified in the abstraction layer are prefered for performance reasons.
Database[table_name]
Database.get_database_table(table_name)
Retrieves a DatabaseTable object representing the table in the database with the specified table name. Table objects are reused where appropriate, to minimize resource usage and state issues.
While a DatabaseTable does not immediately load all data from the table like a MemoryTable does, it does retain an internal cache of retrieved rows. You will want to .purge() this cache regularly if you use the table a lot.
Important: The existence of a table is not checked. You need to make sure that the table exists by yourself, if you cannot rely on this!
table_name
The name of the table you wish to work with.
Example: Accessing a database table
Code:
db = Database("test.db")
table = db["sample_table"]
Example: Accessing a database table through the explicit function
Code:
db = Database("test.db")
table = db.get_database_table("sample_table")
Database.get_memory_table(table_name)
Retrieves a MemoryTable object representing the specified table. A MemoryTable, upon creation, will immediately load all data from the database table it represents, and keep it in memory. It is reused where possible, just like a DatabaseTable.
A MemoryTable is intended to be used for cases where frequent lookup of data in small tables is necessary, eliminating the SQLite lookup overhead.
Important: The existence of a table is not checked. You need to make sure that the table exists by yourself, if you cannot rely on this!
Important: If your database table is modified by another application or instance, the data in your MemoryTable will be out of sync. In this case, use a regular MemoryTable or .refresh() the data frequently.
table_name
The name of the table you wish to work with.
Example: Accessing a database table and keeping it in memory
Code:
db = Database("test.db")
table = db.get_memory_table("sample_table")
DatabaseTable[row_id]
MemoryTable[row_id]
Retrieves a Row object representing the row in the table with the specified identifier (in the id field). Data is retrieved immediately.
row_id
The identifier of the row to retrieve.

Exceptions

KeyError
Raised when a row with the given identifier does not exist in the table.
DatabaseTable[row_id] = row
MemoryTable[row_id] = row
Inserts a new row into the database. This can not be used to edit an existing row; to do so, edit the Row object for that row directly.
If you do not want to explicitly specify a row identifier, use the .append() method instead.
row_id
The identifier to give the row in the database.
row
A Row object representing the new row to insert.

Exceptions

TypeError
Raised when a row with the given identifier already exists.
DatabaseTable.append(row)
MemoryTable.append(row)
Inserts a new row into the table, and lets SQLite assign it an identifier. This is the method you'll usually want to use.
row
A Row object representing the new row to insert.
DatabaseTable.purge()
Purges the internal row cache of a DatabaseTable.
Important: You cannot use this method for a MemoryTable! Use .refresh() instead.
MemoryTable.refresh()
Replaces the current copy of the table in memory, with a newly retrieved copy. You'll need to call this regularly when you have multiple applications modifying the same table, to prevent going out of sync. If synchronized data is absolutely essential at all times, use a DatabaseTable instead.
Important: You cannot use this method for a DatabaseTable! Use .purge() instead.
Row()
Creates a new Row object. You'll only need to use this if you want to insert a new row into a table.
You do not need to immediately specify a table name or row data. Instead, you can just set column values on the Row object after creating it, and tell it what table to be inserted to by using any of the insertion methods on a DatabaseTable or MemoryTable.
Row[column_name]
Returns the value of the specified column in the row.
column_name
The column whose value you wish to retrieve.

Exceptions

KeyError
Returned when there is no such column in the table that the row belongs to.
Row[column_name] = value
Sets (or changes) the data for the given column in the row.
Important: The change is not immediately reflected in the database (or memory table). To apply your changes, you need to .commit().
Important: This does not check whether such a column exists! If you specify an invalid column name, the data will simply never be inserted.
column_name
The column whose value you wish to set or change.
value
The value to set the column to.
Row.commit()
Process all changes you have made to the column data for the row. This will run one or more SQL queries.
You don't need to do this when inserting a new row; the insertion methods for the table will do this for you automatically.
Row.rollback()
Cancels all the changes you have made to the column data for the row, and returns it to the original state. The "original state" will be state the row was in when you last retrieved or committed it.
Note that this will only work with uncommitted changes; after you commit a change, it is final and not reversible.