labm8.db

Extended SQL database interface.

class labm8.db.Database(path, tables={}, enable_traces=True)
attach(path, name)

Attach a database.

Parameters:
  • path (str) – Path to the database to merge.
  • name (str) – Name to attach database as.
close()

Close a database connection.

commit()

Commit the current transaction.

Make sure to call this method after you’ve modified the database’s state!

copy_table(src, dst)

Create a carbon copy of the source table.

Parameters:
  • src (str) – The name of the table to copy.
  • dst (str) – The name of the target duplicate table.
Raises:

sql.OperationalError – If source table does not exist.

create_table(name, schema)

Create a new table.

If the table already exists, nothing happens.

Example

>>> db.create_table("foo", (("id", "integer primary key"),
                            ("value", "text")))
Parameters:
  • name (str) – The name of the table to create.
  • schema (sequence of tuples) – A list of (name, type) tuples representing each of the columns.
create_table_from(name, src)

Create a new table with same schema as the source.

If the named table already exists, nothing happens.

Parameters:
  • name (str) – The name of the table to create.
  • src (str) – The name of the source table to duplicate.
Raises:

sql.OperationalError – If source table does not exist.

detach(name)

Detach a database.

Parameters:name (str) – Name of database to detach.
drop_table(name)

Drop an existing table.

If the table does not exist, nothing happens.

Parameters:name (str) – The name of the table to drop.
empty_table(name)

Delete all rows in a table.

If the table does not exist, nothing happens.

Parameters:name (str) – The name of the table to empty.
execute(*args)

Execute the given arguments.

executemany(*args)

Execute the given arguments.

executescript(*args)

Execute the given arguments.

export_csv(table, output=None, columns='*', **kwargs)

Export a table to a CSV file.

If an output path is provided, write to file. Else, return a string.

Wrapper around pandas.sql.to_csv(). See: http://pandas.pydata.org/pandas-docs/stable/io.html#io-store-in-csv

Parameters:
  • table (str) – Name of the table to export.
  • output (str, optional) – Path of the file to write.
  • columns (str, optional) – A comma separated list of columns to export.
  • **kwargs – Additional args passed to pandas.sql.to_csv()
Returns:

CSV string, or None if writing to file.

Return type:

str

Raises:
  • IOError – In case of error writing to file.
  • SchemaError – If the named table is not found.
isempty(tables=None)

Return whether a table or the entire database is empty.

A database is empty is if it has no tables. A table is empty if it has no rows.

Parameters:tables (sequence of str, optional) – If provided, check that the named tables are empty. If not provided, check that all tables are empty.
Returns:True if tables are empty, else false.
Return type:bool
Raises:sql.OperationalError – If one or more of the tables do not exist.
num_rows(table)

Return the number of rows in the named table.

Example

>>> db.num_rows("foo")
3
Parameters:table (str) – The name of the table to count the rows in.
Returns:The number of rows in the named table.
Return type:int
Raises:sql.OperationalError – If the named table does not exist.
schema

Returns the schema of all tables.

For each table, return the name, and a list of tuples representing the columns. Each column tuple consists of a (name, type) pair. Note that additional metadata, such as whether a column may be null, or whether a column is a primary key, is not returned.

Example

>>> db.schema
[("bar", (("id", "integer"), ("name", "table"))]
Returns:
Each tuple has the format (name, columns), where
”columns” is a list of tuples of the form (name, type).
Return type:list of tuples
table_info(table)

Returns information about the named table.

See: https://www.sqlite.org/pragma.html#pragma_table_info

Example

>>> db.table_info("foo")
[{"name": "id", "type": "integer", "primary key": True,
  "notnull": False, "default_value": None}]
Parameters:name (str) – The name of the table to lookup.
Returns:
One dict per column. Each dict contains the
keys: “name”, “type”, “primary key”, “notnull”, and “default_value”.
Return type:list of dicts
Raises:sql.OperationalError – If table does not exist.
tables

Returns a list of table names.

Example

>>> db.tables
["bar", "foo"]
Returns:One string for each table name.
Return type:list of str
exception labm8.db.Error

Module-level base error class.

exception labm8.db.SchemaError

Error thrown in case of conflicting schemas.

labm8.db.placeholders(*columns)

Generate placeholders string for given arguments.

Examples

>>> placeholders(a, b, c)
"(?,?,?)"
>>> placeholders(*sequence)
"(?,?,?,?,?,?,?,?,?,?)"
>>> db.execute("INSERT INTO foo VALUES " +
               placeholders(a, b, c), (a, b, c))
>>> db.execute("INSERT INTO bar VALUES " +
               placeholders(*sequence), sequence)
Parameters:*columns – Arguments to generate placeholders for.
Returns:Bracketed, comma separated placeholders for given arguments.
Return type:str
labm8.db.where(*columns)

String together multiple where clauses.

Examples

>>> where("a", "b", "c")
"a=? AND b=? AND c=?"
>>> db.execute("SELECT * FROM foo WHERE {}"
               .format(where("a", "b", "c")),
               (a, b, c))
Parameters:*columns – Arguments to generate ‘<name>=?’ placeholders for.
Returns:Equivalence checks for all columns.
Return type:str