Database Communication

mswh.comm.sql module

class mswh.comm.sql.Sql(path_OR_dbconn)

Bases: object

Performs python-sqlite db communication.

Parameters:

path_OR_dbconn: str or a database connection instance

Full path to a database file or an already instantiated connection object

commit(sql_command, close=False)

Execute a custom sql command

Parameters:

sql_command: string

sql_command to execute

Returns:

close: boolean, default=False

If True, closes the connection to db

csv2table(path_to_csv, table_name, column_label_row=0, converters=None, close=False)

Use to update bulk price or performance data. If same named table exists, it gets replaced

Parameters:

path_to_csv: str

Full path to the csv table

table_name: str

sql table name of choice

column_label_row: int, default=0

Index of the row which gets converted into column labels

converters: dict, default=None

According to pandas documentation: Dict of functions for converting values in columns. Keys can be integers or column labels.

close: boolean, default=False

If True, closes the connection to db

pd2table(df, table_name, close=False)

Write a dataframe out to the database. If same named table exists, it gets replaced

Parameters:

table_name: str

sql table name

close: boolean, default=False

If True, closes the connection to db

table2pd(table_name, column_label_row=0)

Reads in a single sql table.

Parameters:

table_name: str

sql table name

column_label_row: int, default=0

Index of the row which gets converted into column labels

Returns:

df: pandas dataframe

Sql table read in as a pandas df.

tables2dict(close=True)

Reads all tables contained in a sql database and converts them to a pandas dataframe.

Parameters:

close: boolean, default=True

If True, closes the connection to db

Returns:

data: dict of pandas dataframes

Saves each of the sql tables as a pandas dataframe under a sql table name as a key