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
|
import os
import MySQLdb
import mdbConstants
def create_db(fields, rcrditer):
"""
Populates the mysql database with records from the record iter
"""
conn = MySQLdb.connect(db=mdbConstants._DBNAME, user=mdbConstants._USER)
cur = conn.cursor()
# Create the admin table
cur.execute('CREATE TABLE %s (ID int NOT NULL auto_increment, '\
'FIELDNAME TEXT, PRIMARY KEY(ID))' % mdbConstants._SCREEDADMIN)
for attribute in fields:
cur.execute("INSERT INTO %s (FIELDNAME) VALUES ('%s')" % \
(mdbConstants._SCREEDADMIN, attribute))
# Setup the dictionary table creation field substring
otherFields = fields[1:]
createsub = ['%s TEXT' % field for field in otherFields]
createsub.insert(0, '%s VARCHAR(100)' % fields[0])
createsub = ','.join(createsub)
# Create the dictionary table
cur.execute('CREATE TABLE %s (%s int NOT NULL auto_increment, %s, PRIMARY KEY(%s))' %
(mdbConstants._DICT_TABLE, mdbConstants._PRIMARY_KEY,
createsub,
mdbConstants._PRIMARY_KEY))
# Attribute to index
queryby = fields[0]
# Make the index on the 'queryby' attribute
cur.execute('CREATE UNIQUE INDEX %sidx ON %s(%s)' %
(queryby, mdbConstants._DICT_TABLE, queryby))
# Setup the 'perc' pgres substring
perc = ', '.join(['%s' for i in range(len(fields))])
# Setup the sql substring for inserting data into db
fieldsub = ','.join(fields)
# Pull data from rcrditer and store in database
for record in rcrditer:
data = tuple([record[key] for key in fields])
cur.execute('INSERT INTO %s (%s) VALUES (%s)' %\
(mdbConstants._DICT_TABLE, fieldsub, perc),
data)
conn.commit()
cur.close()
conn.close()
def droptables():
"""
Drops tables in db
"""
conn = MySQLdb.connect(db=mdbConstants._DBNAME, user=mdbConstants._USER)
cur = conn.cursor()
try:
cur.execute('DROP TABLE %s;' % mdbConstants._DICT_TABLE)
except:
pass
try:
cur.execute('DROP TABLE %s;' % mdbConstants._SCREEDADMIN)
except:
pass
conn.commit()
cur.close()
conn.close()
|
