Populate sample DB for CRUD app

create_contactsDB.sql

 DROP TABLE IF EXISTS person;
 DROP TABLE IF EXISTS address;
 DROP TABLE IF EXISTS phone_number;
 DROP TABLE IF EXISTS email;

 PRAGMA FOREIGN_KEYS = TRUE;

 CREATE TABLE person (
   ID INTEGER PRIMARY KEY,
   Title TEXT,
   FirstName TEXT NOT NULL,
   MidName TEXT,
   LastName TEXT NOT NULL,
   Suffix TEXT,
   DOB TEXT,
   AddressID INTEGER
 );

 CREATE TABLE address (
   ID INTEGER PRIMARY KEY,
   Street1 TEXT,
   Street2 TEXT,
   City TEXT NOT NULL,
   State TEXT NOT NULL,
   ZIP_Code TEXT NOT NULL
 );

 CREATE TABLE phone_number (
   ID INTEGER PRIMARY KEY,
   PersonID INTEGER NOT NULL REFERENCES person(ID),
   PhoneNumber TEXT NOT NULL,
   Type TEXT NOT NULL
 );

 CREATE TABLE email (
   ID INTEGER PRIMARY KEY,
   PersonID INTEGER NOT NULL REFERENCES person(ID),
   EmailAddress TEXT NOT NULL,
   Type TEXT NOT NULL
 );

populate_contactsDB.py

import random
import sqlite3
import string

from church import Personal, Address
import radar

#  Adapted from http://stackoverflow.com/questions/19472922/reading-external-sql-script-in-python/19473206#19473206
#  and adjusted to work with Python 3.x
#  Create function to import & execute SQL file
def executeScriptsFromFile(filename):

    # Open and read the file as a single buffer
    fd = open(filename, 'r')
    sqlFile = fd.read()
    fd.close()

    # All SQL commands (split on ';')
    sqlCommands = sqlFile.split(';')

    # Execute  every command from the input file
    for command in sqlCommands:
        # This will skip and report errors
        # For example, if the tables do not exist yet, this will skip over
        # the DROP TABLE commands
        try:
            c.execute(command)
        except Exception as inst:
            print("Command skipped: ", inst)


person = Personal('en')
address = Address('en')

#  Populate sample_data list with FirstName, MidInit, LastName, Telephone, Email, Street Address, City, State, Postal
#  code and Date-of-Birth
sample_data = []
for i in range(10):
    row = [person.name(), random.choice(string.ascii_letters[26:]), person.surname(), person.telephone(),
           person.email(), address.address(), address.city(), address.state(), address.postal_code(),
           str(radar.random_date(start='1960-01-01', stop='2000-12-31'))]

    sample_data.append(row)

#  Records should contain the following:
#  ['Title','FirstName','MidName','LastName','Suffix','DOB','']
people = []
for i in range(len(sample_data)):
    peep = [sample_data[i][0], sample_data[i][1], sample_data[i][2], sample_data[i][9]]
    people.append(peep)

people_sql = '''INSERT INTO person (FirstName, MidName, LastName, DOB) VALUES (?, ?, ?, ?)'''

#  Records should contain the following:
#  ['PersonID', 'PhoneNumber', 'Type']
phone_numbers = []
for i in range(len(sample_data)):
    row = [i+1, sample_data[i][3], random.choice(['Home', 'Work', 'Mobile'])]
    phone_numbers.append(row)

phone_number_sql = '''INSERT INTO phone_number (PersonID,PhoneNumber,Type) VALUES (?, ?, ?)'''

#  Records should contain the following:
#  ['PersonID', 'EmailAddress', 'Type']
email_addresses = []
for i in range(len(sample_data)):
    # Originally had random.randint(1, len(sample_data)) for 'PersonID' so as to simulate a one-to-many
    # relationship between people and emails, but opted for simplicity while creating intial DB
    row = [i+1, sample_data[i][4], random.choice(['Home', 'Work', 'School'])]
    email_addresses.append(row)

email_sql = '''INSERT INTO email (PersonID, EmailAddress, Type) VALUES (?, ?, ?)'''

#  Records should contain the following:
#  ['StreetAddress', 'City', 'State', 'PostalCode']
addresses = []
for i in range(len(sample_data)):
    row = [sample_data[i][5], sample_data[i][6], sample_data[i][7], sample_data[i][8]]
    addresses.append(row)

address_sql = '''INSERT INTO address (Street1, City, State, ZIP_Code) VALUES (?, ?, ?, ?)'''

# set_member_address_sql = '''UPDATE person SET AddressID = (SELECT ID FROM person WHERE FirstName = 'Kathleen')'''


#  Create database connection
conn = sqlite3.connect('contacts.db')
c = conn.cursor()

#  Set up and define database schema; will wipe clean each time it is run!
executeScriptsFromFile('create_contactsDB.sql')

#  Populate database with initial data
c.executemany(people_sql, people)
c.executemany(address_sql, addresses)
# c.execute(set_member_address_sql, '')
c.executemany(phone_number_sql, phone_numbers)
c.executemany(email_sql, email_addresses)

#  Commit changes and close connection
c.close()
conn.commit()
conn.close()