![Mike Bayer talking about SQLAlchemy at PyCon 2012 a](https://upload.wikimedia.org/wikipedia/commons/4/4d/Mike_Bayer_talking_about_SQLAlchemy_at_PyCon_2012_a.jpg)
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 sof ...
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 In software engineering, the data mapper pattern is an architectural pattern. It was named by Martin Fowler in his 2003 book ''Patterns of Enterprise Application Architecture''. The interface of an object conforming to this pattern would include f ...
(similar to
Hibernate
Hibernation is a state of minimal activity and metabolic depression undergone by some animal species. Hibernation is a seasonal heterothermy characterized by low body-temperature, slow breathing and heart-rate, and low metabolic rate. It most ...
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 List ...
) 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
Python may refer to:
Snakes
* Pythonidae, a family of nonvenomous snakes found in Africa, Asia, and Australia
** ''Python'' (genus), a genus of Pythonidae found in Africa and Asia
* Python (mythology), a mythical serpent
Computing
* Python (pro ...
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
SQLObject is a Python object-relational mapper between a SQL database and Python objects. It is experiencing community popularity, and forms a part of many applications (e.g., TurboGears). It is very similar to Ruby on Rails' ActiveRecord in oper ...
* Storm
A storm is any disturbed state of the natural environment or the atmosphere
An atmosphere () is a layer of gas or layers of gases that envelop a planet, and is held in place by the gravity of the planetary body. A planet retains an atmos ...
* 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
TurboGears is a Python web application framework consisting of several WSGI components such as WebOb, SQLAlchemyKajikitemplate language and Repoze.
TurboGears is designed around the model–view–controller (MVC) architecture, much like Struts ...
* Cubes (OLAP server)
Cubes is a light-weight open source multidimensional modelling and OLAP toolkit for development reporting applications and browsing of aggregated data written in Python programming language released under the MIT License.
Cubes provides to an ana ...
References
;Notes
*
* Rick Copeland, Essential SQLAlchemy, O'Reilly
O'Reilly ( ga, Ó Raghallaigh) is a group of families, ultimately all of Irish Gaels, 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 kin ...
, 2008,
External links
* {{Official website
SQLAlchemy Tutorial
2006 software
Object-relational mapping
Python (programming language) libraries
Software using the MIT license