Working with Databases in Python

Databases are essential for storing and managing structured data in applications. In this lesson, we will explore how to interact with databases in Python, focusing on popular options like SQLite, MySQL, and PostgreSQL.

Why Use Databases?

Using a database allows you to:

Store large amounts of data efficiently.
Retrieve and update information quickly.
Maintain data integrity and security.

This makes databases indispensable for web applications, analytics, and more.

Popular Python Database Libraries

Python offers several libraries to connect to and manage databases:

sqlite3: Built-in library for SQLite databases.
SQLAlchemy: A powerful ORM (Object-Relational Mapping) tool.
mysql-connector-python: For connecting to MySQL databases.
psycopg2: For working with PostgreSQL.

Connecting to a Database

Let's start by connecting to an SQLite database using Python's built-in `sqlite3` module:

import sqlite3

# Connect to a database (or create it if it doesn't exist)
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,
                    age INTEGER)''')

# Commit changes and close the connection
conn.commit()
conn.close()

This script creates a new SQLite database file named `example.db` and defines a `users` table with three columns: `id`, `name`, and `age`.

Executing Queries

Once connected, you can execute SQL queries to insert, retrieve, update, or delete data. Here's an example of inserting and fetching records:

# Insert data into the table
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 25))

# Fetch all records
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

This snippet inserts a new user and retrieves all users from the `users` table.