Python script to convert JSON file into CSV file for easy uploading on MySQL database

This tutorial show how to use a python script that converts a JSON file data into a CSV file. And how to export CSV file data into Sqlite database using a python script.

The JSON file data is stored in the following format:

{"URL": "http://www.zoovision.com/apps-tv.html", "headerName": null, "Domain": "www.zoovision.com", "headerValue": null},
{"URL": "https://support.google.com/zagat/", "headerName": null, "Domain": "support.google.com", "headerValue": null},


Python Script File to convert a JSON file data into a CSV file: (json2csv.py)

import fileinput
import json
import csv
import sys

l = []
for line in fileinput.input():
    l.append(line)
myjson = json.loads(''.join(l))
keys = {}
for i in myjson:
    for k in i.keys():
        keys[k] = 1
mycsv = csv.DictWriter(sys.stdout, fieldnames=keys.keys(),
                       quoting=csv.QUOTE_MINIMAL)
mycsv.writeheader()
for row in myjson:
    mycsv.writerow(row)


$ python json2csv.py  fx23.json > out.csv

After executing above command, the output in the CSV file will be as follows:
http://www.zoovision.com/apps-tv.html,,www.zoovision.com,
https://support.google.com/zagat/,,support.google.com,

Now Suppose, You want to append a string at the end of each line. In other words, You want to add one more column to the CSV file, then following awk command can be used:

$ awk -F"," 'BEGIN { OFS = "," } {$4="Fx23,"; print}' out.csv  >  output.csv

It will produce output as follows:
http://www.zoovision.com/apps-tv.html,,www.zoovision.com,Fx23,
https://support.google.com/zagat/,,support.google.com,Fx23,


Python Script file to convert CSV file data into Sqlite Database: (csv2sqlite.py)

import csv, sqlite3
conn = sqlite3.connect("mydbrecord.sqlite")
curs = conn.cursor()
curs.execute("CREATE TABLE PCFC ( id INTEGER PRIMARY KEY, url TEXT, headerName TEXT, domain TEXT, headerValue TEXT, userAgent Text);")
counter = 1
reader = csv.reader(open('output.csv', 'r'), delimiter=',')
for row in reader:
    to_db = [counter, unicode(row[0], "utf8"), unicode(row[1], "utf8"), unicode(row[2], "utf8"), unicode(row[3], "utf8")]
    curs.execute("INSERT INTO PCFC (id, url, headerName, domain, headerValue) VALUES (?, ?, ?, ?, ?);", to_db)
    counter += 1
conn.commit()

Save above file and run following command to create a database.

$ python csv2sqlite.py

It will create a Sqlite database file with name mydbrecord.sqlite in the current working directory.