fundamentals.mysql.directory_script_runner

Given a directory of MySQL scripts, execute the scripts and process the script files according to their success or failure

Run the following code once to set a login-path for your mysql server:

mysql_config_editor set --login-path=<uniqueLoginName> --host=localhost --user=<myUsername> --password

This store’s your credentials in an encrypted file located at ‘~/.mylogin.cnf’. Use mysql_config_editor print --all to see all of the login-paths set.

Usage: mysqlSucker [-s successRule -f failureRule]

pathToDirectory       path to the directory containing the sql scripts to run (scripts must have `.sql` extension)
loginPath             the local-path as set with `mysql_config_editor` (`mysqlSucker -h` for more details)
databaseName          the name of the database to execute the scripts within

Options: -h, –help show this help message -s successRule, –success successRule what to do if script succeeds. Default None [None|delete|subFolderName] -f failureRule, –failure failureRule what to do if script fails. Default None [None|delete|subFolderName]

Examples:

To simply execute the scripts in a directory you can run:

mysqlSucker /path/to/scriptdir myLoginPath myDatabaseName

To delete script after thay have executed successfully:

mysqlSucker /path/to/scriptdir myLoginPath myDatabaseName -s delete

To move successful script to a passed sub-directory of /path/to/scriptdir and failed scripts to a failed sub-directory

mysqlSucker /path/to/scriptdir myLoginPath myDatabaseName -s pass -f failed

Author

David Young

Module Contents

Functions

directory_script_runner	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
execute_mysql_script	execute a mysql script given its file path and return the success or failure status of the execution
main	The main function used when directory_script_runner.py is run as a single script from the cl, or when installed as a cl command

API

fundamentals.mysql.directory_script_runner.directory_script_runner(log, pathToScriptDirectory, dbConn=False, waitForResult=True, successRule=None, failureRule=None, loginPath=False, databaseName=False, force=True)

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!)

fundamentals.mysql.directory_script_runner.execute_mysql_script(pathToScript, dbConn, log)

execute a mysql script given its file path and return the success or failure status of the execution

Key Arguments:

• pathToScript – path to the sql script to be executed

• dbConn – mysql database connection

• log – logger

Return:

• exception – None or the exception thrown during execution of the sql script.

Usage:

To execute a mysql script file located on your local file system:

from fundamentals.mysql import execute_mysql_script
exception = execute_mysql_script(
    pathToScript="/path/to/my/script.sql",
    log=log,
    dbConn=dbConn
)

if exception:
    ...

fundamentals.mysql.directory_script_runner.main(arguments=None)

The main function used when directory_script_runner.py is run as a single script from the cl, or when installed as a cl command