windows环境下python2.7连接mysql插入数据乱码问题

最新推荐文章于 2018-12-21 17:31:44 发布

小兴安岭mx

最新推荐文章于 2018-12-21 17:31:44 发布

阅读量419

点赞数

CC 4.0 BY-SA版权

分类专栏： python 文章标签： python2.7 编码

本文链接：https://blog.youkuaiyun.com/qq_34621987/article/details/80880025

python 专栏收录该内容

4 篇文章

订阅专栏

本文介绍如何解决在PyCharm使用Python2.x时，向MySQL数据库插入包含中文的数据出现乱码的问题，并给出正确的解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.问题：在windows环境下使用PyCharm时，我想向mysql的一张表中插入数据，代码如下

import MySQLdb
from scrapy.conf import settings
class XiaoHuar_Pipeline(object):
    def process_item(self, item, spider):
        host = settings['MYSQL_HOST']
        user = settings['MYSQL_USER']
        psd = settings['MYSQL_PASSWORD']
        db = settings['MYSQL_DB']
        port = settings['MYSQL_PORT']
        charset = settings['CHARSET']
        con = MySQLdb.connect(host=host,user=user,passwd=psd,db=db,port=port)
        cue = con.cursor()
        print "mysql connect successfully"
        sql = "insert into girl(name,school,pic) values('{0}','{1}','{2}')".format(item['name'],item['school'],item['pic'])
        cue.execute(sql)
        con.commit()
        con.close()
        return item

由于插入的数据中有中文，mysql中出现了乱码

2.原因：考虑到我的数据库编码是utf-8

于是我在连接数据库时，加入charset='utf-8'，结果出现错误

con = MySQLdb.connect(host=host,user=user,passwd=psd,db=db,port=port,charset='utf-8')

Traceback (most recent call last):
  File "C:\Python27\lib\site-packages\twisted\internet\defer.py", line 653, in _runCallbacks
    current.result = callback(current.result, *args, **kw)
  File "D:\PyWorkspace\p1\p1\pipelines.py", line 17, in process_item
    con = MySQLdb.connect(host=host,user=user,passwd=psd,db=db,port=port,charset='utf-8')
  File "C:\Python27\lib\site-packages\MySQLdb\__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 215, in __init__
    self.set_character_set(charset)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 294, in set_character_set
    super(Connection, self).set_character_set(charset)
OperationalError: (2019, "Can't initialize character set utf-8 (path: c:\\Program Files\\MySQL\\MySQL Server 5.1\\\\share\\charsets\\)")

通过google之后发现，原来python2中使用的是'utf8'而不是'utf-8'，于是对代码进行修改

con = MySQLdb.connect(host=host,user=user,passwd=psd,db=db,port=port,charset='utf8')

更改之后，中文就可以正常插入数据库了。