当UnicodeEncodeError显示警告时，MySQLdb

Question

我使用的: Python2.7 / MySQLdb 1.2.3

当我使用MySQLdb.cursors执行INSERT IGNORE INTO reporter('张三', '2013-11-11'), ('张三', '2013-11-11')时，

如果出现这样的UnicodeEncodeError错误，则会在显示警告时发生

File "/usr/local/lib/python2.7/dist-packages/MySQL_python-1.2.3-py2.7-linux-i686.egg/MySQLdb/cursors.py", line 224, in executemany
    if not self._defer_warnings: self._warning_check()
  File "/usr/local/lib/python2.7/dist-packages/MySQL_python-1.2.3-py2.7-linux-i686.egg/MySQLdb/cursors.py", line 92, in _warning_check
    warn(w[-1], self.Warning, 3)
  File "/usr/lib/python2.7/warnings.py", line 29, in _show_warning
    file.write(formatwarning(message, category, filename, lineno, line))
  File "/usr/lib/python2.7/warnings.py", line 38, in formatwarning
    s =  "%s:%s: %s: %s\n" % (filename, lineno, category.__name__, message)
UnicodeEncodeError: 'ascii' codec can't encode characters in position 17-18: ordinal not in range(128)

我该怎么办？

这是我的密码：

# -*- coding: utf-8 -*-
import MySQLdb

db_conn = MySQLdb.connect(
    host='localhost', user='root', passwd='', charset='utf8', db='test')
cursor = db_conn.cursor()
cursor.executemany(
    'INSERT IGNORE INTO unicode_test values(%s, %s)',
    [('张三', '2013-11-11'), ('张三', '2013-11-11')])
db_conn.commit()
cursor.close()
db_conn.close()

它来了

Traceback (most recent call last):
  File "/home/cooper/Document/20131106.py", line 9, in <module>
    [('张三', '2013-11-11'), ('张三', '2013-11-11')])
  File "/usr/local/lib/python2.7/dist-packages/MySQL_python-1.2.3-py2.7-linux-i686.egg/MySQLdb/cursors.py", line 224, in executemany
    if not self._defer_warnings: self._warning_check()
  File "/usr/local/lib/python2.7/dist-packages/MySQL_python-1.2.3-py2.7-linux-i686.egg/MySQLdb/cursors.py", line 92, in _warning_check
    warn(w[-1], self.Warning, 3)
  File "/usr/lib/python2.7/warnings.py", line 29, in _show_warning
    file.write(formatwarning(message, category, filename, lineno, line))
  File "/usr/lib/python2.7/warnings.py", line 38, in formatwarning
    s =  "%s:%s: %s: %s\n" % (filename, lineno, category.__name__, message)
UnicodeEncodeError: 'ascii' codec can't encode characters in position 17-18: ordinal not in range(128)
[Finished in 0.2s with exit code 1]

这是我在MariaDB 10.0中使用的表

CREATE TABLE `unicode_test` (
 `name` varchar(10) NOT NULL,
 `date_of` date NOT NULL,
 PRIMARY KEY (`name`,`date_of`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

最后，似乎我有了一个线索。我们似乎忽略了跟踪中的关键点，unicode错误是在警告中引发的，而不是在MySQL中。

要消除这种情况，可以过滤MySQLdb警告，然后添加

warnings.filterwarnings('ignore', category=MySQLdb.Warning)

敬你的剧本。不知道确切的警告是什么，在我的安装上没有。您可以手动检查它，在mysql控制台中执行查询，然后运行SHOW WARNINGS;。

或者，您可以修补来自warnings.py的相关函数，使其能够容忍非ascii编码，从而使用python检查它。

Answer 2

如果显示表/MySQL参数，我可以在我的笔记本电脑上执行(字符因cp1250编码终端而不同)：

>>> MySQLdb.__version__
'1.2.3'
>>> sys.version
'2.7.2 (default, Aug 15 2012, 16:03:20) \n[GCC 4.4.3]'

$ mysqldump --no-data -ualko test unicode_test
--
-- Table structure for table `unicode_test`
--
DROP TABLE IF EXISTS `unicode_test`;
CREATE TABLE `unicode_test` (
  `name` varchar(100) DEFAULT NULL,
  `date` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

$ python test.py
$ mysql -ualko test
mysql> select * from unicode_test;
+--------+------------+
| name   | date       |
+--------+------------+
| 张三   | 2013-11-11 |
| 张三   | 2013-11-11 |
+--------+------------+
2 rows in set (0.00 sec)

更新

您的连接设置是什么？

>>> import MySQLdb
>>> db_conn = MySQLdb.connect(**settings)
>>> cursor = db_conn.cursor()
>>> cursor.execute('select @@COLLATION_CONNECTION, '
...     '@@CHARACTER_SET_RESULTS, @@CHARACTER_SET_CLIENT')
1L
>>> cursor.fetchone()
(u'utf8_general_ci', u'utf8', u'utf8')