SQLAlchemy
![]() Abbreviated SQLAlchemy Logo | |
Original author(s) | Michael Bayer[1] |
---|---|
Initial release | February 14, 2006[2] |
Stable release | 2.0.31[3] ![]() |
Repository | |
Written in | Python |
Operating system | Cross-platform |
Type | Object-relational mapping |
License | MIT License[4] |
Website | www![]() |
![](http://upload.wikimedia.org/wikipedia/commons/thumb/4/4d/Mike_Bayer_talking_about_SQLAlchemy_at_PyCon_2012_a.jpg/220px-Mike_Bayer_talking_about_SQLAlchemy_at_PyCon_2012_a.jpg)
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]
- ^ Mike Bayer is the creator of SQLAlchemy and Mako Templates for Python.
- ^ "Download - SQLAlchemy". SQLAlchemy. Retrieved 21 February 2015.
- ^ "Release 2.0.31". 18 June 2024. Retrieved 26 June 2024.
- ^ "zzzeek / sqlalchemy / source / LICENSE". BitBucket. Retrieved 21 February 2015.
- ^ "0.1 Changelog — SQLAlchemy 2.0 Documentation". docs.sqlalchemy.org. Retrieved 2024-07-04.
- ^ "1.0 Changelog — SQLAlchemy 2.0 Documentation". docs.sqlalchemy.org. Retrieved 2024-07-04.
- ^ "1.4 Changelog — SQLAlchemy 2.0 Documentation". docs.sqlalchemy.org. Retrieved 2024-07-04.
- Notes
- Gift, Noah (12 Aug 2008). "Using SQLAlchemy". Developerworks. IBM. Retrieved 8 Feb 2011.
- Rick Copeland, Essential SQLAlchemy, O'Reilly, 2008, ISBN 0-596-51614-2