def create_table():
if check_tables():
return "Table already exists"
else:
with DB("users.db") as db:
db.curs.execute("CREATE TABLE Users "
"(username text, "
"password text)")
print("Table created")
def fetch_all():
with DB("users.db") as db:
if check_tables():
db.curs.execute("SELECT * FROM Users")
results = db.curs.fetchall()
if results:
return tuple(results)
else:
print("Users table empty")
def check_tables():
with DB("users.db") as db:
db.curs.execute("SELECT * FROM sqlite_master where type='table'")
results = db.curs.fetchall()
if results and "Users" in results[0]:
return True
else:
print("No Users table")
return False
def add_one(user_name, pw):
with DB("users.db") as db:
if check_tables():
if fetch_one(user_name):
print("User already exists")
return
hashpw = hashlib.sha256(bytes(pw, encoding='utf-8')).hexdigest()
db.curs.execute("INSERT INTO Users "
"(username, password) "
"VALUES (?, ?)",
(user_name, hashpw))
db.conn.commit()
print("User added")
def delete_one(user_name):
with DB("users.db") as db:
if not check_tables():
return
if not fetch_one(user_name):
print("User does not exists")
return
db.curs.execute("DELETE FROM Users "
"WHERE username = ? ",
(user_name,))
db.conn.commit()
print("User deleted")
def fetch_one(user_name):
with DB("users.db") as db:
if check_tables():
db.curs.execute("SELECT * FROM Users "
"WHERE username = ?",
(user_name,))
try:
results = db.curs.fetchone()
if results:
return tuple(results)
else:
print("Not found")
except TypeError:
return None
def change_one(user_name, newpw):
# http://www.sqlitetutorial.net/sqlite-update/
with DB("users.db") as db:
if not check_tables():
return
if not fetch_one(user_name):
print("User does not exists")
return
# pass to placeholders in right order!
hashpw = hashlib.sha256(bytes(newpw, encoding='utf-8')).hexdigest()
db.curs.execute("UPDATE Users "
"SET password = ? "
"WHERE username = ? ",
(hashpw, user_name))
db.conn.commit()
print("Info changed")
def zap_table():
with DB("users.db") as db:
db.curs.execute("DROP TABLE IF EXISTS Users")