23
Day 23: Database Operations
Chapter 23 • Advanced
60 min
Databases are essential for storing and managing data in applications. Python provides excellent tools for working with databases.
What are Databases?
A database is an organized collection of data that can be easily accessed, managed, and updated. It's like a digital filing cabinet for your application's data.
Types of Databases:
- SQL Databases - Structured data with relationships
- NoSQL Databases - Flexible, non-relational data
- In-memory Databases - Fast, temporary storage
- File-based Databases - Simple, file-based storage
Popular Databases:
- SQLite - Lightweight, file-based SQL database
- PostgreSQL - Powerful, open-source SQL database
- MySQL - Popular, open-source SQL database
- MongoDB - Document-based NoSQL database
- Redis - In-memory data structure store
Python Database Libraries:
- sqlite3 - Built-in SQLite support
- psycopg2 - PostgreSQL adapter
- PyMySQL - MySQL connector
- pymongo - MongoDB driver
- redis-py - Redis client
Database Operations (CRUD):
- Create - Insert new records
- Read - Query and retrieve data
- Update - Modify existing records
- Delete - Remove records
SQL Basics:
- SELECT - Query data
- INSERT - Add new records
- UPDATE - Modify records
- DELETE - Remove records
- CREATE TABLE - Create new tables
- DROP TABLE - Remove tables
Hands-on Examples
Database Operations
import sqlite3
import json
# Create and connect to database
def create_database():
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# Create table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
age INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
conn.commit()
return conn
# Insert data
def insert_user(conn, name, email, age):
cursor = conn.cursor()
try:
cursor.execute(
"INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
(name, email, age)
)
conn.commit()
return cursor.lastrowid
except sqlite3.IntegrityError:
return None
# Query data
def get_users(conn):
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
return cursor.fetchall()
def get_user_by_email(conn, email):
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE email = ?", (email,))
return cursor.fetchone()
# Update data
def update_user_age(conn, email, new_age):
cursor = conn.cursor()
cursor.execute(
"UPDATE users SET age = ? WHERE email = ?",
(new_age, email)
)
conn.commit()
return cursor.rowcount
# Test database operations
def test_database():
# Create database
conn = create_database()
# Insert users
print("Inserting users...")
insert_user(conn, "Priya", "[email protected]", 25)
insert_user(conn, "Marcus", "[email protected]", 30)
insert_user(conn, "Sofia", "[email protected]", 35)
# Query all users
print("\nAll users:")
users = get_users(conn)
for user in users:
print(f"ID: {user[0]}, Name: {user[1]}, Email: {user[2]}, Age: {user[3]}")
# Query specific user
print("\nFinding user by email:")
user = get_user_by_email(conn, "[email protected]")
if user:
print(f"Found: {user[1]} ({user[2]})")
# Update user
print("\nUpdating user age...")
updated = update_user_age(conn, "[email protected]", 26)
print(f"Updated {updated} record(s)")
# Close connection
conn.close()
# Run the test
test_database()This example demonstrates basic database operations including creating tables, inserting data, querying records, updating information, and managing database connections.