SQLite wrapper

 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
class DataBase():
def __init__(self, file_path):
self.path = file_path
self.tables = []
# TODO: сделать получение таблиц из существующей базы
return
def connect(self):
self.conn = sq3.connect(self.path)
self.cur = self.conn.cursor()
return
def create_table(self, tablename, items):
'''
items - список полей с типом данных в виде словаря. Пример:
{'id':'key', 'date':'text', 'price':'real'}
'''
instruction = ''
for field, ftype in items.items():
if instruction != '': instruction += ', '
if ftype == 'key':
instruction += field+' integer PRIMARY KEY'
elif ftype == 'integer' or ftype == 'real' or ftype == 'float':
instruction += field+' '+ftype+' default 0'
elif ftype == 'text':
instruction += field+' '+ftype+' default ""'
instruction = 'CREATE TABLE IF NOT EXISTS '+tablename.lower()+' ('+instruction+')'
# print (instruction)
self.cur.execute(instruction)
self.tables.append(tablename)
self.conn.commit()
return
def insert_data(self, tablename, data):
'''
data - данные для внесения в виде словаря. Пример:
{'date':'12.04.2014', 'price':25.4}
Так же возможно передать список подобных словарей.
'''
def insert(insert_data):
instruction = ''
fields = []
values = []
for field, value in insert_data.items():
fields.append(field)
values.append(str(value))
instruction = 'INSERT INTO '+tablename.lower()+' ('+','.join(fields)+') VALUES ("'+'","'.join(values)+'")'
self.cur.execute(instruction)
return
if type(data) == type({}):
insert(data)
elif type(data) == type([]):
for i in data:
insert(i)
self.conn.commit()
return
def get_if(self, tablename, field, condition):
'''
field - имя(или список имен) запрашиваемого поля
condition - условия поиска в виде словаря. Пример:
{'data':'2014.05.14', 'price':25.4}
'''
if type(field) == type([]):
fields = ','.join(field)
else: fields=field
if condition == {}:
i = []
else:
i = [field+'="'+str(value)+'"' for field, value in condition.items()]
print(i)
instruction = 'SELECT '+fields+' FROM '+tablename
if condition != {}:
instruction += ' WHERE ' +','.join(i)
print(instruction)
self.cur.execute(instruction)
return self.cur.fetchall()
def update(self, tablename, set, condition):
'''
set - словарь заменяемых значений, пододный condition.
condition - условия поиска в виде словаря. Пример:
{'data':'2014.05.14', 'price':25.4}
'''
fields = ','.join([field+'="'+str(value)+'"' for field, value in set.items()])
if condition == {}:
i = []
else:
i = [field+'="'+str(value)+'"' for field, value in condition.items()]
print('fields:\t', fields, '\nwhere:\t', i)
instruction = 'UPDATE '+tablename+' SET '+fields
if condition != {}:
instruction += ' WHERE ' +','.join(i)
print('instruction:\t', instruction)
self.cur.execute(instruction)
self.conn.commit()
return