Database Interaction - CameronAuler/python-devops GitHub Wiki

Python provides multiple ways to interact with databases, including:

  • SQLite (lightweight, file-based database)
  • MySQL & PostgreSQL (relational databases for production applications)
  • SQLAlchemy ORM (object-relational mapper for working with databases using Python objects)

Table of Contents

SQLite with the sqlite3 Module

SQLite is a lightweight, serverless, self-contained database where data is stored in a single file (.db). Support for SQLite is built into Python via the sqlite3 module.

Connecting to SQLite

This is mainly used for small applications, local storage, and prototyping.

import sqlite3

# Connect to database (creates `database.db` if it doesn’t exist)
conn = sqlite3.connect("database.db")
cursor = conn.cursor()

Creating a Table

Mainly used for storing user information, product catalogs, or logs.

cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
)
""")
conn.commit()  # Save changes

Inserting Data

NOTE: Placeholders (?) prevent SQL injection.

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 25))
conn.commit()

Fetching Data

Mainly used for querying user data, logs, or stored settings.

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

# Output:
(1, 'Alice', 25)

Updating & Deleting Data

Mainly used for Modifying or removing specific records.

cursor.execute("UPDATE users SET age = ? WHERE name = ?", (26, "Alice"))
cursor.execute("DELETE FROM users WHERE name = ?", ("Alice",))
conn.commit()

Closing the Connection

NOTE: Always close the connection to prevent data corruption.

conn.close()

Interfacing with MySQL and PostgreSQL

For production databases, use MySQL or PostgreSQL with the mysql-connector or psycopg2 library.

Install Required Modules

pip install mysql-connector-python psycopg2

Connecting to MySQL

Mainly used for web applications, enterprise software, large-scale databases.

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="test_db"
)

cursor = conn.cursor()

Connecting to PostgreSQL

Mainly used for data-intensive applications, analytics, and transactional systems.

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="test_db",
    user="postgres",
    password="password"
)

cursor = conn.cursor()

Creating a Table (MySQL/PostgreSQL)

PostgreSQL uses SERIAL for auto-incrementing IDs.

cursor.execute("""
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER
)
""")
conn.commit()

Inserting & Fetching Data

NOTE: %s placeholders prevent SQL injection.

cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Bob", 30))
conn.commit()

cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

Using SQLAlchemy (Object-Relational Mapper)

SQLAlchemy ORM allows interacting with databases using Python objects instead of raw SQL.

Install SQLAlchemy

pip install sqlalchemy

Connecting to a Database

NOTE: Works with multiple databases (SQLite, MySQL, PostgreSQL, etc.).

from sqlalchemy import create_engine

engine = create_engine("sqlite:///database.db")  # SQLite
# engine = create_engine("mysql+mysqlconnector://user:password@localhost/test_db")  # MySQL
# engine = create_engine("postgresql://user:password@localhost/test_db")  # PostgreSQL

conn = engine.connect()

Defining a Model (Table)

Mainly used for defining tables as Python classes instead of raw SQL.

from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False)
    age = Column(Integer)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

Creating Tables

NOTE: Automatically generates SQL commands.

Base.metadata.create_all(engine)  # Creates tables based on model definitions

Inserting Data

NOTE: More readable and maintainable than writing SQL manually.

new_user = User(name="Charlie", age=28)
session.add(new_user)
session.commit()

Querying Data

Mainly used for retrieve data using Python-like syntax.

users = session.query(User).all()
for user in users:
    print(user.name, user.age)

Updating & Deleting Data

NOTE: Cleaner syntax for updating and deleting.

user = session.query(User).filter_by(name="Charlie").first()
user.age = 29
session.commit()  # Save changes

session.delete(user)
session.commit()  # Delete record