获取SQL查询及其列名并将其转换为以下json格式的Python代码

Question

我尝试过获取SQL查询结果并将其转换为JSON格式。我已经部分成功了。但是，我正在寻找以下格式：

[
{
   "JId": 1,
    "Jinfo": "Failed",
   "Jno": "Job1"
},
{
    "JId":2,
    "Jinfo":"Failed",
    "Jno":"Job2"
},
{
    "JId":3,
    "Jinfo":"Failed",
    "Jno":"Job3"
}
]

存在一个名为“jobfail”的表，其中包含以下数据：

JId Jinfo  Jno
1,  "Failed", "Job1"
2,  "Failed", "Job2"
3,  "Failed", "Job3"

我能够分别检索列名和值。我还能够将这些值转换成JSON。但我并没有以“column\u name:value”格式得到它。你知道吗

import pyodbc
import json

cncn=pyodbc.connect('DRIVER={SQL Server};Server=Server_name;Database=dbname;Trusted_Connection=yes;')
if(cncn):
    print("Connected")
    query1="SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'jobfail'"
    query2="SELECT * FROM dbo.jobfail"
    data=[]
    column_list=[]
    cursor = cncn.cursor()
    cursor.execute(query1)
    for cname in cursor.fetchall():
        column_list.append(list(cname))
    print(column_list)
    cursor.execute(query2)
    rs=cursor.fetchall()
    for row in rs:
        data.append(list(row))
    print(json.dumps(data, indent=4))

我希望输出采用上述格式。我无法合并列名和相应的值以形成JSON。下面是我得到的部分输出。你知道吗

[['jid'], ['Jinfo'], ['Jno']]             


[
[
    1,
    "Failed",
    "Job1"
],
[
    2,
    "Failed",
    "Job2"
],
[
    3,
    "Failed",
    "Job3"
]
]

Answer 1

如果需要，SQL Server将返回查询结果as JSON。例如

use tempdb
drop table if exists #t
go
create table #t(JId int, Jinfo varchar(30), Jno varchar(30))


insert into #t
values
(1,  'Failed', 'Job1'),
(2,  'Failed', 'Job2'),
(3,  'Failed', 'Job3')

go
select *
from #t t
for json path

输出

[
    {
        "JId": 1,
        "Jinfo": "Failed",
        "Jno": "Job1"
    },
    {
        "JId": 2,
        "Jinfo": "Failed",
        "Jno": "Job2"
    },
    {
        "JId": 3,
        "Jinfo": "Failed",
        "Jno": "Job3"
    }
]