Python connection MSSQL database coding problem solving

Python has support for Chinese is not good, old coding problems recently, and there was no general solution to this problem, but after the common methods are tried, found that still can solve, summarizes the commonly used below support Chinese coding problem (may be one in these methods can solve the problem, can also be multiple combination).

(1) first of all, to ensure that the beginning of the file to add the encoding Settings to explain the encoding of the file

#encoding=utf-8

(2), then, in the connection data connection parameters to add the character set to explain the results of the query code, this may not add the consequences of the Chinese character character is a question mark

conn=pymssql.connect(server='.',user='', password='',database='MyTest',charset='utf8')

(3) set the default code of the python system (for files, this trick almost always works, hehe ~~)

import sys

reload(sys)

sys.setdefaultencoding('utf8')

Note: the code above is "utf8", not "utf-8". I didn't understand this either. Most of the time, it doesn't matter, but here I tried it must be "utf8".

A simple example of a complete python connection to mssqlserver is as follows (pymssql package is installed) :

#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.execute(sql)

    data=cur.fetchall()

    conn.close()

    print data

except Exception,e:

    print e

The operation results are as follows:

[(u'20093501', u'xb9xf9xbexb8', u'u7537 ', 35, u'xb4xf3xcfxc0'),

 (u'20093502', u'xbbxc6xc8xd8', u'u5973 ', 34, u'xc3xc0xc5xae'), 

 (u'20093503', u'xc1xeexbaxfcxb3xe5', u'u7537 ', 25, u'2Bxc7xe0xc4xea'), 

 (u'20093504', u'xc8xcexd3xafxd3xaf', u'u5973 ', 24, u'xc6xafxc1xc1')]

 [Finished in 0.2s]

 

This is still not the result we wanted, even without the question mark and garble, but it is true because the result is utf8 encoding. This phenomenon is really strange, consulted many experts, that the best result is a field query, to display Chinese, the entire query, will be shown in utf8 format.

The data in line 14 above is the result of the entire query, and if you specify a specific field, such as print data[0][2], which represents the value of the field in the first row and third column of the query result, the Chinese will be printed.

In fact, not only mssqlserver database, mysql (need to download the MySQLdb package), sqllite (python's own file database), mongodb (need to download the PyMongo package) and other ordinary text files are also similar solutions.