利用pandas和numpy将mssql数据库表中的记录转为json

利用pandas和numpy将mssql数据库表中的记录转为json，只需要几条语句就能完美实现，十分强悍。
需求：
数据表：

json格式：
[{‘Id’: 1,
‘WaterCo’: ‘NXHSB’,
‘MeterFactory’: ‘JD’,
‘MeterSeal’: ‘31007096’,
‘ConCentratorNo’: None,
‘Open_Close’: 1,
‘CreateTime’: Timestamp(‘2020-06-26 08:56:38’),
‘SendTime’: NaT,
‘Success’: ‘Y’},
{‘Id’: 4,
‘WaterCo’: ‘NXHSB’,
‘MeterFactory’: ‘JD’,
‘MeterSeal’: ‘31007096’,
‘ConCentratorNo’: None,
‘Open_Close’: 0,
‘CreateTime’: Timestamp(‘2020-06-26 08:58:59’),
‘SendTime’: NaT,
‘Success’: ‘Y’},
{‘Id’: 5,
‘WaterCo’: ‘NXHSB’,
‘MeterFactory’: ‘JD’,
‘MeterSeal’: ‘31007092’,
‘ConCentratorNo’: None,
‘Open_Close’: 1,
‘CreateTime’: Timestamp(‘2020-07-17 16:43:18’),
‘SendTime’: NaT,
‘Success’: ‘Y’}]

要实现上述数据表中记录转换为json格式，代码如下：

#!/usr/bin/env python
# coding=utf-8
import pandas as pd
import numpy as np
import pyodbc

host = "tcp:mssql的IP"
user = "mssql的帐户"
passwd = "mssql的密码"
db = "mssql的数据库名称"
conn = pyodbc.connect(
      "DRIVER={ODBC Driver 17 for SQL Server};SERVER=" + host +
      ";DATABASE=" + db + ";UID=" + user + ";PWD=" + passwd)
data = pd.read_sql("select * from [mssql的表名]", conn)

results = list()
for i in np.array(data):
  a = {x: y for x, y in zip(data.columns, i)}
  results.append(a)

运行程序，变量results得到的结果是列表，使用json.dumps()转换后即可得到json对象。