/
db.py
369 lines (335 loc) · 11.5 KB
/
db.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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
import sqlite3
from collections import namedtuple
import datetime
import json
Column = namedtuple(
"Column", ("cid", "name", "type", "notnull", "default_value", "is_pk")
)
ForeignKey = namedtuple(
"ForeignKey", ("table", "column", "other_table", "other_column")
)
Index = namedtuple("Index", ("seq", "name", "unique", "origin", "partial", "columns"))
class Database:
def __init__(self, filename_or_conn):
if isinstance(filename_or_conn, str):
self.conn = sqlite3.connect(filename_or_conn)
else:
self.conn = filename_or_conn
def __getitem__(self, table_name):
return Table(self, table_name)
def __repr__(self):
return "<Database {}>".format(self.conn)
@property
def table_names(self):
return [
r[0]
for r in self.conn.execute(
"select name from sqlite_master where type = 'table'"
).fetchall()
]
@property
def tables(self):
return [self[name] for name in self.table_names]
def execute_returning_dicts(self, sql, params=None):
cursor = self.conn.execute(sql, params or tuple())
keys = [d[0] for d in cursor.description]
return [dict(zip(keys, row)) for row in cursor.fetchall()]
def create_table(
self, name, columns, pk=None, foreign_keys=None, column_order=None
):
foreign_keys = foreign_keys or []
foreign_keys_by_name = {fk[0]: fk for fk in foreign_keys}
column_items = list(columns.items())
if column_order is not None:
column_items.sort(
key=lambda p: column_order.index(p[0]) if p[0] in column_order else 999
)
extra = ""
col_type_mapping = {
float: "FLOAT",
int: "INTEGER",
bool: "INTEGER",
str: "TEXT",
bytes.__class__: "BLOB",
bytes: "BLOB",
datetime.datetime: "TEXT",
None.__class__: "TEXT",
}
columns_sql = ",\n".join(
" [{col_name}] {col_type} {primary_key} {references}".format(
col_name=col_name,
col_type=col_type_mapping[col_type],
primary_key=" PRIMARY KEY" if (pk == col_name) else "",
references=(
" REFERENCES [{other_table}]([{other_column}])".format(
other_table=foreign_keys_by_name[col_name][2],
other_column=foreign_keys_by_name[col_name][3],
)
if col_name in foreign_keys_by_name
else ""
),
)
for col_name, col_type in column_items
)
sql = """CREATE TABLE [{table}] (
{columns_sql}
){extra};
""".format(
table=name, columns_sql=columns_sql, extra=extra
)
self.conn.execute(sql)
return self[name]
def create_view(self, name, sql):
self.conn.execute(
"""
CREATE VIEW {name} AS {sql}
""".format(
name=name, sql=sql
)
)
class Table:
def __init__(self, db, name):
self.db = db
self.name = name
self.exists = self.name in self.db.table_names
def __repr__(self):
return "<Table {}{}>".format(
self.name, " (does not exist yet)" if not self.exists else ""
)
@property
def count(self):
return self.db.conn.execute(
"select count(*) from [{}]".format(self.name)
).fetchone()[0]
@property
def columns(self):
if not self.exists:
return []
rows = self.db.conn.execute(
"PRAGMA table_info([{}])".format(self.name)
).fetchall()
return [Column(*row) for row in rows]
@property
def foreign_keys(self):
fks = []
for row in self.db.conn.execute(
"PRAGMA foreign_key_list([{}])".format(self.name)
).fetchall():
if row is not None:
id, seq, table_name, from_, to_, on_update, on_delete, match = row
fks.append(
ForeignKey(
table=self.name,
column=from_,
other_table=table_name,
other_column=to_,
)
)
return fks
@property
def schema(self):
return self.db.conn.execute(
"select sql from sqlite_master where name = ?", (self.name,)
).fetchone()[0]
@property
def indexes(self):
sql = 'PRAGMA index_list("{}")'.format(self.name)
indexes = []
for row in self.db.execute_returning_dicts(sql):
index_name = row["name"]
index_name_quoted = (
'"{}"'.format(index_name)
if not index_name.startswith('"')
else index_name
)
column_sql = "PRAGMA index_info({})".format(index_name_quoted)
columns = []
for seqno, cid, name in self.db.conn.execute(column_sql).fetchall():
columns.append(name)
row["columns"] = columns
# These coluns may be missing on older SQLite versions:
for key, default in {"origin": "c", "partial": 0}.items():
if key not in row:
row[key] = default
indexes.append(Index(**row))
return indexes
def create(self, columns, pk=None, foreign_keys=None, column_order=None):
columns = {name: value for (name, value) in columns.items()}
self.db.create_table(
self.name,
columns,
pk=pk,
foreign_keys=foreign_keys,
column_order=column_order,
)
self.exists = True
return self
def create_index(self, columns, index_name=None):
if index_name is None:
index_name = "idx_{}_{}".format(
self.name.replace(" ", "_"), "_".join(columns)
)
sql = """
CREATE INDEX {index_name}
ON {table_name} ({columns});
""".format(
index_name=index_name, table_name=self.name, columns=", ".join(columns)
)
self.db.conn.execute(sql)
return self
def drop(self):
return self.db.conn.execute("DROP TABLE {}".format(self.name))
def add_foreign_key(self, column, column_type, other_table, other_column):
sql = """
ALTER TABLE {table} ADD COLUMN {column} {column_type}
REFERENCES {other_table}({other_column});
""".format(
table=self.name,
column=column,
column_type=column_type,
other_table=other_table,
other_column=other_column,
)
self.db.conn.execute(sql)
self.db.conn.commit()
return self
def enable_fts(self, columns, fts_version="FTS5"):
"Enables FTS on the specified columns"
sql = """
CREATE VIRTUAL TABLE "{table}_fts" USING {fts_version} (
{columns},
content="{table}"
);
""".format(
table=self.name, columns=", ".join(columns), fts_version=fts_version
)
self.db.conn.executescript(sql)
self.populate_fts(columns)
return self
def populate_fts(self, columns):
sql = """
INSERT INTO "{table}_fts" (rowid, {columns})
SELECT rowid, {columns} FROM {table};
""".format(
table=self.name, columns=", ".join(columns)
)
self.db.conn.executescript(sql)
return self
def detect_column_types(self, records):
all_column_types = {}
for record in records:
for key, value in record.items():
all_column_types.setdefault(key, set()).add(type(value))
column_types = {}
for key, types in all_column_types.items():
if len(types) == 1:
t = list(types)[0]
# But if it's list / tuple / dict, use str instead as we
# will be storing it as JSON in the table
if t in (list, tuple, dict):
t = str
elif {int, bool}.issuperset(types):
t = int
elif {int, float, bool}.issuperset(types):
t = float
elif {bytes, str}.issuperset(types):
t = bytes
else:
t = str
column_types[key] = t
return column_types
def search(self, q):
sql = """
select * from {table} where rowid in (
select rowid from [{table}_fts]
where [{table}_fts] match :search
)
order by rowid
""".format(
table=self.name
)
return self.db.conn.execute(sql, (q,)).fetchall()
def insert(
self, record, pk=None, foreign_keys=None, upsert=False, column_order=None
):
return self.insert_all(
[record],
pk=pk,
foreign_keys=foreign_keys,
upsert=upsert,
column_order=column_order,
)
def insert_all(
self,
records,
pk=None,
foreign_keys=None,
upsert=False,
batch_size=100,
column_order=None,
):
"""
Like .insert() but takes a list of records and ensures that the table
that it creates (if table does not exist) has columns for ALL of that
data
"""
if not self.exists:
self.create(
self.detect_column_types(records),
pk,
foreign_keys,
column_order=column_order,
)
all_columns = set()
for record in records:
all_columns.update(record.keys())
all_columns = list(sorted(all_columns))
for chunk in chunks(records, batch_size):
sql = """
INSERT {upsert} INTO [{table}] ({columns}) VALUES {rows};
""".format(
upsert="OR REPLACE" if upsert else "",
table=self.name,
columns=", ".join("[{}]".format(c) for c in all_columns),
rows=", ".join(
"""
({placeholders})
""".format(
placeholders=", ".join(["?"] * len(all_columns))
)
for record in chunk
),
)
values = []
for record in chunk:
values.extend(
jsonify_if_needed(record.get(key, None)) for key in all_columns
)
result = self.db.conn.execute(sql, values)
self.db.conn.commit()
self.last_id = result.lastrowid
return self
def upsert(self, record, pk=None, foreign_keys=None, column_order=None):
return self.insert(
record,
pk=pk,
foreign_keys=foreign_keys,
upsert=True,
column_order=column_order,
)
def upsert_all(self, records, pk=None, foreign_keys=None, column_order=None):
return self.insert_all(
records,
pk=pk,
foreign_keys=foreign_keys,
upsert=True,
column_order=column_order,
)
def chunks(sequence, size):
for i in range(0, len(sequence), size):
yield sequence[i : i + size]
def jsonify_if_needed(value):
if isinstance(value, (dict, list, tuple)):
return json.dumps(value)
else:
return value