::::: : the wood : davidrobins.com

Many cheerful facts about the classes of SQLAlchemy

Technical ·Sunday September 12, 2010 @ 01:52 EDT (link)

Stashing some SQLAlchemy (Python database toolkit, the only decent one I found that was compatible with Python 3) / Elixir (declarative improvements library) declarative object details I learned from scouring the 'net and perusing the source in this space… may they prove useful for others. Some of this is better documented than others—check the PyDoc: sometimes I'm just pointing out useful things to check among a large set of methods or attributes.

Entity class inherits from Elixir's Entity class; entity object is instance of same. Its database-related attributes contain regular types (e.g., strings); nothing special. .table is a Table object, .mapper is a Mapper object. Table.columns (and .primary_key.columns) are iterable ColumnCollection objects (which inherit from util.OrderedProperties and can be manipulated as collections, cast to list, etc.), and contain Columns.

For introspection, examine the object's .__class__; declared fields and relationships appear as InstrumentedAttribute properties, whose .property (hence .p) attributes can be (at least) sa.orm.properties.ColumnProperty or .RelationshipProperty. Both have .key, matching the attribute name, and CPs have a .columns list (most have one item, a Column object, but composites are possible).

RPs have some more interesting properties: .table, the foreign Table object (or .target, or .argument for the entity class), .local_side and .remote_side, OrderedSets of Column objects (from each side of the relationship); .local_remote_pairs (.l_r_p), a list of tuples, with our Column object first; ..synchronize_pairs (.s_p), like previous as above, but with the "primary" ("one" in "one to many") Column object first and the foreign ("many") second:
Path.files.p.l_r_p == Path.files.p.s_p == File.path.p.s_p, distinct from File.path.p.l_r_p.
This allows some useful entity introspection, like finding linked tables. The debugger trick of import pdb; pdb.set_trace() is useful for examination (view __dict__, etc.).

Later note: to get a useful properties list, unfortunately one has to go to private attributes (their fault): the object has a hidden, private (by convention, because of the initial _) attribute _descriptor which is an (Elixir) EntityDescriptor with some interesting properties, the most useful of which seems to be .builders which is a list of (Elixir) Property objects (Field, Relationship). All have .name which is the attribute name and .entity which is the entity class. Fields have .colname (string) and .column (Column). Relationships (e.g. elixir.relationships.ManyToOne) have .property as the(SQLAlchemy) property object (ColumnProperty, RelationshipProperty, etc.), ._target, the foreign entity class, .colname (a list of strings), and .foreign_key (a list of Column objects in our table). It only includes user-added properties, unlike .mapper.iterate_properties which contains a hodge-podge of properties and columns (i.e., both when columns have been renamed).

Even later note: gave up on Elixir. It may work for very tiny toy projects, especially where nobody understands metaprogramming, but it's not robust or well-documented enough. Pure SQLAlchemy is better documented and better able to provide the required facilities.