SQLAlchemy is a Python SQL toolkit and Object Relational Mapper that unlocks the full power and flexibility of SQL. It provides application developers with a robust tool developed for efficient and high-performing database access. Its main features are: implementation of the "unit of work" pattern and fluency in work with complex SQL queries and object mappings. SQLAlchemy is the de facto standard tool for working with relational databases in Python. It is compatible with Python 2.5 - 3.x versions and also supports Jython and Pypy.
SQLAlchemy consists of the two main parts: the Core and the ORM. The Core is a fully featured SQL abstraction toolkit, that has a wide variety of Database API (DBAPI) implementations, behaviors and SQL Expression Language. SQLAlchemy is focused on supporting as many databases as possible, including SQLite, Postgresql, MySQL, Oracle, MS-SQL, Firebird, Sybase, etc. Others are published as external projects.
The Object Relational Mapper (ORM) is an optional component which builds upon the Core. It contains the data mapper pattern so that classes can be mapped to the database in open ended, multiple ways. This method ensures the clean decoupled development of the object model and database schema from the beginning. Moreover, SQLAlchemy provides advanced ORM optimizations: in-memory collections, fewer and more efficient queries, eager-loading and caching of related objects and collections via joins or secondary subselects, etc.
The Unit Of Work system is the key component of the ORM that manages pending insert/update/delete operations. It provides a topological "dependency” sorting of all modified items in the queue that reduces chances of deadlocks and ensures the maximum efficiency and transaction safety.
Main advantages of SQLAlchemy toolkit
- No ORM Required
- Varied databases support
- Unit Of Work
- Mature, High Performing Architecture
- DBA Approved Non-Opinionated
- Function-based query construction
- Separate mapping and class design
- Composite (multiple-column) primary keys
- Self-referential Object Mappings
- Inheritance Mapping
- Raw SQL statement mapping
- Pre- and post-processing of data
- Modular and Extensible
SQLAlchemy was first introduced in 2005 and was basically an end-to-end Python toolkit for working with relational databases, using the Python DBAPI for database interactivity. Now this system is high performing and accurate, well covered in tests, and used by a large number of organizations in a variety of fields. To get more information on it visit SQLAlchemy website.