Database connectivity is a crucial aspect of modern applications, allowing programs to store, retrieve, and manipulate data efficiently. Python provides several ways to interact with databases, with the Python Database API (DB-API) being the standard interface.
Why Use Databases?
Persistence: Data remains available even after the program ends
Data Integrity: Ensures data consistency and accuracy
Concurrent Access: Multiple users can access data simultaneously
Security: Built-in user authentication and access control
Efficient Querying: Fast data retrieval using SQL
Note: The Python DB-API provides a consistent interface for connecting to different database systems, making it easier to switch between them if needed.
2. Relational Databases
Relational databases store data in tables with rows and columns, and use SQL (Structured Query Language) for defining and manipulating data.
Key Concepts
Tables: Store data in rows and columns
Primary Key: Unique identifier for each record
Foreign Key: Establishes relationships between tables
Indexes: Improve query performance
Transactions: Group operations that must all succeed or fail together
Popular Relational Databases
Database
Description
Python Driver
SQLite
Lightweight, serverless, embedded database
sqlite3 (built-in)
MySQL
Popular open-source database
mysql-connector-python, PyMySQL
PostgreSQL
Advanced open-source database
psycopg2, asyncpg
Oracle
Enterprise-grade database
cx_Oracle
Microsoft SQL Server
Microsoft's enterprise database
pyodbc, pymssql
SQLite Example
import sqlite3
from sqlite3 import Error
def create_connection(db_file):
""" Create a database connection to a SQLite database """
conn = None
try:
conn = sqlite3.connect(db_file)
print(f"SQLite version: {sqlite3.version}")
return conn
except Error as e:
print(e)
return conn
def create_table(conn):
""" Create a table from the create_table_sql statement """
try:
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS projects (
id integer PRIMARY KEY,
name text NOT NULL,
begin_date text,
end_date text
)
''')
conn.commit()
print("Table created successfully")
except Error as e:
print(e)
def main():
database = r"pythonsqlite.db"
# Create a database connection
conn = create_connection(database)
# Create tables
if conn is not None:
create_table(conn)
conn.close()
else:
print("Error! Cannot create the database connection.")
if __name__ == '__main__':
main()
3. NoSQL Databases
NoSQL databases provide a mechanism for storage and retrieval of data that is modeled in means other than tabular relations used in relational databases.
Types of NoSQL Databases
Type
Description
Examples
Python Driver
Document
Stores data in JSON-like documents
MongoDB, CouchDB
pymongo, couchdb-python
Key-Value
Stores data as key-value pairs
Redis, DynamoDB
redis-py, boto3
Wide-Column
Stores data in columns instead of rows
Cassandra, HBase
cassandra-driver, happybase
Graph
Stores data in nodes and edges
Neo4j, ArangoDB
py2neo, python-arango
MongoDB Example
from pymongo import MongoClient
from pprint import pprint
# Connect to MongoDB (default: localhost:27017)
client = MongoClient('mongodb://localhost:27017/')
# Create or access a database
db = client['company']
# Create or access a collection (similar to a table)
employees = db.employees
# Insert a document
employee = {
'name': 'John Doe',
'position': 'Software Engineer',
'department': 'IT',
'skills': ['Python', 'MongoDB', 'Docker'],
'hire_date': '2023-01-15'
}
# Insert the document
employee_id = employees.insert_one(employee).inserted_id
print(f"Inserted employee with ID: {employee_id}")
# Find documents
print("All employees in IT department:")
for emp in employees.find({'department': 'IT'}):
pprint(emp)
# Close the connection
client.close()
When to Use NoSQL:
Handling large volumes of structured and unstructured data
Rapid development with flexible schema
Horizontally scalable applications
Real-time analytics and big data applications
4. Python DB-API 2.0
The Python Database API (DB-API) defines a standard interface for database access in Python, making it easier to write database-agnostic code.
DB-API Main Components
Connection Object: Manages the connection to the database
Cursor Object: Used to execute SQL statements and fetch results
Exceptions: Standard exceptions for error handling
Types: Standard type objects and constructors
DB-API Flow
# 1. Import the database module
import sqlite3 # or import mysql.connector, psycopg2, etc.
try:
# 2. Establish a connection
conn = sqlite3.connect('example.db')
# 3. Create a cursor object
cursor = conn.cursor()
# 4. Execute SQL statements
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
# 5. Insert data (using parameterized queries for security)
user_data = ('johndoe', 'john@example.com')
cursor.execute('INSERT INTO users (username, email) VALUES (?, ?)', user_data)
# 6. Commit the transaction
conn.commit()
# 7. Query data
cursor.execute('SELECT * FROM users')
for row in cursor.fetchall():
print(row)
except sqlite3.Error as e:
print(f"Database error: {e}")
# 8. Rollback in case of error
if conn:
conn.rollback()
finally:
# 9. Close the connection
if conn:
conn.close()
DB-API Exceptions
Warning: Raised for important warnings
Error: Base class for all database errors
InterfaceError: Errors related to the database interface
DatabaseError: Errors related to the database
DataError: Errors due to data processing
OperationalError: Errors related to database operations
IntegrityError: Errors related to database integrity
InternalError: Internal database errors
ProgrammingError: Programming errors like syntax errors
NotSupportedError: For unsupported features
5. Object-Relational Mappers (ORMs)
ORMs provide a high-level abstraction for database operations, allowing you to work with database records as Python objects.
Popular Python ORMs
ORM
Description
Databases
SQLAlchemy
Full-featured ORM with SQL expression language
SQLite, MySQL, PostgreSQL, Oracle, etc.
Django ORM
High-level ORM that comes with Django
SQLite, MySQL, PostgreSQL, Oracle
Peewee
Simple, expressive ORM with minimal overhead
SQLite, MySQL, PostgreSQL
Tortoise ORM
Asyncio ORM inspired by Django
PostgreSQL, MySQL, SQLite
SQLAlchemy Example
from sqlalchemy import create_engine, Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
# Create the base class
Base = declarative_base()
# Define the User model
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
username = Column(String(50), unique=True, nullable=False)
email = Column(String(100), unique=True, nullable=False)
created_at = Column(DateTime, default=datetime.utcnow)
def __repr__(self):
return f""
# Create database engine
engine = create_engine('sqlite:///users.db')
# Create tables
Base.metadata.create_all(engine)
# Create a session
Session = sessionmaker(bind=engine)
session = Session()
# Create (Insert)
new_user = User(username='johndoe', email='john@example.com')
session.add(new_user)
session.commit()
# Read (Query)
# Get all users
users = session.query(User).all()
# Get user by ID
user = session.query(User).get(1)
# Filter users
johns = session.query(User).filter(User.username.like('%john%')).all()
# Update
user = session.query(User).get(1)
user.email = 'newemail@example.com'
session.commit()
# Delete
user = session.query(User).get(1)
session.delete(user)
session.commit()
Benefits of ORMs:
Write database-agnostic code
Prevent SQL injection through parameterized queries
Simplify complex queries with Python code
Database schema migrations
Better code organization and maintainability
6. Database Best Practices
Security
Always use parameterized queries to prevent SQL injection
Never store plain-text passwords (use hashing with salt)
Limit database user permissions (principle of least privilege)
Use environment variables or secure configuration for credentials
Performance
Use indexes on frequently queried columns
Limit the amount of data retrieved (use LIMIT and OFFSET)
Use connection pooling for web applications
Consider using an ORM's eager loading for related data
Error Handling
import sqlite3
from contextlib import contextmanager
@contextmanager
def get_db_connection(db_file):
"""Context manager for database connections"""
conn = None
try:
conn = sqlite3.connect(db_file)
conn.row_factory = sqlite3.Row # Return rows as dictionaries
yield conn
except sqlite3.Error as e:
print(f"Database error: {e}")
if conn:
conn.rollback()
raise
finally:
if conn:
conn.close()
# Usage
try:
with get_db_connection('example.db') as conn:
cursor = conn.cursor()
cursor.execute('SELECT * FROM users WHERE id = ?', (user_id,))
user = cursor.fetchone()
print(dict(user)) # Convert Row to dict
except Exception as e:
print(f"Error: {e}")
Summary
In this lecture, we've covered:
Relational Databases: SQL-based databases like SQLite, MySQL, and PostgreSQL
NoSQL Databases: Document, key-value, wide-column, and graph databases
Python DB-API: Standard interface for database connectivity in Python
ORMs: Object-Relational Mappers for working with databases using Python objects
Best Practices: Security, performance, and error handling
Practice Exercise:
Create a Python application that demonstrates database connectivity with the following features:
Connect to a SQLite database (create if it doesn't exist)
Create tables for a simple blog system (users, posts, comments)
Implement CRUD operations for each table
Add search functionality to find posts by title or content
Add user authentication (simple username/password)
Use proper error handling and transactions
Bonus: Implement the same functionality using an ORM like SQLAlchemy.