1#!/usr/local/bin/python 

2# encoding: utf-8 

3""" 

4*Execute a MySQL write query on a database table* 

5 

6:Author: 

7 David Young 

8""" 

9from builtins import str 

10import sys 

11import os 

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

13from fundamentals import tools 

14import time 

15 

16 

17def writequery( 

18 log, 

19 sqlQuery, 

20 dbConn, 

21 Force=False, 

22 manyValueList=False 

23): 

24 """*Execute a MySQL write command given a sql query* 

25 

26 **Key Arguments** 

27 

28 - ``sqlQuery`` -- the MySQL command to execute 

29 - ``dbConn`` -- the db connection 

30 - ``Force`` -- do not exit code if error occurs, move onto the next command 

31 - ``manyValueList`` -- a list of value tuples if executing more than one insert 

32 

33 

34 **Return** 

35 

36 - ``message`` -- error/warning message 

37 

38 

39 **Usage** 

40 

41 Here's an example of how to create a table using the database connection passed to the function: 

42 

43 ```python 

44 from fundamentals.mysql import writequery 

45 sqlQuery = "CREATE TABLE `testing_table` (`id` INT NOT NULL, PRIMARY KEY (`id`))" 

46 message = writequery( 

47 log=log, 

48 sqlQuery=sqlQuery, 

49 dbConn=dbConn, 

50 Force=False, 

51 manyValueList=False 

52 ) 

53 ``` 

54 

55 Here's a many value insert example: 

56 

57 ```python 

58 from fundamentals.mysql import writequery 

59 sqlQuery = "INSERT INTO testing_table (id) values (%s)" 

60 message = writequery( 

61 log=log, 

62 sqlQuery=sqlQuery, 

63 dbConn=dbConn, 

64 Force=False, 

65 manyValueList=[(1,), (2,), (3,), (4,), (5,), (6,), (7,), 

66 (8,), (9,), (10,), (11,), (12,), ] 

67 ) 

68 ``` 

69 

70 """ 

71 log.debug('starting the ``writequery`` function') 

72 import pymysql 

73 import warnings 

74 warnings.filterwarnings('error', category=pymysql.Warning) 

75 message = "" 

76 try: 

77 cursor = dbConn.cursor(pymysql.cursors.DictCursor) 

78 except Exception as e: 

79 log.error('could not create the database cursor.') 

80 # EXECUTE THE SQL COMMAND 

81 

82 log.debug("\nSQLQUERY: %(sqlQuery)s}\n" % locals()) 

83 if sqlQuery[-1] == ",": 

84 sqlQuery = sqlQuery[:-1] 

85 

86 tryAgain = True 

87 tries = 1 

88 while tryAgain: 

89 tryAgain = False 

90 try: 

91 if manyValueList == False: 

92 cursor.execute(sqlQuery) 

93 else: 

94 # cursor.executemany(sqlQuery, manyValueList) 

95 # INSET LARGE LISTS IN BATCHES TO STOP MYSQL SERVER BARFING 

96 batch = 100000 

97 offset = 0 

98 stop = 0 

99 

100 while stop == 0: 

101 thisList = manyValueList[offset:offset + batch] 

102 offset += batch 

103 a = len(thisList) 

104 cursor.executemany(sqlQuery, thisList) 

105 dbConn.commit() 

106 if len(thisList) < batch: 

107 stop = 1 

108 except pymysql.err.InternalError as e: 

109 if tries < 61: 

110 tryAgain = True 

111 log.warning(f"MySQL error: {e}. Attempt {tries}/60.") 

112 tries += 1 

113 else: 

114 log.warning(f"MySQL error: {e}. Attempt {tries}/60 failed. ") 

115 raise 

116 

117 except pymysql.err.ProgrammingError as e: 

118 message = 'MySQL write command not executed for this query: << %s >>\nThe error was: %s \n' % (sqlQuery, 

119 str(e)) 

120 if Force == False: 

121 log.error(message) 

122 raise 

123 else: 

124 log.warning(message) 

125 except pymysql.Error as e: 

126 try: 

127 e = e.args 

128 except: 

129 pass 

130 

131 if e[0] == 1050 and 'already exists' in e[1]: 

132 log.info(str(e) + '\n') 

133 elif e[0] == 1062: 

134 # Duplicate Key error 

135 log.debug('Duplicate Key error: %s\n' % (str(e), )) 

136 message = "duplicate key error" 

137 elif e[0] == 1061: 

138 # Duplicate Key error 

139 log.debug('index already exists: %s\n' % (str(e), )) 

140 message = "index already exists" 

141 elif "Duplicate entry" in str(e): 

142 log.debug('Duplicate Key error: %s\n' % (str(e), )) 

143 message = "duplicate key error" 

144 elif "Deadlock" in str(e): 

145 i = 0 

146 while i < 10: 

147 time.sleep(1) 

148 i += 1 

149 try: 

150 if manyValueList == False: 

151 cursor.execute(sqlQuery) 

152 else: 

153 # cursor.executemany(sqlQuery, manyValueList) 

154 # INSET LARGE LISTS IN BATCHES TO STOP MYSQL SERVER 

155 # BARFING 

156 batch = 100000 

157 offset = 0 

158 stop = 0 

159 

160 while stop == 0: 

161 thisList = manyValueList[offset:offset + batch] 

162 offset += batch 

163 a = len(thisList) 

164 cursor.executemany(sqlQuery, thisList) 

165 dbConn.commit() 

166 if len(thisList) < batch: 

167 stop = 1 

168 i = 20 

169 except: 

170 pass 

171 if i == 10: 

172 log.error('Deadlock: %s\n' % (str(e), )) 

173 message = "Deadlock error" 

174 raise 

175 

176 else: 

177 message = 'MySQL write command not executed for this query: << %s >>\nThe error was: %s \n' % (sqlQuery, 

178 str(e)) 

179 if Force == False: 

180 log.error(message) 

181 raise 

182 else: 

183 log.warning(message) 

184 

185 except pymysql.Warning as e: 

186 log.info(str(e)) 

187 except Exception as e: 

188 if "truncated" in str(e): 

189 log.error('%s\n Here is the sqlquery:\n%s\n' % 

190 (str(e), sqlQuery)) 

191 if manyValueList: 

192 log.error('... and the values:\n%s\n' % (thisList, )) 

193 elif "Duplicate entry" in str(e): 

194 log.warning('Duplicate Key error: %s\n' % (str(e), )) 

195 message = "duplicate key error" 

196 else: 

197 log.error( 

198 'MySQL write command not executed for this query: << %s >>\nThe error was: %s \n' % 

199 (sqlQuery, str(e))) 

200 if Force == False: 

201 sys.exit(0) 

202 cursor.close() 

203 return -1 

204 dbConn.commit() 

205 # CLOSE THE CURSOR 

206 cOpen = True 

207 count = 0 

208 while cOpen: 

209 try: 

210 cursor.close() 

211 cOpen = False 

212 except Exception as e: 

213 time.sleep(1) 

214 count += 1 

215 if count == 10: 

216 log.warning('could not close the db cursor ' + str(e) + '\n') 

217 raise e 

218 count = 0 

219 

220 log.debug('completed the ``writequery`` function') 

221 return message