SQLAlchemy

<h2 id="description">Description</h2>
<p>SQLAlchemy offers tools for <a href="/facts/Database_schema/DBq8fooY">database schema</a> generation, querying, and object-relational mapping. Key features include:
</p>
<ul><li>A comprehensive embedded <a href="/facts/Domain-specific_language/n18H1foD">domain-specific language</a> for SQL in Python called "SQLAlchemy Core" that provides means to construct and execute SQL queries.</li>
<li>A powerful ORM that allows the mapping of Python classes to database tables.</li>
<li>Support for database schema migrations.</li>
<li>Compatibility with multiple database backends.</li>
<li>Tools for <a href="/facts/Database_connection/k4Co1rVw">database connection</a> pooling and transaction management.</li></ul>
<h2 id="history">History</h2>
<p>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:
</p>
<ul><li>Version 0.1 (2006):<a class="footnote-ref" id="fnref:1" href="#fn:1"><sup>1</sup></a> Initial release.</li>
<li>Version 1.0 (2015):<a class="footnote-ref" id="fnref:2" href="#fn:2"><sup>2</sup></a> Major enhancements in ORM and SQL expression language.</li>
<li>Version 1.4 (2021):<a class="footnote-ref" id="fnref:3" href="#fn:3"><sup>3</sup></a> Introduction of a new ORM <a href="/facts/API/GMlN4vUr">API</a>.</li></ul>
<h2 id="example">Example</h2>
<p>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 <a href="/facts/Lazy_loading/BpH8qyWJ">lazy</a> and eager loading.
</p>
<h3>Schema definition</h3>
<p>Creating two Python classes and corresponding database tables in the DBMS:
</p>
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)

<h3>Data insertion</h3>
<p>One can insert a director-movie relationship via either entity:
</p>
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()

<h3>Querying</h3>
alldata = session.query(Movie).all()
for somedata in alldata:
    print(somedata)

<p>SQLAlchemy issues the following query to the DBMS (omitting aliases):
</p>
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

<p>The output:
</p>
Movie('Robocop', 1987L, Director('Paul Verhoeven'))
Movie('Star Wars', 1977L, Director('George Lucas'))
Movie('THX 1138', 1971L, Director('George Lucas'))

<p>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:
</p>
SELECT movies.id, movies.title, movies.year, movies.directed_by
FROM movies

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

<h2 id="see-also">See also</h2>
<ul>
<li>Free and open-source software portal</li></ul>
<ul><li><a href="/facts/SQLObject/W71jpsCV">SQLObject</a></li>
<li><a href="/facts/Storm_(software)/Durcusj3">Storm</a></li>
<li><a href="/facts/Pylons_project/cWdJxCwa">Pylons</a></li>
<li><a href="/facts/TurboGears/6qyQBskU">TurboGears</a></li>
<li><a href="/facts/Cubes_(OLAP_server)/8nQBfpPw">Cubes (OLAP server)</a></li></ul>

Notes
<ul><li>Gift, Noah (12 Aug 2008). <a href="https://www.ibm.com/developerworks/aix/library/au-sqlalchemy/">"Using SQLAlchemy"</a>. <i>Developerworks</i>. IBM. Retrieved 8 Feb 2011.</li>
<li>Rick Copeland, Essential SQLAlchemy, <a href="/facts/O%2527Reilly_Media/Fr3Kv7sm">O'Reilly</a>, 2008, <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 0-596-51614-2</li></ul>

<h2 id="references">References</h2>

<ol>
<li id="fn:1"><p>"0.1 Changelog — SQLAlchemy 2.0 Documentation". docs.sqlalchemy.org. Retrieved 2024-07-04. <a href="https://docs.sqlalchemy.org/en/20/changelog/changelog_01.html" target="_blank">https://docs.sqlalchemy.org/en/20/changelog/changelog_01.html</a> <a href="#fnref:1" class="footnote-back-ref">↩</a></p></li>
<li id="fn:2"><p>"1.0 Changelog — SQLAlchemy 2.0 Documentation". docs.sqlalchemy.org. Retrieved 2024-07-04. <a href="https://docs.sqlalchemy.org/en/20/changelog/changelog_10.html#change-1.0.0" target="_blank">https://docs.sqlalchemy.org/en/20/changelog/changelog_10.html#change-1.0.0</a> <a href="#fnref:2" class="footnote-back-ref">↩</a></p></li>
<li id="fn:3"><p>"1.4 Changelog — SQLAlchemy 2.0 Documentation". docs.sqlalchemy.org. Retrieved 2024-07-04. <a href="https://docs.sqlalchemy.org/en/20/changelog/changelog_14.html#change-1.4.0" target="_blank">https://docs.sqlalchemy.org/en/20/changelog/changelog_14.html#change-1.4.0</a> <a href="#fnref:3" class="footnote-back-ref">↩</a></p></li>
</ol>

SQLAlchemy open-in-new

SQLAlchemy