-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.py
90 lines (65 loc) · 2.72 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
"""
Tools to create, manage and interact with Sqlite3 Database
"""
import sqlite3
import logging
import pandas as pd
from typing import List, Dict
from sqlite3 import Error as SqliteError
import configure
GIT_CONTENT_DEFINITION = ('CREATE TABLE IF NOT EXISTS content ('
'url TEXT,'
'username TEXT, '
'repo TEXT, '
'path TEXT, '
'content TEXT, '
'date_added DATETIME DEFAULT CURRENT_TIMESTAMP, '
'PRIMARY KEY (username, repo, path)'
');')
class SqliteConnection(object):
def __init__(self, db_path: str):
"""Create a database connection to a SQLite database
Args:
db_path: System path to current or desired DB file.
"""
try:
self.conn = sqlite3.connect(db_path)
except SqliteError as e:
logging.exception('Caught error during connection creation: %s', repr(e))
def __enter__(self) -> sqlite3.Connection:
return self.conn
def __exit__(self, exc_type, exc_val, exc_tb):
self.conn.close()
def create_table(cursor: sqlite3.Cursor, definition: str) -> None:
try:
cursor.execute(definition)
except SqliteError as e:
logging.exception('Caught error during table creation: %s', repr(e))
def drop_table(cursor: sqlite3.Cursor, table_name: str) -> None:
try:
cursor.execute(f'DROP TABLE {table_name};')
except SqliteError as e:
logging.exception('Caught error during table creation: %s', repr(e))
def query(cursor: sqlite3.Cursor, command: str) -> List:
try:
cursor.execute(command)
return cursor.fetchall()
except SqliteError as e:
logging.exception('Caught error during query: %s', repr(e))
def insert(cursor: sqlite3.Cursor, table: str, data: Dict) -> None:
keys = ', '.join([str(x) for x in data.keys()])
values = list(data.values())
values_placeholders = ', '.join(['?'] * len(values))
command = f'INSERT INTO {table} ({keys}) VALUES ({values_placeholders});'
cursor.execute(command, values)
def database_table_to_pandas_table(cursor: sqlite3.Cursor, table_name: str) -> pd.DataFrame:
cursor.execute(f'PRAGMA table_info({table_name});')
column_names = [x[1] for x in cursor.fetchall()]
cursor.execute(f'SELECT * FROM {table_name};')
values = cursor.fetchall()
data = {column: [k[ix] for k in values] for ix, column in enumerate(column_names)}
return pd.DataFrame(data)
if __name__ == '__main__':
with SqliteConnection(configure.db_path) as conn:
cur = conn.cursor()
create_table(cur, GIT_CONTENT_DEFINITION)