sqlite3 + json

catdb.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys
import json

from pprint import pprint

with open('cats.json') as data_file:
    cat_data = json.load(data_file)

pprint(cat_data)

with open('owners.json') as data_file:
    owner_data = json.load(data_file)

pprint(owner_data)

con = None

try:
    con = lite.connect('cats.db')

    cur = con.cursor()

    cur.execute("DROP TABLE IF EXISTS cats")
    cur.execute("DROP TABLE IF EXISTS owners")

    cur.execute("""CREATE TABLE owners (
                     id int PRIMARY KEY,
                     human_label text)""")

    cur.execute("""CREATE TABLE cats (
                     cat_id int PRIMARY KEY,
                     id int,
                     owner_id int,
                     uuid text,
                     cat_label text,
                     FOREIGN KEY (owner_id) REFERENCES owners(id))""")

    for row in owner_data:
        cur.execute("""INSERT INTO owners VALUES (
                        ?,
                        ?)""", (row["id"],
                                row["human-label"]))

    for row in cat_data:
        cur.execute("""INSERT INTO cats VALUES (
                        ?,
                        ?,
                        ?,
                        ?,
                        ?)""", (row["cat-id"],
                                row["id"],
                                row["owner-id"],
                                row["uuid"],
                                row["cat-label"]))

    con.commit()

    cur.execute("SELECT * FROM cats")

    results = cur.fetchall()

    print "\nAll the cats table:"
    for c in results:
        print "  %d\t%d\t%d\t%s\t%s" % (c[0], c[1], c[2], c[3], c[4])
    print "Query: SELECT * FROM cats"


    my_cats_query = """SELECT cats.cat_label
       FROM cats
       JOIN owners ON cats.owner_id = owners.id
       WHERE owners.human_label = 'Libby'"""
    cur.execute(my_cats_query)

    results = cur.fetchall()

    print "\nJust my cats:"
    for c in results:
        print "    %s" % c[0]
    print "Query: %s" % my_cats_query

except lite.Error, e:

    print "Error %s:" % e.args[0]
    sys.exit(1)

finally:

    if con:
        con.close()

cats.json

[{ "cat-id": 1,
   "id": 1,
   "owner-id":1,
   "uuid": "08281565-1acc-4fe0-aa6d-694c87f3e6cd",
   "cat-label":"McGonagall"},
 { "cat-id": 2,
   "id": 2,
   "owner-id": 1,
   "uuid": "12c20989-8693-47bd-baed-6f59291c426f",
   "cat-label":"Haskell"},
 { "cat-id": 3,
   "id": 3,
   "owner-id": 2,
   "uuid": "3cb56d84-8eed-44ca-ae1d-6935a74b5a39",
   "cat-label":"Brigadier General Saxby Chambliss"},
 { "cat-id": 4,
   "id": 4,
   "owner-id": 2,
   "uuid": "fe051b57-e240-4185-8d0b-c6ca84d7d36f",
   "cat-label":"Milka"}
]

owners.json

[{ "id": 1,
   "human-label":"Libby"},
 { "id": 2,
   "human-label":"Cari"}]