 | This article contains content that is written like an advertisement .(December 2017) |
 Abbreviated SQLAlchemy Logo | |
| Original author(s) | Michael Bayer [1] [2] |
|---|---|
| Initial release | February 14, 2006 [3] |
| Stable release | |
| Repository | |
| Written in | Python |
| Operating system | Cross-platform |
| Type | Object-relational mapping |
| License | MIT License [5] |
| Website | www |
SQLAlchemy is an open-source SQL toolkit and object-relational mapper (ORM) for the Python programming language released under the MIT License. [5]
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) rather than the active record pattern used by a number of other object-relational mappers. [6]
SQLAlchemy was first released in February 2006. [3] SQLAlchemy beta 2.0 was released in October 2022, and the full 2.0 release in early 2023. [7] [8] The current release, 2.0.28, was released in March, 2024. [9]
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.
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)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()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_byThe 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
In computing, a database is an organized collection of data or a type of data store based on the use of a database management system (DBMS), the software that interacts with end users, applications, and the database itself to capture and analyze the data. The DBMS additionally encompasses the core facilities provided to administer the database. The sum total of the database, the DBMS and the associated applications can be referred to as a database system. Often the term "database" is also used loosely to refer to any of the DBMS, the database system or an application associated with the database.
An object database or object-oriented database is a database management system in which information is represented in the form of objects as used in object-oriented programming. Object databases are different from relational databases which are table-oriented. A third type, object–relational databases, is a hybrid of both approaches. Object databases have been considered since the early 1980s.
PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. PostgreSQL features transactions with atomicity, consistency, isolation, durability (ACID) properties, automatically updatable views, materialized views, triggers, foreign keys, and stored procedures. It is supported on all major operating systems, including Linux, FreeBSD, OpenBSD, macOS, and Windows, and handles a range of workloads from single machines to data warehouses or web services with many concurrent users.
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.
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.
Db2 is a family of data management products, including database servers, developed by IBM. It initially supported the relational model, but was extended to support object–relational features and non-relational structures like JSON and XML. The product name was originally styled as DB2 until 2017, when it changed to its present form.
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.
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.
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.
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.
The uniform access principle of computer programming was put forth by Bertrand Meyer. It states "All services offered by a module should be available through a uniform notation, which does not betray whether they are implemented through storage or through computation." This principle applies generally to the syntax of object-oriented programming languages. In simpler form, it states that there should be no syntactical difference between working with an attribute, pre-computed property, or method/query of an object.
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.
iBATIS is a persistence framework which automates the mapping between SQL databases and objects in Java, .NET, and Ruby on Rails. In Java, the objects are POJOs. The mappings are decoupled from the application logic by packaging the SQL statements in XML configuration files. The result is a significant reduction in the amount of code that a developer needs to access a relational database using lower level APIs like JDBC and ODBC.
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.
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.
The Jakarta Persistence Query Language is a platform-independent object-oriented query language defined as part of the Jakarta Persistence specification.
RedBeanPHP is an independent, free, BSD licensed, open-source object–relational mapping (ORM) software written by Gabor de Mooij. It is a stand-alone library, not part of any framework. RedBeanPHP is an on-the-fly object–relational mapper, this means there is no upfront configuration. The system relies on conventions entirely and adapts the database schema to fit the needs of the program. This way, it strikes a balance between NoSQL and traditional RDBMS solutions.
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.
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).
