Coverage for fundamentals/mysql/directory_script

Hot-keys on this page

r m x p toggle line displays

j k next/prev highlighted chunk

0 (zero) top of page

1 (one) first highlighted chunk

1#!/usr/local/bin/python

2# encoding: utf-8

3"""

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

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

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

10This store's your credentials in an encrypted file located at '~/.mylogin.cnf'.

11Use `mysql_config_editor print --all` to see all of the login-paths set.

13Usage:

14 mysqlSucker <pathToDirectory> <loginPath> <databaseName> [-s successRule -f failureRule]

16 pathToDirectory path to the directory containing the sql scripts to run (scripts must have `.sql` extension)

17 loginPath the local-path as set with `mysql_config_editor` (`mysqlSucker -h` for more details)

18 databaseName the name of the database to execute the scripts within

20Options:

21 -h, --help show this help message

22 -s successRule, --success successRule what to do if script succeeds. Default *None* [None|delete|subFolderName]

23 -f failureRule, --failure failureRule what to do if script fails. Default *None* [None|delete|subFolderName]

25:Examples:

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

29 mysqlSucker /path/to/scriptdir myLoginPath myDatabaseName

31 To delete script after thay have executed successfully:

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

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

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

39:Author:

40 David Young

41"""

42from builtins import str

43import sys

44import os

45import time

46import collections

47import datetime

48from subprocess import Popen, PIPE, STDOUT

49os.environ['TERM'] = 'vt100'

50from fundamentals import tools

53def directory_script_runner(

54 log,

55 pathToScriptDirectory,

56 dbConn=False,

57 waitForResult=True,

58 successRule=None,

59 failureRule=None,

60 loginPath=False,

61 databaseName=False,

62 force=True):

63 """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

65 The function can be run with either with an established database connection (`dbConn`) or with a mysql generated `login-path` name (`loginPath`).

67 **with `dbConn`**

69 Simply pass the connection `dbConn` established elsewhere in your code.

71 **with `loginPath`**

73 As it's insecure to pass in mysql database credentials via the command-line, run the following command from the terminal

75 ```bash

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

77 > Enter password:

78 ```

80 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.

82 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.

84 Pass the login-path name via the `loginPath` parameter to use `directory_script_runner` in this manner.

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

88 **Key Arguments**

90 - ``log`` -- logger

91 - ``pathToScriptDirectory`` -- the path to the directory containing the sql script to be run

92 - ``databaseName`` -- the name of the database

93 - ``force`` -- force the script to run, skipping over lines with errors, Default *True*

94 - ``loginPath`` -- the local-path as set with `mysql_config_editor`

95 - ``dbConn`` -- the database connection

96 - ``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]

97 - ``successRule`` -- what to do if script succeeds. Default *None* [None|delete|subFolderName]

98 - ``failureRule`` -- what to do if script fails. Default *None* [None|delete|subFolderName]

101 **Return**

103 - None

106 **Usage**

108 To run the scripts in the directroy and not act on the script file use something similar to:

109

110 ```python

111 from fundamentals.mysql import directory_script_runner

112 directory_script_runner(

113 log=log,

114 pathToScriptDirectory="/path/to/mysql_scripts",

115 databaseName="imports",

116 loginPath="myLoginDetails"

117 )

118 ```

119

120 To delete successful scripts and archive failed scripts for later inspection:

121

122 ```python

123 from fundamentals.mysql import directory_script_runner

124 directory_script_runner(

125 log=log,

126 pathToScriptDirectory="/path/to/mysql_scripts",

127 databaseName="imports",

128 loginPath="myLoginDetails",

129 successRule="delete",

130 failureRule="failed"

131 )

132 ```

133

134 This creates a folder at `/path/to/mysql_scripts/failed` and moves the failed scripts into that folder.

135

136 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):

137

138 ```python

139 from fundamentals.mysql import directory_script_runner

140 directory_script_runner(

141 log=log,

142 pathToScriptDirectory="/path/to/mysql_scripts",

143 databaseName="imports",

144 loginPath="myLoginDetails",

145 waitForResults=False

146 )

147 ```

148

149 Setting ``waitForResults`` = 'delete' will trash the script once it has run (or failed ... be very careful!)

150

151 """

152 log.debug('starting the ``directory_script_runner`` function')

153

154 # COMPILE A DICTIONARY OF SCRIPTS / MODIFIED TIMES

155 scriptList = {}

156 for d in os.listdir(pathToScriptDirectory):

157 filePath = os.path.join(pathToScriptDirectory, d)

158 filename = os.path.basename(filePath)

159 extension = filePath.split(".")[-1]

160 if os.path.isfile(filePath) and extension == "sql":

161 modified = datetime.datetime.strptime(time.ctime(

162 os.path.getmtime(filePath)), "%a %b %d %H:%M:%S %Y")

163 scriptList[str(modified) + filename] = filePath

164

165 # ORDER THE DICTIONARY BY MODIFIED TIME - OLDEST FIRST

166 scriptList = collections.OrderedDict(sorted(scriptList.items()))

167

168 # FORCE MYSQL SCRIPT?

169 if force:

170 force = "--force"

171 else:

172 force = ""

173

174 if dbConn:

175 for k, v in list(scriptList.items()):

176 scriptname = os.path.basename(v)

177 exception = execute_mysql_script(

178 pathToScript=v,

179 log=log,

180 dbConn=dbConn

181 )

182 # FAILED SCRIPTS

183 if exception:

184 log.error(

185 'The script %(scriptname)s failed with the following exception: "%(exception)s"' % locals())

186 rule = failureRule

187 print(exception)

188 else:

189 rule = successRule

190 _process_script_file(

191 pathToScriptDirectory=pathToScriptDirectory,

192 scriptname=scriptname,

193 rule=rule,

194 log=log)

195 else:

196 for k, v in list(scriptList.items()):

197 scriptname = os.path.basename(v)

198 if waitForResult == True:

199 cmd = """mysql --login-path=%(loginPath)s %(force)s %(databaseName)s < "%(v)s" """ % locals(

200 )

201 p = Popen(cmd, stdout=PIPE, stderr=PIPE, close_fds=True,

202 env={'PATH': os.getenv('PATH') + ":/usr/local/bin:/usr/bin:/usr/bin:/usr/local/mysql/bin", "MYSQL_TEST_LOGIN_FILE": os.getenv('HOME') + "/.mylogin.cnf"}, shell=True)

203 stdout, stderr = p.communicate()

204

205 if len(stderr):

206 log.error(

207 "MySQL Script `%(scriptname)s` Failed: '%(stderr)s'" % locals())

208 rule = failureRule

209 else:

210 rule = successRule

211 _process_script_file(

212 pathToScriptDirectory=pathToScriptDirectory,

213 scriptname=scriptname,

214 rule=rule,

215 log=log)

216 else:

217 if waitForResult == "delete":

218 cmd = """mysql --login-path=%(loginPath)s %(force)s %(databaseName)s < "%(v)s" > /dev/null 2>&1 & rm "%(v)s" """ % locals()

219 else:

220 cmd = """mysql --login-path=%(loginPath)s %(force)s %(databaseName)s < "%(v)s" > /dev/null 2>&1 """ % locals()

221 p = Popen(cmd, close_fds=True,

222 env={'PATH': os.getenv('PATH') + ":/usr/local/bin:/usr/bin:", "MYSQL_TEST_LOGIN_FILE": os.getenv('HOME') + "/.mylogin.cnf"}, shell=True, stdin=None, stdout=None, stderr=None)