14.07 Consolidated Financial Reporting
Overview: Script for Creating and Maintaining the quickbooks_consolidated_profit_and_loss
View
profit_and_loss
ViewPurpose:
This Python script is designed to ensure that the profit_and_loss
view in the quickbooks_consolidated
schema of the PostgreSQL database is created and properly maintained. The script checks if the view exists every 5 minutes, and if it does not exist, the script creates the view and grants the metabase
user read access to it.
Functionality:
Connection to PostgreSQL:
The script connects to the PostgreSQL database using credentials stored in a
.env
file. The credentials include the database host, name, user, password, and port.
Checking for the View:
The script checks whether the
profit_and_loss
view exists in thequickbooks_consolidated
schema by querying theinformation_schema
.
Creating the View (if it doesn't exist):
If the view does not exist, the script executes a SQL query to create it. The view is composed of a union of several
profit_and_loss
tables from different schemas, excluding specific account classes.
Granting Permissions to
metabase
:After the view is created, the script grants
SELECT
permission on the view to themetabase
user to ensure that Metabase can access the view for reporting purposes.
Error Logging:
The script uses Python’s built-in
logging
module to log any errors that occur during execution. Errors are recorded in a log file located in alogs
directory.The
RotatingFileHandler
is used to limit the size of the log file to 1MB, with only one backup kept to prevent excessive log file accumulation.
Setup on the Mac Mini:
Virtual Environment:
A virtual environment is set up in the project directory (
/Users/joshuaholt/hermes/projects/07-consolidated-financial-reporting
) to isolate dependencies. The virtual environment contains all necessary Python packages, which were installed usingpip3
.
Shell Script:
A shell script (
run_script.sh
) is created to activate the virtual environment and run the main Python script (main.py
).The shell script includes the following commands:
The script is marked as executable with the
chmod +x
command.
Cron Job:
A cron job is set up to run the shell script every 5 minutes. The cron job is added to the crontab using the following command:
This command ensures that the script runs every 5 minutes and logs the output (both stdout and stderr) to
cron_log.log
in thelogs
directory.
Logs:
Error Log:
The script writes error messages to
logs/error.log
. The log file is managed byRotatingFileHandler
, which ensures that the log file doesn’t grow too large. Only errors are logged, which helps in focusing on critical issues without cluttering the logs with non-essential information.
Cron Job Log:
The cron job’s output (standard output and errors) is redirected to
logs/cron_log.log
. This file logs every run of the script, capturing any messages or errors that occur during the execution of the cron job.
How to Test and Monitor:
Testing:
Drop the
profit_and_loss
view manually and wait for the cron job to trigger the script. After 5 minutes, check if the view has been recreated. You can also verify that themetabase
user has access to the view.Check
logs/cron_log.log
andlogs/error.log
for any messages or errors during the execution.
Monitoring:
Regularly check the
cron_log.log
anderror.log
files to ensure the script is running smoothly.If any issues arise, investigate the error logs first to understand what might be causing the problem.
Troubleshooting:
Too Many Database Connections:
Ensure that the script properly closes the database connection after each run to prevent connection leaks. The
connection.close()
method in the script ensures that the session is fully disconnected.
Permission Issues:
If the
metabase
user encounters permission issues, verify that theGRANT SELECT
command is executed after the view is created.
Last updated