Python SQL查询并生成json文件操作示例_Python

Python SQL查询并生成json文件操作示例

2021-03-28 00:17sulin Python

这篇文章主要介绍了Python SQL查询并生成json文件操作,涉及Python基于mssqlhelper模块进行mysql数据库查询与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("\t\t,\n")

									          else:

									            f.write("\n")

									          f.write("\t\t{\n")

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

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

									          f.write("\t\t}\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)