Export JSON to SQLite

In [1]:
# General packages: Operating System Interface, System-specific parameters and functions, 
# Regular expression operations, SQLite3 database, JSON capability, 
# Data structure and analysis tools, Unix style pathname pattern expansion
import os, sys, re, sqlite3, json, pandas, glob

# Ordered entries for dictionary-type
from collections import OrderedDict

# Date-parsing and manipulation
import datetime
import dateutil.parser

Read JSON-data

In [2]:
try:
    with open('Data/Applications.json', 'r') as data_file:
        data = json.load(data_file, object_pairs_hook=OrderedDict)
    pass
except IOError as e:
    print (e)
    pass

Export to DataFrame

In [3]:
df = pandas.DataFrame([], index=[], columns=['Title', 'Date', 'creation_date', 'modification_date', 
                                             'Interview', 'Offer', 'Reply', 'Words', 'Sentences', 
                                             'Lines', 'Readability', 'flesch_reading_ease', 
                                             'smog_index', 'flesch_kincaid_grade', 
                                             'coleman_liau_index', 'automated_readability_index', 
                                             'dale_chall_readability_score', 'difficult_words', 
                                             'linsear_write_formula', 'gunning_fog'])
for index, item in data.items():
    df.loc[index] = [item['Title'], 
                     item['Date'], 
                     item['creation_date'], 
                     item['modification_date'], 
                     item['Results']['Interview'], 
                     item['Results']['Offer'], 
                     item['Results']['Reply'],
                     item['Descriptive']['Words'],
                     item['Descriptive']['Sentences'],
                     item['Descriptive']['Lines'],
                     item['Analytical']['Readability']['text_standard'],
                     item['Analytical']['Readability']['flesch_reading_ease'],
                     item['Analytical']['Readability']['smog_index'],
                     item['Analytical']['Readability']['flesch_kincaid_grade'],
                     item['Analytical']['Readability']['coleman_liau_index'],
                     item['Analytical']['Readability']['automated_readability_index'],
                     item['Analytical']['Readability']['dale_chall_readability_score'],
                     item['Analytical']['Readability']['difficult_words'],
                     item['Analytical']['Readability']['linsear_write_formula'],
                     item['Analytical']['Readability']['gunning_fog']
                    ]

Write to SQLite

In [4]:
try:
    db = sqlite3.connect('Data/Applications.db')
    cur = db.cursor()
    df.to_sql(name='applications', con=db, index=False, if_exists='replace')
except Exception as e:
    db.rollback()
    raise e
finally:
    db.close()
    print ('Saved Data/Applications.db.')
Saved Data/Applications.db.