SQLDatabase#

class dbastable.SQLDatabase(db=None, autocommit=True, logger=None, allow_b32_colnames=False, **kwargs)#

Bases: _WhereParserMixin, _SanitizerMixin, _ItemAccessorMixin, _RowAccessorMixin, _ColumnAccessorMixin, _TableAccessorMixin

Database creation and manipulation with SQL.

Parameters:

dbstr: The name of the database file. If ‘:memory:’ or None is given, the database will be created in memory.
autocommitbool (optional): Whether to commit changes to the database after each operation. Defaults to True.
loggerLogger (optional): Logger to use. If None, a logger will be created.
allow_b32_colnamesbool (optional): With a column name is invalid, it will be encoded in base32 and a prefix will be added. This is useful to avoid invalid characters like ‘-’ in column names. If False, an error will be raised instead.
**kwargs: Keyword arguments to pass to the connect function.

Notes

‘__id__’ is only for internal indexing. It is ignored on returns.
‘__b32__’ will be used as prefix for base32 encoded column names. So it is not allowed to use this prefix in column names.

Attributes Summary

`db`	Get the database name.
`table_names`	Get the table names in the database.

Methods Summary

`add_column`(table, column[, data])	Add a column to a table.
`add_rows`(table, data[, add_columns, ...])	Add a dict row to a table.
`add_table`(table[, columns, data])	Create a table in database.
`column_names`(table[, do_not_decode])	Get the column names of the table.
`commit`()	Commit the current transaction.
`copy`([indexes])	Get a copy of the database.
`count`(table[, where])	Get the number of rows in the table.
`delete_column`(table, column)	Delete a column from a table.
`delete_row`(table, index)	Delete a row from the table.
`drop_table`(table)	Drop a table from the database.
`execute`(command[, arguments])	Execute a SQL command in the database.
`executemany`(command, arguments)	Execute a SQL command in the database using multiple entries.
`get_column`(table, column)	Get a column from the table.
`get_item`(table, column, row)	Get an item from the table.
`get_row`(table, index)	Get a row from the table.
`get_table`(table)	Get a table from the database.
`index_of`(table, where)	Get the index(es) where a given condition is satisfied.
`select`(table[, columns, where, order, ...])	Select rows from a table.
`set_column`(table, column, data)	Set a column in the table.
`set_item`(table, column, row, value)	Set a value in a cell.
`set_row`(table, row, data)	Set a row in the table.

Attributes Documentation

db#: Get the database name.

table_names#: Get the table names in the database.

Methods Documentation

add_column(table, column, data=None)#

Add a column to a table.

Parameters:

table: str: Name of the table to add the column.
column: str: Name of the column to add.
data: list (optional): List of values to add to the column. If None, no data is added.

add_rows(table, data, add_columns=False, skip_sanitize=False)#

Add a dict row to a table.

Parameters:

datadict, list or ndarray: Data to add to the table. If dict, keys are column names, if list, the order of the values is the same as the order of the column names. If ndarray, dtype names are interpreted as column names.
add_columnsbool (optional): If True, add missing columns to the table.
skip_sanitize: bool (optional): If True, skip the sanitization of the data. Use this if the data is already sanitized. Use with caution, as it may cause problems with your data.

add_table(table, columns=None, data=None)#

Create a table in database.

Parameters:

tablestr: Name of the table to create.
columnslist (optional): List of column names to create in the table. If None, no columns are created.
datalist (optional): List of rows to add to the table. If None, no rows are added. Each row is a list of values in the same order as the columns.

column_names(table, do_not_decode=False)#

Get the column names of the table.

Parameters:

table: str: Name of the table to get the column names.
do_not_decode: bool (optional): If True, do not decode Base32 encoded column names. This is useful to get the real column names in the database. If False, the decoded names are returned.

commit()#: Commit the current transaction.

copy(indexes=None)#

Get a copy of the database.

Parameters:

indexesdict, optional: A dictionary of table names and the indexes of the rows in each table to copy. If None, all rows are copied.

count(table, where=None)#

Get the number of rows in the table.

Parameters:

table: str: Name of the table to count from.
wheredict (optional): Dictionary of conditions to count rows. Keys are column names, values are values to compare. All rows equal to the values will be counted. If None, all rows are counted.

Returns:

resint: Number of rows in the table.

delete_column(table, column)#

Delete a column from a table.

Parameters:

table: str: Name of the table to delete the column.
column: str: Name of the column to delete.

delete_row(table, index)#

Delete a row from the table.

Parameters:

table: str: Name of the table to delete the row.
index: int: Index of the row to delete.

drop_table(table)#

Drop a table from the database.

Parameters:

tablestr: Name of the table to drop.

execute(command, arguments=None)#

Execute a SQL command in the database.

Parameters:

commandstr: SQL command to execute.
argumentslist or tuple (optional): Arguments to pass to the command. A ‘?’ in the command will be replaced by the argument. If None, no arguments are passed.

Returns:

reslist: List of tuples with the results of the command.

executemany(command, arguments)#

Execute a SQL command in the database using multiple entries.

Parameters:

commandstr: SQL command to execute.
argumentslist or tuple (optional): Arguments to pass to the command. A ‘?’ in the command will be replaced by the argument. If None, no arguments are passed.

Returns:

reslist: List of tuples with the results of the command.

get_column(table, column)#: Get a column from the table.

get_item(table, column, row)#

Get an item from the table.

Parameters:

table: str: Name of the table to get the item.
column: str: Name of the column to get the item.
row: int: Index of the row to get the item.

Returns:

resobject: The item value in the table.

get_row(table, index)#

Get a row from the table.

Parameters:

table: str: Name of the table to get the row.
index: int: Index of the row to get.

Returns:

resSQLRow: The row object viewer.

get_table(table)#

Get a table from the database.

Parameters:

tablestr: Name of the table to get.

Returns:

resSQLTable: The table object viewer.

index_of(table, where)#: Get the index(es) where a given condition is satisfied.

select(table, columns=None, where=None, order=None, limit=None, offset=None)#

Select rows from a table.

Parameters:

table: str: Name of the table to select from.
columnslist (optional): List of columns to select. If None, select all columns.
wheredict (optional): Dictionary of conditions to select rows. Keys are column names, values are values to compare. If it is a dict of values, all rows equal to the values will be selected. If it is a dict of Where objects, the conditions will be combined with the AND operator. If None, all rows are selected.
orderstr (optional): Column name to order by.
limitint (optional): Number of rows to select.
offsetint (optional): Number of rows to skip before selecting.

Returns:

reslist: List of tuples with the selected rows. Each row values will be returned in a tuple in the same order as the columns.

set_column(table, column, data)#: Set a column in the table.

set_item(table, column, row, value)#

Set a value in a cell.

Parameters:

table: str: Name of the table to set the item.
column: str: Name of the column to set the item.
row: int: Index of the row to set the item.
value: object: Value to set in the cell.

set_row(table, row, data)#

Set a row in the table.

Parameters:

table: str: Name of the table to set the row.
row: int: Index of the row to set.
data: dict, list or `~numpy.ndarray`: Data to set in the row. If dict, keys are column names, if list, the order of the values is the same as the order of the column names. If ndarray, dtype names are interpreted as column names.