SQL reference

The py314.sql package contains SQL helpers functions and classes. In particular, set, dict and list can be inserted into the SQLITE database by using, internally, the marshal module.

Furthermore, the select() method handles the deserialization of bytes object whenever it is possible and thus users do not need to take care of the serialization and the deserialization process for most of the builtins types.

User-defined types can be serialized or deserialized easily as soon as they inherit SqlVariant, yet specific adapters and converters methods need to be implemented accordingly. For more details, refer to SqlVariant documentation.

SQL Driver

class py314.sql.driver.SqlDriver(*, database)

An sqlite3 Client Interface.

Parameters:database (str) – The database file.
classmethod enable_timeit_strategy()

Decorate some methods with WithTimeitPolicy.wraps().

classmethod disable_timeit_strategy()

Remove the timing decorators.

run_script(path)

Execute a specific SQL script.

interrupt()

Prematurely shutdown the SQL connection without errors.

close()

Close the SQL connection.

execute(statement, values=None)

Execute the a non-prepared statement, or a prepared one filled with given values and return the connection cursor.

Parameters:
  • statement (str) – SQL Statement with possible placeholders.
  • values (tuple [optional]) – SQL Statement’s placeholders values.
Returns:

The SQL cursor.
fetch(statement, values=None, unique=True)

Execute a statement stmt and return an adapted result set whose items are constructed from a result set L := { L(1), …, L(N) }. Note that it may happen that L is empty, i.e. N = 0. By default, L is given by sqlite3.Cursor.fetchone() (i.e. 0 ≤ N ≤ 1). Otherwise, it is given by sqlite3.Cursor.fetchall().

Parameters:
  • statement (str) – SQL Statement with possible placeholders.
  • values (tuple [optional]) – SQL Statement’s placeholders values.
  • unique (bool [optional]) – Fetch the first row only.
commit(statement, values=None)

Execute the statement and commit the transaction.

select(table, *args, **kwargs)

Fetch the first row in table with columns values matching those given by the keyword arguments and return a tuple consisting of the columns in args.

Parameters:table (str) – The SQL table name.
Returns:A subrow of the result row.

See also

fetch()

selectall(table, *args, **kwargs)

Fetch all the rows in table with columns values matching those given by the keyword arguments and return a tuple consisting of the subset of the result set.

Parameters:table (str) – The SQL table name.
Returns:Subrows of the result row.

See also

fetch()

update(table, record, **kwargs)

Update with record the rows in table satisfying a clause given by the keyword arguments.

Parameters:
update_or_ignore(table, record, **kwargs)

Similar to update() except that columns whose values prior being updated do not differ with the new values are ignored.

Comparing all possible rows is too costly, and only the first row will be updated if needed.

Parameters:
insert(table, **kwargs)

Execute an INSERT SQL statement.

Parameters:table (str) – SQL Table name.
insert_or_ignore(table, **kwargs)

Execute an INSERT_OR_IGNORE SQL statement, that is if the database is already up-to-date, nothing is done.

Parameters:table (str) – SQL Table name.
insert_or_replace(table, **kwargs)

Execute an INSERT_OR_REPLACE SQL statement.

Parameters:table (str) – SQL Table name.
delete(table, **kwargs)

Execute a DELETE SQL statement on table. Keyword arguments respect the syntax of a normal py314.sql.field.SqlField, even if they are used to construct the predicate the deleted rows must satisfy.

Parameters:table (str) – SQL Table name.
exists(table, **kwargs)

Check that table satisfies a suitable predicate.

Parameters:table (str) – The table where the search is to be done.
count_cols(table)

Count the number of columns in a table.

Parameters:table (str) – The table where the columns are counted.

SQL Fields data

class py314.sql.field.SqlIdentifier

An SQL Identifier with escape and PARSE AS support if needed, but do nothing if identifier contains quotes or asterisks. If typename exists, then the SQL identifier will be name AS 'identifier [typename]', where the name string is a quoted version of identifier if any.

Parameters:
  • identifier (str) – The identifier to escape.
  • typename (str [optional]) – Describe the AS type.
class py314.sql.field.SqlField(name, value)

Manipulate the fields in SQL database tables and views.

Represent the characteristics of a column in a database table or view, such as the data type and column name. A field also contains the value of the database column, which can be viewed or changed.

Parameters:
  • name (str) – The field name.
  • value (object) – The field value.
name

str – The field name.

value

object – The field value.

conform()

dict – A single arrow name → value.

SQL Query helpers

class py314.sql.clause.SqlOperatorAND

An SQL operator containing multiple AND slots.

class py314.sql.clause.SqlOperatorOR

An SQL operator containing multiple OR slots.

class py314.sql.clause.SqlWhereClause

An SQL WHERE clause.

class py314.sql.statements.SqlSelect(table, *columns, clause=None)

Helper for a SELECT request.

classmethod format(table, *columns)

A clause-missing SQL Select Statement.

Parameters:
  • table (str) – SQL Table name.
  • columns (list) – Columns to select.
class py314.sql.statements.SqlUpdate(table, *columns, clause)
classmethod format(table, *columns)

A clause-missing SQL Update prepared statement.

Parameters:
  • table (str) – SQL Table name.
  • columns (list) – Columns to update.
class py314.sql.statements.SqlInsert(table, *columns)
class py314.sql.statements.SqlInsertOrIgnore(table, *columns)
class py314.sql.statements.SqlInsertOrReplace(table, *columns)
class py314.sql.statements.SqlDelete(table, clause)

Create an SQL Statement for a DELETE request.

classmethod format(table)
Parameters:table (str) – The table where to delete.

SQL Records

class py314.sql.record.SqlRecord(*fields, **kwargs)

Encapsulate a database record, i.e. the functionality and characteristics of a database record (usually a row in a table or view within a database).

Support adding and removing fields as well as setting and retrieving field values. These fields can be modified via put() method and retrieved via the get() method.

Parameters:fields (list) – A list of SqlField
get(name)

Return the SqlField named name.

Parameters:name (str) – The field name.
put(field)

Insert an SqlField into the record.

Parameters:field (py314.sql.field.SqlField) – The field to insert.
class py314.sql.record.SqlRowRecord(record=None)

An SQL record row holder.

Parameters:record (sqlite3.Row [optional]) – An SQL Row Record.
is_valid

Return whether the parent record is valid or not.

SQL Variant

py314.sql.variant.support_sqlite3(cls)

Define a serializable class when inheritance is impossible due to different meta classes. If cls is a subclass of Enum, add default adapter and converter, but do not override existing ones.

class py314.sql.variant.SqlVariant(**kwargs)

Object implementing a dict converter and loader. Inherit this metaclass for user-defined type to handle sqlite3 dumps and loads. If inheritance is problematic, use the support_sqlite3() decorator instead.

conform()

Convert the current instance to a marshal-dumpable data.

classmethod from_conformed(conformed)

Construct an instance from data obtained via conform().

If the conformed value is a dictionary with bytes values, this method allows to convert these values into suitable values.

Identity cls.from_conformed(self.conform()) = self is always true.

classmethod adapter(instance)

Serialize instance into an sqlite3 BLOB.

Parameters:instance (SqlVariant) – An instance to adapt.
classmethod converter(blob)

Convert blob to an SqlVariant by calling the constructor with an unique argument given by marshal.loads(blob).

If multiple positional arguments or keywords arguments are needed, this method must be reimplemented in children classes.

If after deserialization, blob contains serialized data, this method must be reimplemented in children classes, for marshal does not recursively deserialized nested iterables.

Parameters:blob (bytes) – A bytes object obtained from adapter().
classmethod serialize(value)

Serialize an object via marshal.dumps().

Parameters:value (object) – A value to dump.
classmethod deserialize(blob)

Deserialize a blob via marshal.loads().

Parameters:blob (bytes) – An SQL BLOB object.