Jump to content

SQLAlchemy

From Wikipedia, the free encyclopedia
Original author(s)Michael Bayer[1]
Initial releaseFebruary 14, 2006; 18 years ago (2006-02-14)[2]
Stable release
2.0.31[3] Edit this on Wikidata / 18 June 2024; 31 days ago (18 June 2024)
Repository
Written inPython
Operating systemCross-platform
TypeObject-relational mapping
LicenseMIT License[4]
Websitewww.sqlalchemy.org Edit this on Wikidata
Mike Bayer talking about SQLAlchemy at PyCon 2012

SQLAlchemy is an open-source Python library that provides a SQL toolkit and Object Relational Mapper (ORM) for database interactions. It allows developers to work with databases using Python objects, enabling efficient and flexible database access.

Description

[edit]

SQLAlchemy offers tools for database schema generation, querying, and object-relational mapping. Key features include:

  • A comprehensive SQL expression language for constructing and executing SQL queries.
  • A powerful ORM that allows the mapping of Python classes to database tables.
  • Support for database schema migrations.
  • Compatibility with multiple database backends.
  • Tools for database connection pooling and transaction management.

History

[edit]

SQLAlchemy was first released in February 2006. It has evolved to include a wide range of features for database interaction and has gained popularity among Python developers. Notable versions include:

  • Version 0.1 (2006):[5] Initial release.
  • Version 1.0 (2015):[6] Major enhancements in ORM and SQL expression language.
  • Version 1.4 (2021):[7] Introduction of a new ORM API.

Example

[edit]

The following example represents an n-to-1 relationship between movies and their directors. It is shown how user-defined Python classes create corresponding database tables, how instances with relationships are created from either side of the relationship, and finally how the data can be queried — illustrating automatically generated SQL queries for both lazy and eager loading.

Schema definition

[edit]

Creating two Python classes and corresponding database tables in the DBMS:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, sessionmaker

Base = declarative_base()

class Movie(Base):
    __tablename__ = "movies"

    id = Column(Integer, primary_key=True)
    title = Column(String(255), nullable=False)
    year = Column(Integer)
    directed_by = Column(Integer, ForeignKey("directors.id"))

    director = relation("Director", backref="movies", lazy=False)

    def __init__(self, title=None, year=None):
        self.title = title
        self.year = year

    def __repr__(self):
        return f"Movie({self.title}, {self.year}, {self.director})"

class Director(Base):
    __tablename__ = "directors"

    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False, unique=True)

    def __init__(self, name=None):
        self.name = name

    def __repr__(self):
        return f"Director({self.name})"

engine = create_engine("dbms://user:pwd@host/dbname")
Base.metadata.create_all(engine)

Data insertion

[edit]

One can insert a director-movie relationship via either entity:

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

m1 = Movie("Robocop", 1987)
m1.director = Director("Paul Verhoeven")

d2 = Director("George Lucas")
d2.movies = [Movie("Star Wars", 1977), Movie("THX 1138", 1971)]

try:
    session.add(m1)
    session.add(d2)
    session.commit()
except:
    session.rollback()

Querying

[edit]
alldata = session.query(Movie).all()
for somedata in alldata:
    print(somedata)

SQLAlchemy issues the following query to the DBMS (omitting aliases):

SELECT movies.id, movies.title, movies.year, movies.directed_by, directors.id, directors.name
FROM movies LEFT OUTER JOIN directors ON directors.id = movies.directed_by

The output:

Movie('Robocop', 1987L, Director('Paul Verhoeven'))
Movie('Star Wars', 1977L, Director('George Lucas'))
Movie('THX 1138', 1971L, Director('George Lucas'))

Setting lazy=True (default) instead, SQLAlchemy would first issue a query to get the list of movies and only when needed (lazy) for each director a query to get the name of the corresponding director:

SELECT movies.id, movies.title, movies.year, movies.directed_by
FROM movies

SELECT directors.id, directors.name
FROM directors
WHERE directors.id = %s

See also

[edit]

References

[edit]
  1. ^ Mike Bayer is the creator of SQLAlchemy and Mako Templates for Python.
  2. ^ "Download - SQLAlchemy". SQLAlchemy. Retrieved 21 February 2015.
  3. ^ "Release 2.0.31". 18 June 2024. Retrieved 26 June 2024.
  4. ^ "zzzeek / sqlalchemy / source / LICENSE". BitBucket. Retrieved 21 February 2015.
  5. ^ "0.1 Changelog — SQLAlchemy 2.0 Documentation". docs.sqlalchemy.org. Retrieved 2024-07-04.
  6. ^ "1.0 Changelog — SQLAlchemy 2.0 Documentation". docs.sqlalchemy.org. Retrieved 2024-07-04.
  7. ^ "1.4 Changelog — SQLAlchemy 2.0 Documentation". docs.sqlalchemy.org. Retrieved 2024-07-04.
Notes