Python connection SQL Server garbled solution _python

Vi/etc/freetds/freetds.conf

Copy Code code 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 Code code 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

Close connection:

Copy Code code as follows:

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

Python uses pymssql to connect to SQL Server databases

Copy 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:
"""
Simple encapsulation of the pymssql
Pymssql Library, the library is here to download: 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
        back: 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, "Connection Database Failed")
        else:
             return cur

    def ExecQuery (self,sql):
        ""
         Execute Query Statement
        Returns a list containing tuple, The element of the list is the record row, and the tuple element is the field for each row of records

        Call 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):
"""
Execute a non-query statement

Call 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 the list is the record row, the tuple element 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 ()

Note:
Use pymssql to do Chinese operation time may appear Chinese garbled, my solution is:
File header plus #coding =utf8
Encode when there is Chinese in the SQL statement
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")