csvw.db

SQLite as alternative storage backend for a TableGroup’s data.

For the most part, translation of a TableGroup’s tableSchema to SQL works as expected:

• each table is converted to a CREATE TABLE statement

• each column specifies a column in the corresponding CREATE TABLE statement

• foreignKey constraints are added according to the corresponding tableSchema property.

List-valued foreignKeys are supported as follows: For each pair of tables related through a list-valued foreign key, an association table is created. To make it possible to distinguish multiple list-valued foreign keys between the same two tables, the association table has a column context, which stores the name of the foreign key column from which a row in the assocation table was created.

Other list-valued columns work in two different ways: If the atomic datatype is string, the specified separator is used to create a concatenated string representation in the database field. Otherwise, the list of values is serialized as JSON.

SQL table and column names can be customized by passing a translator callable when instantiating a Database.

SQLite support has the following limitations:

• regex constraints on strings (as specified via a csvw.Datatype’s format attribute) are not enforced by the database.

class csvw.db.ColSpec(name, csvw_type='string', separator=None, db_type=None, required=False, csvw=None)

A ColSpec captures sufficient information about a csvw.Column for the DB schema.

Parameters:

• name (str) –

• csvw_type (str) –

• separator (str) –

• db_type (csvw.db.DBType) –

• required (bool) –

• csvw (csvw.metadata.Datatype) –

check(translate)

We try to convert as many data constraints as possible into SQLite CHECK constraints.

Parameters:

translate (csvw.db.ColumnTranslator) – Callable to translate column names between CSVW metadata and DB schema.

Return type:

typing.Optional[str]

Returns:

A string suitable as argument of an SQL CHECK constraint.

sql(translate)

Format the column metadata suitable for inclusion in a CREATE TABLE statement.

Parameters:

translate (csvw.db.ColumnTranslator) –

Return type:

str

class csvw.db.ColumnTranslator(*args, **kwargs)

class csvw.db.DBType(name, convert=<function identity>, read=<function identity>)

A DB datatype together with read/write converters.

Parameters:

• name (str) –

• convert (typing.Callable[[typing.Any], typing.Any]) –

• read (typing.Callable[[typing.Any], typing.Any]) –

class csvw.db.Database(tg, fname=None, translate=None, drop_self_referential_fks=True)

Represents a SQLite database associated with a csvw.TableGroup instance.

Parameters:

• tg (csvw.metadata.TableGroup) – TableGroup instance defining the schema of the database.

• fname (typing.Union[str, pathlib.Path, None]) – Path to which to write the database file.

• translate (typing.Optional[csvw.db.SchemaTranslator]) – Schema object name translator.

• drop_self_referential_fks (typing.Optional[bool]) – Flag signaling whether to drop or enforce self-referential foreign-key constraints.

Warning

We write rows of a table to the database sequentially. Since CSVW does not require ordering rows in tables such that self-referential foreign-key constraints are satisfied at each row, we don’t enforce self-referential foreign-keys by default in order to not trigger “false” integrity errors. If data in a CSVW Table is known to be ordered appropriately, False should be passed as drop_self_referential_fks keyword parameter to enforce self-referential foreign-keys.

association_table_context(_, column, fkey)

Context for association tables is created calling this method.

Note: If a custom value for the context column is created by overwriting this method, select_many_to_many must be adapted accordingly, to make sure the custom context is retrieved when reading the data from the db.

Parameters:

• table –

• column –

• fkey –

Returns:

a pair (foreign key, context)

connection()

DB connection to be used as context manager.

Return type:

typing.Union[sqlite3.Connection, contextlib.closing]

init_schema(tg, drop_self_referential_fks=True)

Inititialize the db schema, possibly ignoring self-referential foreign keys.

Parameters:

• tg (csvw.metadata.TableGroup) –

• drop_self_referential_fks (bool) –

static name_translator(table, column=None)

A callable with this signature can be passed into DB creation to control the names of the schema objects.

Parameters:

• table (str) – CSVW name of the table before translation

• column (typing.Optional[str]) – CSVW name of a column of table before translation

Return type:

str

Returns:

Translated table name if column is None else translated column name

read()

Return type:

dict[str, list[collections.OrderedDict]]

Returns:

A dict where keys are SQL table names corresponding to CSVW tables and values are lists of rows, represented as dicts where keys are the SQL column names.

select_many_to_many(db, table, context)

Select data from an association table, grouped by first foreign key.

Return type:

dict[str, typing.Union[tuple[str, str], str]]

separator(tname, cname)

Return type:

typing.Optional[str]

Returns:

separator for the column specified by db schema names tname and cname.

Parameters:

• tname (str) –

• cname (str) –

split_value(tname, cname, value)

Split a value if a separator is defined for the column.

Parameters:

• tname (str) –

• cname (str) –

Return type:

typing.Union[list[str], str, None]

write(*, force=False, _exists_ok=False, _skip_extra=False, **items)

Creates a db file with the core schema.

Parameters:

force – If True an existing db file will be overwritten.

write_from_tg(_force=False, _exists_ok=False, _skip_extra=False)

Write the data from the contained tablegroup to a db.

class csvw.db.SchemaTranslator(*args, **kwargs)

class csvw.db.TableReadSpec(table, name, translate, converters=<factory>, separators=<factory>, references=<factory>)

Bundles data informing the reading of table rows.

Parameters:

• table (csvw.db.TableSpec) –

• name (str) –

• translate (csvw.db.SchemaTranslator) –

• converters (dict[str, tuple[str, typing.Callable]]) –

• separators (dict[str, str]) –

• references (dict) –

read_row(row)

Read a table according to spec.

Parameters:

row (collections.abc.Iterator[tuple[str, typing.Any]]) –

Return type:

collections.OrderedDict[str, typing.Any]

class csvw.db.TableSpec(name, columns=<factory>, foreign_keys=<factory>, many_to_many=<factory>, primary_key=None)

A TableSpec captures sufficient information about a csvw.Table for the DB schema.

Note

We support “light-weight” many-to-many relationships by allowing list-valued foreign key columns in CSVW. In the database these columns are turned into an associative table, adding the name of the column as value a context column. Thus, multiple columns in a table my be specified as targets of many-to-many relations with the same table.

See also

https://en.wikipedia.org/wiki/Associative_entity

Parameters:

• name (str) –

• columns (list[csvw.db.ColSpec]) –

• foreign_keys (list) –

• many_to_many (collections.OrderedDict) –

• primary_key (typing.Optional[list[str]]) –

classmethod association_table(atable, apk, btable, bpk)

List-valued foreignKeys are supported as follows: For each pair of tables related through a list-valued foreign key, an association table is created. To make it possible to distinguish multiple list-valued foreign keys between the same two tables, the association table has a column context, which stores the name of the foreign key column from which a row in the assocation table was created.

Return type:

csvw.db.TableSpec

classmethod from_table_metadata(table, drop_self_referential_fks=True)

Create a TableSpec from the schema description of a csvw.metadata.Table.

Parameters:

• table (csvw.metadata.Table) – csvw.metadata.Table instance.

• drop_self_referential_fks (typing.Optional[bool]) – Flag signaling whether to drop self-referential foreign keys. This may be necessary, if the order of rows in a CSVW table does not guarantee referential integrity when inserted in order (e.g. an eralier row refering to a later one).

Return type:

csvw.db.TableSpec

Returns:

TableSpec instance.

sql(translate)

Parameters:

translate (csvw.db.SchemaTranslator) –

Return type:

str

Returns:

The SQL statement to create the table.

csvw.db.insert(db, translate, table, keys, *rows, single=False)

Insert a sequence of rows into a table.

Parameters:

• db (sqlite3.Connection) – Database connection.

• translate (csvw.db.SchemaTranslator) – Callable translating table and column names to proper schema object names.

• table (str) – Untranslated table name.

• keys (collections.abc.Sequence[str]) – Untranslated column names.

• rows (list) – Sequence of rows to insert.

• single (typing.Optional[bool]) – Flag signaling whether to insert all rows at once using executemany or one at a time, allowing for more focused debugging output in case of errors.

csvw.db.quoted(*names)

Returns a comma-separated list of quoted schema object names.

Parameters:

names (str) –

Return type:

str

csvw.db.schema(tg, drop_self_referential_fks=True)

Convert the table and column descriptions of a TableGroup into specifications for the DB schema.

Parameters:

• tg (csvw.metadata.TableGroup) – CSVW TableGroup.

• drop_self_referential_fks (typing.Optional[bool]) – Flag signaling whether to drop self-referential foreign keys. This may be necessary, if the order of rows in a CSVW table does not guarantee referential integrity when inserted in order (e.g. an eralier row refering to a later one).

Return type:

list[csvw.db.TableSpec]

Returns:

A pair (tables, reference_tables).

csvw.db.select(db, table)

Shortcut to construct and execute simple SELECT statements.

Parameters:

• db (sqlite3.Connection) –

• table (str) –

Return type:

tuple[list[str], collections.abc.Sequence]