Select a Subtopic

Day 12: Database Interaction

In this section, you'll learn how to interact with databases using Python. Specifically, you'll focus on connecting to a database using the sqlite3 module, creating tables, inserting, updating, and deleting records, and querying data.

Topics Covered:

Connecting to a Database using sqlite3 - Learn how to connect to SQLite databases.
Creating Tables - Define tables with columns and data types.
Inserting, Updating, and Deleting Records - Add, modify, and remove records in the database.
Querying Data - Retrieve data from the database using SQL queries.

1. Connecting to a Database using `sqlite3`

In SQLite, a database connection is created using the sqlite3.connect() function. Here’s how to connect to a database:

import sqlite3

# Connect to the database (it will create the database file if it doesn't exist)
conn = sqlite3.connect('user_data.db')  # Replace 'user_data.db' with your desired database name

# Create a cursor object to interact with the database
cursor = conn.cursor()

# Always close the connection when done
conn.close()

2. Creating Tables

In SQLite, a table is created using the SQL CREATE TABLE command. Here's how to create a users table:

# Reconnect to the database and create a table
conn = sqlite3.connect('user_data.db')
cursor = conn.cursor()

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

# Commit the changes and close the connection
conn.commit()
conn.close()

3. Inserting, Updating, and Deleting Records

Inserting Records

Here’s how you can insert a new user record into the users table:

conn = sqlite3.connect('user_data.db')
cursor = conn.cursor()

# Inserting a record into the 'users' table
cursor.execute('''
INSERT INTO users (name, age, email) VALUES (?, ?, ?)
''', ('Alice', 30, 'alice@example.com'))

# Commit the changes and close the connection
conn.commit()
conn.close()

Updating Records

Here’s how you can update a user's age:

conn = sqlite3.connect('user_data.db')
cursor = conn.cursor()

# Update a user's age based on their name
cursor.execute('''
UPDATE users
SET age = ?
WHERE name = ?
''', (31, 'Alice'))

# Commit the changes and close the connection
conn.commit()
conn.close()

Deleting Records

To delete a record, use the DELETE statement:

conn = sqlite3.connect('user_data.db')
cursor = conn.cursor()

# Delete a user by name
cursor.execute('''
DELETE FROM users WHERE name = ?
''', ('Alice',))

# Commit the changes and close the connection
conn.commit()
conn.close()

4. Querying Data

Here’s how you can retrieve all users from the database using a SELECT query:

conn = sqlite3.connect('user_data.db')
cursor = conn.cursor()

# Query the database to get all users
cursor.execute('SELECT * FROM users')

# Fetch all rows of the result
users = cursor.fetchall()

# Display the results
for user in users:
    print(user)

conn.close()

Exercises:

Create a database for storing user information. Create a users table with columns for name, age, and email.
Perform CRUD operations: Add a user, update their information, delete a user, and fetch all users.
Write a function to get users who are older than a specific age.

Summary of Key Concepts:

SQLite Database Connection: Interact with SQLite databases using Python's sqlite3 module.
Creating Tables: Use CREATE TABLE to define your schema.
CRUD Operations: Use INSERT, UPDATE, and DELETE to modify data.
Querying Data: Use SELECT to fetch data.

HTML