Cp6s5Jr6

1import sqlite3
2from sqlite3 import Error
3import sys
4
5class DBHandler:
6    conn = None
7    db_file = None
8    cursor = None
9
10    def __init__(self, db_file=None):
11        self.db_file = db_file
12        self.create_connection(db_file=db_file)
13
14    def create_connection(self, db_file=None):
15        """ create a database connection to a SQLite database """
16        if db_file is None:
17            db_file = self.db_file if self.db_file is not None else 'mydb.db'
18        self.db_file = db_file
19        try:
20            self.conn = sqlite3.connect(db_file)
21            self.cursor = self.conn.cursor()
22        except Error as e:
23            print(e)
24
25    def close_connection(self):
26        if self.conn:
27            self.conn.close()
28
29    def execute(self, sql, obj=None):
30        if None not in [self.conn, self.cursor]:
31            if obj is None:
32                self.cursor.execute(sql)
33            else:
34                self.cursor.execute(sql, obj)
35        else:
36            print("Error! cannot create the database connection.")
37
38
39class ReconScraper:
40    handler = None
41    current_workspace = None
42
43    def __init__(self, db_file=None):
44        self.handler = DBHandler(db_file=db_file)
45        self.init_tables()
46
47    def init_tables(self):
48        self.handler.execute("""CREATE TABLE IF NOT EXISTS workspaces (id integer PRIMARY KEY, name text NOT NULL UNIQUE, unique_id_counter integer NOT NULL DEFAULT 0);""")
49        self.handler.execute("""CREATE TABLE IF NOT EXISTS contacts (id integer PRIMARY KEY, workspace_id integer NOT NULL, workspace_unique_id integer NOT NULL, first_name text, last_name text, title text, email text, command text NOT NULL);""")
50
51    def create_workspace(self, name):
52        sql = ''' INSERT INTO workspaces(name) VALUES(?) '''
53        self.handler.cursor.execute(sql, (name,))
54        self.handler.conn.commit()
55        return self.handler.cursor.lastrowid
56
57    def choose_workspace(self):
58        self.current_workspace = None
59        while self.current_workspace is None:
60            self.handler.cursor.execute("SELECT id, name FROM workspaces")
61            rows = self.handler.cursor.fetchall()
62            print("Choose workspace: ")
63            print("new) Create a new workspace")
64            for row in rows:
65                print("{id}) {name}".format(id=row[0], name=row[1]))
66            print("exit) Exit the script")
67            choice = input()
68            if choice.lower() == 'new':
69                new_workspace = input("New workspace name:")
70                id = self.create_workspace(new_workspace)
71                self.current_workspace = (id, new_workspace)
72            elif choice.lower() == 'exit':
73                sys.exit("Exiting...")
74            elif choice.isdigit():
75                for row in rows:
76                    if int(choice) == row[0]:
77                        self.current_workspace = row
78                        break
79            else:
80                for row in rows:
81                    if choice.lower() == row[1].lower():
82                        self.current_workspace = row
83                        break
84            if self.current_workspace is None:
85                print("Choice not valid")
86            else:
87                print("Workspace: {}".format(self.current_workspace[1]))
88            print("")
89
90
91# id, workspace_id, first_name, last_name, title, email, command