Tyler Brown Tyler Brown - 3 months ago 29
MySQL Question

Python and MySQLdb warnings

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

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

    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]