Untitled

# csv-to-tables.py

# writes data to single table.

 

import re

import MySQLdb as mdb

 

import sys

 

con = None

        ### Need to make change in how "'" is delt with in name strings!

 

try:

    # -*- coding: utf-8 -*-

    con = mdb.connect('localhost', 'johnhitz', 'anim@6090', 'hot_dogs');

   

    with con:

        cur = con.cursor()

 

        # person table

        cur.execute("CREATE TABLE IF NOT EXISTS person(person_id INT PRIMARY KEY AUTO_INCREMENT, last_name VARCHAR(25), first_name VARCHAR(25)) ENGINE=InnoDB")

 

        # contact table

        cur.execute("CREATE TABLE IF NOT EXISTS contact(contact_id INT PRIMARY KEY AUTO_INCREMENT, person_id INT, INDEX(person_id), FOREIGN KEY(person_id) REFERENCES person(person_id), address VARCHAR(50), city VARCHAR(50), phone VARCHAR(10)) Engine=InnoDBP")

       

        # animal table

        cur.execute("CREATE TABLE IF NOT EXISTS animal(animal_id INT PRIMARY KEY AUTO_INCREMENT, person_id INT,  dog_name VARCHAR(30), breed VARCHAR(30),color VARCHAR(10), weight VARCHAR(10), age VARCHAR(10), sex VARCHAR(5), INDEX(person_id), FOREIGN KEY(person_id) REFERENCES person(person_id)) ENGINE=InnoDB")

 

        # micro_chip table

        cur.execute("CREATE TABLE IF NOT EXISTS micro_chip(micro_id INT PRIMARY KEY AUTO_INCREMENT, person_id INT, micro_chip VARCHAR(10), INDEX(person_id), FOREIGN KEY(person_id) REFERENCES person(person_id)) ENGINE=InnoDB")

 

        # comments table

        cur.execute("CREATE TABLE IF NOT EXISTS comments(comment_id INT PRIMARY KEY AUTO_INCREMENT, person_id INT, INDEX(person_id), FOREIGN KEY(person_id) REFERENCES person(person_id), animal_id INT, INDEX(animal_id), FOREIGN KEY(animal_id) REFERENCES animal(animal_id), comments VARCHAR(200)) ENGINE=InnoDB")

 

        # registration table

        cur.execute("CREATE TABLE IF NOT EXISTS registration(reg_id INT PRIMARY KEY AUTO_INCREMENT, person_id INT, INDEX(person_id), FOREIGN KEY(person_id) REFERENCES person(person_id), tag_num VARCHAR(10), tag_type VARCHAR(20), tag_num_2006 VARCHAR(10), registration VARCHAR(20), registration_due VARCHAR(10)) ENGINE=InnoDB")

 

        # vacination table

        cur.execute("CREATE TABLE IF NOT EXISTS vacinations(vac_id INT PRIMARY KEY AUTO_INCREMENT, person_id INT, INDEX(person_id), FOREIGN KEY(person_id) REFERENCES person(person_id), vac_date VARCHAR(10), revac_date VARCHAR(10), issued_by VARCHAR(30),  vac_date_06 VARCHAR(10), revac_date_06 VARCHAR(10)) ENGINE=InnoDB")

 

except mdb.Error, e:

    print "Error %d: %s" % (e.args[0], e.args[1])

finally:

        if con:

            con.close()

 

# keys = ["Tag Number","Type","Last Name","First Name","Address","City","Phone","Dog Name","Breed","Color","Weight","Age","Sex","Comments","Vac Date","Revac Date","Registration","Issued By","Micro Chip .","2006 Tag Number","06 Vac Date","06 Revac Date","Registration Due"]