In this lesson you will learn how to create a basic login script with Python and SQLite. SQLite is ideal for small-scale data-driven Python applications. It is a lightweight, self-contained, serverless database management system. You can read more about SQLite here: Python Databases – SQLite Tutorial Part 1.

To follow along with this lesson you will need a free piece of software called DB Browser for SQLite. You should download and install this very useful program now before you proceed.

What’s coming up in this lesson:

• Create a users table
• Add some username/password combinations
• Connect to a SQLite database from Python
• Fetch results from the database using Python
• Use f-strings to safely interpolate variables into SQL statements
• Check whether a provides username/password combination is valid.

Lets’ dive in.

Creating a users Table Using DB Browser for SQLite

• Open DB Browser for SQLite
• Create a new database and save it as data.db.

• Go to the execute sql tab and type in (paste if you are already an SQL ninja) the following SQL code:

  CREATE TABLE IF NOT EXISTS “users” (
  “username” TEXT,
  “password” TEXT
  );
  INSERT INTO “users” VALUES (‘boss’,’1234′);
  INSERT INTO “users” VALUES (‘admin’,’password’);

Note: in a production application you would never store unencrypted passwords in the database. You would used hashed and salted versions instead – don’t worry for now though if those concepts aren’t familiar. The idea is simple – unencrypted passwords stored in a database are not secure.

Alternatively you can create a new database inside DB browser and manually create the table using the GUI.

Make sure you write the changes to the database.

You do this by selecting the option from the toolbar. Now check the data and the structure and get a bit familiar with the interface if you are not already.

SQLite Databases Python Programming Video Course on Udemy

If you want to learn more about using SQLite with Python, check out this course on Udemy.

SQLite Databases Python Programming

Accessing an SQLite database from Python

Next create a Python file in the same directory as the data.db. Add the following code and run it:

import sqlite3 as sql

con = sql.connect("data.db")
cur = con.cursor()
statement = "SELECT username, password FROM users"
cur.execute(statement)
print(cur.fetchall())

You should get this output:

[('boss', '1234'), ('admin', 'password')]

Note: what is cur? Think of it as the read/write head on a CD player or similar. You can see here that all the entries in the table are returned by cur.fetchall().

A couple of other handy functions which are similar are:

• cur.fetchone(), which will return just one row from the SQL query
• cur.fetchone()[0], which will return just the first item in the first row.

If you were to use print(cur.fetchone()[0]) instead of print(cur.fetchall()) in the code above, the output would just be boss.

Checking Login Credentials with Python and SQLite

Now we have most of the ingredients to check login details using Python and SQLite.

A suitable SQL query would be:

f"SELECT username from users WHERE username='{username}' AND password = '{password}';

Now we have the ingredients for a simple Python script to check whether a username/password combination exists in the database and print a user message accordingly. I have hard-coded the values for username and password, but these could come from user input, using input() or from a web form if you are creating a Python web application.

import sqlite3 as sql


username = "boss"
password = "1234"
con = sql.connect("data.db")
cur = con.cursor()
statement = f"SELECT username from users WHERE username='{username}' AND Password = '{password}';"
cur.execute(statement)
if not cur.fetchone():  # An empty result evaluates to False.
    print("Login failed")
else:
    print("Welcome")

In this lesson we have learned how to create a table in a SQLite database and use python to access the database and check for valid username/password combinations. I hope you found it helpful.

Happy computing.

Sharing is caring!