xYRxWXep

1import sqlite3
2
3
4def prepare_database(config, output):
5    # create tables
6    db = Database(config['db_name'])
7    db.query('PRAGMA foreign_keys = ON;')
8
9    db.create_table(db.cards_table_sql)
10    db.create_table(db.cards_singles_table_sql)
11    db.insert_card_single(card_id=111, expansion='lorwyn', url='abc/href/dupa', \
12        price_trend=4.55, price_trend_7=4.44, price_trend_30=5.55)
13    
14    print(db.query('SELECT * FROM cards'))
15    print(db.query('SELECT * FROM cards_singles'))
16    ####
17    f = open(str(output), "a")
18    f.write("Database preparation stage - done")
19    f.close()
20
21
22class Database:
23    # tables
24    cards_table_sql = '''CREATE TABLE IF NOT EXISTS cards(
25        card_id INTEGER PRIMARY KEY AUTOINCREMENT, 
26        card_name TEXT NOT NULL UNIQUE)'''
27    cards_singles_table_sql = '''CREATE TABLE IF NOT EXISTS cards_singles(
28        card_single_id INTEGER PRIMARY KEY AUTOINCREMENT,
29        card_id INTEGER,
30        expansion TEXT NOT NULL,
31        url TEXT NOT NULL UNIQUE,
32        price_trend REAL NOT NULL,
33        price_trend_7 REAL NOT NULL,
34        price_trend_30 REAL_NOT_NULL,
35        FOREIGN KEY(card_id) REFERENCES cards(card_id))'''
36    sellers_table_sql = '''CREATE TABLE''' # todo
37    offers_table_sql = '''CREATE TABLE''' # todo
38
39    # inserts
40    card_insert_sql = 'INSERT INTO cards(card_name) VALUES ("<card_name>")'
41    card_single_insert_sql = '''INSERT INTO cards_singles(
42        card_id, expansion, url, price_trend, price_trend_7, price_trend_30) VALUES (
43            "<card_id>", "<expansion>", "<url>", "<price_trend>", "<price_trend_7>", "<price_trend_30>")'''
44
45    def __init__(self, name):
46        self._conn = sqlite3.connect(name)
47        self._cursor = self._conn.cursor()
48
49    def __enter__(self):
50        return self
51
52    def __exit__(self, exc_type, exc_val, exc_tb):
53        self.close()
54
55    @property
56    def connection(self):
57        return self._conn
58
59    @property
60    def cursor(self):
61        return self._cursor
62
63    def commit(self):
64        self.connection.commit()
65
66    def close(self, commit=True):
67        if commit:
68            self.commit()
69        self.connection.close()
70
71    def execute(self, sql, params=None):
72        self.cursor.execute(sql, params or ())
73
74    def fetchall(self):
75        return self.cursor.fetchall()
76
77    def fetchone(self):
78        return self.cursor.fetchone()
79
80    # utility
81
82    def query(self, sql, params=None):
83        try:
84            self.cursor.execute(sql, params or ())
85        except (sqlite3.Error, sqlite3.Warning) as e:
86            print(f'query error: {e}, sql: {sql}')
87        return self.fetchall()
88
89    def create_table(self, sql):
90        try:
91            self.execute(sql)
92        except (sqlite3.Error, sqlite3.Warning) as e:
93            print(f'create table error: {e}, sql: {sql}')
94
95    def insert_card(self, card_name, commit=True):
96        sql = self.card_insert_sql.replace('<card_name>', card_name)
97        try:
98            self.execute(sql)
99            if commit:
100                self.commit()
101        except (sqlite3.Error, sqlite3.Warning) as e:
102            print(f'insert card error: {e}, sql: {sql}')
103
104    def insert_card_single(self, card_id, expansion, url, price_trend, price_trend_7, price_trend_30, commit=True):
105        sql = self.card_single_insert_sql\
106                .replace('<card_id>', str(card_id)).replace('<expansion>', expansion)\
107                .replace('<url>', url).replace('<price_trend>', str(price_trend))\
108                .replace('<price_trend_7>', str(price_trend_7)).replace('<price_trend_30>', str(price_trend_30))
109            
110        try:
111            self.execute(sql)
112            # if commit:
113            #     self.commit()
114        except (sqlite3.Error, sqlite3.Warning) as e:
115            print(f'insert card error: {e}, sql: {sql}')
116