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 generated login-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 use directory_script_runner in this manner.

If both dbConn and loginPath parameters are given, dbConn will be given precedent.

Key Arguments

  • log – logger

  • pathToScriptDirectory – the path to the directory containing the sql script to be run

  • databaseName – the name of the database

  • force – force the script to run, skipping over lines with errors, Default True

  • loginPath – the local-path as set with mysql_config_editor

  • dbConn – the database connection

  • waitForResult – 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!)