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