Python and SQLite: Building an Inventory Management System Exercise

Introduction
Prerequisites
Setup
Creating the Database
Setting up the Connection
Creating the Table
Inserting Data
Updating Data
Retrieving Data
Deleting Data
Conclusion

Introduction

In this tutorial, we will learn how to build an inventory management system using Python and SQLite. Throughout the tutorial, we will cover the basics of SQLite, including creating a database, setting up a connection, creating tables, inserting, updating, retrieving, and deleting data. By the end of the tutorial, you will have a functional inventory management system that you can use as a foundation for your own projects.

Prerequisites

To follow along with this tutorial, you should have a basic understanding of Python programming and SQL concepts. Familiarity with the command line and SQLite will also be helpful.

Setup

Before we dive into building the inventory management system, let’s set up our development environment. We will need Python installed on our machine, as well as the SQLite3 module, which is included with Python by default.

To check if Python is installed, open a command prompt or terminal and run the following command: python --version If you see a version number, Python is installed. Otherwise, you can download and install it from the official Python website (https://www.python.org/downloads/).

Next, let’s verify the presence of the SQLite3 module. Open a Python shell by running the following command in the command prompt or terminal: python Once the Python shell is open, import the SQLite3 module by running the following command: python import sqlite3 If no errors occur, the module is installed and ready to use.

Creating the Database

The first step in building our inventory management system is to create a database. The database will store all our inventory data, including product names, quantities, and prices.

To create the database, follow these steps:

Open a Python shell by running the following command in the command prompt or terminal:
```
python
```
Import the SQLite3 module:
```
import sqlite3
```
Connect to a new or existing SQLite database by running the following command:
```
conn = sqlite3.connect('inventory.db')
```
This command establishes a connection to the specified database. If the database doesn’t exist, it will be created automatically. The connection object (conn) will be used to perform database operations throughout the tutorial.

Congrats! You have created a SQLite database for our inventory management system.

Setting up the Connection

Now that we have created the database, let’s set up the connection to it. The connection allows us to interact with the database, execute SQL statements, and retrieve data.

To set up the connection, follow these steps:

Open a Python shell and import the SQLite3 module if not already imported:
```
import sqlite3
```
Connect to the SQLite database by running the following command:
```
conn = sqlite3.connect('inventory.db')
```
Ensure that you use the same filename inventory.db as specified during the database creation step.

The connection is now established, and we can proceed to create the table in our database.

Creating the Table

In our inventory management system, we will create a table named products to store the product information. The table will have columns for the product name, quantity, and price.

To create the table, follow these steps:

Open a Python shell and import the SQLite3 module if not already imported:
```
import sqlite3
```
Connect to the SQLite database:
```
conn = sqlite3.connect('inventory.db')
```
Create a cursor object to execute SQL commands:
```
cursor = conn.cursor()
```

Define the SQL statement to create the table:

create_table_query = '''
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    price REAL NOT NULL
)
'''

This statement creates a table named products with columns for id, name, quantity, and price.

Execute the SQL statement:
```
cursor.execute(create_table_query)
```
The table is now created in the database.

Great job! We have successfully created the table to store our inventory data.

Inserting Data

Now that we have our table set up, let’s insert some data into it. We will create a function that allows us to add new products to the inventory.

To insert data, follow these steps:

Open a Python shell and import the SQLite3 module if not already imported:
```
import sqlite3
```
Connect to the SQLite database:
```
conn = sqlite3.connect('inventory.db')
```
Create a cursor object:
```
cursor = conn.cursor()
```

Define a function to insert new products:

def add_product(name, quantity, price):
    insert_query = '''
    INSERT INTO products (name, quantity, price)
    VALUES (?, ?, ?)
    '''
    cursor.execute(insert_query, (name, quantity, price))
    conn.commit()

This function takes in the product name, quantity, and price as arguments and performs an INSERT operation to add the values into the products table.

Call the function and pass the product details:
```
add_product('Widget', 10, 9.99)
```
This will insert a new product with the name ‘Widget’, a quantity of 10, and a price of 9.99 into the table.

Awesome! We are now able to add products to our inventory.

Updating Data

Next, let’s learn how to update existing product information in our inventory. We will create a function that allows us to modify the quantity and price of a specific product.

To update data, follow these steps:

Open a Python shell and import the SQLite3 module if not already imported:
```
import sqlite3
```
Connect to the SQLite database:
```
conn = sqlite3.connect('inventory.db')
```
Create a cursor object:
```
cursor = conn.cursor()
```

Define a function to update product information:

def update_product(id, quantity, price):
    update_query = '''
    UPDATE products
    SET quantity = ?,
        price = ?
    WHERE id = ?
    '''
    cursor.execute(update_query, (quantity, price, id))
    conn.commit()

This function takes in the product ID, quantity, and price as arguments and performs an UPDATE operation on the corresponding record in the products table.

Call the function and pass the product details:
```
update_product(1, 8, 8.99)
```
This will update the quantity to 8 and the price to 8.99 for the product with ID 1.

Well done! We can now update product information in our inventory.

Retrieving Data

Now that we have inserted and updated products, let’s learn how to retrieve the data from our inventory. We will create a function that allows us to get all the products in the inventory.

To retrieve data, follow these steps:

Open a Python shell and import the SQLite3 module if not already imported:
```
import sqlite3
```
Connect to the SQLite database:
```
conn = sqlite3.connect('inventory.db')
```
Create a cursor object:
```
cursor = conn.cursor()
```

Define a function to retrieve all products:

def get_all_products():
    select_query = '''
    SELECT * FROM products
    '''
    cursor.execute(select_query)
    rows = cursor.fetchall()
    return rows

This function performs a SELECT operation to fetch all rows from the products table and returns the result as a list of tuples.

Call the function and print the returned data:
```
products = get_all_products()
for product in products:
    print(product)
```
This will print all the products stored in the database.

Great work! We are now able to retrieve and display product information from our inventory.

Deleting Data

Lastly, let’s learn how to delete products from our inventory. We will create a function that allows us to remove a specific product from the table.

To delete data, follow these steps:

Open a Python shell and import the SQLite3 module if not already imported:
```
import sqlite3
```
Connect to the SQLite database:
```
conn = sqlite3.connect('inventory.db')
```
Create a cursor object:
```
cursor = conn.cursor()
```

Define a function to delete a product:

def delete_product(id):
    delete_query = '''
    DELETE FROM products
    WHERE id = ?
    '''
    cursor.execute(delete_query, (id,))
    conn.commit()

This function takes in the product ID as an argument and performs a DELETE operation on the corresponding record in the products table.

Call the function and pass the product ID:
```
delete_product(1)
```
This will delete the product with ID 1 from the table.

Congratulations! We have completed the tutorial and learned how to build an inventory management system using Python and SQLite. We can now add, update, retrieve, and delete products in our inventory.

Conclusion

In this tutorial, we covered the basics of using SQLite with Python to build an inventory management system. We learned how to create a database, set up a connection, create tables, insert, update, retrieve, and delete data.

By applying the concepts learned in this tutorial, you can extend the functionality of your inventory management system further. Some possible enhancements include adding search functionality, implementing user authentication, and generating reports.

Keep experimenting and exploring SQLite and Python to unleash the full potential of your inventory management system!

Published: 5 July 2023