SQLAlchemy

Last updated
Original author(s) Michael Bayer [1]
Initial releaseFebruary 14, 2006;18 years ago (2006-02-14) [2]
Stable release
2.0.36 [3]   OOjs UI icon edit-ltr-progressive.svg / 15 October 2024;2 months ago (15 October 2024)
Repository
Written in Python
Operating system Cross-platform
Type Object-relational mapping
License MIT License [4]
Website www.sqlalchemy.org   OOjs UI icon edit-ltr-progressive.svg
Mike Bayer talking about SQLAlchemy at PyCon 2012 Mike Bayer talking about SQLAlchemy at PyCon 2012 a.jpg
Mike Bayer talking about SQLAlchemy at PyCon 2012

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

Contents

Description

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

History

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:

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:

fromsqlalchemyimport*fromsqlalchemy.ext.declarativeimportdeclarative_basefromsqlalchemy.ormimportrelation,sessionmakerBase=declarative_base()classMovie(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=titleself.year=yeardef__repr__(self):returnf"Movie({self.title}, {self.year}, {self.director})"classDirector(Base):__tablename__="directors"id=Column(Integer,primary_key=True)name=Column(String(50),nullable=False,unique=True)def__init__(self,name=None):self.name=namedef__repr__(self):returnf"Director({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=[Movie("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()forsomedatainalldata:print(somedata)

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

SELECTmovies.id,movies.title,movies.year,movies.directed_by,directors.id,directors.nameFROMmoviesLEFTOUTERJOINdirectorsONdirectors.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:

SELECTmovies.id,movies.title,movies.year,movies.directed_byFROMmoviesSELECTdirectors.id,directors.nameFROMdirectorsWHEREdirectors.id=%s

See also

Related Research Articles

A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.

Structured Query Language (SQL) is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.

First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if and only if no attribute domain has relations as elements. Or more informally, that no table column can have tables as values. Database normalization is the process of representing a database in terms of relations in standard normal forms, where first normal is a minimal requirement. SQL-92 does not support creating or using table-valued columns, which means that using only the "traditional relational database features" most relational databases will be in first normal form by necessity. Database systems which do not require first normal form are often called NoSQL systems. Newer SQL standards like SQL:1999 have started to allow so called non-atomic types, which include composite types. Even newer versions like SQL:2016 allow JSON.

The SQL SELECT statement returns a result set of rows, from one or more tables.

Hibernate ORM is an object–relational mapping tool for the Java programming language. It provides a framework for mapping an object-oriented domain model to a relational database. Hibernate handles object–relational impedance mismatch problems by replacing direct, persistent database accesses with high-level object handling functions.

<span class="mw-page-title-main">MonetDB</span> Open source column-oriented relational database management system

MonetDB is an open-source column-oriented relational database management system (RDBMS) originally developed at the Centrum Wiskunde & Informatica (CWI) in the Netherlands. It is designed to provide high performance on complex queries against large databases, such as combining tables with hundreds of columns and millions of rows. MonetDB has been applied in high-performance applications for online analytical processing, data mining, geographic information system (GIS), Resource Description Framework (RDF), text retrieval and sequence alignment processing.

<span class="mw-page-title-main">Null (SQL)</span> Marker used in SQL databases to indicate a value does not exist

In SQL, null or NULL is a special marker used to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL null serves to fulfill the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent null in database theory. In SQL, NULL is a reserved word used to identify this marker.

CherryPy is an object-oriented web application framework using the Python programming language. It is designed for rapid development of web applications by wrapping the HTTP protocol but stays at a low level and does not offer much more than what is defined in RFC 7231.

<span class="mw-page-title-main">NHibernate</span> Object–relational mapping solution

NHibernate is a port of the Hibernate object–relational mapping (ORM) tool for the Microsoft .NET platform. It provides a framework for mapping an object-oriented domain model to a traditional relational database. Its purpose is to relieve the developer from a significant portion of relational data persistence-related programming tasks. NHibernate is free and open-source software that is distributed under the GNU Lesser General Public License.

<span class="mw-page-title-main">RDFLib</span> Python library to serialize, parse and process RDF data

RDFLib is a Python library for working with RDF, a simple yet powerful language for representing information. This library contains parsers/serializers for almost all of the known RDF serializations, such as RDF/XML, Turtle, N-Triples, & JSON-LD, many of which are now supported in their updated form. The library also contains both in-memory and persistent Graph back-ends for storing RDF information and numerous convenience functions for declaring graph namespaces, lodging SPARQL queries and so on. It is in continuous development with the most recent stable release, rdflib 6.1.1 having been released on 20 December 2021. It was originally created by Daniel Krech with the first release in November, 2002.

SAP IQ is a column-based, petabyte scale, relational database software system used for business intelligence, data warehousing, and data marts. Produced by Sybase Inc., now an SAP company, its primary function is to analyze large amounts of data in a low-cost, highly available environment. SAP IQ is often credited with pioneering the commercialization of column-store technology.

The Doctrine Project is a set of PHP libraries primarily focused on providing persistence services and related functionality. Its most commonly known projects are the object–relational mapper (ORM) and the database abstraction layer it is built on top of.

Web2py is an open-source web application framework written in the Python programming language. Web2py allows web developers to program dynamic web content using Python. Web2py is designed to help reduce tedious web development tasks, such as developing web forms from scratch, although a web developer may build a form from scratch if required.

A database shard, or simply a shard, is a horizontal partition of data in a database or search engine. Each shard may be held on a separate database server instance, to spread load.

The MySQLi Extension is a relational database driver used in the PHP scripting language to provide an interface with MySQL protocol compatible databases.

Apache Empire-db is a Java library that provides a high level object-oriented API for accessing relational database management systems (RDBMS) through JDBC. Apache Empire-db is open source and provided under the Apache License 2.0 from the Apache Software Foundation.

In database management systems (DBMS), a prepared statement, parameterized statement, or parameterized query is a feature where the database pre-compiles SQL code and stores the results, separating it from data. Benefits of prepared statements are:

Persist is a Java-based ORM/DAO tool. It provides only the minimal amount of functionalities necessary to map objects or maps from database queries and to statement parameters.

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.

Hi/Lo is an algorithm and a key generation strategy used for generating unique keys for use in a database as a primary key. It uses a sequence-based hi-lo pattern to generate values. Hi/Lo is used in scenarios where an application needs its entities to have an identity prior to persistence. It is a value generation strategy. An alternative to Hi/Lo would be for the application to generate keys as universally unique identifiers (UUID).

References

  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.36". 15 October 2024. Retrieved 27 October 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
This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.