yTeFv1ZK

1import pandas as pd
2import sqlite3
3
4# CREATE TABLE
5def create_database():
6    try:
7        sqliteConnection = sqlite3.connect('USER.db')
8        cursor = sqliteConnection.cursor()
9        print("Attempting to connect to database..")
10        print("Successfully Connected to USER.db")
11        sqlite_create_table_query = '''CREATE TABLE TB_USER (
12                                        USER_ID INTEGER PRIMARY KEY AUTOINCREMENT,
13                                        LOGIN TEXT NOT NULL,
14                                        CRYPTOGRAPHIC_PASSWORD TEXT NOT NULL UNIQUE,
15                                        ACCESS_COUNT INTEGER DEFAULT 0);'''
16
17        cursor.execute(sqlite_create_table_query)
18        sqliteConnection.commit()
19        print("SQLite table created")
20        cursor.close()
21
22    except sqlite3.Error as error:
23        print("Error while connecting to sqlite", error)
24
25
26    finally:
27        if sqliteConnection:
28            sqliteConnection.close()
29            print("The SQLite connection is closed")
30
31def check_email(email):
32    try:
33        sqliteConnection = sqlite3.connect('USER.db')
34        cursor = sqliteConnection.cursor()
35        print("Attempting to connect to database..")
36        print("Successfully Connected to USER.db")
37        sql_select_query = """select * from TB_USER where LOGIN = ?"""
38        data = email
39        cursor.execute(sql_select_query, (data,))
40        if cursor.rowcount:
41            print('Username already exists')
42            count = 1
43        else:
44            print("Username doesn't exist")
45            count = 0
46        return count
47
48
49
50        print("SQLite table created")
51        cursor.close()
52
53    except sqlite3.Error as error:
54        print("Error while connecting to sqlite", error)
55
56
57    finally:
58        if sqliteConnection:
59            sqliteConnection.close()
60            print("The SQLite connection is closed")
61
62
63
64# INSERT INTO TABLE
65def insert_todb(LOGIN, CRYPTOGRAPHIC_PASSWORD):
66    import sqlite3
67
68    try:
69        sqliteConnection = sqlite3.connect('USER.db')
70        cursor = sqliteConnection.cursor()
71        print("Attempting to connect to database..")
72        print("Successfully connected to USER.db")
73
74        sqlite_insert_with_param = """INSERT INTO TB_USER
75                          (LOGIN, CRYPTOGRAPHIC_PASSWORD) 
76                          VALUES (?, ?);"""
77
78        data_tuple = (LOGIN, CRYPTOGRAPHIC_PASSWORD)
79        cursor.execute(sqlite_insert_with_param, data_tuple)
80        sqliteConnection.commit()
81        print("Login and Password created transferred into database")
82
83        cursor.close()
84
85    except sqlite3.Error as error:
86        print("Failed to insert Login and Password into database", error)
87    finally:
88        if sqliteConnection:
89            sqliteConnection.close()
90            print("The connection is closed")
91
92###UPDATE_SQL###
93def update_sql(x):
94    try:
95        sqliteConnection = sqlite3.connect('USER.db')
96        cursor = sqliteConnection.cursor()
97        cursor1 = sqliteConnection.cursor()
98        cursor2= sqliteConnection.cursor()
99        print("Connected to database")
100
101        sql_update_query = """Update TB_USER set ACCESS_COUNT = ACCESS_COUNT + 1 where LOGIN = ?"""
102        data = x
103        cursor.execute(sql_update_query, (data,))
104        sqliteConnection.commit()
105        print("Attempting to connect to database..")
106        print("Login attempt recorded successfully")
107        sql_select_query = """select * from TB_USER where LOGIN = ?"""
108        data = x
109        cursor.execute(sql_select_query, (data,))
110        cursor1.execute(sql_select_query, (data,))
111        cursor2.execute(sql_select_query, (data,))
112        print("Your USER_ID is: ", cursor.fetchone()[0])
113        print("Your Login is: ", cursor1.fetchone()[1])
114        print("The number of login attempts is:", cursor2.fetchone()[3])
115
116        cursor.close()
117        cursor1.close()
118        cursor2.close()
119
120    except sqlite3.Error as error:
121        print("Failed to update database", error)
122    finally:
123        if sqliteConnection:
124            sqliteConnection.close()
125            print("The database connection is closed")
126
127
128
129#########START##########
130
131print('Welcome to the Dmitrii DB')
132
133
134def create_user():
135    x = str(input("Please, enter your email:"))
136    y = str(input("Please, enter your password: "))
137    keys = pd.read_excel('chyper-code.xlsx', engine='openpyxl')
138    keys = keys.set_index(['USER TYPE'])["SYSTEM CONVERT"].to_dict()
139    keys_values = keys.items()
140    new_d = {str(key): str(value) for key, value in keys_values}
141    string = list(y.upper())
142
143    for index, item in enumerate(string):
144        for key, value in new_d.items():
145            if item == key:
146                string[index] = value
147
148    y = "".join(string)
149    print("Your login is: ", x)
150    print("Your new crypto-password is: ", y)
151    insert_todb(x, y)
152
153
154def check_user():
155    choice = str(input("Are you new user? Please select '1' for YES and '2' for NO:")).upper()
156    print(choice)
157    while not choice.isdigit() or int(choice) < 1 or int(choice) > 2:
158        print("Please, only 1 or 2 for choice")
159        choice = str(input("Are you new user?Y/N?"))
160
161    if choice == str(1):
162        create_user()
163    elif choice == str(2):
164        x = str(input("Please, enter your email:"))
165        count = check_email(x)
166        if count == 1:
167            update_sql(x)
168        else:
169            print("Please, enter the existent login")
170
171
172
173
174
175
176check_user()
177