Python连接MySQL数据库通信实例以及中文乱码的解决办法

Python连接MySQL数据库通信实例以及中文乱码的解决办法

  (2011-11-14 08:40:16)

转载▼

标签：  python   mysql

分类： 技术备忘

我的开发环境：Windows 7 + Navicat for MySQL + Python 2.7

想要Python和MySQL进行通信，就需要根据自己开发时的操作系统下载相对应的Python和MySQL的接口模块，并编译安装。我下载的是MySQL-python-1.2.3.win32-py2.7.exe，下载后双击安装即可。准备工作都做好后，就可以使用python进行数据库访问了，下面给出一个简单的实例:

#!/usr/bin/env python import sys import MySQLdb   #Connect to database try:     conn = MySQLdb.connect(host = "localhost",                            user = "user",                            passwd = "passwd",                            db = "pythontest") except MySQLdb.Error, e:     print "Error %d: %s" % (e.args[0], e.args[1])     sys.exit(1)   #Get a cursor to execute sql command cursor = conn.cursor() cursor.execute("DROP TABLE IF EXISTS student") cursor.execute("""     CREATE TABLE student     (         name CHAR(20),         number CHAR(20)     )     """) cursor.execute("""     INSERT INTO student(name, number)     VALUES         ('Smith', '2011001'),         ('John', '2011002'),         ('Mike', '2011003'),         ('Joey', '2011004')     """)   sqlcmd = "SELECT * from student" cursor.execute(sqlcmd)   #Deal with the results results = cursor.fetchall() for row in results:     print "%s, %s" % (row[0], row[1]) print "Number of rows returned: %d" % cursor.rowcount   #Close cursor and connection cursor.close() conn.close()

 

如果数据库中存储的数据包含中文，那么在python的控制台打印查询结果时可能会出现乱码，下面是解决方案：

1.       在python脚本开始设置编码方案为utf-8，即在脚本文件开头加上一行：

#encoding=utf-8

2.       连接MySQL数据库时设置编码集为utf8：

conn = MySQLdb.connect(host = "localhost",

                 user = "user",

                 passwd = "passwd",

                 db = "pythontest",

                 charset = "utf8")

3.  （可选，不一定需要）在脚本文件中设置Python的默认编码为utf-8：

Import sys

reload(sys)

sys.setdefaultencoding("utf-8")

4.       （可选，不一定需要）修改数据库中的编码方案为utf-8。如果使用Navicat，直接从“数据库属性”中即可修改数据库编码方案；如果是Linux下的MySQL数据库，那么需要修改MySQL的配置文件，即设置 MySQL 的my.cnf 文件，在 [client]/[mysqld]部分都设置默认的字符集（通常在/etc/mysql/my.cnf)：

[client]

default-character-set = utf8
[mysqld]

default-character-set = utf8

 

经过我的测试，一般来说完成前两步就可以正常输出中文了，以下是修改后的脚本：

#!/usr/bin/env python #encoding=utf-8 import sys import MySQLdb   #reload(sys) #sys.setdefaultencoding("utf-8")   #Connect to database try:     conn = MySQLdb.connect(host = "localhost",                            user = "user",                            passwd = "passwd",                            db = "pythontest",                            charset = "utf8") except MySQLdb.Error, e:     print "Error %d: %s" % (e.args[0], e.args[1])     sys.exit(1)   #Get a cursor to execute sql command cursor = conn.cursor() cursor.execute("DROP TABLE IF EXISTS student") cursor.execute("""     CREATE TABLE student     (         name CHAR(20),         number CHAR(20)     )     """) cursor.execute("""     INSERT INTO student(name, number)     VALUES         ('张三', '2011001'),         ('李四', '2011002'),         ('王五', '2011003'),         ('薛六', '2011004')     """)   sqlcmd = "SELECT * from student" cursor.execute(sqlcmd)   #Deal with the results results = cursor.fetchall() for row in results:     print "%s, %s" % (row[0], row[1]) print "Number of rows returned: %d" % cursor.rowcount   #Close cursor and connection cursor.close() conn.close()