I'm trying to learn how to get the following format of json to sql table. I used python pandas and it is converting the json nodes to dictionary.
Same json:
{
"Volumes": [
{
"AvailabilityZone": "us-east-1a",
"Attachments": [
{
"AttachTime": "2013-12-18T22:35:00.000Z",
"InstanceId": "i-1234567890abcdef0",
"VolumeId": "vol-049df61146c4d7901",
"State": "attached",
"DeleteOnTermination": true,
"Device": "/dev/sda1"
}
],
"Tags": [
{
"Value": "DBJanitor-Private",
"Key": "Name"
},
{
"Value": "DBJanitor",
"Key": "Owner"
},
{
"Value": "Database",
"Key": "Product"
},
{
"Value": "DB Janitor",
"Key": "Portfolio"
},
{
"Value": "DB Service",
"Key": "Service"
}
],
"VolumeType": "standard",
"VolumeId": "vol-049df61146c4d7901",
"State": "in-use",
"SnapshotId": "snap-1234567890abcdef0",
"CreateTime": "2013-12-18T22:35:00.084Z",
"Size": 8
},
{
"AvailabilityZone": "us-east-1a",
"Attachments": [],
"VolumeType": "io1",
"VolumeId": "vol-1234567890abcdef0",
"State": "available",
"Iops": 1000,
"SnapshotId": null,
"CreateTime": "2014-02-27T00:02:41.791Z",
"Size": 100
}
]
}
until now.. this what I was trying... in python:
asg_list_json_Tags=asg_list_json["AutoScalingGroups"]
Tags=pandas.DataFrame(asg_list_json_Tags)
n = []
for i in Tags.columns:
n.append(i)
print n
engine = create_engine("mysql+mysqldb://user:"+'pwd'+"@mysqlserver/dbname")
Tags.to_sql(name='TableName', con=engine, if_exists='append', index=True)
MaxU - stand with Ukraine :
I would do it this way:\n\nfn = r'D:\\temp\\.data\\40450591.json'\n\nwith open(fn) as f:\n data = json.load(f)\n\n# some of your records seem NOT to have `Tags` key, hence `KeyError: 'Tags'`\n# let's fix it\nfor r in data['Volumes']:\n if 'Tags' not in r:\n r['Tags'] = []\n\nv = pd.DataFrame(data['Volumes']).drop(['Attachments', 'Tags'],1)\na = pd.io.json.json_normalize(data['Volumes'], 'Attachments', ['VolumeId'], meta_prefix='parent_')\nt = pd.io.json.json_normalize(data['Volumes'], 'Tags', ['VolumeId'], meta_prefix='parent_')\n\nv.to_sql('volume', engine)\na.to_sql('attachment', engine)\nt.to_sql('tag', engine)\n\n\nOutput:\n\nIn [179]: v\nOut[179]:\n AvailabilityZone CreateTime Iops Size SnapshotId State VolumeType\nVolumeId\nvol-049df61146c4d7901 us-east-1a 2013-12-18T22:35:00.084Z NaN 8 snap-1234567890abcdef0 in-use standard\nvol-1234567890abcdef0 us-east-1a 2014-02-27T00:02:41.791Z 1000.0 100 None available io1\n\nIn [180]: a\nOut[180]:\n AttachTime DeleteOnTermination Device InstanceId State VolumeId parent_VolumeId\n0 2013-12-18T22:35:00.000Z True /dev/sda1 i-1234567890abcdef0 attached vol-049df61146c4d7901 vol-049df61146c4d7901\n1 2013-12-18T22:35:11.000Z True /dev/sda1 i-1234567890abcdef1 attached vol-049df61146c4d7111 vol-049df61146c4d7901\n\nIn [217]: t\nOut[217]:\n Key Value parent_VolumeId\n0 Name DBJanitor-Private vol-049df61146c4d7901\n1 Owner DBJanitor vol-049df61146c4d7901\n2 Product Database vol-049df61146c4d7901\n3 Portfolio DB Janitor vol-049df61146c4d7901\n4 Service DB Service vol-049df61146c4d7901\n\n\nTest JSON file:\n\n{\n \"Volumes\": [\n {\n \"AvailabilityZone\": \"us-east-1a\",\n \"Attachments\": [\n {\n \"AttachTime\": \"2013-12-18T22:35:00.000Z\",\n \"InstanceId\": \"i-1234567890abcdef0\",\n \"VolumeId\": \"vol-049df61146c4d7901\",\n \"State\": \"attached\",\n \"DeleteOnTermination\": true,\n \"Device\": \"/dev/sda1\"\n },\n {\n \"AttachTime\": \"2013-12-18T22:35:11.000Z\",\n \"InstanceId\": \"i-1234567890abcdef1\",\n \"VolumeId\": \"vol-049df61146c4d7111\",\n \"State\": \"attached\",\n \"DeleteOnTermination\": true,\n \"Device\": \"/dev/sda1\"\n }\n ],\n \"Tags\": [\n {\n \"Value\": \"DBJanitor-Private\",\n \"Key\": \"Name\"\n },\n {\n \"Value\": \"DBJanitor\",\n \"Key\": \"Owner\"\n },\n {\n \"Value\": \"Database\",\n \"Key\": \"Product\"\n },\n {\n \"Value\": \"DB Janitor\",\n \"Key\": \"Portfolio\"\n },\n {\n \"Value\": \"DB Service\",\n \"Key\": \"Service\"\n }\n ],\n \"VolumeType\": \"standard\",\n \"VolumeId\": \"vol-049df61146c4d7901\",\n \"State\": \"in-use\",\n \"SnapshotId\": \"snap-1234567890abcdef0\",\n \"CreateTime\": \"2013-12-18T22:35:00.084Z\",\n \"Size\": 8\n },\n {\n \"AvailabilityZone\": \"us-east-1a\",\n \"Attachments\": [],\n \"VolumeType\": \"io1\",\n \"VolumeId\": \"vol-1234567890abcdef0\",\n \"State\": \"available\",\n \"Iops\": 1000,\n \"SnapshotId\": null,\n \"CreateTime\": \"2014-02-27T00:02:41.791Z\",\n \"Size\": 100\n }\n ]\n}\n",
2016-11-06T15:44:13