Table class

class dbtools.Table(db, name, verbose=False)

Bases: object

Creates a frame-like interface to the SQLite table name in the database db.

Parameters :

db : (string)

The path to the SQLite database.

name : (string)

The name of the table in the database.

verbose : bool (default=False)

Print out SQL command information.

Methods

create(db, name, init[, primary_key, ...])	Create a table called name in the database db.
delete([where])	Delete rows from the table.
drop()	Drop the table from its database.
exists(db, name[, verbose])	Check if a table called name exists in the database db.
insert([values])	Insert values into the table.
list_tables(db[, verbose])	Get the list of tables present in the database db.
save_csv(path[, columns, where])	Write table data to a CSV text file.
select([columns, where])	Select data from the table.
update(values[, where])	Update data in the table.

__getitem__(key)

Select data from the table.

This method wraps around select in a few ways.

1. If a string is given, the column with that name is selected. For example:

table['name']

2. If a list of strings is given, the columns with those names are selected. For example:

table['name', 'age']

3. If the table has an autoincrementing primary key, you can use integer indexing and slicing syntax to select rows by their primary keys. For example:

table[0]
table[:5]
table[7:]

Returns :

data : pandas.DataFrame

The output of select, called as described above.

classmethod list_tables(db, verbose=False)

Get the list of tables present in the database db.

Parameters :

db : string

Path to the SQLite database.

verbose : bool (optional)

Print out SQL command information.

Returns :

tables : list

A list of strings corresponding to table names

classmethod exists(db, name, verbose=False)

Check if a table called name exists in the database db.

Parameters :

db : string

Path to the SQLite database.

name : string

Name of the desired table.

verbose : bool (optional)

Print out SQL command information.

Returns :

exists : bool

True if the table exists, False otherwise

classmethod create(db, name, init, primary_key=None, autoincrement=False, verbose=False)

Create a table called name in the database db.

Depending on what is given for the init parameter, this method has several different behaviors.

1. init is a list of 2-tuples.

   Each tuple corresponds to a desired column and has the format (column name, data type). An empty table will be created with these column names and data types, in the order that they are given in the list.

2. init is a pandas DataFrame.

   The column names of the DataFrame will be used as column names in the table, and the datatype of each column will be inferred by the first row of data in the DataFrame.

   If the DataFrame has an index name, a primary key column will be created (it will also be AUTOINCREMENT if autoincrement is True) by that name and its values will be the index of the DataFrame.

   The corresponding values for the other columns will be the DataFrame’s actual matrix data.

3. init is a dictionary or list of dictionaries.

   The dictionary keys will be used as column names in the table, in alphabetical order, and the datatype of each column will be inferred from the corresponding values in the dictionary or dictionaries.

   If primary_key is given and it corresponds to a key name, that column will be created with PRIMARY KEY (it will also be AUTOINCREMENT if autoincrement is True). If it does not correspond to a key name, a new primary key column will be created with values ranging from 1 to N, where N is the number of dictionaries given.

   The Table data will be populated with appropriate values from the dictionary or dictionaries.

Parameters :

db : string

Path to the SQLite database.

name : string

Name of the desired table.

init : list, pandas.DataFrame, or dictionary

See above

primary_key : string (optional)

Name of the primary key column. If None, no primary key is set.

autoincrement : bool (optional)

Set the primary key column to automatically increment.

verbose : bool (optional)

Print out SQL command information.

Returns :

tbl : dbtools.Table

Newly created Table object

drop()

Drop the table from its database.

Note that after calling this method, this Table object will no longer function properly, as it will correspond to a table that does not exist.

insert(values=None)

Insert values into the table.

The values parameter should be a list of non-string sequences (or a single sequence, which will then be encased in a list). Each sequence is handled as follows:

• If the sequence is a dictionary, then the keys should correspond to column names and the values should match the data types of the columns.
• If the sequence is not a dictionary, it should have length equal to the number of columns and each element should be a value to be inserted in the corresponding column.

If the table has an autoincrementing primary key, then this value should be excluded from every sequence as it will be filled in automatically.

select(columns=None, where=None)

Select data from the table.

Parameters :

columns : (default=None)

The column names to select. If None, all columns are selected. Can be either a single value (string) or a list of strings.

where : (default=None)

Additional filtering to perform on the data akin to the WHERE SQL statement, e.g.:

where="age=25"

If you need to pass in variable arguments, use question marks, e.g.:

where=("age=?", 25)
where=("age=? OR name=?", (25, "Ben Bitdiddle"))

Returns :

data : pandas.DataFrame

A pandas DataFrame containing the queried data. Column names correspond to the table column names, and if there is a primary key column, it will be used as the index.

update(values, where=None)

Update data in the table.

Parameters :

values : dict

The column names (keys) and new values to update.

where : str or tuple (optional)

Additional filtering to perform on the data akin to the WHERE SQL statement, e.g.:

where="age=25"

If you need to pass in variable arguments, use question marks, e.g.:

where=("age=?", 25)
where=("age=? OR name=?", (25, "Ben Bitdiddle"))

delete(where=None)

Delete rows from the table.

Parameters :

where : (default=None)

Filtering to determine which rows should be deleted, akin to the WHERE SQL statement, e.g.:

where="age=25"

If you need to pass in variable arguments, use question marks, e.g.:

where=("age=?", 25)
where=("age=? OR name=?", (25, "Ben Bitdiddle"))

NOTE: If where is None, then ALL rows will be deleted!

save_csv(path, columns=None, where=None)

Write table data to a CSV text file.

Takes in a path for the file as well as any arguments and/or keyword arguments to be passed to select. The output of select with those arguments is what will be written to the csv file.

Parameters :

path : string

Path to save the csv file.

columns : (optional)

See select

where : (optional)

See select