Creating a Data Structure from JSON Using Python

Question

I'm new to python and I have a json file that I'm trying to use to create a data structure using python.

Here is a sample of what one of the lines would look like:

[{'name': 'Nick', 'age': '35', 'city': 'New York'}...]

I read it into memory, but am unsure what additional steps I would need to take to use it to create a table.

Here is what I have tried so far:

import json
import csv
from pprint import pprint

with open("/desktop/customer_records.json") as customer_records:
    data=json.load(customer_records)
    for row in data:
        print(row)

Ideally, I would it in the following format:

Name Age City
Nick 35  New York

Any help would be appreciated. Thanks in advance.

What do you mean by table? A data structure or a visual representation? — DYZ
– DYZ, Commented Feb 4, 2018 at 4:02
Could you please precise your question? Do you want to create a table in a SQL database? If so, what kind of database engine, MySQL? SQLite? SQL Server etc...? — Alexis.Rolland
– Alexis.Rolland, Commented Feb 4, 2018 at 10:17

Marcin Zukowski · Accepted Answer · 2018-02-06 08:39:16Z

6

Your problem is not specified too precisely, but if you want to generate SQL that can be inserted into MySQL, here's a little program that converts JSON to a sequence of SQL statements:

#!/usr/bin/env python

import json

# Load data
with open("zz.json") as f:
    data=json.load(f)

# Find all keys
keys = []
for row in data:
    for key in row.keys():
        if key not in keys:
            keys.append(key)

# Print table definition
print """CREATE TABLE MY_TABLE(
  {0}
);""".format(",\n  ".join(map(lambda key: "{0} VARCHAR".format(key), keys)))

# Now, for all rows, print values
for row in data:
    print """INSERT INTO MY_TABLE VALUES({0});""".format(
        ",".join(map(lambda key: "'{0}'".format(row[key]) if key in row else "NULL", keys)))

For this JSON file:

[
  {"name": "Nick", "age": "35", "city": "New York"},
  {"name": "Joe", "age": "21", "city": "Boston"},
  {"name": "Alice", "city": "Washington"},
  {"name": "Bob", "age": "49"}
]

It generates

CREATE TABLE MY_TABLE(
  city VARCHAR,
  age VARCHAR,
  name VARCHAR
);
INSERT INTO MY_TABLE VALUES('New York','35','Nick');
INSERT INTO MY_TABLE VALUES('Boston','21','Joe');
INSERT INTO MY_TABLE VALUES('Washington',NULL,'Alice');
INSERT INTO MY_TABLE VALUES(NULL,'49','Bob');

And for the future, please make your question WAY more specific :)

answered Feb 6, 2018 at 8:39

Marcin Zukowski

4,8391 gold badge25 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user8659376 Over a year ago

Thanks for your help! Will definitely make my question a lot more specific in the future

Collectives™ on Stack Overflow

Creating a Data Structure from JSON Using Python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related