SQLAlchemy is an
open-source
Open source is source code that is made freely available for possible modification and redistribution. Products include permission to use the source code, design documents, or content of the product. The open-source model is a decentralized so ...
SQL toolkit and
object-relational mapper (ORM) for the
Python programming language
Python is a high-level, general-purpose programming language. Its design philosophy emphasizes code readability with the use of significant indentation.
Python is dynamically-typed and garbage-collected. It supports multiple programming p ...
released under the
MIT License
The MIT License is a permissive free software license originating at the Massachusetts Institute of Technology (MIT) in the late 1980s. As a permissive license, it puts only very limited restriction on reuse and has, therefore, high license comp ...
.
Description
SQLAlchemy's philosophy is that relational databases behave less like object collections as the scale gets larger and performance starts being a concern, while object collections behave less like tables and rows as more abstraction is designed into them. For this reason it has adopted the
data mapper pattern (similar to
Hibernate for
Java
Java (; id, Jawa, ; jv, ꦗꦮ; su, ) is one of the Greater Sunda Islands in Indonesia. It is bordered by the Indian Ocean to the south and the Java Sea to the north. With a population of 151.6 million people, Java is the world's mo ...
) rather than the
active record pattern In software engineering, the active record pattern is an architectural pattern. It is found in software that stores in-memory object data in relational databases. It was named by Martin Fowler in his 2003 book ''Patterns of Enterprise Application ...
used by a number of other object-relational mappers.
[i]
The architecture of open source applications
/ref>
History
SQLAlchemy was first released in February 2006
Example
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
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 "Movie(%r, %r, %r)" % (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 "Director(%r)" % (self.name)
engine = create_engine("dbms://user:pwd@host/dbname")
Base.metadata.create_all(engine)
Data insertion
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 = ovie("Star Wars", 1977), Movie("THX 1138", 1971)
try:
session.add(m1)
session.add(d2)
session.commit()
except:
session.rollback()
Querying
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
* SQLObject
* Storm
A storm is any disturbed state of the natural environment or the atmosphere of an astronomical body. It may be marked by significant disruptions to normal conditions such as strong wind, tornadoes, hail, thunder and lightning (a thunderstorm), ...
* Pylons
Pylon may refer to:
Structures and boundaries
* Pylon (architecture), the gateway to the inner part of an Ancient Egyptian temple or Christian cathedral
* Pylon, a support tower structure for suspension bridges or highways
* Pylon, an orange mar ...
* TurboGears
* Cubes (OLAP server)
References
;Notes
*
* Rick Copeland, Essential SQLAlchemy, O'Reilly
O'Reilly ( ga, Ó Raghallaigh) is a group of families, ultimately all of Irish Gaelic origin, who were historically the kings of East Bréifne in what is today County Cavan. The clan were part of the Connachta's Uí Briúin Bréifne kindred a ...
, 2008,
External links
* {{Official website
SQLAlchemy Tutorial
2006 software
Object-relational mapping
Python (programming language) libraries
Software using the MIT license