解決python連接mysql報錯問題

解決python連接mysql報錯問題

最近跑python腳本時遇到一個問題，當sql語句中有中文時，執行python腳本報以下錯誤：

Traceback (most recent call last):

  File "kpi_daily_report.py", line 356, in <module>

    result = cal(line, sys.argv[1], sys.argv[2])

  File "kpi_daily_report.py", line 324, in cal

    result = run(sql)

  File "kpi_daily_report.py", line 49, in run

    rtn = conn31.selectall(sql)

  File "/data0/home/yangjing150/test/kpidaily222/mysql_db_connector.py", line 100, in selectall

    raise ex;

UnicodeEncodeError: 'latin-1' codec can't encode characters in position 204-206: ordinal not in range(256)

經過查詢與測試，下面三點可以完美解決問題

1、python腳本設置utf-8為默認字符集

import os, sys

import mysql

reload(sys)

sys.setdefaultencoding( "utf-8" )

2、mysql連接時，設置字符集charset='utf8'

3、cursor執行sql語句時，加上字符集utf8的設置 

self.cursor.execute('SET NAMES utf8;')

具體代碼如下：

def selectall(self, commandText):

        rtn = None

        try:

            if self.auto and self.con == None:

                self.con = mysql.connect(host = self.host, port = self.port,

                                user = self.user, passwd = self.passwd, db = self.db,

                                connect_timeout = 1 ,charset='utf8')

                if self.con:

                    self.cursor = self.con.cursor()

                    self.cursor.execute('SET NAMES utf8;')

            if self.cursor:

                self.cursor.execute('SET NAMES utf8;')

                self.cursor.execute(commandText)

                rtn = self.cursor.fetchall()

        except Exception as ex:

            raise ex;

        finally:

            if self.auto:

                self.close()

        return rtn