python2+mysq 的中文问题

  今天又又又又又又又又又又掉入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()