Python for Profit

Building a Complete Inventory System

11

Building the Database with sqlite3

The Big Idea

This chapter translates our database design from Chapter 10 into reality by writing a Python script that uses the built-in sqlite3 module to create the physical inventory.db file and the Products table within it.

Roadmap

  • From Blueprint to Building: We'll discuss how we're now moving from the design phase (the ERD) to the construction phase (writing code).

  • Starting Fresh for Part 2: We will create a new, blank main.py file for our desktop application.

  • The Core sqlite3 Objects: An introduction to the two key players: the connection object (our link to the database file) and the cursor object (the tool we use to execute commands).

  • Writing Our First SQL Command: We'll write the CREATE TABLE command in SQL, defining our columns and their data types (INTEGER PRIMARY KEY AUTOINCREMENT, TEXT, REAL).

  • Building main.py Step-by-Step:

1.  Import the `sqlite3` module.

2.  Define our database filename.

3.  Create an `initialize_database` function to contain all our setup logic.

4.  Write the SQL to create the `Products` table, making it safe to run multiple times with `IF NOT EXISTS`.

5.  Execute the command and save our changes to the database file.

Full Chapter Content

From Blueprint to Building

In the last chapter, we were architects. We designed the blueprint for our database using an Entity-Relationship Diagram (ERD). Today, we become builders. We will take that blueprint and use Python and SQL to construct the foundation of our new application.

For this new phase, we will start with a fresh script. In your project folder, you can either rename your old main.py from Part 1 to main_cli.py to keep it safe, or you can create a new project folder for our desktop app. Either way, create a new, completely blank main.py file. This is where we will build our desktop application.

Step 1: Import sqlite3 and Define the Filename

Just as we imported json in Part 1, we must now import Python's built-in module for handling SQLite databases. We will also define the name for our database file as a global variable.

Add the following lines to your new, empty main.py file:

# --- PyInventory: A Step-by-Step Journey to Profit ---
# Part 2, Chapter 11: Building the Database with sqlite3

import sqlite3

DATABASE_FILE = "inventory.db"

Explanation:

  • import sqlite3: This line gives our script access to all the tools within the sqlite3 module.

  • DATABASE_FILE = "inventory.db": We store the filename in a variable. This is a professional practice that makes it easy to change the filename in one place if we ever need to, rather than hunting for it throughout our code.

Step 2: Connect to the Database and Create a Cursor

To interact with the database, we need two key objects:

  1. A Connection object: This represents the actual connection to our database file on the disk.

  2. A Cursor object: You can think of this as the "pen" you use to write commands or the "magnifying glass" you use to read data. All our SQL commands will be executed through a cursor.

Let's create a function to handle this setup process. It's good practice to wrap related steps in a function.

Add this function to your main.py:

def initialize_database():
    """Creates the database and the initial Products table if they don't exist."""

    # 1. Connect to the database.
    # This will create the inventory.db file if it does not already exist.
    conn = sqlite3.connect(DATABASE_FILE)

    # 2. Create a cursor object to execute SQL commands.
    cursor = conn.cursor()

    print("Database connection established.")

    # (We will add the command execution here in the next step)

    # 4. Close the connection.
    conn.close()
    print("Database connection closed.")

Explanation:

  • sqlite3.connect(DATABASE_FILE): This is the command that opens a connection to our database file. A crucial feature is that if inventory.db does not exist, this command will create it for you.

  • conn.cursor(): We use our new conn object to create a cursor. Now we're ready to send commands.

  • conn.close(): It's very important to close the connection when you're done to free up resources.

If you run this script now (python main.py), you will see the messages printed, and a new, empty file named inventory.db will appear in your project folder!

Step 3: Write and Execute the CREATE TABLE Command

Now we need to write the SQL command that creates our Products table based on the ERD from Chapter 10. We can write this command as a multi-line string inside our initialize_database function.

Modify the initialize_database function in your main.py file. We will add the SQL command and the code to execute it.

def initialize_database():
    """Creates the database and the initial Products table if they don't exist."""

    conn = sqlite3.connect(DATABASE_FILE)
    cursor = conn.cursor()

    # 3. Write the SQL command to create the Products table.
    # Using "IF NOT EXISTS" prevents an error if the table is already there.
    create_table_query = """
    CREATE TABLE IF NOT EXISTS Products (
        product_id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        quantity INTEGER NOT NULL,
        price REAL NOT NULL
    );
    """

    # Execute the command.
    cursor.execute(create_table_query)

    # Commit the changes to the database file.
    conn.commit()

    print("Database and Products table initialized successfully.")

    conn.close()

Explanation of What's New:

  • CREATE TABLE IF NOT EXISTS Products (...): This is our SQL command.

    • IF NOT EXISTS: This is a critical addition. It tells SQLite: "Create this table only if a table with this name doesn't already exist." This makes our function "safe" to run multiple times without causing an error.
    • product_id INTEGER PRIMARY KEY AUTOINCREMENT: As designed, this is our unique ID. It's a number (INTEGER), it's the PRIMARY KEY, and the database will handle creating a new number for each product automatically (AUTOINCREMENT).
    • name TEXT NOT NULL UNIQUE: The product's name is text. NOT NULL means this field cannot be empty. UNIQUE means we cannot have two products with the exact same name. This is a database-level validation rule!
    • quantity INTEGER NOT NULL: An integer that cannot be empty.
    • price REAL NOT NULL: A decimal number (REAL) that cannot be empty.

  • cursor.execute(create_table_query): This line takes our SQL string and sends it to the database to be executed by the cursor.

  • conn.commit(): This is the most important new step. Making changes to a database is often a two-step process. You execute one or more commands, and then you commit them. Committing is the action that actually saves all the changes to the file. Without it, your changes would be lost when the connection is closed.

Step 4: Run the Initialization

Finally, we need to actually call our function. Add this to the very bottom of your main.py file.

# This block ensures the initialization runs only when the script is executed directly.
if __name__ == "__main__":
    initialize_database()

Explanation:

  • if __name__ == "__main__":: This is a standard Python convention. It means "run the code inside this block only if this script is being run directly by the user," not if it's being imported as a module into another script. It's the perfect place to put our one-time setup code.

Now, run your script: python main.py. You will see your success messages. The inventory.db file is still there, but now it's no longer empty. It contains the structure of our Products table, ready and waiting for data.

Chapter 11: Summary & What's Next

You have successfully built the physical foundation for our new application.

  • You learned how to use the sqlite3 module to connect to a database file.

  • You understand the roles of the connection and cursor objects.

  • You wrote and executed your first SQL command (CREATE TABLE) from a Python script.

  • You learned the importance of committing your changes to save them permanently.

Our database is empty but ready. In the next chapter, we will scrap our old file-based functions from Part 1 and write new, SQL-powered functions to add, view, update, and delete products directly from our new database. After mastering this, we will then explore how an ORM can make this process even more elegant.