ManageDB module

class ManageDB.ManageDBSettingsHandler

Bases: object

Class for holding the settings for ManageDB

settings = None
class ManageDB.ManageDBSignalHandler

Bases: PyQt5.QtCore.QObject

Object for emitting the database changed signal

database_changed_signal
emit_database_changed_signal()

Emits the database changed signal

class ManageDB.UpdateDatabaseProgressDialogController(parent_widget=None)

Bases: object

Controls the Update Database Progress Dialog

on_progress_changed(progress)

Invoked when the progress of the worker changes

Parameters

progress (int) – the new progress completed

on_status_changed(status)

Invoked when the status of the worker changes

Parameters

status (str) – the new status of the worker

on_task_finished(task)

Invoked when the worker finishes a task

Parameters

task (str) – the name of the task that was completed

on_thread_finish(code)

Invoked when the worker’s thread finishes

Parameters

code (int) – the exit code of the thread

update_database(files, recreate_tables)

Updates the database with the given files

Parameters

  • files (Sequence[Dict[str, Any]]) – a list of files to insert into the database

  • recreate_tables (bool) – whether or not to drop the tables and recreated the tables before inserting

class ManageDB.UpdateDatabaseWorker(files, recreate_tables)

Bases: PyQt5.QtCore.QObject

The worker that updates the database

Parameters

  • files (Sequence[Dict[str, Any]]) – a list of files to insert into the database

  • recreate_tables (bool) – whether or not to drop the tables and recreated the tables before inserting

progress_changed_signal
status_changed_signal
task_finished_signal
work()

Performs the work of the worker

worker_finished_signal
ManageDB.backup_costs_data(report_type)

Backs up the data in the costs table in a file in the costs directory

Parameters

report_type (str) – the type of the report (master report name)

ManageDB.cost_chart_search_sql_text(report, vendor, name, metric_type, start_month, start_year, end_month, end_year)

Makes the SQL statement to search the database for cost chart data

Parameters

  • report (str) – the kind of the report

  • start_month (int) – the starting month of the search

  • start_year (int) – the starting year of the search

  • end_month (int) – the ending month of the search

  • end_year (int) – the ending year of the search

  • name (str) – the name field (database/item/platform/title) value

  • metric_type (str) – the metric type value

  • vendor (str) – the vendor name you want to search for

Return type

Tuple[str, Sequence[Any]]

Returns

(sql_text, values) a Tuple with the parameterized SQL statement to search the database, and the values for it

ManageDB.create_connection(db_file)

Creates the connection to the database

Parameters

db_file (str) – the file the database is in

Return type

Connection

Returns

the connection to the database

ManageDB.create_cost_table_sql_texts(report_type)

Makes the SQL statement to create the cost tables from the table definition

Parameters

report_type (str) – the type of the report (master report name)

Return type

str

Returns

the sql statement

ManageDB.create_table_sql_texts(report)

Makes the SQL statement to create the tables from the table definition

Parameters

report (str) – the kind of the report

Return type

str

Returns

the sql statement

ManageDB.create_view_sql_texts(report)

Makes the SQL statement to create the views from the table definition

Parameters

report (str) – the kind of the report

Return type

str

Returns

the sql statement

ManageDB.delete_costs_sql_text(report_type, vendor, month, year, name)

Makes the SQL statement to delete data from a cost table

Parameters

  • report_type (str) – the type of the report (master report name)

  • vendor (str) – the vendor name of the cost

  • month (int) – the month of the cost

  • year (int) – the year of the cost

  • name (str) – the name the cost is associated with (database/item/platform/title)

Return type

Tuple[str, Sequence[Sequence[Any]]]

Returns

(sql_text, values) a Tuple with the parameterized SQL statement to delete the costs row, and the values for it

ManageDB.first_time_setup()

Sets up the folders and database when the program is set up for the first time

ManageDB.get_all_cost_files()

Gets the list of the cost files in the costs directory

Return type

Sequence[Dict[str, Any]]

Returns

list of cost files

ManageDB.get_all_report_files()

Gets the list of the report files in the protected directory

Return type

Sequence[Dict[str, Any]]

Returns

list of report files

ManageDB.get_cost_chart_report_fields_list(report)

Gets the fields in the report chart

Parameters

report (str) – the kind of the report

Return type

Sequence[Dict[str, Any]]

Returns

list of fields in this report’s chart

ManageDB.get_cost_fields_list(report_type)

Gets the fields in the report type cost table

Parameters

report_type (str) – the type of the report (master report name)

Return type

Sequence[Dict[str, Any]]

Returns

list of fields in this report type’s cost table

ManageDB.get_costs_sql_text(report_type, vendor=None, name=None)

Makes the SQL statement to get costs from the database

Parameters

  • report_type (str) – the type of the report (master report name)

  • vendor (Optional[str]) – the vendor name of the cost

  • month – the month of the cost

  • year – the year of the cost

  • name (Optional[str]) – the name the cost is associated with (database/item/platform/title)

Return type

Tuple[str, Sequence[Any]]

Returns

(sql_text, values) a Tuple with the parameterized SQL statement to search the database, and the values for it

ManageDB.get_field_attributes(report, field_name)

Gets the field attributes

Parameters

  • report (str) – the kind of the report

  • field_name (str) – the name of the field

Return type

Optional[Dict[str, Any]]

Returns

attributes of the field

ManageDB.get_monthly_chart_report_fields_list(report)

Gets the fields in the report chart

Parameters

report (str) – the kind of the report

Return type

Sequence[Dict[str, Any]]

Returns

list of fields in this report’s monthly chart

ManageDB.get_names_sql_text(report, vendor=None)

Makes the SQL statement to get all the unique name values for a report and vendor

Parameters

  • report (str) – the kind of the report

  • vendor (Optional[str]) – the vendor name of the data in the file

Return type

Tuple[str, Sequence[Any]]

Returns

(sql_text, values) a Tuple with the parameterized SQL statement to search the database, and the values for it

ManageDB.get_names_with_costs_sql_text(report, vendor, start_year, end_year)

Makes the SQL statement to get all the unique name values with costs data in a period for a report and vendor

Parameters

  • report (str) – the kind of the report

  • vendor (str) – the vendor name of the data in the file

  • start_year (int) – the starting year to check for costs data for

  • end_year (int) – the ending year to check for costs data for

Return type

Tuple[str, Sequence[Any]]

Returns

(sql_text, values) a Tuple with the parameterized SQL statement to search the database, and the values for it

ManageDB.get_report_fields_list(report)

Gets the fields in the report table

Parameters

report (str) – the kind of the report

Return type

Sequence[Dict[str, Any]]

Returns

list of fields in this report’s table

ManageDB.get_sql_select_statement(select_fields, from_tables, where_conditions=None, group_by_fields=None, order_by_fields=None, distinct=None, num_extra_tabs=0, is_multiline=True)

Makes a select SQL statement

Parameters

  • select_fields (Sequence[str]) – a list of fields to get; use a list containing only ‘*’ to get all the fields from the tables

  • from_tables (Sequence[str]) – a list of tables to get fields from; assumes inner join

  • where_conditions (Optional[Sequence[Sequence[str]]]) – a list of lists of conditions for the WHERE keyword; assumes in POS form

  • group_by_fields (Optional[Sequence[str]]) – a list of fields for the GROUP BY keyword

  • order_by_fields (Optional[Sequence[str]]) – a list of fields for the ORDER BY keyword

  • distinct (Optional[bool]) – whether to only get distinct rows from the database

  • num_extra_tabs (int) – the number of tabs to put at the start of each line of the statement

  • is_multiline (bool) – whether or not to break the statement into multiple lines

Return type

str

Returns

the SQL statement to send the database

ManageDB.get_top_number_chart_report_fields_list(report)

Gets the fields in the report top # chart

Parameters

report (str) – the kind of the report

Return type

Sequence[Dict[str, Any]]

Returns

list of fields in this report’s top # chart

ManageDB.get_view_report_fields_list(report)

Gets the fields in the report month view

Parameters

report (str) – the kind of the report

Return type

Sequence[Dict[str, Any]]

Returns

list of fields in this report’s month view

ManageDB.get_yearly_chart_report_fields_list(report)

Gets the fields in the report chart

Parameters

report (str) – the kind of the report

Return type

Sequence[Dict[str, Any]]

Returns

list of fields in this report’s monthly chart

ManageDB.insert_single_cost_file(report_type, file_path, emit_signal=True)

Inserts a single file’s data into the database

Parameters

  • report_type (str) – the type of the report (master report name)

  • file_path (str) – the path of the file the data is from

  • emit_signal (bool) – whether to emit a signal upon completion

ManageDB.insert_single_file(file_path, vendor, year, emit_signal=True)

Inserts a single file’s data into the database

Parameters

  • file_path (str) – the path of the file the data is from

  • vendor (str) – the vendor name of the data in the file

  • year (int) – the year of the data in the file

  • emit_signal (bool) – whether to emit a signal upon completion

ManageDB.monthly_chart_search_sql_text(report, vendor, name, metric_type, start_month, start_year, end_month, end_year)

Makes the SQL statement to search the database for monthly chart data

Parameters

  • report (str) – the kind of the report

  • start_year (int) – the starting year of the search

  • start_month (int) – the starting month of the search

  • end_year (int) – the ending year of the search

  • end_month (int) – the ending month of the search

  • name (str) – the name field (database/item/platform/title) value

  • metric_type (str) – the metric type value

  • vendor (str) – the vendor name you want to search for

Return type

Tuple[str, Sequence[Any]]

Returns

(sql_text, values) a Tuple with the parameterized SQL statement to search the database, and the values for it

ManageDB.read_costs_file(file_name)

Reads a specific csv/tsv cost file and returns the values for inserting

Parameters

file_name (str) – the name of the file the data is from

Return type

Optional[Sequence[Dict[str, Any]]]

Returns

list of values from the file

ManageDB.read_report_file(file_name, vendor, year)

Reads a specific csv/tsv file and returns the kind of report and the values for inserting

Parameters

  • file_name (str) – the name of the file the data is from

  • vendor (str) – the vendor name of the data in the file

  • year (int) – the year of the data in the file

Return type

Optional[Tuple[str, str, Sequence[Dict[str, Any]]]]

Returns

(file_name, report, values) a Tuple with the file name, the kind of report, and the data from the file

ManageDB.replace_costs_sql_text(report_type, data)

Makes the SQL statement to ‘replace or insert’ data into a cost table

Parameters

  • report_type (str) – the type of the report (master report name)

  • data (Sequence[Dict[str, Any]]) – the new data for the table

Return type

Tuple[str, Sequence[Sequence[Any]]]

Returns

(sql_text, values) a Tuple with the parameterized SQL statement to ‘replace or insert’ the costs, and the values for it

ManageDB.replace_sql_text(file_name, report, data)

Makes the SQL statements to delete old data from a table and ‘replace or insert’ data into a table

Parameters

  • file_name (str) – the name of the file the data is from

  • report (str) – the kind of the report

  • data (Sequence[Dict[str, Any]]) – the data from the file

Return type

Tuple[str, Sequence[Sequence[Any]], str, Sequence[Sequence[Any]]]

Returns

(sql_delete_text, delete_values, sql_replace_text, replace_values) a Tuple with the parameterized SQL statement to delete the old data, the values for it, the parameterized SQL statement to ‘replace or insert’ data into the table, and the values for it

ManageDB.run_select_sql(connection, sql_text, data=None)

Runs the SQL statement to get data from the database

Parameters

  • connection (Connection) – the connection to the database

  • sql_text (str) – the SQL statement

  • data (Optional[Sequence[Any]]) – the parameters to the SQL statement

Return type

Optional[Sequence[Sequence[Any]]]

Returns

a list of rows that return from the statement

ManageDB.run_sql(connection, sql_text, data=None, emit_signal=True)

Runs the SQL statement to modify the database

Parameters

  • connection (Connection) – the connection to the database

  • sql_text (str) – the SQL statement

  • data (Optional[Sequence[Sequence[Any]]]) – the parameters to the SQL statement

  • emit_signal (bool) – whether to emit a signal upon completion

ManageDB.search_sql_text(report, start_year, end_year, search_parameters)

Makes the SQL statement to search the database based on a search

Parameters

  • report (str) – the kind of the report

  • start_year (int) – the starting year of the search

  • end_year (int) – the ending year of the search

  • search_parameters (Sequence[Sequence[Dict[str, Any]]]) – list of search parameters in POS form

Return type

Tuple[str, Sequence[Any]]

Returns

(sql_text, values) a Tuple with the parameterized SQL statement to search the database, and the values for it

ManageDB.setup_database(drop_tables, emit_signal=True)

Sets up the database

Parameters

  • drop_tables (bool) – whether to drop the tables before creating them

  • emit_signal (bool) – whether to emit a signal upon completion

ManageDB.top_number_chart_search_sql_text(report, vendor, metric_type, number, start_month, start_year, end_month, end_year)

Makes the SQL statement to search the database for ranking chart data

Parameters

  • report (str) – the kind of the report

  • start_month (int) – the starting month of the search

  • start_year (int) – the starting year of the search

  • end_month (int) – the ending month of the search

  • end_year (int) – the ending year of the search

  • metric_type (str) – the metric type value

  • vendor (str) – the vendor name you want to search for

  • number (int) – the number to show of the top months

Return type

Tuple[str, Sequence[Any]]

Returns

(sql_text, values) a Tuple with the parameterized SQL statement to search the database, and the values for it

ManageDB.update_settings(settings)

Called when the settings are saved

Parameters

settings (SettingsModel) – the new settings

ManageDB.update_vendor_in_all_tables(old_name, new_name)

Updates the vendor’s name in all tables

Parameters

  • old_name (str) – the old name of the vendor

  • new_name (str) – the new name of the vendor

ManageDB.update_vendor_name_sql_text(table, old_name, new_name)

Makes the SQL statement to update the vendor’s name in a table

Parameters

  • table (str) – the name of the table to replace in

  • old_name (str) – the old name of the vendor

  • new_name (str) – the new name of the vendor

Return type

Tuple[str, Sequence[Sequence[Any]]]

Returns

(sql_text, values) a Tuple with the parameterized SQL statement to update the vendor name in the table, and the values for it

ManageDB.yearly_chart_search_sql_text(report, vendor, name, metric_type, start_month, start_year, end_month, end_year)

Makes the SQL statement to search the database for yearly chart data

Parameters

  • report (str) – the kind of the report

  • start_month (int) – the starting month of the search

  • start_year (int) – the starting year of the search

  • end_month (int) – the ending month of the search

  • end_year (int) – the ending year of the search

  • name (str) – the name field (database/item/platform/title) value

  • metric_type (str) – the metric type value

  • vendor (str) – the vendor name you want to search for

Return type

Tuple[str, Sequence[Any]]

Returns

(sql_text, values) a Tuple with the parameterized SQL statement to search the database, and the values for it