LmCWeb46

1import pandas as pd
2import sqlite3
3import csv
4import os
5import re
6
7
8# Creating a table
9def create_database():
10    try:
11        sqliteConnection = sqlite3.connect('USER.db')
12        cursor = sqliteConnection.cursor()
13        print("Attempting to connect to database..")
14        print("Successfully Connected to USER.db")
15        sqlite_create_table_query = '''CREATE TABLE TB_USER (
16                                        USER_ID INTEGER PRIMARY KEY AUTOINCREMENT,
17                                        LOGIN TEXT NOT NULL,
18                                        CRYPTOGRAPHIC_PASSWORD TEXT NOT NULL UNIQUE,
19                                        ACCESS_COUNT INTEGER DEFAULT 0);'''
20
21        cursor.execute(sqlite_create_table_query)
22        sqliteConnection.commit()
23        print("SQLite table created")
24        cursor.close()
25
26    except sqlite3.Error as error:
27        print("Error while connecting to Sqlite:", error)
28
29
30    finally:
31        if sqliteConnection:
32            sqliteConnection.close()
33            print("The SQLite connection is closed")
34
35
36# Check if the email consist in the DB
37def check_email(email):
38    try:
39        sqliteConnection = sqlite3.connect('USER.db')
40        cursor = sqliteConnection.cursor()
41        print("Attempting to connect to database..")
42        print("Successfully Connected to USER.db")
43        sql_select_query = """select * from TB_USER where LOGIN = ?"""
44        data = email
45        cursor.execute(sql_select_query, (data,))
46        if cursor.fetchone() is not None:
47            print('Username already exists')
48            count = 1
49        else:
50            print("Username doesn't exist")
51            count = 0
52        return count
53
54        print("SQLite table created")
55        cursor.close()
56
57    except sqlite3.Error as error:
58        print("Error while connecting to sqlite", error)
59
60
61    finally:
62        if sqliteConnection:
63            sqliteConnection.close()
64            print("The SQLite connection is closed")
65
66
67# Insert entered data into the DB
68def insert_todb(LOGIN, CRYPTOGRAPHIC_PASSWORD):
69    import sqlite3
70
71    try:
72        sqliteConnection = sqlite3.connect('USER.db')
73        cursor = sqliteConnection.cursor()
74        print("Attempting to connect to database..")
75        print("Successfully connected to USER.db")
76
77        sqlite_insert_with_param = """INSERT INTO TB_USER
78                          (LOGIN, CRYPTOGRAPHIC_PASSWORD) 
79                          VALUES (?, ?);"""
80
81        data_tuple = (LOGIN, CRYPTOGRAPHIC_PASSWORD)
82        cursor.execute(sqlite_insert_with_param, data_tuple)
83        sqliteConnection.commit()
84        print("Created Login and Password successfully transferred into database")
85
86        cursor.close()
87
88    except sqlite3.Error as error:
89        print("Failed to insert Login and Password into database", error)
90    finally:
91        if sqliteConnection:
92            sqliteConnection.close()
93            print("The connection is closed")
94
95
96# Updating DB
97def update_sql(x):
98    try:
99        sqliteConnection = sqlite3.connect('USER.db')
100        cursor = sqliteConnection.cursor()
101        cursor1 = sqliteConnection.cursor()
102        cursor2 = sqliteConnection.cursor()
103        cursor3 = sqliteConnection.cursor()
104        print("Connected to database")
105
106        sql_update_query = """Update TB_USER set ACCESS_COUNT = ACCESS_COUNT + 1 where LOGIN = ?"""
107        data = x
108        cursor.execute(sql_update_query, (data,))
109        sqliteConnection.commit()
110        print("Attempting to connect to database..")
111        print("Login attempt recorded successfully")
112        sql_select_query = """select * from TB_USER where LOGIN = ?"""
113        data = x
114        cursor.execute(sql_select_query, (data,))
115        cursor1.execute(sql_select_query, (data,))
116        cursor2.execute(sql_select_query, (data,))
117        rows = cursor3.fetchall()
118        print("Your USER_ID is: ", cursor.fetchone()[0])
119        print("Your Login is: ", cursor1.fetchone()[1])
120        print("The number of login attempts is:", cursor2.fetchone()[3])
121
122        print("Exporting data into CSV..")
123        cursor3.execute("select * from TB_USER")
124        with open("usersdb-backup.csv", "w") as csv_file:
125            csv_writer = csv.writer(csv_file, delimiter="-")
126            csv_writer.writerow([i[0] for i in cursor3.description])
127            csv_writer.writerows(cursor3)
128
129        dirpath = os.getcwd() + "/usersdb-backup.csv"
130        print("Data exported Successfully into {}".format(dirpath))
131
132        cursor.close()
133        cursor1.close()
134        cursor2.close()
135        cursor3.close()
136
137    except sqlite3.Error as error:
138        print("Failed to update database", error)
139    finally:
140        if sqliteConnection:
141            sqliteConnection.close()
142            print("The database connection is closed")
143
144
145# Creating a new user
146def create_user():
147    x = str(input("Please, enter your email:"))
148
149    def check_email(x):
150        regex = '^[a-z0-9]+[\._]?[a-z0-9]+[@]\w+[.]\w{2,3}$'
151        if re.search(regex, x):
152            print("Valid Email address")
153            email = 1
154        else:
155            print("Invalid Email address")
156            email = 2
157        return str(email)
158
159    temp_x = str(check_email(x))
160
161    if temp_x == str(1):
162        pass
163    else:
164        print("Please, enter the correct email address")
165        x = str(input("Please, enter your email:"))
166
167    y = str(input("Please, enter your password: "))
168    keys = pd.read_excel('chyper-code.xlsx', engine='openpyxl')
169    keys = keys.set_index(['USER TYPE'])["SYSTEM CONVERT"].to_dict()
170    keys_values = keys.items()
171    new_d = {str(key): str(value) for key, value in keys_values}
172    string = list(y.upper())
173
174    for index, item in enumerate(string):
175        for key, value in new_d.items():
176            if item == key:
177                string[index] = value
178
179    y = "".join(string)
180    print("Your login is: ", x)
181    print("Your new crypto-password is: ", y)
182    insert_todb(x, y)
183
184
185# Login already existing user
186def login_user():
187    x = str(input("Please, enter your email:"))
188
189    def check_email(x):
190        regex = '^[a-z0-9]+[\._]?[a-z0-9]+[@]\w+[.]\w{2,3}$'
191        if re.search(regex, x):
192            print("Valid Email address")
193            email = 1
194        else:
195            print("Invalid Email address")
196            email = 2
197        return str(email)
198
199    temp_x = str(check_email(x))
200    if temp_x == str(1):
201        update_sql(x)
202    else:
203        print("Please, enter the existent login")
204
205
206# Main driver:
207running = True
208
209while running:
210    print('Welcome to the Dmitrii DB')
211    print()
212    print("""Choose an option:
213
214        1. Create a database 
215        2. Create an account
216        3. Login into account 
217        4. Exit
218        """)
219    choice = str(input("1, 2, 3, 4 or 5: "))
220    while not choice.isdigit() or int(choice) < 1 or int(choice) > 5:
221        print("Please, choose only from 1, 2, 3, 4 or 5")
222        choice = input("1, 2, 3, 4 or 5: ")
223
224    if choice == str(1):
225        create_database()
226
227    elif choice == str(2):
228        create_user()
229
230    elif choice == str(3):
231        login_user()
232
233    elif choice == str(4):
234        print("Arrivederci!")
235        running = False
236