-
Notifications
You must be signed in to change notification settings - Fork 4
/
create_database.py
114 lines (95 loc) · 3.53 KB
/
create_database.py
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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
"""
import sqlite3
import os
import time
con = sqlite3.connect('sqlite_database')
con.execute("create table if not exists word_record(code_id integer, image_id integer, dx REAL, dy REAL)")
start = time.time()
con.execute("create index if not exists icode_id on word_record(code_id)")
print end- start
end = time.time()
con.execute("create index if not exists iimage_id on word_record(image_id)")
print time.time() - end
end = time.time()
con.commit()
print time.time() - end
exit(1)
f_names = ["./book_cover_image_codes.txt_1_9", "./book_cover_image_codes.txt_2_14",\
"./book_cover_image_codes.txt_3_10", "./book_cover_image_codes.txt_4_10",\
"./book_cover_image_codes.txt_5_10", "./book_cover_image_codes.txt_6_10",\
"./book_cover_image_codes.txt_7_10"]#, "./book_cover_image_codes.txt_2_14",\
#"./book_cover_image_codes.txt_1_9", "./book_cover_image_codes.txt_2_14"]
print "inserting"
for f_name in f_names[:]:
print f_name
f = open(f_name,"r")
count = 0
time_count = time.time()
for line in f:
count += 1
if count % 100000 == 0:
final_count = time.time()
print count, final_count - time_count
time_count = final_count
image_id,code_id, dx,xy = line.split()
con.execute("insert into word_record values(%s, %s, %s, %s)"%(code_id,image_id,dx,xy))
f.close()
print "committing"
va = time.time()
con.commit()
print "committed", time.time()-va
# Print the table contents
start2 = time.time()
for row in con.execute("select * from word_record where image_id = 63717"):
#print row
pass
start = time.time()
for row in con.execute("select * from word_record where code_id = 24138207"):
#print row
pass
print time.time() - start
print start - start2
"""
import os
import MySQLdb
import time
conn=MySQLdb.connect(host="localhost",user="root",passwd="xxxxxx",db="book_cover",charset="utf8")
cur = conn.cursor()
cur.execute("create table if not exists word_record(code_id integer, image_id integer, dx REAL, dy REAL)")
cur.execute("CREATE INDEX if not exists code_id ON word_record(code_id);")
f_names = ["./book_cover_image_codes.txt_1_9", "./book_cover_image_codes.txt_2_14",\
"./book_cover_image_codes.txt_3_10", "./book_cover_image_codes.txt_4_10",\
"./book_cover_image_codes.txt_5_10", "./book_cover_image_codes.txt_6_10",\
"./book_cover_image_codes.txt_7_10", "./book_cover_image_codes.txt_8_10",\
"./book_cover_image_codes.txt_9_10", "./book_cover_image_codes.txt_10_10"]
print "inserting"
for f_name in f_names[2:]:
print f_name
f = open(f_name,"r")
count = 0
time_count = time.time()
for line in f:
count += 1
if count % 100000 == 0:
final_count = time.time()
print count, final_count - time_count
time_count = final_count
image_id,code_id, dx,xy = line.split()
cur.execute("insert into word_record values(%s, %s, %s, %s)"%(code_id,image_id,dx,xy))
f.close()
print "committing"
va = time.time()
#con.commit()
print "committed", time.time()-va
# Print the table contents
start2 = time.time()
#for row in cur.execute("select * from word_record where image_id = 63717"):
# #print row
# pass
start = time.time()
cur.execute("select * from word_record where code_id = 24138207")
for row in cur.fetchall():
#print row
pass
print time.time() - start
print start - start2