mt.sql.sqlite

Base functions dealing with an sqlite3 file database.

Functions

• list_schemas(): Lists all schemas/attached databases of an sqlite engine.

• rename_table(): Renames a table of a schema.

• drop_table(): Drops a table if it exists, with restrict or cascade options.

• rename_column(): Renames a column of a table.

• get_table_sql_code(): Gets the SQL string of a table.

• list_indices(): Lists all table indices.

• make_index(): Makes an index via a given column of a table.

• vacuum(): Makes the sqlite file as compact as possible.

• clone_database(): Clones an sqlite3 db from a source filepath to a target filepath.

mt.sql.sqlite.list_schemas(engine, nb_trials: int = 3, logger=None)

Lists all schemas/attached databases of an sqlite engine.

Parameters:

• engine (sqlalchemy.engine.Engine) – connection engine to an sqlite3 database

• nb_trials (int) – number of query trials

• logger (logging.Logger or None) – logger for debugging

Returns:

a dataframe containing columns ‘name’ and ‘file’ representing currently attached database names and files

Return type:

pandas.DataFrame

mt.sql.sqlite.rename_table(old_table_name, new_table_name, engine, schema: str | None = None, nb_trials: int = 3, logger=None)

Renames a table of a schema.

Parameters:

• old_table_name (str) – old table name

• new_table_name (str) – new table name

• engine (sqlalchemy.engine.Engine) – an sqlalchemy connection engine created by function create_engine()

• schema (str, optional) – a valid schema name returned from list_schemas()

• nb_trials (int) – number of query trials

• logger (logging.Logger or None) – logger for debugging

Return type:

whatever exec_sql() returns

mt.sql.sqlite.drop_table(table_name, engine, schema: str | None = None, nb_trials: int = 3, logger=None)

Drops a table if it exists, with restrict or cascade options.

Parameters:

• table_name (str) – table name

• engine (sqlalchemy.engine.Engine) – an sqlalchemy connection engine created by function create_engine()

• schema (str, optional) – a valid schema name returned from list_schemas()

• nb_trials (int) – number of query trials

• logger (logging.Logger or None) – logger for debugging

Return type:

whatever exec_sql() returns

mt.sql.sqlite.rename_column(table_name, old_column_name, new_column_name, engine, schema: str | None = None, nb_trials: int = 3, logger=None)

Renames a column of a table.

Parameters:

• table_name (str) – table name

• old_column_name (str) – old column name

• new_column_name (str) – new column name

• engine (sqlalchemy.engine.Engine) – an sqlalchemy connection engine to a sqlite3 database

• schema (str, optional) – a valid schema name returned from list_schemas()

• nb_trials (int) – number of query trials

• logger (logging.Logger or None) – logger for debugging

mt.sql.sqlite.get_table_sql_code(table_name, engine, nb_trials: int = 3, logger=None)

Gets the SQL string of a table.

Parameters:

• table_name (str) – table name

• engine (sqlalchemy.engine.Engine) – an sqlalchemy sqlite3 connection engine created by function create_engine()

• nb_trials (int) – number of query trials

• logger (logging.Logger or None) – logger for debugging

Returns:

retval – SQL query string defining the table

Return type:

str

mt.sql.sqlite.list_indices(engine, nb_trials: int = 3, logger=None)

Lists all table indices.

Parameters:

• engine (sqlalchemy.engine.Engine) – an sqlalchemy sqlite3 connection engine created by function create_engine()

• nb_trials (int) – number of query trials

• logger (logging.Logger or None) – logger for debugging

Returns:

index_map – a {table_name: index_dict} dictionary mapping each table to a dictionary. Only tables with at least one index are listed. Each table-level dictionary is a mapping that maps an indexed column of the table to an SQL query that defines the index.

Return type:

dict

mt.sql.sqlite.make_index(table_name: str, index_col: str, engine, nb_trials: int = 3, logger=None)

Makes an index via a given column of a table.

Parameters:

• table_name (str) – table name

• index_col (str) – name of the column to be indexed

• engine (sqlalchemy.engine.Engine) – an sqlalchemy sqlite3 connection engine created by function create_engine()

• nb_trials (int) – number of query trials

• logger (logging.Logger or None) – logger for debugging

Returns:

True if a new index has been created. False if the index exists

Return type:

bool

mt.sql.sqlite.vacuum(engine)

Makes the sqlite file as compact as possible.

Parameters:

engine (sqlalchemy.engine.Engine) – connection engine to an sqlite3 database

mt.sql.sqlite.clone_database(src_filepath, dst_filepath, logger=None)

Clones an sqlite3 db from a source filepath to a target filepath.

Parameters:

• src_filepath (str) – filepath to the source database

• dst_filepath (str) – filepath to the target database

• logger (logging.Logger or None) – logger for debugging

Notes

This function is not yet working properly.