Python and MySQLdb warnings

Question

I have a program that is exporting MSSQL data and importing it into MySQL. I have a function that is importing as follows:

def importMySql (mycursor,exportedfilename,table,delimiter):
    file_loc = str(sys.path[0] +"\\" +exportedfilename.lower()+".out").replace("\\", "\\\\")
    mycursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY '%s' LINES TERMINATED BY '\r\n'" %(str(file_loc), table, delimiter))

the cursor (MySQLdb) is raising the following warnings:

C:\Users\tfy\Documents\PyProj\UTL (Export, Import, RDF)\eic.py:98: Warning: Data truncated for column 'DateofCharges' at row 1194
  mycursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY '%s' LINES TERMINATED BY '\r\n'" %(str(file_loc), table, delimiter))
C:\Users\tfy\Documents\PyProj\UTL (Export, Import, RDF)\eic.py:98: Warning: Data truncated for column 'DateofCharges' at row 2009
  mycursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY '%s' LINES TERMINATED BY '\r\n'" %(str(file_loc), table, delimiter))
C:\Users\tfy\Documents\PyProj\UTL (Export, Import, RDF)\eic.py:98: Warning: Data truncated for column 'DateofCharges' at row 4793
  mycursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY '%s' LINES TERMINATED BY '\r\n'" %(str(file_loc), table, delimiter))

but I need to control the warning to only output:

Warning: Data truncated for column 'DateofCharges' at row 1194
Warning: Data truncated for column 'DateofCharges' at row 2009
Warning: Data truncated for column 'DateofCharges' at row 4739

I have looked around and found plenty of information that illustrates hows to create custom warnings. However, not sure how I would achieve the above. I do not want to turn off the warnings, I just want to "format" them. I thought about editing the actual MySQLdb file but it is in .egg format and unable to do that. I also played around warning.format() but was unsuccessful.

Thanks!

Answer 1

Using MySQLdb

You could monkey patch MySQLdb to achieve this:

import types

def warning_check(self):
    if not self._warnings:
        self.messages = ()
        return
    self.messages = self._get_db().show_warnings()

Then patch the Cursor object in your function like this:

cur._warning_check = types.MethodType(warning_check, cur)

Then, when you are done executing LOAD DATA.., you can print the messages:

cur.execute("LOAD DATA..")
for msg in cur.messages:
    print "Warning: {msg}".format(msg=msg[2])

Using MySQL Connector/Python

Using MySQL Connector/Python, you would do something like this:

cnx.get_warnings = True
cur.execute("LOAD DATA..")
for msg in cur.fetchwarnings():
    print "Warning: {msg}".format(msg=msg[2])

(Note that you need the client flag set with the connection argument client_flags=[mysql.connector.ClientFlag.LOCAL_FILES])

Answer 2

So this is the easiest way I have found... Not sure why I did not think of this originally... but I simply suppressed the warnings issued by the cursor:

import warnings
warnings.filterwarnings("ignore", category = MySQLdb.Warning)

I then added this code to my importMySql function:

mycursor.execute("SHOW WARNINGS")
warnings = mycursor.fetchall()
for i in range(len(warnings)):
    print "Warning - " +warnings[i][2]

Answer 3

figure this out to use pprint. As the OPs solution the default warnings need be suppressed, and then add the show_warnings function, and then use the new print format.

from warnings import filterwarnings
import MySQLdb as mdb
from pprint import pprint

filterwarnings('ignore', category = mdb.Warning)
con = mdb.connect(...)
cur = con.cursor()
query = "Update table ..."
cur.execute(query)
con.commit()
warnings = con.show_warnings() # return in tuple type
pprint(warnings, width=100, depth=2) # width is the num of characters in each line, and depth is the level of the warnings in the tuple

Answer 4

It is possible to run your mysql code in a subprocess? If so, you can use Python's subprocess to run the mysql code, read output from stdout and format it accordingly. For example, use process.stdout.readline().

You can refer to this question: Starting and Controlling an External Process via STDIN/STDOUT with Python