Resolve the problem of mysql error in python connection

Resolve the problem of mysql error in python connection

Recently, I encountered a problem when running the python script. When there is Chinese in the sql statement, execute the following error in the python footer:

Traceback (most recent call last):

File "kpi_daily_report.py", line 356, in

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 204206: ordinal not in range

After query and testing, the following three points can solve the problem perfectly.

1. Python script sets utf-8 as the default character set

Import os, sys

Import mysql

Reload (sys)

Sys.setdefaultencoding ("utf-8")

2. When connecting to mysql, set the character set charset='utf8'

3. When cursor executes the sql statement, add the setting of the character set utf8

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

The specific code is as follows:

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