the_information_nexus/tech_docs/python/SQLAlchemy.md

SQLAlchemy is an indispensable Python library for database operations, providing a full-featured SQL toolkit and Object-Relational Mapping (ORM) capabilities. It allows for efficient and high-performing database access, abstracting away the complexities of raw SQL queries. SQLAlchemy supports a wide range of database backends, including MySQL, PostgreSQL, SQLite, and Oracle, making it a versatile choice for any project requiring database interaction. Here’s a concise reference guide for common use cases with SQLAlchemy:

SQLAlchemy Reference Guide

Installation

pip install sqlalchemy

Basic Usage

Connecting to a Database

from sqlalchemy import create_engine

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

Defining Models

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

Creating Schema

# Create all tables stored in this metadata
Base.metadata.create_all(engine)

Starting a Session

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

Inserting Data

new_user = User(name='john', fullname='John Doe', nickname='johnny')
session.add(new_user)
session.commit()

Querying Data

# Query for one instance
user = session.query(User).filter_by(name='john').first()
print(user.fullname)

# Query for multiple instances
for user in session.query(User).order_by(User.id):
    print(user.name)

Updating Data

user = session.query(User).filter_by(name='john').first()
user.nickname = 'john the ripper'
session.commit()

Deleting Data

user = session.query(User).filter_by(name='john').first()
session.delete(user)
session.commit()

Advanced Features

Relationships

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))
    
    user = relationship("User", back_populates="addresses")

User.addresses = relationship("Address", order_by=Address.id, back_populates="user")

Transactions

# SQLAlchemy automatically wraps SQL operations in a transaction. 
# Commit to apply changes or rollback to undo them.
session.commit()
# or
session.rollback()

Working with Engines and Connection Pooling

SQLAlchemy engine is a factory for database connections, supporting connection pooling and dialect options for different database backends.

SQLAlchemy provides a comprehensive set of tools for working with databases in Python, making it easier to implement models, queries, and transactions with less code and greater flexibility. This guide introduces the basic operations, but SQLAlchemy's capabilities extend far beyond these, offering powerful patterns for database interaction and application development.

SQLAlchemy's ORM approach encourages the use of high-level abstractions for database operations, significantly reducing the amount of boilerplate code needed for database interactions and making your code more maintainable.