SQLAlchemy

Language: Python

Data

SQLAlchemy was created by Mike Bayer in 2005 to provide a powerful and flexible way to interact with relational databases in Python. It allows developers to use both raw SQL and ORM abstractions, making it suitable for both small projects and large-scale enterprise applications.

SQLAlchemy is a Python SQL toolkit and Object-Relational Mapping (ORM) library that gives developers full power and flexibility of SQL along with a high-level, Pythonic interface to relational databases.

Installation

pip: pip install sqlalchemy

conda: conda install -c anaconda sqlalchemy

Usage

SQLAlchemy allows you to define database schemas as Python classes (ORM), execute raw SQL queries, and manage connections. It supports multiple relational databases such as SQLite, PostgreSQL, MySQL, and Oracle.

Creating an Engine and connecting to a database

from sqlalchemy import create_engine
engine = create_engine('sqlite:///example.db')

Creates an engine that connects to a SQLite database file named `example.db`.

Defining a table using ORM

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

Base = declarative_base()

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

Defines a `User` table with columns `id`, `name`, and `age` using the ORM approach.

Creating tables in the database

Base.metadata.create_all(engine)

Creates all tables defined by ORM classes in the connected database.

Inserting data

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
new_user = User(name='Alice', age=25)
session.add(new_user)
session.commit()

Creates a session, adds a new user to the table, and commits the transaction.

Querying data

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

Queries the `User` table for rows where name is 'Alice' and prints the results.

Updating data

user = session.query(User).filter_by(name='Alice').first()
user.age = 26
session.commit()

Updates the age of the first user named 'Alice' and commits the change.

Deleting data

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

Deletes the user named 'Alice' from the database.

Error Handling

IntegrityError: Occurs when database constraints are violated. Ensure unique or foreign key constraints are respected.

OperationalError: Check database connectivity, credentials, and correct SQL syntax.

ProgrammingError: Typically arises from invalid queries or table definitions. Verify table and column names.

Best Practices

Use `sessionmaker` to create sessions instead of raw connections.

Close sessions after use to prevent resource leaks.

Use ORM for complex applications and raw SQL for optimized queries when needed.

Define relationships using `relationship` and `ForeignKey` for normalized schemas.

Use transactions to ensure data integrity.

Official Docs Github