gfu3s1nr

1import sqlite3
2
3
4class DbHandler:
5
6    def __init__(self, dbfile="database02.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        self.get_id_username = 1
19
20    def setup(self):
21        statement = "CREATE TABLE IF NOT EXISTS usernametable (username text , id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)"
22        statement2 = "CREATE TABLE IF NOT EXISTS domaintable (domain text PRIMARY KEY, number INTEGER NOT NULL, FOREIGN KEY(number) REFERENCES usernametable(id))"
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        self.check_domain_table(email)
32        self.cache.append((username,)) # must keep the comma because it's a tuple
33
34    def store_items(self):
35        statement = "INSERT OR IGNORE INTO usernametable (username) VALUES (?)"
36        statement_add_domain = "INSERT INTO domaintable (domain, number) VALUES (?, ?)"
37        self.connection.executemany(statement, self.cache)
38        self.connection.executemany(statement, self.cache2)
39        self.connection.commit()
40        self.cache = []
41        self.cache2 = []
42
43    def check_domain_table(self, email):
44        username = email[:email.index("@")]
45        domain = email[email.index("@")+1:]
46        
47        i = 1
48        print(username +" id ====  "+str(self.get_id_username))
49        self.get_id_username += i
50        i +=1
51
52        statement_check_domain = "SELECT EXISTS(SELECT 1 FROM domaintable WHERE domain=? LIMIT 1)"    
53        self.cur.execute(statement_check_domain, (domain,))
54        
55        data=self.cur.fetchone()[0]
56        if data==0:
57            print('There is no domain named ' + domain + ' in the database')
58            self.cache2.append((domain, int(self.get_id_username)))
59        else:
60            print('i have to point the username to the correct domain')