Parses json from https://randomuser.me/ and stores the info into sqlite DB

getRandUser.py

import sys
import sqlite3
import urllib.request
import json

## Parses json from https://randomuser.me/ and puts the info into sqlite DB

def insertN(string, n):
    if string[-1]=='/':
        return string+'?results='+str(n)
    else:
        return string+'&results='+str(n)

def insertSeed(string, seed='a0471bc7979371df'):
    if string[-1]=='/':
        return string+'?seed='+seed
    else:
        return string+'&seed='+seed
    
# Execute this with cur.execute if it is desirable to drop table first.
'''
    DROP TABLE IF EXISTS login;
    DROP TABLE IF EXISTS name;
    DROP TABLE IF EXISTS gender;
    DROP TABLE IF EXISTS location;
    DROP TABLE IF EXISTS ident;
    DROP TABLE IF EXISTS picture;
    DROP TABLE IF EXISTS nationality;
    DROP TABLE IF EXISTS member;
'''

if __name__ == "__main__":
    # Set number of users to get
    N=50
    
    # Setup Database
    conn = sqlite3.connect('content.sqlite')
    cur = conn.cursor()
    conn.text_factory = str
    cur.executescript('''
    CREATE TABLE IF NOT EXISTS login 
        (
        id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, 
        username TEXT UNIQUE NOT NULL, 
        password TEXT NOT NULL, 
        salt TEXT, 
        md5 TEXT,
        sha1 TEXT,
        sha256 TEXT
        );
        
    CREATE TABLE IF NOT EXISTS name
        (
        id INTEGER,
        title INTEGER, 
        first TEXT, 
        last TEXT
        );
        
    CREATE TABLE IF NOT EXISTS gender
        (
        gender_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
        gen TEXT UNIQUE
        );
        
    CREATE TABLE IF NOT EXISTS location
        (
        id INTEGER,
        street TEXT,
        city TEXT,
        state TEXT,
        postcode INTEGER
        );
        
    CREATE TABLE IF NOT EXISTS ident
        (
        id INTEGER,
        type TEXT,
        value TEXT
        );
        
    CREATE TABLE IF NOT EXISTS picture
        (
        id integer,
        large TEXT,
        medium TEXT,
        thumbnail TEXT
        );
        
    CREATE TABLE IF NOT EXISTS nationality
        (
        nat_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
        nat TEXT UNIQUE
        );
        
    CREATE TABLE IF NOT EXISTS member
        (
        id INTEGER,
        gender INTEGER,
        email TEXT,
        dob DATETIME,
        regit DATETIME,
        home TEXT,
        cell TEXT,
        nat INTEGER
        );
    ''')

    # Get and read the json document from the api
    baseurl = "http://api.randomuser.me/"
    request = urllib.request.urlopen(insertSeed(insertN(baseurl,N)))
    document = request.read().decode('utf-8')
    json_data=json.loads(document)

    # If results is not in the json returned, something is wrong
    if "results" not in json_data.keys():
        print('Results not in JSON, please check')
        print(json_data)
        exit()
        
    # printing seed for reference
    print(json_data["info"]["seed"])
    
    # Looking at each result entry from json_data
    for entry in json_data["results"]:
        name = entry["name"]
        gender = entry["gender"]
        login = entry["login"]
        location = entry["location"]
        email = entry["email"]
        dob = entry["dob"]
        registered = entry["registered"]
        home = entry["phone"]
        cell = entry["cell"]
        ident = entry["id"]
        picture = entry["picture"]
        nationality = entry["nat"]
        
        # Check if username is repeated
        if len(cur.execute('''SELECT username FROM login WHERE username = ?        
        ''', (login["username"],)).fetchall())>0:
            print(login["username"], 'is already in use!')
            continue
        
        # Build login details
        cur.execute('''INSERT OR IGNORE INTO login (username, password, salt, 
        md5, sha1, sha256) VALUES (?, ?, ?, ?, ? ,?)''', \
        (login["username"], login["password"], login["salt"], \
        login["md5"], login["sha1"], login["sha256"],))
        cur.execute('SELECT id FROM login WHERE username = ?', (login["username"],))
        id=cur.fetchone()[0]
        
        # Build name details
        cur.execute('''INSERT OR IGNORE INTO name (id, title, first, last) 
        VALUES (?, ?, ?, ?)''', (id, name["title"], name["first"], \
        name["last"],))
        
        # Build gender
        cur.execute('''INSERT OR IGNORE INTO gender (gen) VALUES (?)''', \
        (gender, ))
        
        gen_id = cur.execute('SELECT gender_id FROM gender WHERE gen = ?',\
        (gender,)).fetchone()[0]
        
        # Build location details
        cur.execute('''INSERT OR IGNORE INTO location (id, street, city,
        state, postcode) VALUES (?, ?, ?, ?, ?)''', (id, location["street"],
        location["city"], location["state"], location["postcode"], ))
        
        # Build picture url
        cur.execute('''INSERT OR IGNORE INTO picture (id, thumbnail,
        medium, large) VALUES (?, ?, ?, ?)''', (id, picture["thumbnail"],
        picture["medium"], picture["large"],))
        
        # Build nationality
        cur.execute('''INSERT OR IGNORE INTO nationality (nat) VALUES (?)''', \
        (nationality,))
        nat_id = cur.execute('SELECT nat_id FROM nationality WHERE nat = ?',\
        (nationality,)).fetchone()[0]
        
        # Build identification details
        cur.execute('''INSERT OR IGNORE INTO ident (id, type, value) VALUES
        (?, ?, ?)''', (id, ident["name"], ident["value"], ))
        
        # Place into member table
        cur.execute('''INSERT OR IGNORE INTO member (id, gender, 
        email, dob, regit, home, cell, nat)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)''', (id, gen_id, email, dob, \
        registered, home, cell, nat_id,))
    
    # Save changes, if going for 2,000 entries, better to commit in stages
    conn.commit()