今天又又又又又又又又又又掉入python和mysql的中文问题的坑里了,所有决定写下来记录。
1.
数据库建表时,把表设置成DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci,且每个varchar字段都设成CHARACTER SET utf8 COLLATE utf8_general_ci
DROP TABLE IF EXISTS `follow`;
CREATE TABLE `follow` (
`weiboid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`username` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`catch` int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`weiboid`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
;
2. 在py连接数据库文件里的第一行
# -*- coding=utf-8 -*-
且设置sys的默认编码
import sys
reload(sys)
sys.setdefaultencoding('utf8')
在数据库的连接参数设置self.charset = 'utf8'
3. 按照这些步骤下来 基本上可以解决python与mysql的中文问题了
以下是我的代码
# -*- coding=utf-8 -*-
import sys
import ConfigParser
import MySQLdb
from model.weiboOriginal import weiboOriginal
from model.follow import follow
reload(sys)
sys.setdefaultencoding('utf8')
class followDao(object):
def __init__(self):
self.host = None
self.user = None
self.passwd = None
self.port = None
self.db = None
self.charset = None
self.use_unicode = None
self.table = 'education'
self.initParams()
self.conn = None
self.cur = None
self.initDb()
def initParams(self):
cf = ConfigParser.ConfigParser()
cf.read('config.ini')
self.host = cf.get('db', 'host')
self.port = int(cf.get('db', 'port'))
self.user = cf.get('db', 'user')
self.passwd = cf.get('db', 'passwd')
self.db = cf.get('db', 'db')
self.charset = 'utf8'
self.use_unicode = cf.get('db', 'use_unicode')
def initDb(self):
try:
self.conn = MySQLdb.connect(host=self.host, port=self.port, user=self.user, \
passwd=self.passwd, db=self.db, charset=self.charset, use_unicode=self.use_unicode)
self.cursor = self.conn.cursor()
except MySQLdb.Error, e:
print 'Mysql Error %d: %s' % (e.args[0], e.args[1])
print 'Failed to connect to database! Please check your config file and confirm your database is open'
sys.exit(-1)
print 'Success connect database'
def executeDB(self, sql):
self.cursor.execute(sql)
self.conn.commit()
print 'Success execute sql'
#插入一个follow
def insertOneWeibo(self, obj):
sql = "INSERT INTO `test`.`follow` (`weiboid`, `username`, `catch`) VALUES ('%s','%s','%s');" % \
(obj.weiboId.decode('utf-8'),obj.userName.decode('utf-8'),obj.catch)
self.cursor.execute(sql)
self.conn.commit()
print 'Success execute sql'
def select(self,sql):
self.cursor.execute(sql)
rows = self.cursor.fetchall()
weiboOriginalList = []
for row in rows:
weibooriginal = weiboOriginal()
weibooriginal.MD5 = row[0]
weiboOriginalList.append(weibooriginal)
self.conn.commit()
return weiboOriginalList
def closeResource(self):
if self.cur:
self.cur.close()
if self.conn:
self.conn.close()