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,delimite

Solution 1:

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]

Solution 2:

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

Solution 3:

Using MySQLdb

You could monkey patch MySQLdb to achieve this:

import types

defwarning_check(self):
    ifnot 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])

Solution 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