关于PYTHON用PYODBC连接SQLSERVER的封装

来源：互联网发布：宾馆记账软件编辑：程序博客网时间：2024/05/21 16:54

【环境】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-8import pyodbcclass 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-8import tracebackfrom DBHelper import  DBHelperhelper = 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()

阅读全文

0 0