How to deal with Python3.4 using pymssql garbled characters

This article mainly introduces how to deal with Python3.4's pymssql garbled information, involving pythonpymssql related knowledge. if you are interested in this article, learn about it and find such a problem in the project: the SQL Server database is encoded as gbk and uses python3.4 + pymssql to query and garbled Chinese characters. after some consideration, the solution is as follows:

conn = pymssql.connect(host="192.168.122.141", port=1433, user="myshop", password="oyf20140208HH", database="mySHOPCMStock", charset='utf8', as_dict=True) cur = conn.cursor()sql = "select top 10 [ID],[Name] from [User]"cur.execute(sql)list = cur.fetchall()for row in list: print(row["ID"],row["Name"].encode('latin-1').decode('gbk'))

Next we will introduce how to use pymssql to connect to the SQL server database in python.

# Coding = UTF-8 #! /Usr/bin/env python # consumer # Name: pymssqlTest. py # Purpose: Test the pymssql Library. download the library here: http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql # Author: scott # Created: 04/02/2012 # export import pymssqlclass MSSQL: "simple encapsulation of pymssql Library, which is downloaded here: http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql When using this library, you need to enable TCP/IP protocol usage in SQL Server Configuration Manager: "def _ init _ (self, host, user, pwd, db): self. host = hostself. user = userself. pwd = pwdself. db = dbdef _ GetConnect (self): "returned connection information: conn. cursor () "" if not self. db: raise (NameError, "no database information set") self. conn = pymssql. connect (host = self. host, user = self. user, password = self. pwd, database = self. db, charset = "utf8") cur = self. conn. cursor () if not cur: raise (NameError, "failed to connect to database") else: return curdef ExecQuery (self, SQL ): "execution of the query statement returns a list containing tuple. the list element is the record row, and the tuple element is the example of calling the Fields recorded in each row: ms = MSSQL (host = "localhost", user = "sa", pwd = "123456", db = "PythonWeiboStatistics") resList = ms. execQuery ("SELECT id, NickName FROM WeiBoUser") for (id, NickName) in resList: print str (id), NickName "" cur = self.w.getconnect(w.cur.exe cute (SQL) resList = cur. fetchall () # The connection to self must be closed after the query is complete. conn. close () return resListdef ExecNonQuery (self, SQL): "example of executing a non-query statement CALL: cur = self.20.getconnect()cur.exe cute (SQL) self. conn. commit () self. conn. close () "" cur = self.w.getconnect(+cur.exe cute (SQL) self. conn. commit () self. conn. close () def main (): # MS = MSSQL (host = "localhost", user = "sa", pwd = "123456", db = "PythonWeiboStatistics ") ### the returned result is a list containing tuple. the list element is the record row, and the tuple element is the record field in each row ## ms. execNonQuery ("insert into WeiBoUser values ('2', '3')") MS = MSSQL (host = "localhost", user = "sa", pwd = "123456 ", db = "PythonWeiboStatistics") resList = ms. execQuery ("SELECT id, weibocontent FROM WeiBo") for (id, weibocontent) in resList: print str (weibocontent ). decode ("utf8") if _ name _ = '_ main _': main ()

Note:

Chinese garbled characters may occur during Chinese operations using pymssql. the solution is as follows:

Add # coding = utf8 to the file header

Encode when the SQL statement contains Chinese characters

InsertSql = "insert into WeiBo ([UserId], [WeiBoContent], [PublishDate]) values (1, 'test', '2017/1 ')". encode ("utf8 ")

Add charset settings during connection

pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8")