sql_databases module

class sql_databases.SQLHandler(secret, mysql_db_endpoint, database)

Bases: object

Enables managing data in a mysql database such as AWS RDS cloud service.

Example use:

secret = get_secret(config['secret_name'], config['region_name'])

with SQLHandler(secret, config['mysql_db_endpoint'], config['database_name']) as sql:
    sql.create_db()
    #reconnect to new database
    sql.connect()
Parameters:

  • secret (dict) – Contains the username and password needed for database access.

  • mysql_db_endpoint (str) – Location of the database

  • database (str) – The name of the database to connect with. If the database does not exist, the object will create the database and restablish connection specificaly to the database.

Return type:

None.

database_exist()

Check if the database exists.

Return type:

bool

Returns:

If true the database exists.

Return type:

bool

connect()

Establish connection to the database.

Return type:

None

Return type:

None.

create_db()

Create the database if it does not exist.

Return type:

None

Return type:

None.

delete_db()

Attempt to delete the database, but will gracefully exit if there are existing connections to the database.

Return type:

None

Return type:

None.

create_table(table_name, columns)

Create a table with the schema define by the ‘columns’. The unique table key labeled as (id)

Return type:

None

Parameters:

  • table_name (str) –

  • columns (list[str]) –

    List of sql schema strings such as:

    columns = [

    ‘batch int’, ‘flowrate double’

    ]

Return type:

None

get_tables()

Returns a list of all the tables in the specified database.

Return type:

list

Returns:

Table names

Return type:

list

send_sql_data(table_name, columns, data)

Insert the data provided in the ‘data’ list into the SQL table. The function will batch submit data.

Return type:

None

Parameters:

  • table_name (str) –

  • columns (list[str]) – Columns in the SQL database which correspond to the columns provied in ‘data’

  • data (list) – List of data of anytype and is converted to string before inserted into the SQL database

Return type:

None

delete_sql_data_table(table_name, delete_table=True)

Delete all data in the table. The table itself will not be deleted unless specified.

Return type:

None

Parameters:

  • table_name (str) –

  • delete_table (bool, optional) – Also delete the table in addition to the data. The default is True.

Return type:

None

get_sql_column_names(table_name)

Return the column names from an SQL table.

Return type:

list[str]

Parameters:

table_name (str) –

Return type:

list[str]

get_sql_data(table_name)

Return a list of all data from the SQL table.

Return type:

list

Parameters:

table_name (str) –

Return type:

list

sql_databases.get_dataframe_results(config, table)

Using the ‘mysql_db_endpoint’ and ‘database_name’ in the config dictionary, obtain all data from the ‘table’ and return in a pandas DataFrame

Return type:

DataFrame

Parameters:

  • config (dict) –

    Dictionary containing:

    ’mysql_db_endpoint’ : location of the sql database ‘database_name’ : name of the database to obtain the data

  • table (str) – Name of the table to obtain the data from.

Returns:

SQL data converted into a pandas dataframe

Return type:

pandas.DataFrame