Python连接SQL Server获取数据转JSON

# -*- coding: utf-8 -*-
'''
不同的SQL server版本对应的DRIVER字段不同。对应关系如下
{SQL Server} - released with SQL Server 2000
{SQL Native Client} - released with SQL Server 2005 (also known as version 9.0)
{SQL Server Native Client 10.0} - released with SQL Server 2008
{SQL Server Native Client 11.0} - released with SQL Server 2012
'''
import sys
reload(sys)
sys.setdefaultencoding('utf-8')

import json,pyodbc

def Table():
    try:

        conn=pyodbc.connect(r'DRIVER={SQL Server Native Client 10.0};SERVER=1.1.1.1;DATABASE=test_1;UID=1;PWD=123')
        cursor = conn.cursor()
        cursor.execute("select F1,F2,F3,F4 from A2017")
        jsonDate=[]
        rows = cursor.fetchall()
        # 循环读取元组数据
        for row in rows:
            result={}
            result['F1']=row[0]
            result['F2'] = row[1]
            result['F3'] = row[2]
            result['F4'] = row[3]
            jsonDate.append(result)
    except:
        print 'ss'
    else:
        #加ensure_ascii=False，能够防止中文乱码。
         jsonDateChar=json.dumps(jsonDate,ensure_ascii=False)
        #去除中括号
         return jsonDateChar[1:len(jsonDateChar)-1]

if __name__=='__main__':
    # 调用函数
    jsonData=Table()
    #JSON保存文件名
    w=open(r'H:\json.txt','w+')
    # 写数据
    w.write(jsonData)
    w.close()

头部文件

import sys
reload(sys)
sys.setdefaultencoding('utf-8')

将字符串写入到文件时会出现"UnicodeEncodeError: 'ascii' codec can't encode characters in position 22-24: ordinal not in range(128)"的错误,原因是由于python基于ASCII处理字符的，当出现不属于ASCII的字符时，会出现错误信息。加入头部代码可以解决