This article mainly introduces how to solve the problem of Python connection to mssql database encoding. This article also applies to mysql, sqllite, mongodb, and other databases. if you need a friend, refer to the following: python has always had poor support for Chinese characters, I have encountered encoding problems recently, and almost no general solutions are available to solve this problem. However, I have tried common methods and found that they 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:
# 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:
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:
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:
# 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:
[(U'000000', u' \ xb9 \ xf9 \ xbe \ xb8', u' \ u7537', 35, u' \ xb4 \ xf3 \ xcf \ xc0 '),
(U '000000', u' \ xbb \ xc6 \ xc8 \ xd8 ', u' \ u5973', 34, u' \ xc3 \ xc0 \ xc5 \ xae '),
(U '000000', u' \ xc1 \ xee \ xba \ xfc \ xb3 \ xe5', u' \ u7537', 25, u'2b \ xc7 \ xe0 \ xc4 \ xea '),
(U '000000', u' \ xc8 \ xce \ xd3 \ xaf \ xd3 \ xaf ', u' \ u5973', 24, u' \ xc6 \ xaf \ xc1 \ xc1 ')]
[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.