directory_script_runner (function) ∞
-
directory_script_runner
(log, pathToScriptDirectory, dbConn=False, waitForResult=True, successRule=None, failureRule=None, loginPath=False, databaseName=False, force=True)[source] ∞ A function to run all mysql scripts in a given directory (in a modified date order, oldest first) and then act on the script files in accordance with the succcess or failure of their execution
The function can be run with either with an established database connection (
dbConn
) or with a mysql generatedlogin-path
name (loginPath
).**with
dbConn
**Simply pass the connection
dbConn
established elsewhere in your code.**with
loginPath
**As it’s insecure to pass in mysql database credentials via the command-line, run the following command from the terminal
mysql_config_editor set --login-path=<uniqueLoginName> --host=localhost --user=<myUsername> --password > Enter password:
This will store your database credentials in an encrypted file located at ‘~/.mylogin.cnf’. Use
mysql_config_editor print --all
to see all of the login-paths set.The
directory_script_runner
function can work by taking advantage of mysql’s--login-path
argument so not to require knowledge of the database credentials.Pass the login-path name via the
loginPath
parameter to usedirectory_script_runner
in this manner.If both
dbConn
andloginPath
parameters are given,dbConn
will be given precedent.Key Arguments
log
– loggerpathToScriptDirectory
– the path to the directory containing the sql script to be rundatabaseName
– the name of the databaseforce
– force the script to run, skipping over lines with errors, Default TrueloginPath
– the local-path as set withmysql_config_editor
dbConn
– the database connectionwaitForResult
– wait for the mysql script to finish execution? If ‘False’ the MySQL script will run in background (do not wait for completion), or if ‘delete’ the script will run then delete regardless of success status. Default True. [True|False|delete]successRule
– what to do if script succeeds. Default None [None|delete|subFolderName]failureRule
– what to do if script fails. Default None [None|delete|subFolderName]
Return
None
Usage
To run the scripts in the directroy and not act on the script file use something similar to:
from fundamentals.mysql import directory_script_runner directory_script_runner( log=log, pathToScriptDirectory="/path/to/mysql_scripts", databaseName="imports", loginPath="myLoginDetails" )
To delete successful scripts and archive failed scripts for later inspection:
from fundamentals.mysql import directory_script_runner directory_script_runner( log=log, pathToScriptDirectory="/path/to/mysql_scripts", databaseName="imports", loginPath="myLoginDetails", successRule="delete", failureRule="failed" )
This creates a folder at
/path/to/mysql_scripts/failed
and moves the failed scripts into that folder.Finally to execute the scripts within a directory but not wait for the results to return (much fast but you lose error checking in the MySQL scripts):
from fundamentals.mysql import directory_script_runner directory_script_runner( log=log, pathToScriptDirectory="/path/to/mysql_scripts", databaseName="imports", loginPath="myLoginDetails", waitForResults=False )
Setting
waitForResults
= ‘delete’ will trash the script once it has run (or failed … be very careful!)