Unlocking Database Magic with SQLite in Python

Dive into the world of database interactions with Python and SQLite! This tutorial will guide you through the essentials of creating, reading, updating, and deleting data using Python’s built-in sqlite3 library. Whether you’re building a web application, managing data, or just exploring the power of databases, this is your starting point.

⚙️ Setup and Connection

First, let’s establish a connection to our SQLite database:

import sqlite3

conn = sqlite3.connect('my_database.db')

cursor = conn.cursor()

This code snippet imports the sqlite3 library and creates a connection to a database file named my_database.db. If the file doesn’t exist, SQLite will create it for you.

✍️ Creating a Table

Now, let’s define the structure of our data by creating a table:

cursor.execute('''
    CREATE TABLE IF NOT EXISTS customers (
        id INTEGER PRIMARY KEY,
        name TEXT,
        email TEXT
    )
''')

This code creates a table named customers with columns for id, name, and email. The id column is set as the primary key, ensuring each customer has a unique identifier.

🔹 Inserting Data

Let’s populate our table with some initial data:

cursor.execute("INSERT INTO customers (name, email) VALUES ('Alice Smith', 'alice@example.com')")
cursor.execute("INSERT INTO customers (name, email) VALUES ('Bob Johnson', 'bob@example.com')")

These lines insert two new rows into the customers table, adding information for Alice Smith and Bob Johnson.

🖊️ Updating Data

What if Alice changes her email address? No problem!

cursor.execute("UPDATE customers SET email = 'alice.new@example.com' WHERE name = 'Alice Smith'")

This code updates the email field for the customer named ‘Alice Smith’.

🔄 Deleting Data

Farewell, Bob! Let’s remove him from our database:

cursor.execute("DELETE FROM customers WHERE name = 'Bob Johnson'")

This line deletes the row corresponding to Bob Johnson.

🔍 Querying Data

Finally, let’s retrieve and display the data in our table:

cursor.execute("SELECT * FROM customers")
rows = cursor.fetchall()
for row in rows:
    print(row)

This code selects all rows from the customers table and prints them to the console.

💾 Committing Changes and Closing the Connection

Don’t forget to save your changes and close the connection:

conn.commit()
conn.close()

conn.commit() saves all the changes made during the session, and conn.close() closes the connection to the database.

🔝 Wrap-Up

Congratulations! You’ve successfully navigated the basics of SQLite database operations with Python. From creating tables to manipulating data, you’re now equipped to build powerful applications that interact with databases.

📌 Summary

• Connected to an SQLite database.
• Created a table.
• Inserted, updated, and deleted data.
• Queried and displayed data.
• Committed changes and closed the connection.

Explore more and keep building amazing applications!