SQLAlchemy

Last updated
Original author(s) Michael Bayer [1]
Initial releaseFebruary 14, 2006;18 years ago (2006-02-14) [2]
Stable release
2.0.31 [3]   OOjs UI icon edit-ltr-progressive.svg / 18 June 2024;47 days ago (18 June 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. A database management system used to maintain relational databases is a relational database management system (RDBMS). Many relational database systems are equipped with the option of using SQL for querying and updating the database.

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.

<span class="mw-page-title-main">Object–relational database</span> Database management system

An object–relational database (ORD), or object–relational database management system (ORDBMS), is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language. In addition, just as with pure relational systems, it supports extension of the data model with custom data types and methods.

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 fulfil 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.

Object–relational impedance mismatch is a set of difficulties going between data in relational data stores and data in domain-driven object models. Relational Database Management Systems (RDBMS) is the standard method for storing data in a dedicated database, while object-oriented (OO) programming is the default method for business-centric design in programming languages. The problem lies in neither relational databases nor OO programming, but in the conceptual difficulty mapping between the two logic models. Both logical models are differently implementable using database servers, programming languages, design patterns, or other technologies. Issues range from application to enterprise scale, whenever stored relational data is used in domain-driven object models, and vice versa. Object-oriented data stores can trade this problem for other implementation difficulties.

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.

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.31". 18 June 2024. Retrieved 26 June 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.