Databases

Working with Databases in Python

Introduction to Databases in Python

What is a Database?

A database is an organized collection of structured information or data, typically stored electronically in a computer system. It allows for efficient retrieval, management, and updating of data. In the context of Python programming, databases are essential for storing and managing large amounts of information in applications.

Types of Databases

There are several types of databases commonly used with Python:

Relational Databases: These use structured query language (SQL) and include popular options like:
- MySQL
- PostgreSQL
- SQLite
- Oracle
NoSQL Databases: These provide a mechanism for storage and retrieval of data that is modeled differently from relational databases. Examples include:
- MongoDB
- Cassandra
- CouchDB

Python Database APIs

Python offers several ways to interact with databases:

DB-API: This is a standard Python interface for database access. It defines a set of methods that a database module should implement.
ORM (Object-Relational Mapping): ORMs like SQLAlchemy and Django ORM allow you to interact with your database using Python objects instead of writing SQL queries directly.

Working with SQLite in Python

SQLite is a lightweight, serverless database engine that comes built-in with Python. It’s an excellent choice for small to medium-sized applications.

Connecting to an SQLite Database

import sqlite3

# Connect to a database (or create it if it doesn't exist)
conn = sqlite3.connect('example.db')

# Create a cursor object
cursor = conn.cursor()

Creating a Table

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

Inserting Data

# Insert a single row of data
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("John Doe", "[email protected]"))

# Insert multiple rows
users = [("Jane Smith", "[email protected]"), ("Bob Johnson", "[email protected]")]
cursor.executemany("INSERT INTO users (name, email) VALUES (?, ?)", users)

# Commit the changes
conn.commit()

Querying Data

# Fetch all rows
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

for row in rows:
    print(row)

# Fetch a single row
cursor.execute("SELECT * FROM users WHERE name=?", ("John Doe",))
user = cursor.fetchone()
print(user)

Updating and Deleting Data

# Update data
cursor.execute("UPDATE users SET email=? WHERE name=?", ("[email protected]", "John Doe"))

# Delete data
cursor.execute("DELETE FROM users WHERE name=?", ("Jane Smith",))

# Commit the changes
conn.commit()

Closing the Connection

# Close the cursor and connection when done
cursor.close()
conn.close()

Working with PostgreSQL in Python

For larger applications, you might want to use a more robust database like PostgreSQL. You’ll need to install the psycopg2 library to work with PostgreSQL in Python.

pip install psycopg2

Connecting to PostgreSQL

import psycopg2

# Connect to an existing database
conn = psycopg2.connect(
    host="localhost",
    database="mydatabase",
    user="myusername",
    password="mypassword"
)

# Create a cursor object
cursor = conn.cursor()

The rest of the operations (creating tables, inserting, querying, updating, and deleting data) are similar to SQLite, but you’ll use PostgreSQL-specific SQL syntax.

Using SQLAlchemy ORM

SQLAlchemy is a popular ORM that provides a high-level abstraction for database operations.

pip install sqlalchemy

Setting up SQLAlchemy

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

# Create an engine
engine = create_engine('sqlite:///example.db', echo=True)

# Create a base class for declarative models
Base = declarative_base()

# Define a model
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

# Create the table
Base.metadata.create_all(engine)

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

Working with Data using SQLAlchemy

# Add a new user
new_user = User(name="Alice Brown", email="[email protected]")
session.add(new_user)
session.commit()

# Query users
users = session.query(User).all()
for user in users:
    print(f"ID: {user.id}, Name: {user.name}, Email: {user.email}")

# Update a user
user_to_update = session.query(User).filter_by(name="Alice Brown").first()
user_to_update.email = "[email protected]"
session.commit()

# Delete a user
user_to_delete = session.query(User).filter_by(name="Bob Johnson").first()
session.delete(user_to_delete)
session.commit()

Conclusion

Working with databases in Python provides a powerful way to store and manage data in your applications. Whether you’re using a simple SQLite database for a small project or a more complex PostgreSQL setup for a large application, Python offers robust tools and libraries to interact with databases efficiently.

Remember to always close your database connections when you’re done, and use best practices like parameterized queries to prevent SQL injection attacks. As your applications grow, consider using an ORM like SQLAlchemy to abstract away the database-specific code and focus on your application logic.

JSON and XML SQLite