【环境】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()