-
Notifications
You must be signed in to change notification settings - Fork 2
/
db_obj.py
131 lines (101 loc) · 4.78 KB
/
db_obj.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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
#!/usr/bin/python
import sqlite3
import sys
class DB_Generic():
'''generic class to add DB functionality'''
def __init__(self, table_name, db_path = ''):
#par= name of the table to be used
self.table_name = table_name
if len(db_path) > 0:
self.db_path = db_path
print db_path
def get_data_generic(self, range = 1, bac_to_get = 0):
'''gets the data from the database
generic so far, needs to be updated to include range'''
if sys.platform == 'darwin':
(cursor, database) = link_db(self.db_path)
else:
(cursor, database) = link_db()
if range == 1:
cursor.execute("""SELECT * FROM %s""" % self.table_name)
elif range == 2:
cursor.execute("""SELECT * FROM %s where idbac = %d""" % (self.table_name, bac_to_get))
table_data = cursor.fetchall()
raw_data = []
for i in table_data:
raw_data.append(list(i))
self.table_data = raw_data
database.close()
def insert_data(self, values_list, insert_string):
'''inserts data in the database'''
if sys.platform == 'darwin':
(cursor, database) = link_db(self.db_path)
else:
(cursor, database) = link_db()
cursor.execute(insert_string % self.table_name, values_list)
database.commit()
database.close()
def update_data(self, update_string):
'''edits and updates fields'''
if sys.platform == 'darwin':
(cursor, database) = link_db(self.db_path)
else:
(cursor, database) = link_db()
cursor.execute(update_string)
database.commit()
database.close()
def delete_data(self, delete_string):
'''deletes one field'''
if sys.platform == 'darwin':
(cursor, database) = link_db(self.db_path)
else:
(cursor, database) = link_db()
cursor.execute(delete_string)
database.commit()
database.close()
class Bac(DB_Generic):
def __init_(self):
self.bac_data = []
if sys.platform == 'darwin':
DB_Generic.__init__(self, bac, db_path)
else:
DB_Generic.__init__(self, 'bac')
def get_data(self, range = 1, bac_to_get = 0):
self.get_data_generic(range, bac_to_get)
return self.table_data
def load_data(self):
pass
def add_data(self, values_list):
insert_string = """INSERT INTO %s (projects, comments, temperature, cell, box, tubes, chromosome, sdate, clone, source,
location1, startpos, endpos, gene, genelink, dnaex, validation, pcr, refs, antibiotic)
VALUES (:projects, :comments, :temperature, :cell, :box, :tubes, :chromosome, :sdate, :clone, :source, :location1, :startpos,
:endpo, :gene, :genelink, :dnaex, :validation, :pcr, :refs, :antibiotic)"""
self.insert_data(values_list, insert_string)
def edit_data(self, values_list):
update = ','.join(['%s=\"%s\"' % (y, values_list[y]) for y in values_list])
edit_string = 'UPDATE bac set ' + update + ' where idbac=' + values_list['idbac']
self.update_data(edit_string)
def delete_entry(self, idbac):
delete_string = "DELETE from bac WHERE idbac = %s" % idbac
self.delete_data(delete_string)
##########################
def link_db(db_path =''):
'''initializes the database file'''
try:
if not sys.platform == 'darwin':
db = sqlite3.connect("samples.db")
else:
print db_path
db = sqlite3.connect(str(db_path))
except sqlite3.Error, errmsg:
print 'DB not available ' + str(errmsg)
sys.exit()
db_cursor = db.cursor()
return db_cursor, db
#"legacy code"
# cursor.execute('UPDATE %s set %s where %s=%s' % self.table_name, insert, values_list, "idbac", )
# cursor.execute("UPDATE bac SET projects = ?, comments = ?, temperature = ?, cell = ?, box = ?, tubes = ?, chromosome = ?, sdate = ?, clone = ?, source = ?, location1 = ?, startpos = ?, endpos = ?, gene = ?, genelink = ?, dnaex = ?, validation = ?, pcr = ?, refs = ?, antibiotic = ? WHERE idbac = ?",
# (values_list['projects'], values_list['comments'], values_list['temperature'], values_list['cell'], values_list['box'], values_list['tubes'],
# values_list['chromo'], values_list['date'], values_list['clone'], values_list['source'], values_list['location'], values_list['start'], values_list['end'],
# values_list['gene'], values_list['genelink'], values_list['dna'], values_list['validation'], values_list['pcr'],
# values_list['refs'], values_list['antibiotic'], values_list['idbac']))