Chapter 9: Database Connectivity in Python

Database connectivity is a fundamental aspect of modern software development. It involves establishing connections with databases, querying and manipulating data, and retrieving results for processing. This chapter explores the basics of database connectivity, including database management systems (DBMS), SQL queries, connecting to databases using Python, executing queries, and working with result sets. Additionally, it covers topics such as transaction management, error handling, and using Object-Relational Mapping (ORM) frameworks.

Introduction to Databases

A database is an organized collection of structured data stored in a computer system. It provides a centralized and efficient way to store, manage, and retrieve large volumes of data. Database management systems (DBMS) are software applications that enable users to interact with databases. Common types of DBMS include relational databases (e.g., MySQL, PostgreSQL), NoSQL databases (e.g., MongoDB, Cassandra), and in-memory databases (e.g., Redis).

Structured Query Language (SQL)

Structured Query Language (SQL) is a standard language for interacting with relational databases. It allows users to define, manipulate, and retrieve data stored in the database. SQL comprises various commands, such as SELECT, INSERT, UPDATE, DELETE, which enable operations like data retrieval, insertion, modification, and deletion.

Connecting to Databases

In Python, you can connect to databases using specific database drivers or modules that provide the necessary functionality. Each DBMS has its own driver/module for connecting to its database. For example, the mysql.connector module is commonly used to connect to MySQL databases, while the psycopg2 module is used for PostgreSQL databases.

Here's an example of connecting to a MySQL database using the mysql.connector module:

import mysql.connector

# Establish a connection
connection = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="mydatabase"
)

# Perform database operations
# ...

# Close the connection
connection.close()

Executing SQL Queries

Once connected to a database, you can execute SQL queries to perform various operations. The cursor() method is used to create a cursor object, which allows you to execute queries and fetch results. Here's an example:

# Create a cursor
cursor = connection.cursor()

# Execute a query
query = "SELECT * FROM customers"
cursor.execute(query)

# Fetch and process the results
results = cursor.fetchall()
for row in results:
    print(row)

# Close the cursor
cursor.close()

In this example, a SELECT query is executed to fetch all rows from the "customers" table. The fetchall() method retrieves all rows as a list of tuples, which can then be processed as desired.

Transaction Management

Database transactions are sequences of operations that are treated as a single unit of work. Transactions ensure data consistency and integrity by allowing multiple operations to be executed atomically. Transactions follow the ACID (Atomicity, Consistency, Isolation, Durability) properties to maintain data integrity even in the presence of failures.

Python provides a way to manage transactions using the commit() and rollback() methods. Here's an example:

# Start a transaction
connection.start_transaction()

try:
    # Perform database operations
    # ...

    # Commit the transaction
    connection.commit()
except:
    # Rollback the transaction on error
    connection.rollback()

In this example, the start_transaction() method initiates a transaction, and the commit() method is called to commit the changes made within the transaction. If an error occurs, the rollback() method is used to revert the changes made during the transaction.

Error Handling

Error handling is crucial when working with databases to ensure that errors are appropriately handled and that data integrity is maintained. Python provides mechanisms to catch and handle database-related errors using try-except blocks. By catching exceptions and implementing appropriate error handling logic, you can ensure that your program responds gracefully to errors and takes the necessary actions.

Object-Relational Mapping (ORM)

Object-Relational Mapping (ORM) is a technique that allows developers to interact with databases using object-oriented paradigms. ORM frameworks, such as SQLAlchemy and Django's ORM, provide a higher-level abstraction over traditional SQL queries, allowing developers to work with databases using Python classes and objects.

ORM frameworks map database tables to Python classes, rows to objects, and columns to object attributes. This abstraction simplifies database operations, enhances code readability, and reduces the need for writing complex SQL queries manually. Here's an example using SQLAlchemy:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# Create an engine and session
engine = create_engine("mysql://username:password@localhost/mydatabase")
Session = sessionmaker(bind=engine)
session = Session()

# Define a model class
Base = declarative_base()

class Customer(Base):
    __tablename__ = "customers"
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    email = Column(String(50))

# Query the database
customers = session.query(Customer).all()
for customer in customers:
    print(customer.name)

# Close the session
session.close()

In this example, the SQLAlchemy library is used to define a model class Customer that maps to the "customers" table. The session.query() method is used to retrieve all customers, and the results are processed as objects of the Customer class.

Conclusion

This chapter explored the fundamentals of database connectivity, including DBMS, SQL queries, connecting to databases using Python, executing queries, working with result sets, transaction management, error handling, and Object-Relational Mapping (ORM) frameworks. Understanding database connectivity is crucial for building robust applications that interact with databases efficiently and securely. In the next chapter, we will delve into the world of web development and explore frameworks and tools for building web applications using Python.