SQLAlchemy A SQL toolkit and object relational mapper for Python.
Download

SQLAlchemy Ranking & Summary

SQLAlchemy Tags

SQL Toolkit SQL toolkit object relational mapper Python mapper SQLAlchemy

SQLAlchemy Description

A SQL toolkit and object relational mapper for Python. SQLAlchemy is a SQL toolkit and object relational mapper for Python.The Python SQL toolkit and Object Relational Mapper that gives 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.- extremely easy to use for all the basic tasks, such as: accessing thread-safe and pooled connections, constructing SQL from Python expressions, finding object instances, and commiting object modifications back to the database. - powerful enough for complicated tasks, such as: eager load a graph of objects and their dependencies via joins; map recursive adjacency structures automatically; map objects to not just tables but to any arbitrary join or select statement; combine multiple tables together to load whole sets of otherwise unrelated objects from a single result set; commit entire graphs of object changes in one step.- built to conform to what DBAs demand, including the ability to swap out generated SQL with hand-optimized statements, full usage of bind parameters for all literal values, fully transactionalized and consistent updates using Unit of Work. - modular. Different parts of SQLAlchemy can be used independently of the rest, including the connection pool, SQL construction, and ORM. SQLAlchemy is constructed in an open style that allows plenty of customization, with an architecture that supports custom datatypes, custom SQL extensions, and ORM plugins which can augment or extend mapping functionality.SQLAlchemy's Philosophy:- SQL databases behave less and less like object collections the more size and performance start to matter; object collections behave less and less like tables and rows the more abstraction starts to matter. SQLAlchemy aims to accomodate both of these principles.- Your classes aren't tables, and your objects aren't rows. Databases aren't just collections of tables; they're relational algebra engines. You don't have to select from just tables, you can select from joins, subqueries, and unions. Database and domain concepts should be visibly decoupled from the beginning, allowing both sides to develop to their full potential.- For example, table metadata (objects that describe tables) are declared distinctly from the classes theyre designed to store. That way database relationship concepts don't interfere with your object design concepts, and vice-versa; the transition from table-mapping to selectable-mapping is seamless; a class can be mapped against the database in more than one way. SQLAlchemy provides a powerful mapping layer that can work as automatically or as manually as you choose, determining relationships based on foreign keys or letting you define the join conditions explicitly, to bridge the gap between database and domain.SQLAlchemy's Advantages:- The Unit Of Work system organizes pending CRUD operations into queues and commits them all in one batch. It then performs a topological "dependency sort" of all items to be committed and deleted and groups redundant statements together. This produces the maxiumum efficiency and transaction safety, and minimizes chances of deadlocks. Modeled after Fowler's "Unit of Work" pattern as well as Java Hibernate.- Function-based query construction allows boolean expressions, operators, functions, table aliases, selectable subqueries, create/update/insert/delete queries, correlated updates, correlated EXISTS clauses, UNION clauses, inner and outer joins, bind parameters, free mixing of literal text within expressions, as little or as much as desired. Query-compilation is vendor-specific; the same query object can be compiled into any number of resulting SQL strings depending on its compilation algorithm. - 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.- Custom list classes can be used with eagerly or lazily loaded child object lists, allowing rich relationships to be created on the fly as SQLAlchemy appends child objects to an object attribute.- Composite (multiple-column) primary keys are supported, as are "association" objects that represent the middle of a "many-to-many" relationship.- Self-referential tables and mappers are supported. Adjacency list structures can be created, saved, and deleted with proper cascading, with no extra programming. - Data mapping can be used in a row-based manner. Any bizarre 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. For a rowset that contains more than one kind of object per row, multiple mappers can be chained together to return multiple object instance lists from a single database round trip. - 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. Requirements: · Python What's New in This Release: · new features · general · Documentation has been converted to Sphinx. In particular, the generated API documentation has been constructed into a full blown "API Reference" section which organizes editorial documentation combined with generated docstrings. Cross linking between sections and API docs are vastly improved, a javascript-powered search feature is provided, and a full index of all classes, functions and members is provided. · setup.py now imports setuptools only optionally. If not present, distutils is used. The new "pip" installer is recommended over easy_install as it installs in a more simplified way. · added an extremely basic illustration of a PostGIS integration to the examples folder. · orm · Query.with_polymorphic() now accepts a third argument "discriminator" which will replace the value of mapper.polymorphic_on for that query. Mappers themselves no longer require polymorphic_on to be set, even if the mapper has a polymorphic_identity. When not set, the mapper will load non-polymorphically by default. Together, these two features allow a non-polymorphic concrete inheritance setup to use polymorphic loading on a per-query basis, since concrete setups are prone to many issues when used polymorphically in all cases. · dynamic_loader accepts a query_class= to customize the Query classes used for both the dynamic collection and the queries built from it. · query.order_by() accepts None which will remove any pending order_by state from the query, as well as cancel out any mapper/relation configured ordering. This is primarily useful for overriding the ordering specified on a dynamic_loader(). · sql · RowProxy objects can be used in place of dictionary arguments sent to connection.execute() and friends. · dialect · Added a new description_encoding attribute on the dialect that is used for encoding the column name when processing the metadata. This usually defaults to utf-8. · mssql · Added in a new MSGenericBinary type. This maps to the Binary type so it can implement the specialized behavior of treating length specified types as fixed-width Binary types and non-length types as an unbound variable length Binary type. · Added in new types: MSVarBinary and MSImage. · Added in the MSReal, MSNText, MSSmallDateTime, MSTime, MSDateTimeOffset, and MSDateTime2 types · sqlite · Table reflection now stores the actual DefaultClause value for the column. · bugfixes, behavioral changes · general · orm · Exceptions raised during compile_mappers() are now preserved to provide "sticky behavior" · if a hasattr() call on a pre-compiled mapped attribute triggers a failing compile and suppresses the exception, subsequent compilation is blocked and the exception will be reiterated on the next compile() call. This issue occurs frequently when using declarative. · property.of_type() is now recognized on a single-table inheriting target, when used in the context of prop.of_type(..).any()/has(), as well as query.join(prop.of_type(...)). · query.join() raises an error when the target of the join doesn't match the property-based attribute · while it's unlikely anyone is doing this, the SQLAlchemy author was guilty of this particular loosey-goosey behavior. · Fixed bug when using weak_instance_map=False where modified events would not be intercepted for a flush(). · Fixed some deep "column correspondence" issues which could impact a Query made against a selectable containing multiple versions of the same table, as well as unions and similar which contained the same table columns in different column positions at different levels. · Custom comparator classes used in conjunction with column_property(), relation() etc. can define new comparison methods on the Comparator, which will become available via __getattr__() on the InstrumentedAttribute. In the case of synonym() or comparable_property(), attributes are resolved first on the user-defined descriptor, then on the user-defined comparator. · Added ScopedSession.is_active accessor. · Can pass mapped attributes and column objects as keys to query.update({}). · Mapped attributes passed to the values() of an expression level insert() or update() will use the keys of the mapped columns, not that of the mapped attribute. · Corrected problem with Query.delete() and Query.update() not working properly with bind parameters. · Query.select_from(), from_statement() ensure that the given argument is a FromClause, or Text/Select/Union, respectively. · Query() can be passed a "composite" attribute as a column expression and it will be expanded. Somewhat related to . · Query() is a little more robust when passed various column expressions such as strings, clauselists, text() constructs (which may mean it just raises an error more nicely). · first() works as expected with Query.from_statement(). · Fixed bug introduced in 0.5rc4 involving eager loading not functioning for properties which were added to a mapper post-compile using add_property() or equivalent. · Fixed bug where many-to-many relation() with viewonly=True would not correctly reference the link between secondary->remote. · Duplicate items in a list-based collection will be maintained when issuing INSERTs to a "secondary" table in a many-to-many relation. Assuming the m2m table has a unique or primary key constraint on it, this will raise the expected constraint violation instead of silently dropping the duplicate entries. Note that the old behavior remains for a one-to-many relation since collection entries in that case don't result in INSERT statements and SQLA doesn't manually police collections. · Query.add_column() can accept FromClause objects in the same manner as session.query() can. · Comparison of many-to-one relation to NULL is properly converted to IS NOT NULL based on not_(). · Extra checks added to ensure explicit primaryjoin/secondaryjoin are ClauseElement instances, to prevent more confusing errors later on. · Improved mapper() check for non-class classes. · comparator_factory argument is now documented and supported by all MapperProperty types, including column_property(), relation(), backref(), and synonym() . · Changed the name of PropertyLoader to RelationProperty, to be consistent with all the other names. PropertyLoader is still present as a synonym. · fixed "double iter()" call causing bus errors in shard API, removed errant result.close() left over from the 0.4 version. · made Session.merge cascades not trigger autoflush. Fixes merged instances getting prematurely inserted with missing values. Two fixes to help prevent out-of-band columns from being rendered in polymorphic_union inheritance scenarios (which then causes extra tables to be rendered in the FROM clause causing cartesian products): · improvements to "column adaption" for a->b->c inheritance situations to better locate columns that are related to one another via multiple levels of indirection, rather than rendering the non-adapted column. · the "polymorphic discriminator" column is only rendered for the actual mapper being queried against. The column won't be "pulled in" from a subclass or superclass mapper since it's not needed. · Fixed shard_id argument on ShardedSession.execute(). · sql · Columns can again contain percent signs within their names. · sqlalchemy.sql.expression.Function is now a public class. It can be subclassed to provide user-defined SQL functions in an imperative style, including with pre-established behaviors. The postgis.py example illustrates one usage of this. · PickleType now favors == comparison by default, if the incoming object (such as a dict) implements __eq__(). If the object does not implement __eq__() and mutable=True, a deprecation warning is raised. · Fixed the import weirdness in sqlalchemy.sql to not export __names__ . · Using the same ForeignKey object repeatedly raises an error instead of silently failing later. · Added NotImplementedError for params() method on Insert/Update/Delete constructs. These items currently don't support this functionality, which also would be a little misleading compared to values(). · Reflected foreign keys will properly locate their referenced column, even if the column was given a "key" attribute different from the reflected name. This is achieved via a new flag on ForeignKey/ForeignKeyConstraint called "link_to_name", if True means the given name is the referred-to column's name, not its assigned key. · select() can accept a ClauseList as a column in the same way as a Table or other selectable and the interior expressions will be used as column elements. · the "passive" flag on session.is_modified() is correctly propagated to the attribute manager. · union() and union_all() will not whack any order_by() that has been applied to the select()s inside. If you union() a select() with order_by() (presumably to support LIMIT/OFFSET), you should also call self_group() on it to apply parenthesis. · engine/pool · Connection.invalidate() checks for closed status to avoid attribute errors. · NullPool supports reconnect on failure behavior. · Added a mutex for the initial pool creation when using pool.manage(dbapi). This prevents a minor case of "dogpile" behavior which would otherwise occur upon a heavy load startup. · _execute_clauseelement() goes back to being a private method. Subclassing Connection is not needed now that ConnectionProxy is available. · documentation · Tickets . · Added note about create_session() defaults. · Added section about metadata.reflect(). · Updated `TypeDecorator` section. · Rewrote the "threadlocal" strategy section of the docs due to recent confusion over this feature. · Removed badly out of date 'polymorphic_fetch' and 'select_table' docs from inheritance, reworked the second half of "joined table inheritance". · Documented `comparator_factory` kwarg, added new doc section "Custom Comparators". · mssql · Refactored the Date/Time types. The ``smalldatetime`` data type no longer truncates to a date only, and will now be mapped to the MSSmallDateTime type. · Corrected an issue with Numerics to accept an int. · Mapped ``char_length`` to the ``LEN()`` function. · If an ``INSERT`` includes a subselect the ``INSERT`` is converted from an ``INSERT INTO VALUES`` construct to a ``INSERT INTO SELECT`` construct. · If the column is part of a ``primary_key`` it will be ``NOT NULL`` since MSSQL doesn't allow ``NULL`` in primary_key columns. · ``MSBinary`` now returns a ``BINARY`` instead of an ``IMAGE``. This is a backwards incompatible change in that ``BINARY`` is a fixed length data type whereas ``IMAGE`` is a variable length data type. · ``get_default_schema_name`` is now reflected from the database based on the user's default schema. This only works with MSSQL 2005 and later. · Added collation support through the use of a new collation argument. This is supported on the following types: char, nchar, varchar, nvarchar, text, ntext. · Changes to the connection string parameters favor DSN as the default specification for pyodbc. See the mssql.py docstring for detailed usage instructions. · Added experimental support of savepoints. It currently does not work fully with sessions. · Support for three levels of column nullability: NULL, NOT NULL, and the database's configured default. The default Column configuration (nullable=True) will now generate NULL in the DDL. Previously no specification was emitted and the database default would take effect (usually NULL, but not always). To explicitly request the database default, configure columns with nullable=None and no specification will be emitted in DDL. This is backwards incompatible behavior. · postgres · "%" signs in text() constructs are automatically escaped to "%%". Because of the backwards incompatible nature of this change, a warning is emitted if '%%' is detected in the string. · Calling alias.execute() in conjunction with server_side_cursors won't raise AttributeError. · Added Index reflection support to Postgres, using a great patch we long neglected, submitted by Ken Kuhlman. · oracle · Adjusted the format of create_xid() to repair two-phase commit. We now have field reports of Oracle two-phase commit working properly with this change. · Added OracleNVarchar type, produces NVARCHAR2, and also subclasses Unicode so that convert_unicode=True by default. NVARCHAR2 reflects into this type automatically so these columns pass unicode on a reflected table with no explicit convert_unicode=True flags. · Fixed bug which was preventing out params of certain types from being received; thanks a ton to huddlej at wwu.edu ! · mysql · "%" signs in text() constructs are automatically escaped to "%%". Because of the backwards incompatible nature of this change, a warning is emitted if '%%' is detected in the string. · Fixed bug in exception raise when FK columns not present during reflection. · Fixed bug involving reflection of a remote-schema table with a foreign key ref to another table in that schema. · associationproxy · The association proxy properties are make themselves available at the class level, e.g. MyClass.aproxy. Previously this evaluated to None. · declarative · The full list of arguments accepted as string by backref() includes 'primaryjoin', 'secondaryjoin', 'secondary', 'foreign_keys', 'remote_side', 'order_by'.

SQLAlchemy Related Software