SQLAlchemy Python SQL Toolkit and Object Relational Mapper
Download

SQLAlchemy Ranking & Summary

SQLAlchemy Tags

database database interface toolkit sql toolkit query construction construct

SQLAlchemy Description

Python SQL Toolkit and Object Relational Mapper SQLAlchemy is the solution that gives Python application developers the full power and flexibility of SQL.SQLAlchemy provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.SQL databases behave less like object collections the more size and performance start to matter; object collections behave less like tables and rows the more abstraction starts to matter. SQLAlchemy aims to accommodate both of these principles.SQLAlchemy doesn't view databases as just collections of tables; it sees them as relational algebra engines. Its object relational mapper enables classes to be mapped against the database in more than one way. SQL constructs don't just select from just tables—you can also select from joins, subqueries, and unions. Thus database relationships and domain object models can be cleanly decoupled from the beginning, allowing both sides to develop to their full potential. Here are some key features of "SQLAlchemy": Supported Databases: · SQLAlchemy includes dialects for SQLite, Postgres, MySQL, Oracle, MS-SQL, Firebird, MaxDB, MS Access, Sybase and Informix; IBM has also released a DB2 driver. The corresponding DB-API 2.0 implementation (or sometimes one of several available) is required to use each particular database. Unit Of Work: · The Unit Of Work system, a central part of SQLAlchemy's Object Relational Mapper (ORM), organizes pending create/insert/update/delete operations into queues and flushes them all in one batch. To accomplish this it performs a topological "dependency sort" of all modified items in the queue so as to honor foreign key constraints, and groups redundant statements together where they can sometimes be batched even further. This produces the maxiumum efficiency and transaction safety, and minimizes chances of deadlocks. Modeled after Fowler's "Unit of Work" pattern as well as Hibernate, Java's leading object-relational mapper. Function-based query construction: · Function-based query construction allows SQL clauses to be built via Python functions and expressions. The full range of what's possible includes boolean expressions, operators, functions, table aliases, selectable subqueries, create/update/insert/delete statements, correlated updates, correlated EXISTS clauses, UNION clauses, inner and outer joins, bind parameters, and free mixing of literal text within expressions. Constructed expressions are compilable specific to any number of vendor database implementations (such as PostGres or Oracle), as determined by the combination of a Dialect and Compiler provided by the implementation. Separate mapping and class design: · Database mapping and class design are totally separate. Persisted objects have no subclassing requirement (other than 'object') and are POPO's : plain old Python objects. They retain serializability (pickling) for usage in various caching systems and session objects. SQLAlchemy "decorates" classes with non-intrusive property accessors to automatically log object creates and modifications with the UnitOfWork engine, to lazyload related data, as well as to track attribute change histories. Eager-loading of objects: · Whole graphs of related objects can often be loaded with a single query that is automatically generated to join the appropriate tables together, known as eager loading. The alternative to eager loading, lazy loading, loads related objects via distinct query executions. Each type of loading produces identical results and are interchangeable, allowing configuration at any level as well as query-time selection of the relationship-loading method to be used. Composite (multiple-column) primary keys: · In SQLAlchemy, primary and foreign keys are represented as sets of columns; truly composite behavior is implemented from the ground up. The ORM has industrial strength support for meaningful (non-surrogate) primary keys, including mutability and compatibility with ON UPDATE CASCADE, as well as explicit support for other common composite PK patterns such as "association" objects (many-to-many relationships with extra meaning attached to each association). Self-referential tables and mappers: · Self-referential tables and mappers are supported. Adjacency list structures can be created, saved, and deleted with proper cascading, with no code overhead beyond that of non-self-referential structures. Self-referential structures of any depth can be eagerly loaded in a single statement using joins; you set how deep you'd like to go. Inheritance Mapping: · Explicit support is available for single-table, concrete-table, and joined table inheritance. Polymorphic loading (that is, a query that returns objects of multple descendant types) is supported for all three styles. The loading of each may be optimized such that only one round trip is used to fully load a polymorphic result set. Raw SQL statment mapping: · SQLA's data mapper can accomodate raw SQL statements as well as plain result sets, and object instances can be generated from these results in the same manner as any other ORM operation. Any hyper-optimized query that you or your DBA can cook up, you can run in SQLAlchemy, and as long as it returns the expected columns within a rowset, you can get your objects from it. Statements which represent multiple kinds of objects can be used as well, with results received as named-tuples, or with dependent objects routed into collections on parent objects. Pre- and post-processing of data: · The type system allows pre- and post- processing of data, both at the bind parameter and the result set level. User-defined types can be freely mixed with built-in types. Generic types as well as SQL-specific types are available.

SQLAlchemy Related Software