Workarounds for Python connection to SQL Server garbled

Vi/etc/freetds/freetds.conf
Copy CodeThe code is as follows:

[Global]
# TDS Protocol version
TDS Version = 8.0
Client CharSet = UTF-8
# A Typical Microsoft server
[Server55]
Host = 192.168.1.55
Port = 1433
TDS Version = 8.0
Vi/etc/odbc.ini
[DSN55]
Description=my DSN
Driver=tds
database=qq99
Servername=server55

Tsql-s server55-u qq-p 123456-d qq99
Copy CodeThe code is as follows:

#coding =utf-8
#!/usr/bin/python
Import Pyodbc
CNXN = Pyodbc.connect ("DSN=DSN55; UID=QQ; pwd=123456 ")
cursor = Cnxn.cursor ()
Cursor.execute (' select * from Orders where username=? ', ' QQ ')
A=cursor.fetchall ()
print ' Pyodbc ', a

To close the connection:
Copy CodeThe code is as follows:

Csr.close ()
Del CSR
Conn.close ()

Python uses pymssql to connect to a SQL Server database
Copy the Code code as follows:

#coding =utf-8
#!/usr/bin/env python
#-------------------------------------------------------------------------------
# Name:pymssqlTest.py
# Purpose: Test pymssql Library, the library to download here: http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
#
# Author:scott
#
# created:04/02/2012
#-------------------------------------------------------------------------------

Import pymssql

Class MSSQL:
"""
A simple package for pymssql
Pymssql Library, the library to download here: http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
When using this library, you need to open the TCP/IP protocol in SQL Server Configuration Manager

Usage:

"""

def __init__ (self,host,user,pwd,db):
Self.host = Host
Self.user = user
Self.pwd = pwd
SELF.DB = db

def __getconnect (self):
"""
Get connection Information
return: Conn.cursor ()
"""
If not self.db:
Raise (Nameerror, "Database information not 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, "Connection database Failed")
Else
Return cur

def execquery (Self,sql):
"""
Execute Query statement
The element that returns a list,list containing a tuple is a record row, and the elements of a tuple are the fields of each row of records

Invocation Example:
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.__getconnect ()
Cur.execute (SQL)
Reslist = Cur.fetchall ()

#查询完毕后必须关闭连接
Self.conn.close ()
Return reslist

def execnonquery (Self,sql):
"""
Executing non-query statements

Invocation Example:
cur = self.__getconnect ()
Cur.execute (SQL)
Self.conn.commit ()
Self.conn.close ()
"""
cur = self.__getconnect ()
Cur.execute (SQL)
Self.conn.commit ()
Self.conn.close ()

def main ():
# # ms = MSSQL (host= "localhost", user= "sa", pwd= "123456", db= "Pythonweibostatistics")
# # #返回的是一个包含tuple的list, the element of list is the record row, the element of tuple is the field of each row record
# # 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 ()

Precautions:
When using pymssql for Chinese operation, Chinese characters may appear garbled, the solution I solve is:
File header plus #coding =utf8
Encode when there are Chinese in SQL statements
Insertsql = "INSERT into WeiBo ([userid],[weibocontent],[publishdate]) VALUES (1, ' Test ', ' 2012/2/1 ')". Encode ("UTF8")
Join charset setup Information when connecting
Pymssql.connect (host=self.host,user=self.user,password=self.pwd,database=self.db,charset= "UTF8")

