9wZMXw6q

1import sqlite3
2
3
4class DbHandler:
5
6    def __init__(self, dbfile="database.db"):
7        self.dbfile = dbfile
8        self.connection = sqlite3.connect(dbfile)
9        self.cur = self.connection.cursor()
10        self.connection.execute('pragma journal_mode=MEMORY')
11        self.connection.execute('pragma SYNCHRONOUS=1')
12        self.connection.execute('pragma PAGE_SIZE=4096')
13        self.connection.execute('pragma cache_size = 8192')
14        self.connection.execute('pragma auto_vacuum=1')
15        self.cur.execute('PRAGMA foreign_keys = ON')
16        self.cache = []
17        self.cache2 = []
18
19
20    def setup(self):
21        statement = "CREATE TABLE IF NOT EXISTS usernametable (id INTEGER PRIMARY KEY, username text NOT NULL)"
22        statement2 = "CREATE TABLE IF NOT EXISTS domaintable (id_domain INTEGER PRIMARY KEY, domain TEXT , CONSTRAINT constraint_name UNIQUE (domain), CONSTRAINT fk_username FOREIGN KEY(id_domain) REFERENCES usernametable(id))"  #, FOREIGN KEY(id_domain) REFERENCES usernametable(_rowid_) id_domain INTEGER NOT NULL,
23        self.cur.execute(statement)
24        self.cur.execute(statement2)
25        self.connection.commit()
26
27    def add_item(self, email):
28        username = email[:email.index("@")]
29        domain = email[email.index("@")+1:]
30        #country = domain[domain.index("."):]
31        
32        self.cache2.append((domain,))
33        self.cache.append((username,))  # must keep the comma because it's a tuple
34
35    def store_items(self):
36        statement = "INSERT OR IGNORE INTO usernametable (username) VALUES (?)"
37        statement_add_domain = "INSERT OR IGNORE INTO domaintable (domain) VALUES (?)"
38        
39        self.connection.executemany(statement, self.cache)
40        self.connection.executemany(statement_add_domain, self.cache2)
41        #self.connection.commit()
42        
43        self.connection.commit()
44        self.cache = []
45        self.cache2 = []