How to Create a User Management System in Python Using SQLite

Submitted by Yorkiebar on Sunday, March 16, 2014 - 21:15.
Introduction: This tutorial is on how to create a user management system in Python using SQLite. The Database: First we need to create the database to store the information, to do this open CMD (on Windows) and type cd followed by your SQLite installation path, mine is: 
  1. cd /d G:
  2. cd Libaries\Documents\SQLite
Now we use the command sqlite3 followed by the database name followed by the extension for a database file which is ".db", I will create one named sourcecodester... 
  1. sqlite3 sourcecodester.db
This will begin the SQLite service and should create the database. To confirm the database is created, use the command ".databases"... 
  1. .databases
If it is there, use ".exit" to exit the service... 
  1. .exit
Scripting - Imports: First we need to import the sqlite3 package to use the databases, we type "as db" to use the import with the name "db" since it is easier to type and work with... 
  1. import sqlite3 as db
Scripting - Connecting: Now we want to connect to our database, we do this through the connect function of SQLite(3), it takes one string parameter which is the directory path of the database file, including the extension ".db"... 
  1. con = db.connect("G:\Libaries\Documents\SQLite\sourcecodester.db")
  2. cursor = con.cursor()
Scripting - Table: Next we want to create the table, I begin with deleting the table if it already exists simply because I was using it for testing purposes, if you want to properly use this system, remove the two execute statements from the following code and simply run the second one from the command line to create the table once... 
  1. cursor.execute("drop table if exists users")
  2. cursor.execute("create table users(username text(255))")
  3. cursor.row_factory = db.Row
So in the above code, we delete (drop) the table users if it already exists, then we create a new one with one column named "username" which is a text variable with the max length of 255 characters. Finally we set the row_factory to db.Row which sets how we will receive information later on. Scripting - Asking For Input: Next we want to create a small function to return what value the cmd (user option variable value) should be. If the user enters a correct integer, it is set, otherwise it is set to -1 so they are forced to re-enter it... 
  1. cmd = -1
  2. def ask():
  3.         try:
  4.                 return(int(input("Enter your option: (0=exit, 1=add, 2=remove, 3=list):\t")))
  5.         except ValueError:
  6.                 return -1
  7. ask()
We create the variable outside of the function to put it in the global scope so other functions are able to access and use it, and we run the function after creating it so it gets a user choice. Scripting - The Loop: Next we want to create a loop where the user enters the action they want to perform, if they enter 0 it will exit the loop, give a thank you print and close program... 
  1. while (cmd is not 0):
  2. 	while (cmd < 0 or cmd > 3):
  3. 		cmd = ask()
  4. print("Thank you for using Yorkiebar's user account management system!")
We store the users input in the cmd variable as in integer (that's why we cast the input with int) because it is easier to check than strings and comes in handy later. As you can see, if the user enters a number below 0 or above 3, they are asked to enter it again until they enter a correct one. If they don't enter a number, they are also asked to re-enter since the ask function sets cmd to minus 1 (-1) which is not a valid option for this system. Scripting - Option 1 - Adding Users: Now for the action options, first we have the option to add users. We first check that cmd is 1, if it is, we get the username input from the user to add, then we check that it doesn't already exist, if it doesn't then we add it to the table (insert into...), otherwise we print out an error stating that the user already exists... 
  1. while (cmd is not 0):
  2. 	while (cmd < 0 or cmd > 3):
  3. 		cmd = ask()
  4. 	if (cmd == 1):
  5. 		userEnter = input("Enter the username to add:\t")
  6. 		cursor.execute("select * from users where username=(?);", (userEnter,))
  7. 		checkQuery = cursor.fetchone()
  8. 		if (checkQuery is not None):
  9. 			print("That user already exists!")
  10. 		else:
  11. 			cursor.execute("insert into users values(?);", (userEnter,))
  12. 			print("Added.")
  13. print("Thank you for using Yorkiebar's user account management system!")
Note: fetchone() allows the cursor to return one (the first) row of data, if it doesn't return anything then the row doesn't exists and therefore neither does that username. Scripting - Option 2 - Removing Users: Next we have option two, similar to option 1 we check if cmd is set to 2, then we check if the input username exists, if it does, we remove it. We output the result... 
  1. while (cmd is not 0):
  2. 	while (cmd < 0 or cmd > 3):
  3. 		cmd = ask()
  4. 	if (cmd == 1):
  5. 		userEnter = input("Enter the username to add:\t")
  6. 		cursor.execute("select * from users where username=(?);", (userEnter,))
  7. 		checkQuery = cursor.fetchone()
  8. 		if (checkQuery is not None):
  9. 			print("That user already exists!")
  10. 		else:
  11. 			cursor.execute("insert into users values(?);", (userEnter,))
  12. 			print("Added.")
  13. 	elif (cmd == 2):
  14. 		userEnter = input("Enter the username to search:\t")
  15. 		cursor.execute("select * from users where username=(?);", (userEnter,))
  16. 		checkQuery = cursor.fetchone()
  17. 		if (checkQuery is not None):
  18. 			cursor.execute("delete from users where username=(?);", (userEnter,))
  19. 			print("Deleted.")
  20. 		else:
  21. 			print("User not found.")
  22. print("Thank you for using Yorkiebar's user account management system!")
Scripting - Option 3 - Listing Users: Finally we have option 3 to list all the users within the database table `users`. This doesn't require any user input so we simply run a query execute function to return every row of the table, then we use fetchall() to get the information in to our rows tuple followed by iterating through them using simply for loop and printing each one at index 0 (the first column, which is username)... 
  1. while (cmd is not 0):
  2. 	while (cmd < 0 or cmd > 3):
  3. 		cmd = ask()
  4. 	if (cmd == 1):
  5. 		userEnter = input("Enter the username to add:\t")
  6. 		cursor.execute("select * from users where username=(?);", (userEnter,))
  7. 		checkQuery = cursor.fetchone()
  8. 		if (checkQuery is not None):
  9. 			print("That user already exists!")
  10. 		else:
  11. 			cursor.execute("insert into users values(?);", (userEnter,))
  12. 			print("Added.")
  13. 	elif (cmd == 2):
  14. 		userEnter = input("Enter the username to search:\t")
  15. 		cursor.execute("select * from users where username=(?);", (userEnter,))
  16. 		checkQuery = cursor.fetchone()
  17. 		if (checkQuery is not None):
  18. 			cursor.execute("delete from users where username=(?);", (userEnter,))
  19. 			print("Deleted.")
  20. 		else:
  21. 			print("User not found.")
  22. 	elif (cmd == 3):
  23. 		cursor.execute("select * from users")
  24. 		rows = cursor.fetchall()
  25. 		for row in rows:
  26. 			print("%s" % row[0])
  27. 	cmd = ask()
  28. print("Thank you for using Yorkiebar's user account management system!")
We also let the user input a new option at the end to avoid: An infinite loop of the same option; And, The user having to enter the new option twice (if we put it at the beginning.

Tags

Add new comment

About text formats
  • 948 views