2 # -*- coding: utf-8 -*-
3 # vim: sw=4 ts=4 expandtab ai
5 """Module for work with database"""
15 """class for work with database"""
18 self.conn = self.connect()
22 """connect to database"""
23 dbname = "meabookdb_oblgas"
24 if os.access(dbname, os.F_OK|os.R_OK|os.W_OK):
25 conn = sqlite3.connect(dbname)
27 print "access to database file error"
31 def get_record(self, id):
33 """return record field"""
34 cur = self.conn.cursor()
35 cur.execute("select field.name, data.value "
36 "from data left join field "
37 "on data.id_field=field.id "
38 "where data.id=?", (id,))
41 print "key = %s value = %s" % (row[0], row[1])
42 self.data[row[0]] = row[1]
45 def get_allrecords(self, field, struct):
48 cur = self.conn.cursor()
49 cur.execute("select data.id, data.value "
50 "from data left join field "
51 "on data.id_field=field.id "
52 "left join relation on data.id=relation.id_data "
53 "where field.name=? and relation.id_struct=?", (field, struct))
57 self.data[row[0]] = row[1]
60 def get_id(self, table, value):
62 """get id for value into table"""
65 cur = self.conn.cursor()
66 query = "select id from %s where name='%s'" % (table, value)
71 query = "insert into %s (name) values ('%s')" % (table, value)
78 def get_parent(self, id):
82 cur = self.conn.cursor()
83 cur.execute("select parent from struct where id=?", (id,))
90 def get_name_struct(self, id):
94 cur = self.conn.cursor()
95 cur.execute("select name from struct where id=?", (id,))
102 def get_id_struct(self, value, parent):
104 """get id for value into struct"""
105 if self.conn is None:
107 cur = self.conn.cursor()
108 cur.execute("select id from struct where name=? and parent=?", (value, parent))
112 cur.execute("insert into struct (name, parent) values (?, ?)", (value, parent))
118 def ins_record(self, rec):
120 """insert record into database"""
122 #conn = self.connect()
123 if self.conn is None:
124 print "database connect error"
126 cur = self.conn.cursor()
127 cur.execute("select max(id) from data")
128 id_record = cur.fetchone()[0]
129 if id_record is None:
132 id_record = id_record + 1
133 print "id_record=%s" % (id_record,)
136 for key, val in rec.items():
139 id_field = self.get_id("field", key)
142 if key == 'department':
145 cur.execute("insert into data (id, id_field, value) values (?, ?, ?)",
146 (id_record, id_field, val))
147 #insert comppane into table struct
148 id0 = self.get_id("struct", struct[0])
149 cur.execute("update struct set parent=0 where id=?", (id0,))
151 id1 = self.get_id_struct(struct[1], id0)
152 #cur.execute("update struct set parent=? where id=?", (id0, id1))
153 cur.execute("insert into relation (id_data, id_struct) values (?, ?)", (id_record, id1))
157 def clear_data(self):
159 """clear all record from table data"""
160 conn = self.connect()
162 print "database connect error"
165 cur.execute("delete from data")
166 cur.execute("delete from field")
167 cur.execute("delete from struct")
168 cur.execute("delete from relation")
174 """create TreeStore from database table Struct"""
175 ts = gtk.TreeStore(int, str)
177 conn = self.connect()
181 cur.execute("select id, name, parent from struct")
185 item[row[0]] = ts.append(None, [row[0], row[1]])
187 ts.append(item[row[2]], [row[0], row[1]])
190 def get_list(self, parent):
192 """return list of struct with same parent"""
193 conn = self.connect()
197 cur.execute("select id, name from struct where parent=?", (parent,))
200 data[row[0]] = row[1]