audio-immersion/initialize_database.py at master · cms636/audio-immersion · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
import csv
import mysql.connector
from enum import Enum, auto
import json

config_file = "config.json"
with open(config_file) as json_file:
    config = json.load(json_file)

# Enums can be used to guarantee data is standardized, if desired
class Scale(Enum):
    personal = auto()
    mit = auto()
    historical = auto()

class Timeline(Enum):
    before_MIT = auto()
    at_MIT = auto()
    after_MIT = auto()

# connect to database
cnx = mysql.connector.connect(**config)
cnx.set_charset_collation('utf8')
cursor = cnx.cursor()

# write SQL for generating the three tables
audio_object_table = (
    "CREATE TABLE `audioobjs` ("
    " `id` varchar(36) NOT NULL,"
    " `emotional_register` int(2) NOT NULL,"
    " `timeframe` varchar(100) NOT NULL,"
    " `scale` varchar(100) NOT NULL,"
    " `count1` int(16) DEFAULT 0,"
    " `count2` int(16) DEFAULT 0,"
    " `count3` int(16) DEFAULT 0,"
    " PRIMARY KEY (`id`)"
    ") ENGINE=InnoDB charset=utf8"
)

mp3_table = (
    "CREATE TABLE `mp3s` ("
    " `id` varchar(36) NOT NULL,"
    " `emotional_register` int(2) NOT NULL,"
    " `timeframe` varchar(100) NOT NULL,"
    " `scale` varchar(100) NOT NULL,"
    " `count1` int(16) DEFAULT 0,"
    " `count2` int(16) DEFAULT 0,"
    " `count3` int(16) DEFAULT 0,"
    " PRIMARY KEY (`id`)"
    ") ENGINE=InnoDB charset=utf8"
)

interviewee_table = (
    "CREATE TABLE `interviewees` ("
    " `name` varchar(36) NOT NULL,"
    " `affiliation` varchar(1000) NOT NULL,"
    " `grad_year` int(4) DEFAULT NULL,"
    " `major` varchar(1000) DEFAULT NULL,"
    " `race` varchar(1000) NOT NULL,"
    " `count1` int(16) DEFAULT 0,"
    " `count2` int(16) DEFAULT 0,"
    " `count3` int(16) DEFAULT 0,"
    " PRIMARY KEY (`name`)"
    ") ENGINE=InnoDB charset=utf8"
)

# run if no table has yet been created
print("creating table")
cursor.execute(audio_object_table)
cursor.execute(mp3_table)
cursor.execute(interviewee_table)

# SQL command for adding an audio object to the table
add_audio_object = ("INSERT INTO audioobjs "
               "(id, emotional_register, timeframe, scale) "
               "VALUES (%s, %s, %s)")

with open('audio_clips.csv') as csvfile:
    reader = csv.reader(csvfile)
    for i, row in enumerate(reader):
        clip_data = tuple(row)
        try:
            cursor.execute(add_audio_object, clip_data)
            cnx.commit()
        except Exception as err:
            print(err)

cursor.close()
cnx.close()