mysql - 从json数据创建mysql数据库

Question

我正在从 ARC Server 在线报告中读取 JSON 数据，并尝试使用该数据创建数据库。我创建了名为:test.db 的数据库

我需要将列标识为“服务”、“文件夹”、“服务 URL”、“配置状态”、“实时状态”、“服务器类型”。以及从报告中返回的每个“服务”的行。

JSON 数据如下所示:

{"reports": [{
  "folderName": "/",
  "serviceName": "SampleWorldCities",
  "type": "MapServer",
  "description": "The SampleWorldCities service is provided so you can quickly and easily preview the functionality of the GIS server. Click the thumbnail image to open in a web application. This sample service is optional and can be deleted.",
  "isDefault": false,
  "isPrivate": false,
  "hasManifest": false,
  "status": {
    "configuredState": "STARTED",
    "realTimeState": "STARTED"
  },
  "instances": {
    "folderName": "/",
    "serviceName": "SampleWorldCities",
    "type": "MapServer",
    "max": 1,
    "busy": 0,
    "free": 1,
    "initializing": 0,
    "notCreated": 0,
    "transactions": 72,
    "totalBusyTime": 127611,
    "isStatisticsAvailable": true
  },
  "properties": {
    "maxRecordCount": "1000",
    "filePath": "${AGSSERVER}/framework/etc/data/WorldCities/WorldCities.msd",
    "cacheOnDemand": "false",
    "useLocalCacheDir": "true",
    "outputDir": "/home/ec2-user/arcgis/server/usr/directories/arcgisoutput",
    "virtualOutputDir": "/rest/directories/arcgisoutput",
    "supportedImageReturnTypes": "MIME+URL",
    "minScale": "295000000",
    "isCached": "false",
    "ignoreCache": "false",
    "maxScale": "4000",
    "clientCachingAllowed": "true",
    "cacheDir": "/home/ec2-user/arcgis/server/usr/directories/arcgiscache"
  },
  "iteminfo": {
    "description": "The SampleWorldCities service is provided so you can quickly and easily preview the functionality of the GIS server. Click the thumbnail image to open in a web application. This sample service is optional and can be deleted.",
    "summary": "The SampleWorldCities service is provided so you can quickly and easily preview the functionality of the GIS server. Click the thumbnail image to open in a web application. This sample service is optional and can be deleted.",
    "tags": [
      "sample",
      "map",
      "service"
    ],
    "thumbnail": "thumbnail.png"
  },
  "permissions": [{
    "principal": "esriEveryone",
    "permission": {"isAllowed": true},
    "childURL": null,
    "operation": null
  }]
}]}

我的脚本如下:

import json
import sqlite3
db = sqlite3.connect('test.db')
traffic = json_read
c = db.cursor()

someitem = traffic.itervalues().next()
columns = ['Service', 'Folder', 'Service URL', 'Configured State', 'Real Time State', 'Server Type']

c.execute("SELECT sql FROM sqlite_master WHERE " \
        "Service='Services' AND type = 'table'")
create_table_string = cursor.fetchall()[0][0]

c.execute('''create table Services
        (Service text primary key,
        Folder text,
        Service URL text,
        Configured State text,
        Real Time State text,
        Server Type text)''')

for service, data in traffic.iteritems():
    services = (service,) + tuple(data[c] for c in columns)
    c = db.cursor()
    c.execute(query)
    c.close()


print "JSON Complete"

有人能指出我正确的方向吗？

忘记说了服务是服务名称，文件夹是文件夹名称，服务 url 是服务的链接，配置状态为configuredState，实时状态是realTimeState，服务器类型为 type

Answer 1

db = sqlite3.connect(
                'server.db')

cursor = db.cursor()

cursor.execute("DROP TABLE if exists Services")
db.commit()

cursor.execute("DROP TABLE if exists Services2")
db.commit()

cursor.execute('''CREATE TABLE Services
                (Service text,
                Folder text,
                Service_URL text,
                Configured_State text,
                Real_Time_State text,
                Server text);''')

这段代码为我提供了我正在寻找的输出。