关于PYTHON用PYODBC连接SQLSERVER的封装

【环境】Windows10 64bit、Python 2.7 64bit、Pycharm 2017.1 Amd64、SqlServer 2008

【安装】pyodbc的安装我这里就不说了，找度娘

【难点】主要是查询出来的中文以及Sql语句中包含中文的乱码问题

转载请注明出处：http://blog.csdn.net/humanbeng/article/details/75620169

下面贴上代码DBHelper.py

#coding:utf-8
import pyodbc

class DBHelper(object):

    def __init__(self, serverIp, port, dbName, uid, pwd):
        conn_info = 'DRIVER={SQL Server};DATABASE=%s;SERVER=%s,%s;UID=%s;PWD=%s' % (dbName, serverIp, port, uid, pwd)
        self.connection = pyodbc.connect(conn_info, unicode_results=True)
        self.cursor = self.connection.cursor()

    def __del__(self):
        if self.cursor:
            self.cursor.close()
            self.cursor = None
            print(self.cursor, '__del__ cursor closed')
        if self.connection:
            self.connection.close()
            self.connection = None

    def destroy(self):
        if self.cursor:
            print(self.cursor, 'destroy cursor closed')
            self.cursor.close()
            self.cursor = None
        if self.connection:
            self.connection.close()
            self.connection = None

    # 获取全部查询结果
    def queryAll(self, qryStr):
        print(qryStr.decode('gbk'))
        self.cursor.execute(qryStr)
        return self.cursor.fetchall()

    # 获取前maxcnt条查询结果
    def querySome(self, qryStr, maxCount):
        self.cursor.execute(qryStr)
        return self.cursor.fetchmany(maxCount)

    #获取分页查询结果
    def queryPage(self, qryStr, skipCnt, pageSize):
        self.cursor.execute(qryStr)
        self.cursor.skip(skipCnt)
        return self.cursor.fetchmany(pageSize)

    #获取查询条数
    def count(self, sql):
        self.cursor.execute(sql)
        return self.cursor.fetchone()[0]

    #执行语句，包括增删改，返回变更数据数量
    def execute(self, sql):
        count = self.cursor.execute(sql).rowcount
        self.connection.commit()
        return count

下面是测试程序DBHelperTest.py，语句如果有中文请先decode成GBK编码

#coding:utf-8
import traceback

from DBHelper import  DBHelper

helper = DBHelper('*.*.*.*', 'port', 'dbname', 'username', 'password')

try:
    sql = u'select * from T_CharityReport where Title like \'%测试%\''
    row = helper.queryAll(sql.encode('GBK'))
    for i in row:
        # 这里如果查询出来的字段有中文，就必须单个查询出来，
        # 查询出来的默认返回的GBK的编码，但是在DBHelper里连接是设置了unicode_results=True
        # 查询出的编码是Unicode，所以这里就不用执行i[1].decode('GBK')操作了
        print i[0], i[1], i[2], i[3], i[4], i[5]

    sql = u'select * from T_CharityReport where Title = \'test\''
    row = helper.querySome(sql.encode('GBK'), 1)
    for i in row:
        print i[0], i[1], i[2], i[3], i[4], i[5]

except Exception as e:
    # 调试时打开如下，方便跟踪异常出处
    print 'str(Exception):\t', str(Exception)
    print 'str(e):\t\t', str(e)
    print 'e.message:\t', e.message
    print 'traceback.print_exc():\n%s' % traceback.print_exc()
    print 'traceback.format_exc():\n%s' % traceback.format_exc()

finally:
    helper.destroy()