u736Mf03

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