How to apply SQLite3 database with Python.

Posted in May 17, 2021 by

Categories: Databases Programming Python

Tags: , , ,

Reading Time: 8 minutes

In this post, I write about connecting and using SQLite3 database with Python programming language. We will create a database using Python and a simple app that will help us make CRUD operations on that database. So let’s start.

About SQLite3 database.

This database engine is very popular today. Maybe it is because it is not recommended for large projects, but rather for smaller ones or for a development stage of software.

SQLite3 does not require installing a client-server software but rather operates on a single file. Luckily Python has already built this database into standard libraries.

We use SQLite3 mainly in the development stages as I wrote previously but it is also used by web browsers and operating systems, or mobile phones.

In this article, we will build a simple app that will handle CRUD operations on this DB. If you don’t know what is CRUD, it is an abbreviation of Create, Read, Update and Delete.

We will do all those operations on SQLite3 database by using Python 🙂 .

Our Application.

So let’s start building our app. First we will import SQLite3 module and then we will create database.

Setting up database.

Let’s import SQLite3 module 👇 :

import sqlite3

Then we will simply create and connect to our database. Database file will be called “products“👇 :

db_connection = sqlite3.connect('products.db')

The next step is to create a cursor, that will be working like a mouse cursor with GUI in our code. It will handle our operations👇 :

db_cursor = db_connection.cursor()

Finally we will create our table and save it to a file👇 :

db_cursor.execute("""
CREATE TABLE IF NOT EXISTS products (
    name TEXT,
    price REAL,
    description TEXT,
    color TEXT,
    availability INTEGER,
    stock_number INTEGER
)
""")

db_connection.commit()

That is all for setting a database. Now we can start creating our app 😌 .

We can also create a function from previous code. It is more useful and clean to create function like👇 :

def create_table_products(conn, cursor):
    # # Creating table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS products (
        name TEXT,
        price REAL,
        description TEXT,
        color TEXT,
        availability INTEGER,
        stock_number INTEGER
    )""")
    conn.commit()

Setting up main menu and main loop.

A user will be interacting with our app through the console menu that will be working in the main loop. A user will be able to make CRUD operations on a table from that main menu.

First we will create show_menu function👇 :

def show_menu():
    print("*" * 30)
    print("1 - Create a record.")
    print("2 - Read all from table.")
    print("3 - Update a record.")
    print("4 - Delete a record.")
    print("5 - Show menu.")
    print("6 - Show all records.")
    print("q - Quit")

Then we will setup main loop in main.py👇 :

option = None
app.show_menu()

while True:
    option = input("Choose option: ")
    if option.lower().startswith("q"):
        print("Program finished. Bye!")
        break
    elif option == "5":
        app.show_menu()

db_connection.close()
Image 1. Console main menu.

In this code, we show the menu before entering the main loop. Then we ask for choosing an option from a user. If the user press q or quit or any word that starts with q the program will terminate, showing a goodbye message.

When user will press 5, the program will show main menu again.

In the end, we terminate the connection with the database which is good programming practice 🤓 .

Creating a new rows in SQLite3 database.

Now we will calmly deal with all four options on main menu. First we will create create_record function in app.py👇 :

def create_record(cursor, conn, *args):
    cursor.execute(f"""
        INSERT INTO products(name, price, description, color, availability, stock_number) 
        VALUES(?, ?, ?, ?, ?, ?)""", (args[0], args[1], args[2], args[3], args[4], args[5]))

    conn.commit()
    print("Record created: ", args)

In this function we use prepare statement but in such simple app build only for learning purposes it is not required.

More about why you should use prepare statement in your code you can find in this article:

Briefly, we use prepared statements to make communication with the database more secure and prevents attacks of hackers, unwanted leaks of data from DB, and such.

We use in this function *args argument which is of special type. We execute that function like:

app.create_record(db_cursor, db_connection, name, price, desc, color, availability, stock_number)

First two arguments are cursor and connection but the remaining are transformed into a list named args.

Because of that, we can pass as many values to this function as we would like to but in the body of the function, we need only *args argument to use them.

Now we can add usage of this function to our main menu👇 :

    elif option == "1":
        name = input("Enter product name: ")
        price = float(input("Enter product price: "))
        desc = input("Enter product short description: ")
        color = input("Enter product color: ")
        availability = int(input("Enter product availability (0/1): "))
        stock_number = int(input("Enter product stock number: "))
        app.create_record(db_cursor, db_connection, name, price, desc, color, availability, stock_number)
Image 2. Creating a row in a database.

Don’t worry, code of whole application will be present at the bottom of this article 🙂 .

Reading data from SQLite3 database.

The next function we will create is show_all_records. This function will read all records from the database and show them in the console👇 :

def show_all_records(cursor, conn):
    cursor.execute("SELECT rowid, * FROM products")

    result = cursor.fetchall()
    print("*" * 30)
    for row in result:
        print(row)

It is very simple function. We use “*” to extract all columns from single row and additionally we ask for row id.

Fetchall method brings as all rows from database which we print in loop.

Now we can add that option in our main menu👇 :

       elif option == "2":
        app.show_all_records(db_cursor, db_connection)
Image 3. Reading all rows from a database.

Updating rows in SQLite3 database.

Now we will create function called update_record_in_database. This function will update one record in our database with given values:

def update_record_in_database(cursor, conn, rowid, *args):
    cursor.execute(f"""
            UPDATE products SET 
            name=?, 
            price=?, 
            description=?, 
            color=?, 
            availability=?, 
            stock_number=?
            WHERE rowid=(?)""", (args[0], args[1], args[2], args[3], args[4], args[5], rowid))

    conn.commit()
    print("Row updated: ", args)

It is very similar to the create_record function but we have here different SQL statement. The only new part here is the WHERE clause. We don’t want to update all records accidentally.

Because of that we make sure to update only this specific record which we pass its row id to this function.

We also add this option to our main menu👇 :

    elif option == "3":
        rowid = int(input("Which row number do you want to update?: "))
        name = input("Enter product new name: ")
        price = float(input("Enter product new price: "))
        desc = input("Enter product new short description: ")
        color = input("Enter product new color: ")
        availability = int(input("Enter product new availability (0/1): "))
        stock_number = int(input("Enter product new stock number: "))
        app.update_record_in_database(db_cursor, db_connection, rowid, name, price, desc, color, availability, stock_number)

At first, we have to ask the user which row we want to update and cast the answer to int because row id in the table is represented also as an int. The rest of the code is similar to previous one.

Image 4. Updating a row in ad database.

The last function we will create is … 😉

Deleting a row from a SQLite3 database.

Delete is the last instruction which left from CRUD. Lets create a function called delete_row_from_database:

def delete_row_from_database(cursor, conn, rowid):
    cursor.execute(f"DELETE FROM products WHERE rowid=(?)", (rowid,))

    conn.commit()
    print(f"Row number {rowid} has been deleted.")

What is odd in this code is that we have to use (rowid,). It is because prepare statement expects the parameters to be a tuple. When we add “,” after rowid we transform it into a tuple.

Now lets add this option to main meny👇 :

    elif option == "4":
        rowid = int(input("Which row number do you want to delete?: "))
        app.delete_row_from_database(db_cursor, db_connection, rowid)

As usual, we ask a user to choose a row to delete by its id. The best is to check first for all rows and then choose one to delete:

Image 5. Deleting a row from a table.

Whole code of our application.

And that is it. We created a very simple console app that execute CRUD operations👌 .

Here is code of whole app.

Main.py

import sqlite3
import sqlite3_app as app

# Creating connection to database that is on file
db_connection = sqlite3.connect('products.db')

# Creating db cursor
db_cursor = db_connection.cursor()

app.create_table_products(db_connection, db_cursor)
option = None
app.show_menu()

while True:

    option = input("Choose option: ")
    if option.lower().startswith("q"):
        print("Program finished. Bye!")
        break
    elif option == "1":
        name = input("Enter product name: ")
        price = float(input("Enter product price: "))
        desc = input("Enter product short description: ")
        color = input("Enter product color: ")
        availability = int(input("Enter product availability (0/1): "))
        stock_number = int(input("Enter product stock number: "))
        app.create_record(db_cursor, db_connection, name, price, desc, color, availability, stock_number)
    elif option == "2":
        app.show_all_records(db_cursor, db_connection)
    elif option == "3":
        rowid = int(input("Which row number do you want to update?: "))
        name = input("Enter product new name: ")
        price = float(input("Enter product new price: "))
        desc = input("Enter product new short description: ")
        color = input("Enter product new color: ")
        availability = int(input("Enter product new availability (0/1): "))
        stock_number = int(input("Enter product new stock number: "))
        app.update_record_in_database(db_cursor, db_connection, rowid, name, price, desc, color, availability,
                                      stock_number)
    elif option == "4":
        rowid = int(input("Which row number do you want to delete?: "))
        app.delete_row_from_database(db_cursor, db_connection, rowid)
    elif option == "5":
        app.show_menu()
    app.show_menu()

db_connection.close()

App.py

def create_table_products(conn, cursor):
    # # Creating table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS products (
        name TEXT,
        price REAL,
        description TEXT,
        color TEXT,
        availability INTEGER,
        stock_number INTEGER
    )""")

    conn.commit()


def create_record(cursor, conn, *args):
    cursor.execute(f"""
        INSERT INTO products(name, price, description, color, availability, stock_number) 
        VALUES(?, ?, ?, ?, ?, ?)""", (args[0], args[1], args[2], args[3], args[4], args[5]))

    conn.commit()
    print("Record created: ", args)


def show_all_records(cursor, conn):
    cursor.execute("SELECT rowid, * FROM products")

    result = cursor.fetchall()
    print("*" * 30)
    for row in result:
        print(row)


def update_record_in_database(cursor, conn, rowid, *args):
    cursor.execute(f"""
            UPDATE products SET 
            name=?, 
            price=?, 
            description=?, 
            color=?, 
            availability=?, 
            stock_number=?
            WHERE rowid=(?)""", (args[0], args[1], args[2], args[3], args[4], args[5], rowid))

    conn.commit()
    print("Row updated: ", args)


def delete_row_from_database(cursor, conn, rowid):
    cursor.execute(f"DELETE FROM products WHERE rowid=(?)", (rowid,))

    conn.commit()
    print(f"Row number {rowid} has been deleted.")


def show_menu():
    print("*" * 30)
    print("1 - Create a record.")
    print("2 - Read all from table.")
    print("3 - Update a record.")
    print("4 - Delete a record.")
    print("5 - Show menu.")
    print("q - Quit")

Feel free to copy and paste this code to your localhost and check if it is working. If you would have any problems with that please leave a comment and I will try to help you.

Summary.

In this article I wanted to bring closer SQLite3 database and it use with Python language.

We built a very simple app to manage CRUD operations in an IDE console.

I am not an expert in those topics but I believe that because of continuous learning and writing articles like this one I can learn better and grow my professional skills in programming 🤓 .

If you find a use in this article, you can share it with me in the comments section.

Give a thumb up under this article and share it on social media if you find it informative and valuable.

Thank you for reading and have an awesome day.

Cheers 👋 .


Leave a Reply

Your email address will not be published. Required fields are marked *

one × 5 =

error: Content is protected !!