Python Lecture 18: Complete Guide to Database Connectivity with SQLite
Welcome to a transformative lecture that elevates your Python applications from temporary data storage to persistent, structured data management! Until now, data in your programs either disappeared when the program ended or was stored in files with limited querying capabilities. Today we're exploring databases - structured systems for storing, organizing, and retrieving data efficiently. We'll focus on SQLite, a lightweight database that's perfect for learning and surprisingly powerful for real applications.
Think about the applications you use: they store massive amounts of structured data - user accounts, products, transactions, messages, settings. This data needs to be stored permanently, queried efficiently, updated safely, and backed up reliably. Databases solve these challenges elegantly. SQLite, despite being "lightweight," powers countless applications including mobile apps, browsers, and embedded systems. It's built into Python, requires no setup, and teaches you database concepts that apply to all database systems.
By the end of this comprehensive lecture, you'll understand what databases are and why they're essential, how to create and manipulate SQLite databases using Python, how to write SQL queries to retrieve exactly the data you need, how to design database schemas effectively, and how to build complete database-driven applications. These skills are fundamental to most real-world software development. Let's dive into the world of databases!
Understanding Databases - Structured Data Storage
A database is an organized collection of structured data stored electronically. Unlike simple files, databases provide powerful features: efficient searching, complex queries, data integrity constraints, concurrent access, and transactional safety. Understanding these concepts separates casual programmers from professional developers.
Why Databases Matter: Imagine storing customer data in text files - one file per customer, or all in one huge file. How do you find customers in a specific city? How do you update prices across thousands of products? How do you ensure two processes don't corrupt data by writing simultaneously? Databases solve these problems elegantly with indexing, SQL queries, transactions, and locking mechanisms.
Relational Databases Concept: Most databases are relational - data is organized in tables (like spreadsheets). Each table has rows (records) and columns (fields). Tables can relate to each other through shared keys. A customer table connects to an orders table through customer_id. This structure eliminates data duplication, maintains consistency, and enables complex queries across related data.
SQLite Advantages: SQLite is perfect for learning because it's zero-configuration (no server to setup), file-based (entire database in one file), fast, and included with Python. Despite being "lite," it's remarkably capable - handling gigabytes of data, supporting complex queries, and powering applications used by billions of people. Most importantly, SQL skills learned with SQLite transfer directly to MySQL, PostgreSQL, and other database systems.
Introduction to SQL - Structured Query Language
SQL (Structured Query Language) is the standard language for interacting with relational databases. While databases store data, SQL is how you create tables, insert records, query data, update values, and delete entries. SQL is declarative - you specify what you want, not how to get it. The database figures out the optimal way to execute your query.
Core SQL Operations - CRUD: Database operations fall into four categories: Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE). Together, these form CRUD operations - the foundation of data management. Additionally, you use Data Definition Language (DDL) commands like CREATE TABLE and DROP TABLE to define database structure.
SQL Syntax Basics: SQL keywords are typically uppercase (though case-insensitive), statements end with semicolons, and strings use single quotes. Table and column names should be descriptive. Understanding SQL syntax is like learning a new language - it takes practice but becomes second nature.
# Common SQL commands (as strings in Python)
# Creating a table
create_table = """
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
age INTEGER,
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
"""
# Inserting data
insert_query = "INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@email.com', 25)"
# Selecting data
select_all = "SELECT * FROM users"
select_specific = "SELECT name, email FROM users WHERE age > 21"
select_ordered = "SELECT * FROM users ORDER BY name ASC"
# Updating data
update_query = "UPDATE users SET age = 26 WHERE name = 'Alice'"
# Deleting data
delete_query = "DELETE FROM users WHERE id = 5"
# Complex queries
complex_query = """
SELECT name, email, age
FROM users
WHERE age BETWEEN 20 AND 30
AND email LIKE '%@gmail.com'
ORDER BY age DESC
LIMIT 10
"""
📚 Related Python Tutorials:
Connecting to SQLite Databases in Python
Python's built-in sqlite3 module provides a complete interface to SQLite databases. No installation needed - it's included with Python. The workflow is: connect to database, create a cursor, execute SQL commands, commit changes, close connection.
import sqlite3
# Connect to database (creates file if doesn't exist)
connection = sqlite3.connect('mydata base.db')
# Create cursor object for executing queries
cursor = connection.cursor()
# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
grade INTEGER,
email TEXT UNIQUE
)
''')
# Insert data
cursor.execute('''
INSERT INTO students (name, grade, email)
VALUES ('Alice', 85, 'alice@school.com')
''')
# Commit changes (save to database)
connection.commit()
# Query data
cursor.execute('SELECT * FROM students')
results = cursor.fetchall()
print("All students:")
for row in results:
print(row)
# Close connection
connection.close()
print("Database operations completed!")
Best Practice - Use Context Manager: Like file operations, database connections should be properly closed. Use the with statement: with sqlite3.connect('db.db') as conn:. This ensures the connection closes automatically, even if errors occur. Always use context managers in production code!
CRUD Operations - Create, Read, Update, Delete
Let's explore each CRUD operation in detail with practical examples. These operations form the backbone of database interaction.
import sqlite3
with sqlite3.connect('school.db') as conn:
cursor = conn.cursor()
# CREATE - Insert single record
cursor.execute('''
INSERT INTO students (name, grade, email)
VALUES ('Bob', 92, 'bob@school.com')
''')
# INSERT multiple records
students_data = [
('Charlie', 78, 'charlie@school.com'),
('Diana', 95, 'diana@school.com'),
('Eve', 88, 'eve@school.com')
]
cursor.executemany('''
INSERT INTO students (name, grade, email)
VALUES (?, ?, ?)
''', students_data)
conn.commit()
print("Records inserted successfully")
# READ - Select all records
cursor.execute('SELECT * FROM students')
all_students = cursor.fetchall()
print("\nAll students:")
for student in all_students:
print(f"ID: {student[0]}, Name: {student[1]}, Grade: {student[2]}")
# READ - Select with condition
cursor.execute('SELECT name, grade FROM students WHERE grade >= 90')
top_students = cursor.fetchall()
print("\nTop students (grade >= 90):")
for name, grade in top_students:
print(f"{name}: {grade}")
# READ - Select one record
cursor.execute('SELECT * FROM students WHERE name = ?', ('Alice',))
alice = cursor.fetchone()
if alice:
print(f"\nAlice's record: {alice}")
# UPDATE - Modify existing record
cursor.execute('''
UPDATE students
SET grade = ?
WHERE name = ?
''', (90, 'Charlie'))
conn.commit()
print(f"\nUpdated {cursor.rowcount} record(s)")
# DELETE - Remove record
cursor.execute('DELETE FROM students WHERE grade < 80')
conn.commit()
print(f"Deleted {cursor.rowcount} record(s)")
# Count remaining records
cursor.execute('SELECT COUNT(*) FROM students')
count = cursor.fetchone()[0]
print(f"\nTotal students remaining: {count}")
SQL Injection Warning: NEVER use string formatting (f-strings or .format()) to insert user input into SQL queries! This creates SQL injection vulnerabilities where malicious users can execute arbitrary SQL. ALWAYS use parameterized queries with ? placeholders. SQLite handles escaping and sanitization automatically. This is critical for security!
Advanced SQL Queries
Real applications require complex queries: filtering with multiple conditions, sorting results, limiting output, grouping data, and performing calculations. Mastering these queries enables powerful data analysis.
import sqlite3
with sqlite3.connect('school.db') as conn:
cursor = conn.cursor()
# Filtering with multiple conditions
cursor.execute('''
SELECT name, grade
FROM students
WHERE grade >= 85 AND email LIKE '%@school.com'
ORDER BY grade DESC
''')
print("High-achieving school email students:")
for name, grade in cursor.fetchall():
print(f"{name}: {grade}")
# Aggregation functions
cursor.execute('''
SELECT
COUNT(*) as total_students,
AVG(grade) as average_grade,
MAX(grade) as highest_grade,
MIN(grade) as lowest_grade
FROM students
''')
stats = cursor.fetchone()
print(f"\n=== Class Statistics ===")
print(f"Total Students: {stats[0]}")
print(f"Average Grade: {stats[1]:.2f}")
print(f"Highest Grade: {stats[2]}")
print(f"Lowest Grade: {stats[3]}")
# GROUP BY for analysis
cursor.execute('''
SELECT
CASE
WHEN grade >= 90 THEN 'A'
WHEN grade >= 80 THEN 'B'
WHEN grade >= 70 THEN 'C'
ELSE 'Below C'
END as letter_grade,
COUNT(*) as count
FROM students
GROUP BY letter_grade
ORDER BY letter_grade
''')
print("\n=== Grade Distribution ===")
for letter, count in cursor.fetchall():
print(f"Grade {letter}: {count} students")
# LIMIT for pagination
page_size = 5
page = 1
offset = (page - 1) * page_size
cursor.execute('''
SELECT * FROM students
ORDER BY name
LIMIT ? OFFSET ?
''', (page_size, offset))
print(f"\n=== Page {page} (showing {page_size} per page) ===")
for student in cursor.fetchall():
print(student)
Building a Complete Database Application
import sqlite3
from datetime import datetime
class StudentDatabase:
"""Complete student management system"""
def __init__(self, db_name='students.db'):
self.db_name = db_name
self.create_table()
def get_connection(self):
"""Create database connection"""
return sqlite3.connect(self.db_name)
def create_table(self):
"""Create students table if not exists"""
with self.get_connection() as conn:
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
grade INTEGER CHECK(grade >= 0 AND grade <= 100),
department TEXT,
enrollment_date DATE DEFAULT CURRENT_DATE
)
''')
conn.commit()
def add_student(self, name, email, grade, department):
"""Add new student"""
try:
with self.get_connection() as conn:
cursor = conn.cursor()
cursor.execute('''
INSERT INTO students (name, email, grade, department)
VALUES (?, ?, ?, ?)
''', (name, email, grade, department))
conn.commit()
return True, f"Student {name} added successfully"
except sqlite3.IntegrityError:
return False, "Email already exists"
except Exception as e:
return False, str(e)
def get_all_students(self):
"""Retrieve all students"""
with self.get_connection() as conn:
cursor = conn.cursor()
cursor.execute('SELECT * FROM students ORDER BY name')
return cursor.fetchall()
def search_student(self, search_term):
"""Search student by name or email"""
with self.get_connection() as conn:
cursor = conn.cursor()
cursor.execute('''
SELECT * FROM students
WHERE name LIKE ? OR email LIKE ?
''', (f'%{search_term}%', f'%{search_term}%'))
return cursor.fetchall()
def update_grade(self, student_id, new_grade):
"""Update student grade"""
with self.get_connection() as conn:
cursor = conn.cursor()
cursor.execute('''
UPDATE students
SET grade = ?
WHERE id = ?
''', (new_grade, student_id))
conn.commit()
return cursor.rowcount > 0
def delete_student(self, student_id):
"""Delete student"""
with self.get_connection() as conn:
cursor = conn.cursor()
cursor.execute('DELETE FROM students WHERE id = ?', (student_id,))
conn.commit()
return cursor.rowcount > 0
def get_department_stats(self):
"""Get statistics by department"""
with self.get_connection() as conn:
cursor = conn.cursor()
cursor.execute('''
SELECT
department,
COUNT(*) as total_students,
AVG(grade) as avg_grade,
MAX(grade) as top_grade
FROM students
GROUP BY department
ORDER BY avg_grade DESC
''')
return cursor.fetchall()
# Usage Example
db = StudentDatabase()
# Add students
db.add_student('Alice Johnson', 'alice@school.com', 95, 'CS')
db.add_student('Bob Smith', 'bob@school.com', 87, 'Math')
db.add_student('Charlie Brown', 'charlie@school.com', 92, 'CS')
# Display all students
print("=== All Students ===")
for student in db.get_all_students():
print(f"ID: {student[0]}, Name: {student[1]}, Grade: {student[3]}")
# Search functionality
print("\n=== Search Results (CS) ===")
for student in db.search_student('CS'):
print(f"{student[1]} - {student[4]}")
# Department statistics
print("\n=== Department Statistics ===")
for dept, total, avg, top in db.get_department_stats():
print(f"{dept}: {total} students, Avg: {avg:.2f}, Top: {top}")
Real-World Applications: Databases power virtually every application. E-commerce stores product catalogs, customer accounts, orders, and inventory in databases. Social media platforms store users, posts, comments, and relationships. Banking systems store accounts and transactions. Content management systems store articles and media. Every substantial application uses databases for persistent, structured data storage.
Database Design Best Practices
1. Use Appropriate Data Types: INTEGER for whole numbers, REAL for decimals, TEXT for strings, BLOB for binary data. Choose types that match your data.
2. Define Primary Keys: Every table should have a primary key - a unique identifier for each record. Use AUTOINCREMENT for automatic ID generation.
3. Add Constraints: Use NOT NULL for required fields, UNIQUE for fields that must be unique, CHECK for validation, DEFAULT for default values.
4. Index Important Columns: Create indexes on columns used in WHERE clauses and JOINs for faster queries.
5. Normalize Data: Avoid duplicating information. If data repeats, consider splitting into related tables.
6. Use Transactions: Wrap related operations in transactions for data consistency. If one operation fails, all rollback.
Summary and Database Mastery
Database connectivity transforms Python programs into data-driven applications. You've learned:
✓ Understanding databases and their importance
✓ SQL syntax and query fundamentals
✓ Connecting to SQLite databases
✓ CRUD operations (Create, Read, Update, Delete)
✓ Advanced queries and data analysis
✓ Building complete database applications
✓ Best practices for database design
✓ Security considerations (SQL injection prevention)
Final Challenge: Build a complete library management system with databases. Create tables for books (title, author, ISBN, available copies), members (name, email, phone), and borrowings (member_id, book_id, borrow_date, return_date). Implement functions to: add books/members, search books, borrow books (decrease available copies), return books (increase copies), list overdue books, generate reports. Include error handling and use SQL best practices!
