A Complete Guide to Connecting Python with SQLite

#python #database #beginners

When building small to medium-scale applications, you often need a lightweight database that requires zero configuration. SQLite is a self-contained, serverless database engine that comes pre-installed with Python, making it an ideal choice for beginner developers and quick projects.
In this tutorial, you will learn how to set up an SQLite database, perform basic CRUD(Create, Read, Update, Delete) operations, and handle database errors smoothly in python.

Prerequisites

To follow along with this tutorial, you will need:

Basic understanding of Python syntax (variables, functions, and loops).
Python 3.x installed on your system.

Step 1: Connecting to the SQLite Database

Python provides a built-in module called "sqlite3", so you do not need to install any external libraries.
Create a new Python file named db_tutorial.py and add the following code:

import sqlite3
try:
    # Connects to 'app_database.db'.If it doesn't exist,   
    Python creates it.
    connection = sqlite3.connect("app_database.db")
    cursor = connection.cursor()
    print("Database connected successfully!")
except sqlite3.Error as e:
       print(f" Error connecting to database: {e}")

How it works:

sqlite3.connect() opens a connection to the database file.
connection.cursor() creates a cursor object, which allows us to execute SQL statements.

Step 2: Creating a Table

Now that we have established a connection let's create a table named "users" to store user information.

# SQL query to create a users table
create_table_query = """
CREATE TABLE IF NOT EXISTS users (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       name TEXT NOT NULL,
       email TEXT UNIQUE NOT NULL
);
"""
cursor.execute(create_table_query)
connection.commit()
print("Table created successfully!")

Key Takeaways:

AUTOINCREMENT ensures every user get a unique id.
connection.commit() saves changes to the database.

Step 3: Inserting Data into the Database

To add data safely, we use parameterized queries instead of formatting raw strings. This prevents "SQL Injection" security vulnerabilities.

def add_user(name, email):
    insert_query = "Insert INTO users (name, email)         
                     VALUES (?, ?)"
    try:
        cursor.execute(insert_query, (name,email))
        connection.commit()
        print(f"User '{name}' added successfully!")
  except sqlite3.IntegrityError:
         print(f" Error: Email '{email}' already
               exists.")
# Testing the insert function
add_user("Rahul Sharma", "rahul@example.com")
add_user("Priya Singh", "priya@example.com")

Step 4: Reading Data from the Database

To fetch and display records from SQLite, use fetchall() or fetchone() methods.

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
print("\n--- Registered Users ---")
for row in rows:
       print(f"ID: {row[0]} | Name: {row[1]} | Email:  
             {row[2]}")

Step 5: Closing the Connection

Always close the database connection once operations are complete to prevent resource leaks and file lock errors.

# Close the cursor and connection at the end of the script
cursor.close()
connection.close()
print("Database connection closed.")

Conclusion

SQLite is a powerful tool for developers looking to integrate local data storage without managing a full SQL server. In this guide, we covered how to connect to SQLite, execute parameterized queries safely, and manage database errors.
Next Steps:
Try adding an UPDATE and DELETE function to expand this project into a full CRUD application!

DEV Community