brokenspoke_analyzer.core.database.dbcore package¶
Define functions used to manipulate database data.
- brokenspoke_analyzer.core.database.dbcore.configure_db(engine, cores, memory_mb, pguser)¶
Configure the database.
Configures the database with the appropriate settings, extensions and schemas.
This function is idempotent.
- Return type:
None
- brokenspoke_analyzer.core.database.dbcore.configure_docker_db(engine)¶
Configure a database running in Docker.
- Return type:
None
- brokenspoke_analyzer.core.database.dbcore.configure_extensions(engine)¶
Configure the required extensions.
- Return type:
None
- brokenspoke_analyzer.core.database.dbcore.configure_schemas(engine, pguser)¶
Configure the schemas.
- Return type:
None
- brokenspoke_analyzer.core.database.dbcore.configure_system(engine, cores, memory_mb)¶
Configure the system parameters.
This requires elevated permissions.
- Return type:
None
- brokenspoke_analyzer.core.database.dbcore.create_psycopg_engine(database_url)¶
Create a SQLAlchemy engine with the psycopg3 driver.
- Return type:
Engine
- brokenspoke_analyzer.core.database.dbcore.execute_query(engine, query)¶
Execute a query and commit it.
- Return type:
None
- brokenspoke_analyzer.core.database.dbcore.execute_sql_file(engine, sqlfile)¶
Execute a SQL file.
- Return type:
None
- brokenspoke_analyzer.core.database.dbcore.execute_with_autocommit(engine, statements)¶
Execute a series of statements with autocommit.
- Return type:
None
- brokenspoke_analyzer.core.database.dbcore.export_to_csv(engine, csvfile, table)¶
Dump the table content into a CSV file.
- Return type:
None
- brokenspoke_analyzer.core.database.dbcore.import_csv_file_with_header(engine, csvfile, table)¶
Import a CSV file into a table.
refs: - https://www.psycopg.org/psycopg3/docs/basic/copy.html#copy - https://www.psycopg.org/articles/2020/11/15/psycopg3-copy/
For some unknown and annoying reason, importing CSV data with a cursor does not work. Therefore we used psql as fallback.
- Return type:
None
- brokenspoke_analyzer.core.database.dbcore.load_csv_file(engine, sqlfile, csvfile, table)¶
Create a table and load the data from the CSV file.
- Return type:
None
- brokenspoke_analyzer.core.database.dbcore.reset_tables(engine)¶
Delete tables and reset the schemas.
Required to get the database to a clean state before a new run.
- Return type:
None
- brokenspoke_analyzer.core.database.dbcore.sanitize_sql_filename(filename)¶
Sanitize a filename for use in PostgreSQL commands by escaping special characters.
This function replaces characters that may cause issues in SQL commands, ensuring that the filename is safe for use in a COPY command. The following characters are handled: - Single quotes (’) are escaped as two single quotes (‘’). - Double quotes (”) are escaped as two double quotes (“”).
Parameters: filename (str): The original filename to be sanitized.
Returns: str: The sanitized filename, safe for use in PostgreSQL commands.
Examples: >>> sanitize_sql_filename(“o’fallon.csv”) “o’’fallon.csv” >>> sanitize_sql_filename(‘file “name”.csv’) ‘file “”name””.csv’
- Return type:
str
- brokenspoke_analyzer.core.database.dbcore.table_exists(engine, table)¶
Check whether a table exists or not.
- Return type:
bool