Python connection mssql database garbled (question mark in Chinese) solution

Python has always had poor support for Chinese characters. Recently it has encountered encoding problems, and there are almost no general solutions to solve this problem. However, after trying common methods, the findings can still be solved. The following summarizes the commonly used Chinese-supported encoding problems (one of these methods may solve the problem or multiple combinations ).

(1) first, ensure that the encoding settings must be added at the beginning of the file to describe the file encoding.

The code is as follows:	Copy code
# Encoding = UTF-8

(2) Then, add the character set in the connection parameter of the connected data to indicate the encoding of the query result. If this parameter is left blank, the result may be that all the Chinese characters to be queried are question marks.

The code is as follows:	Copy code
Conn = pymssql. connect (server = '.', user = '', password ='', database = 'mytest', charset = 'utf8 ')

(3) set the default encoding of the python system (for files, this method is almost always tried and tested, haha ~~)

The code is as follows:	Copy code
Import sys Reload (sys) Sys. setdefaultencoding ('utf8 ')

Note: The above encoding is "utf8" instead of "UTF-8". I didn't understand it either. In most cases, it doesn't matter, but here I tried it, it must be "utf8"

An example of a simple and complete python connection to mssqlserver is as follows (you must install the pymssql package ):

The code is as follows:	Copy code
# Encoding: utf8 Import sys Reload (sys) Sys. setdefaultencoding ('utf8 ') Import pymssql Try: Conn = pymssql. connect (server = '.', user = '', password ='', database = 'mytest', charset = 'utf8 ') SQL = "select * from UserInfo" Cur = conn. cursor () Cur.exe cute (SQL) Data = cur. fetchall () Conn. close () Print data Except t Exception, e: Print e

The running result is as follows:

The code is as follows:	Copy code
[(U '000000', u 'xb9xf9xbexb8', u 'u7537', 35, u 'xb4xf3xcfxc0 '), (U '000000', u 'xbbxc6xc8xd8', u 'u5973 ', 34, u 'xc3xc0xc5xae '), (U '000000', u 'xc1xeexbaxfcxb3xe5', u 'u7537', 25, u '2bxc7xe0xc4xea '), (U '200', u 'xc8xcexd3xafxd3xaf', u 'u5973 ', 24, u 'xc6xafxc1xc1')] [Finished in 0.2 s]

Although we get rid of the question mark and garbled characters, this is not the expected result, but it is true because the result is UTF-8 encoded. This is indeed a strange phenomenon. I have consulted many experts and learned that the best result is to query the fields one by one to display Chinese characters. The entire query will be displayed in utf8 format.

In the above code, row 14th data is the result of the entire query. If a specific field is specified, for example, print data [0] [2] (the value of the field in the third column of the first row of the query result) will output Chinese characters.

In fact, it is not only the mssqlserver database, mysql (the MySQLdb package needs to be downloaded), sqllite (the file database that comes with python), and mongodb (the PyMongo package needs to be downloaded) or common text files are similar solutions.