How to Store and Retrieve Data using SQLite with Python

Are you looking to store and retrieve data using a simple yet powerful database? Enter SQLite and Python—a duo that's perfect for managing your data needs. In this guide, we'll go through the basics of storing and retrieving data using SQLite and Python.

Storing and Retrieving Data in SQLite with Python

Set up SQLite in Python
Create a Database and Table
Insert Data into the Table
Query Data from the Table

1. Set up SQLite in Python

To get started, you need to ensure that SQLite is set up in your Python environment. The good news? Python comes with SQLite built-in! Here’s how you can start using it:

Import the SQLite library: Use import sqlite3. This will give you access to all the functions you need.
Connect to a database: Use sqlite3.connect('database_name.db'). If the database doesn't exist, it'll be created.

import sqlite3

# Creating a connection to the database
connection = sqlite3.connect('my_database.db')

2. Create a Database and Table

Once the connection is established, you can start making your database structure by creating tables.

Create a cursor object: Use connection.cursor() to perform SQL commands.
Create a table: Execute an SQL CREATE TABLE command using cursor.execute().

cursor = connection.cursor()

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users
                  (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')

3. Insert Data into the Table

Adding data to your table is the next step. It’s super simple with SQL INSERT INTO statements.

Insert data: Use cursor.execute() to insert records into your table.
Commit changes: Don’t forget to commit the transaction with connection.commit().

# Insert data into the table
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Bob', 25)")

# Commit the changes
connection.commit()

4. Query Data from the Table

Now, let’s move on to retrieving data using SQL queries.

Select data: Use SELECT statements within cursor.execute().
Fetch results: Use fetchall() or fetchone() to get the data.

# Select and fetch data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

# Print out the rows
for row in rows:
    print(row)

Wrapping Up

You’ve just learned how to store and retrieve data using SQLite and Python! Whether you’re on a small project or building something bigger, these tools make handling data easy. Keep experimenting and expanding your database skills.

Happy Coding! 😊