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