PNHptm9Q

1# Creating a database table  from a cvs file
2import csv, sqlite3
3conn = sqlite3.connect('wrangling/data_wrangling.sqlite')
4cur = conn.cursor()
5cur.executescript('''
6
7DROP TABLE IF EXISTS view_item_event;
8
9CREATE TABLE view_item_event(
10    event_id VARCHAR(32) NOT NULL PRIMARY KEY,
11    event_time VARCHAR(26),
12    user_id INT(10),
13    platform VARCHAR(26) ,
14    item_id INT(10),
15    referrer VARCHAR(26)
16);
17''')
18
19with open('wrangling/view_table2.csv', 'rb' ) as theFile:
20    reader = csv.DictReader(theFile)
21    for line in reader:
22        to_db1 = [line.get('event_id'), line.get('event_time'), line.get('user_id'), 
23                  line.get('platform'), unicode(line.get('item_id'), 'utf8'), unicode(line.get('referrer'), 'utf8')]
24        cur.execute("INSERT OR IGNORE INTO view_item_event (event_id, event_time, user_id, platform, item_id, referrer) VALUES( ?, ?, ?, ?, ?, ?);", to_db1)
25        conn.commit()
26conn.close()