python生成json列表_Python SQL查询并生成json文件操作示例

本文实例讲述了Python SQL查询并生成json文件操作。分享给大家供大家参考，具体如下：

1. 数据准备

SQL数据点击此处本站下载。

2. python代码

import datetime

import os

import mssqlhelper

ms = mssqlhelper.MSSQL(host="192.168.0.108", user="sa", pwd="sa", db="ComPrject")

def getAreas(cityid):

arealist=ms.ExecQuery("select *From dbo.areas where cityid='%s' " % cityid)

return arealist

def getCity(provinces):

citylist=ms.ExecQuery("select *From dbo.cities where provinceid='%s'" % provinces)

return citylist

def getProvinces():

provlist=ms.ExecQuery("select *From dbo.provinces")

return provlist

def createFileJson():

date=datetime.datetime.now().strftime('%Y-%m-%d')

path=date+'-provinces.json'

return path

def writeJson(path):

provlist=getProvinces()

with open(path,"w+",encoding="utf-8") as f:

f.write("[")

lp = 0

for p in provlist:

if lp>0:

f.write(",n")

else:

f.write("n")

f.write("{n")

f.write('"Code":"%s"n'% p[1])

f.write(',"Name":"%s"n'% p[2])

f.write(',Nodes:[n')

citylist=getCity(p[1])

lc = 0

for c in citylist:

if lc>0:

f.write("t,n")

else:

f.write("n")

f.write("t{n")

f.write('t"Code":"%s"n'% c[1])

f.write('t,"Name":"%s"n'% c[2])

f.write('t,Nodes:[n')

arealist = getAreas(c[1])

la = 0

for a in arealist:

if la>0:

f.write("tt,n")

else:

f.write("n")

f.write("tt{n")

f.write('tt"Code":"%s"n'% a[1])

f.write('tt,"Name":"%s"n'% a[2])

f.write("tt}n")

la += 1

f.write("t]n")

f.write("t}n")

lc += 1

f.write("]n")

f.write("}n")

lp += 1

f.write("]n")

if __name__ == '__main__':

path=createFileJson()

writeJson(path)

3.生成预览

PS：这里再为大家推荐几款比较实用的json在线工具供大家参考使用：

希望本文所述对大家Python程序设计有所帮助。