Christian Christian - 5 months ago 20
Python Question

Python warning unicode issue

I know this has been talked about in a few places on the web, but I couldn't find an answer so thought I would ask.

I have the following python code which calls a stored procedure which expects an integer, but I give it a non-ascii £ sign

u'\xa3'
instead, which makes it 'unicode its pants'!

# -*- coding: utf-8 -*-
import MySQLdb
import config.default as cfg

conn = MySQLdb.connect(
host=cfg.db_host,
user=cfg.db_user,
passwd=cfg.db_pass,
db=cfg.db_name,
use_unicode=True
)

curs = conn.cursor(MySQLdb.cursors.DictCursor)

try:
curs.callproc('get_user_by_id', [u'\xa3'])
curs.nextset()
except MySQLdb.Warning, e:
print e.message

curs.close()
conn.close()


The above will cause the following error because the stored procedure expects an
int
so generates a warning, which is returned from the database as unicode:

UnicodeEncodeError: 'ascii' codec can't encode character u'\xa3' in position 26: ordinal not in range(128)


If I set
use_unicode
to its default value of
False
, then I get the warning:

Warning: Incorrect integer value: '£' for column 'p_user_id' at row 1
curs.nextset()


Most of you are probably aware of the unicode Exception issue with python being a little annoying (at least for me and probably non-ascii language ppl)

I found several ways of solving this:

1) override the
_warning_check
function in the
MySQLdb.BaseCursor
and comment out the line
warn(w[-1], self.Warning, 3)
(about line 117)
- didnt like it as I would like to see warnings

2) import warnings and then add
warnings.filterwarnings('ignore', category=MySQLdb.Warning)

- doesn't really solve the problem of other unicode warnings

3) Copy the
warnings.py
file to the root of my app and changed the
formatwarning
function to format the message as unicode:
- seems the best solution so far but is a python core file!

def formatwarning(message, category, filename, lineno, line=None):
"""Function to format a warning the standard way."""
# s = "%s:%s: %s: %s\n" % (filename, lineno, category.__name__, message)
s = u"{}:{}: {}: {}\n".format(filename, lineno, category.__name__, message)
line = linecache.getline(filename, lineno) if line is None else line
if line:
line = line.strip()
s += " %s\n" % line
return s


Surely there is a better way of doing this?

Answer

This doesn't seem to be a problem any more, possibly due to updates in the module or MySQL server - running MySQL 5.7.12.

I have also changed from using use_unicode to setting the charset, so my connection string looks like:

MySQLdb.connect(host=DB_HOST, user=DB_USER, passwd=DB_PSWD, db=DB_NAME, charset='UTF8', connect_timeout=5)

However, I was tweaking a high throughput application and found that normally the Mysqldb module requests the warnings after every execution which cost a few ms. But found that adding curs._defer_warnings = True to the cursor before executing, prevented this, and then you can use self.warnings = curs._warnings to get the number of warnings.

curs = self.conn.cursor(cursors.DictCursor)
curs._defer_warnings = True
try:
    self.affected = curs.execute(sql, args)
    self.warnings = curs._warnings
    if sql.lower().startswith('insert'):
        self.lastrowid = curs.lastrowid
    elif sql.lower().startswith('select'):
        self.data = curs.fetchall()
except MySQLError, e:
    if e.args[0] == 1064:
        raise Exception("MySQL Error [1064]: Error in SQL syntax")
    else:
        raise Exception("MySQL Error [{}]: {}".format(e.args[0], e.args[1]))
finally:
    curs.close()
Comments