ich habe mal versucht eine Art "Ticketsystem" mit SQLite zu bauen, es ist erstmal nur ein grober Test (komplettes Exceptionhandling fehlt) um in SQLite rein zu kommen und auch mein erstes Projekt in die Richtung Datenbanken.
Ich würde mich freuen wenn mal jemand drüber schauen würde und ob ich das soweit alles richtig gemacht habe oder ob es eventuell Möglichkeiten gibt da was zu vereinfachen bzw. zu verbessern.
Code: Alles auswählen
import getpass
import hashlib
import sqlite3
import pathlib
DATABASE_PATH = pathlib.Path(r"C:\Users\xxx\Desktop\TEST.db")
def create_tables(connection):
cursor = connection.cursor()
sql = "CREATE TABLE IF NOT EXISTS User(customer_number INTEGER PRIMARY KEY AUTOINCREMENT, user_name TEXT, password TEXT NOT NULL)"
cursor.execute(sql)
sql = "CREATE TABLE Tickets(ticket_number INTEGER PRIMARY KEY AUTOINCREMENT, author_number INTEGER NOT NULL, topic TEXT NOT NULL, content TEXT NOT NULL)"
cursor.execute(sql)
connection.commit()
print("Datenbank angelegt!")
def hash_password(password):
return hashlib.sha256(password.encode()).hexdigest()
def is_valild_user_registration(connection, username, passwort):
cursor = connection.cursor()
sql = "SELECT user_name FROM User"
all_user = [x[0] for x in cursor.execute(sql).fetchall()]
return True if username not in all_user else False
def register(connection):
cursor = connection.cursor()
new_user_username = input("Bitte Namen eingeben: ")
new_user_passwort = hash_password(getpass.getpass("Bitte Kennwort eingeben: "))
if is_valild_user_registration(connection, new_user_username, new_user_passwort):
sql = "INSERT INTO User (user_name, password) VALUES (?,?)"
cursor.execute(sql, (new_user_username, new_user_passwort))
connection.commit()
print("\nAccount angelegt!")
else:
print("\nDieser Anmeldename wird schon benutzt.")
def login(connection):
login_name = input("Bitte Namen eingeben: ")
login_passwort = hash_password(getpass.getpass("Bitte Kennwort eingeben: "))
cursor = connection.cursor()
sql = "SELECT customer_number FROM User WHERE user_name = ? AND password = ?"
customer_number = cursor.execute(sql, (login_name, login_passwort)).fetchone()
if customer_number:
print(f"\nWillkommen {login_name}")
return customer_number[0]
else:
print("\nFalsche Anmeldedaten!")
def create_ticket(connection, customer_number):
cursor = connection.cursor()
topic = input("Bitte gib das Thema an: ")
content = input("Bitte gib eine genauere Beschreibung an: ")
sql = "INSERT INTO Tickets (author_number, topic, content) VALUES (?,?,?)"
cursor.execute(sql,(customer_number, topic, content))
connection.commit()
print(f"Das Ticket [{topic}] wurde erstellt!")
def show_tickets(connection, customer_number):
cursor = connection.cursor()
sql = "SELECT ticket_number, topic, content FROM Tickets WHERE author_number = ?"
tickets = cursor.execute(sql, (customer_number,)).fetchall()
if tickets:
for ticket in tickets:
ticket_number, topic, content = ticket
print(f"""\n[Ticketnummer]: {ticket_number}
[Thema]:
{topic}
[Inhalt]:
{content}\n\n""")
def delete_account(connection, customer_number):
cursor = connection.cursor()
sql = "DELETE FROM User WHERE customer_number = ?"
cursor.execute(sql,(customer_number,))
sql = "DELETE FROM Tickets WHERE author_number = ?"
cursor.execute(sql,(customer_number,))
connection.commit()
print("\nAccount wurde gelöscht!")
def main():
if not DATABASE_PATH.is_file():
with sqlite3.connect(DATABASE_PATH) as connection:
create_tables(connection)
while True:
customer_number = None
while customer_number == None:
menue_choice = int(input("""\nWas möchtest du tun:
1 - Registrieren
2 - Anmelden
3 - Beenden
> """))
if menue_choice == 1:
with sqlite3.connect(DATABASE_PATH) as connection:
register(connection)
elif menue_choice == 2:
with sqlite3.connect(DATABASE_PATH) as connection:
customer_number = login(connection)
elif menue_choice == 3:
break
while customer_number:
menue_choice = int(input("""\nWas möchtest du tun:
1 - Ticket erstellen
2 - Meine Tickets ansehen
3 - Abmelden
4 - Account löschen
> """))
if menue_choice == 1:
with sqlite3.connect(DATABASE_PATH) as connection:
create_ticket(connection, customer_number)
elif menue_choice == 2:
with sqlite3.connect(DATABASE_PATH) as connection:
show_tickets(connection, customer_number)
elif menue_choice == 3:
customer_number = None
elif menue_choice == 4:
delete_account(connection, customer_number)
customer_number = None
break
main()