-
Notifications
You must be signed in to change notification settings - Fork 1
/
database.py
92 lines (71 loc) · 2.54 KB
/
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
import sqlite3
class DatabaseManager:
def __init__(self, database_filename):
self.connection = sqlite3.connect(database_filename)
def __del__(self):
self.connection.close()
def _execute(self, statement, values=None):
with self.connection:
cursor = self.connection.cursor()
cursor.execute(statement, values or [])
return cursor
def create_table(self, table_name, columns):
columns_with_types = [
f'{column_name} {data_type}'
for column_name, data_type in columns.items()
]
self._execute(
f'''
CREATE TABLE IF NOT EXISTS {table_name}
({', '.join(columns_with_types)});
'''
)
def add(self, table_name, data):
column_names = ', '.join(data.keys())
placeholder = ', '.join('?' * len(data))
column_values = tuple(data.values())
self._execute(
f'''
INSERT INTO {table_name}
({ column_names })
VALUES ({placeholder});
''',
column_values
)
def update(self, table_name, condition, data):
update_placeholders = [f'{column} = ?' for column in condition.keys()]
update_condition = ' AND '.join(update_placeholders)
data_placeholders = ', '.join(f'{key} = ?' for key in data.keys())
values = tuple(data.values()) + tuple(condition.values())
self._execute(
f'''
UPDATE {table_name}
SET {data_placeholders}
WHERE {update_condition};
''',
values,
)
def delete(self, table_name, conditions):
placeholder = [f'{column} = ?' for column in conditions.keys()]
delete_conditions = 'AND '.join(placeholder)
self._execute(
f'''
DELETE FROM { table_name }
WHERE { delete_conditions };
''',
tuple(conditions.values()
)
)
def select(self, table_name, conditions=None, order_by=None):
conditions = conditions or {}
query = f'SELECT * FROM {table_name}'
if conditions:
placeholder = [f'{column} = ?' for column in conditions.keys()]
select_conditions = ' AND '.join(placeholder)
query += f' WHERE {select_conditions}'
if order_by:
query += f' ORDER BY {order_by}'
return self._execute(
query,
tuple(conditions.values()),
)