Python Database Basics

Introduction

In modern software development, databases play a crucial role in storing and managing data. Python provides excellent support for working with various database systems, making it a powerful tool for data management tasks. This tutorial introduces the fundamental concepts and techniques for connecting Python applications to databases.

Whether you're building a web application, analyzing data, or creating a simple record-keeping system, understanding how to interact with databases using Python is an essential skill for any programmer.

Why Use Databases with Python?

Before diving into the technical details, let's understand why combining Python with databases is beneficial:

• Persistence: Unlike variables in Python programs that disappear when the program ends, data stored in databases remains persistent.
• Structured Storage: Databases provide organized ways to store related data.
• Data Integrity: Database systems help maintain accurate and consistent data.
• Efficient Queries: Databases are optimized for searching and retrieving specific information quickly.
• Concurrency: Multiple users or programs can access the database simultaneously.

Python Database API

Python defines a standard API (Application Programming Interface) for database access called DB-API. This standardization allows you to use similar code patterns regardless of which specific database system you're connecting to.

The typical workflow for database operations in Python follows these steps:

1. Connect to the database
2. Create a cursor object
3. Execute SQL queries using the cursor
4. Fetch results (if applicable)
5. Commit changes (for data modifications)
6. Close the cursor and connection

Working with SQLite

SQLite is a lightweight, disk-based database that doesn't require a separate server process. Python includes built-in support for SQLite through the sqlite3 module, making it perfect for beginners and small applications.

Connecting to an SQLite Database

python

import sqlite3

# Connect to a database (creates it if it doesn't exist)
conn = sqlite3.connect('example.db')
print("Database connected successfully!")

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

Output:

Database connected successfully!

Creating Tables and Inserting Data

Let's create a simple table and add some records:

python

import sqlite3

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

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

# Insert a single record
cursor.execute('''
INSERT INTO users (name, email, age) 
VALUES (?, ?, ?)
''', ('John Doe', '[email protected]', 28))

# Insert multiple records
users_data = [
    ('Alice Smith', '[email protected]', 34),
    ('Bob Johnson', '[email protected]', 42),
    ('Charlie Brown', '[email protected]', 19)
]
cursor.executemany('INSERT INTO users (name, email, age) VALUES (?, ?, ?)', users_data)

# Commit the changes
conn.commit()
print("Data inserted successfully!")

conn.close()

Output:

Data inserted successfully!

Querying and Retrieving Data

Now let's retrieve and display the data we've stored:

python

import sqlite3

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

# Basic SELECT query
print("All users:")
cursor.execute('SELECT * FROM users')
all_users = cursor.fetchall()
for user in all_users:
    print(f"ID: {user[0]}, Name: {user[1]}, Email: {user[2]}, Age: {user[3]}")

# Filtering with WHERE clause
print("\nUsers older than 30:")
cursor.execute('SELECT name, age FROM users WHERE age > 30')
older_users = cursor.fetchall()
for user in older_users:
    print(f"Name: {user[0]}, Age: {user[1]}")

conn.close()

Output:

All users:
ID: 1, Name: John Doe, Email: [email protected], Age: 28
ID: 2, Name: Alice Smith, Email: [email protected], Age: 34
ID: 3, Name: Bob Johnson, Email: [email protected], Age: 42
ID: 4, Name: Charlie Brown, Email: [email protected], Age: 19

Users older than 30:
Name: Alice Smith, Age: 34
Name: Bob Johnson, Age: 42

Updating and Deleting Records

Let's see how to modify and remove data:

python

import sqlite3

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

# Update a record
cursor.execute('''
UPDATE users
SET age = 29
WHERE name = 'John Doe'
''')
print(f"Updated {cursor.rowcount} record(s)")

# Delete a record
cursor.execute('''
DELETE FROM users
WHERE name = 'Charlie Brown'
''')
print(f"Deleted {cursor.rowcount} record(s)")

# Commit the changes
conn.commit()

# Verify changes
print("\nCurrent users after changes:")
cursor.execute('SELECT name, age FROM users')
for user in cursor.fetchall():
    print(f"Name: {user[0]}, Age: {user[1]}")

conn.close()

Output:

Updated 1 record(s)
Deleted 1 record(s)

Current users after changes:
Name: John Doe, Age: 29
Name: Alice Smith, Age: 34
Name: Bob Johnson, Age: 42

Error Handling

When working with databases, it's important to handle potential errors gracefully:

python

import sqlite3

conn = None
try:
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    
    # Attempt an operation that might fail
    cursor.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
                  ('Jane Doe', '[email protected]', 25))  # Email already exists
    
    conn.commit()
    print("Operation successful!")
    
except sqlite3.Error as e:
    if conn:
        conn.rollback()  # Undo any changes if an error occurs
    print(f"Database error: {e}")
    
finally:
    if conn:
        conn.close()  # Always close the connection

Output:

Database error: UNIQUE constraint failed: users.email

Using Context Managers

Python's with statement can help manage database connections more elegantly:

python

import sqlite3

# Using a context manager for the connection
with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    
    cursor.execute("SELECT COUNT(*) FROM users")
    count = cursor.fetchone()[0]
    print(f"Total number of users: {count}")
    
    # No need to explicitly commit or close the connection
    # It's handled automatically when the with block ends

print("Connection closed automatically")

Output:

Total number of users: 3
Connection closed automatically

Real-World Example: Simple Contact Management System

Let's put everything together in a more practical example - a simple contact management system:

python

import sqlite3
import datetime

def setup_database():
    """Initialize the database and create tables if they don't exist."""
    with sqlite3.connect('contacts.db') as conn:
        cursor = conn.cursor()
        
        # Create tables
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS contacts (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            phone TEXT,
            email TEXT,
            created_at TEXT
        )
        ''')
        conn.commit()
    
def add_contact(name, phone, email):
    """Add a new contact to the database."""
    current_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    with sqlite3.connect('contacts.db') as conn:
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO contacts (name, phone, email, created_at) VALUES (?, ?, ?, ?)",
            (name, phone, email, current_time)
        )
        conn.commit()
        return cursor.lastrowid

def search_contacts(search_term):
    """Search for contacts matching the search term."""
    with sqlite3.connect('contacts.db') as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT * FROM contacts WHERE name LIKE ? OR phone LIKE ? OR email LIKE ?",
            (f'%{search_term}%', f'%{search_term}%', f'%{search_term}%')
        )
        return cursor.fetchall()

def list_all_contacts():
    """Retrieve and return all contacts."""
    with sqlite3.connect('contacts.db') as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM contacts ORDER BY name")
        return cursor.fetchall()

def delete_contact(contact_id):
    """Delete a contact by ID."""
    with sqlite3.connect('contacts.db') as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM contacts WHERE id = ?", (contact_id,))
        conn.commit()
        return cursor.rowcount

# Demo usage
if __name__ == "__main__":
    setup_database()
    
    # Add some sample contacts
    print("Adding contacts...")
    add_contact("Alex Johnson", "555-1234", "[email protected]")
    add_contact("Sam Smith", "555-5678", "[email protected]")
    add_contact("Taylor Jones", "555-9012", "[email protected]")
    
    # List all contacts
    print("\nAll contacts:")
    contacts = list_all_contacts()
    for contact in contacts:
        print(f"ID: {contact[0]}, Name: {contact[1]}, Phone: {contact[2]}, Email: {contact[3]}")
    
    # Search for contacts
    search_term = "Smith"
    print(f"\nSearch results for '{search_term}':")
    results = search_contacts(search_term)
    for contact in results:
        print(f"ID: {contact[0]}, Name: {contact[1]}, Phone: {contact[2]}, Email: {contact[3]}")
    
    # Delete a contact
    if contacts:
        deleted = delete_contact(contacts[0][0])
        print(f"\nDeleted {deleted} contact(s)")
    
    # Show final contact list
    print("\nRemaining contacts:")
    for contact in list_all_contacts():
        print(f"ID: {contact[0]}, Name: {contact[1]}, Phone: {contact[2]}, Email: {contact[3]}")

Output:

Adding contacts...

All contacts:
ID: 1, Name: Alex Johnson, Phone: 555-1234, Email: [email protected]
ID: 2, Name: Sam Smith, Phone: 555-5678, Email: [email protected]
ID: 3, Name: Taylor Jones, Phone: 555-9012, Email: [email protected]

Search results for 'Smith':
ID: 2, Name: Sam Smith, Phone: 555-5678, Email: [email protected]

Deleted 1 contact(s)

Remaining contacts:
ID: 2, Name: Sam Smith, Phone: 555-5678, Email: [email protected]
ID: 3, Name: Taylor Jones, Phone: 555-9012, Email: [email protected]

Best Practices for Python Database Programming

To ensure your database code is efficient, secure, and maintainable, follow these guidelines:

1. Use Parameterized Queries: Always use placeholders (?) and query parameters rather than string concatenation to prevent SQL injection attacks.

2. Close Connections: Always close database connections when done, preferably using context managers (with statements).

3. Handle Transactions: Use transactions for related operations that should succeed or fail as a group.

4. Error Handling: Implement proper error handling with try-except blocks around database operations.

5. Connection Pooling: For applications with frequent database operations, consider using connection pooling to improve performance.

6. Protect Sensitive Data: Never store sensitive information like passwords in plain text; use encryption or hashing.

7. Use ORM for Complex Applications: For larger applications, consider using Object-Relational Mapping (ORM) libraries like SQLAlchemy, Django ORM, or Peewee.

Summary

In this tutorial, we've covered the basics of working with databases in Python:

• Connecting to SQLite databases using Python's built-in sqlite3 module
• Creating tables and inserting data
• Querying and retrieving information
• Updating and deleting records
• Handling errors and using context managers
• Building a simple real-world application

Working with databases is a fundamental skill for Python developers. The concepts you've learned here apply not just to SQLite but also to other database systems like MySQL, PostgreSQL, and Microsoft SQL Server (with different connection libraries).

Additional Resources and Exercises

Further Learning Resources

1. Official Python SQLite Documentation
2. SQLAlchemy - A popular Python SQL toolkit and ORM
3. SQLite Official Website
4. W3Schools SQL Tutorial

Practice Exercises

1. Library Management System: Create a database application that manages books, authors, and borrowers for a small library.

2. Personal Finance Tracker: Build an application that records income, expenses, and generates simple reports.

3. Blog Database: Design and implement a database for a blog with posts, comments, and user information.

4. Product Inventory: Create an inventory management system for a small store that tracks products, suppliers, and sales.

5. Modify the Contact Manager:

   • Add the ability to update existing contacts
   • Add categories for contacts (family, work, friend)
   • Implement date-based searching (find contacts created within a date range)

Remember that consistent practice is the key to mastering database programming. Start with small projects and gradually increase complexity as you become more comfortable with the concepts!