s4b5EQdr

1import sqlite3
2
3
4class DbHandler:
5
6    def __init__(self, dbfile="database_using_pragma78.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
18    def setup(self):
19        statement = "CREATE TABLE IF NOT EXISTS usernametable (username text , id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)"
20        statement2 = "CREATE TABLE IF NOT EXISTS domaintable (domain text PRIMARY KEY, number INTEGER NOT NULL, FOREIGN KEY(number) REFERENCES usernametable(id))"
21        self.cur.execute(statement)
22        self.cur.execute(statement2)
23        self.connection.commit()
24
25    def add_item(self, email):
26        username = email[:email.index("@")]
27        #domain = email[email.index("@")+1:]
28        #country = domain[domain.index("."):]
29        self.check_domain_table(email)
30        self.cache.append((username,)) # must keep the comma because it's a tuple
31
32    def store_items(self):
33        statement = "INSERT OR IGNORE INTO usernametable (username) VALUES (?)"
34        self.connection.executemany(statement, self.cache)
35        self.connection.commit()
36        self.cache = []
37
38    def check_domain_table(self, email):
39        username = email[:email.index("@")]
40        domain = email[email.index("@")+1:]
41
42        print(username)
43
44        statement_check_domain = "select domain from domaintable where domain = ?"
45        get_id_username = "select id from usernametable where username = ?"
46
47        self.cur.execute(get_id_username, (username, ))
48        row = self.cur.fetchone()
49
50        if row is not None:
51            id_of_username = row[0]
52            print("okaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaay")
53        else:
54            print("+++++++++++++++++++++++++ why +++++++++++++++++++++++++")
55            # https://stackoverflow.com/questions/27718385/typeerror-nonetype-object-is-not-subscriptable
56
57        if list(self.cur.execute(statement_check_domain, (domain,))) == 0:
58            print(domain + " added to database")
59            #aggiungi e punta
60            statement_add_domain = "INSERT INTO domaintable (domain, number) VALUES (?, ?)"
61            self.cur.execute(statement_add_domain, (domain, id_of_username))
62        else:
63            #punta e basta
64            print("found "+ domain +"adding pointer")