plugins.txt

SQLAlchemy. 经典的Python ORM框架。学习必看。

    # [(1, 'ZZK', 10), (1, 'JEK', 123), (1, 'STEPZ', 123)]

Further examples can be found in the `examples/` directory in the SQLAlchemy distribution.

The `association_proxy` convenience function is not present in SQLAlchemy versions 0.3.1 through 0.3.7, instead instantiate the class directly:

    {python}
    from sqlalchemy.ext.associationproxy import AssociationProxy

    class Article(object):
       keywords = AssociationProxy('keyword_associations', 'keyword')


### orderinglist

**Author:** Jason Kirtland

`orderinglist` is a helper for mutable ordered relations.  It will intercept
list operations performed on a relation collection and automatically
synchronize changes in list position with an attribute on the related objects.
(See [advdatamapping_properties_entitycollections](rel:advdatamapping_properties_customcollections) for more information on the general pattern.)

Example: Two tables that store slides in a presentation.  Each slide
has a number of bullet points, displayed in order by the 'position'
column on the bullets table.  These bullets can be inserted and re-ordered
by your end users, and you need to update the 'position' column of all
affected rows when changes are made.

    {python}
    slides_table = Table('Slides', metadata,
                         Column('id', Integer, primary_key=True),
                         Column('name', String))

    bullets_table = Table('Bullets', metadata,
                          Column('id', Integer, primary_key=True),
                          Column('slide_id', Integer, ForeignKey('Slides.id')),
                          Column('position', Integer),
                          Column('text', String))

     class Slide(object):
         pass
     class Bullet(object):
         pass

     mapper(Slide, slides_table, properties={
           'bullets': relation(Bullet, order_by=[bullets_table.c.position])
     })
     mapper(Bullet, bullets_table)

The standard relation mapping will produce a list-like attribute on each Slide
containing all related Bullets, but coping with changes in ordering is totally
your responsibility.  If you insert a Bullet into that list, there is no
magic- it won't have a position attribute unless you assign it it one, and
you'll need to manually renumber all the subsequent Bullets in the list to
accommodate the insert.

An `orderinglist` can automate this and manage the 'position' attribute on all
related bullets for you.

    {python}        
    mapper(Slide, slides_table, properties={
           'bullets': relation(Bullet,
                               collection_class=ordering_list('position'),
                               order_by=[bullets_table.c.position])
    })
    mapper(Bullet, bullets_table)

    s = Slide()
    s.bullets.append(Bullet())
    s.bullets.append(Bullet())
    s.bullets[1].position
    >>> 1
    s.bullets.insert(1, Bullet())
    s.bullets[2].position
    >>> 2

Use the `ordering_list` function to set up the `collection_class` on relations
(as in the mapper example above).  This implementation depends on the list
starting in the proper order, so be SURE to put an order_by on your relation.

`ordering_list` takes the name of the related object's ordering attribute as
an argument.  By default, the zero-based integer index of the object's
position in the `ordering_list` is synchronized with the ordering attribute:
index 0 will get position 0, index 1 position 1, etc.  To start numbering at 1
or some other integer, provide `count_from=1`.

Ordering values are not limited to incrementing integers.  Almost any scheme
can implemented by supplying a custom `ordering_func` that maps a Python list
index to any value you require.  See the [module
documentation](rel:docstrings_sqlalchemy.ext.orderinglist) for more
information, and also check out the unit tests for examples of stepped
numbering, alphabetical and Fibonacci numbering.

### SqlSoup

**Author:** Jonathan Ellis

SqlSoup creates mapped classes on the fly from tables, which are automatically reflected from the database based on name.  It is essentially a nicer version of the "row data gateway" pattern.

    {python}
    >>> from sqlalchemy.ext.sqlsoup import SqlSoup
    >>> soup = SqlSoup('sqlite:///')

    >>> db.users.select(order_by=[db.users.c.name])
    [MappedUsers(name='Bhargan Basepair',email='[e-mail]',password='basepair',classname=None,admin=1),
     MappedUsers(name='Joe Student',email='[e-mail]',password='student',classname=None,admin=0)]

Full SqlSoup documentation is on the [SQLAlchemy Wiki](http://www.sqlalchemy.org/trac/wiki/SqlSoup).


### Deprecated Extensions

A lot of our extensions are deprecated.  But this is a good thing.  Why ?  Because all of them have been refined and focused, and rolled into the core of SQLAlchemy (or in the case of `ActiveMapper`, it's become **Elixir**).  So they aren't removed, they've just graduated into fully integrated features.  Below we describe a set of extensions which are present in 0.4 but are deprecated.

#### SelectResults

**Author:** Jonas Borgstr枚m

*NOTE:* As of version 0.3.6 of SQLAlchemy, most behavior of `SelectResults` has been rolled into the base `Query` object.  Explicit usage of `SelectResults` is therefore no longer needed.

`SelectResults` gives transformative behavior to the results returned from the `select` and `select_by` methods of `Query`. 

    {python}
    from sqlalchemy.ext.selectresults import SelectResults

    query = session.query(MyClass)
    res = SelectResults(query)
    
    res = res.filter(table.c.column == "something") # adds a WHERE clause (or appends to the existing via "and")
    res = res.order_by([table.c.column]) # adds an ORDER BY clause

    for x in res[:10]:  # Fetch and print the top ten instances - adds OFFSET 0 LIMIT 10 or equivalent
      print x.column2

    # evaluate as a list, which executes the query
    x = list(res)

    # Count how many instances that have column2 > 42
    # and column == "something"
    print res.filter(table.c.column2 > 42).count()

    # select() is a synonym for filter()
    session.query(MyClass).select(mytable.c.column=="something").order_by([mytable.c.column])[2:7]

An important facet of SelectResults is that the actual SQL execution does not occur until the object is used in a list or iterator context.  This means you can call any number of transformative methods (including `filter`, `order_by`, list range expressions, etc) before any SQL is actually issued.

Configuration of SelectResults may be per-Query, per Mapper, or per application:

    {python}
    from sqlalchemy.ext.selectresults import SelectResults, SelectResultsExt
    
    # construct a SelectResults for an individual Query
    sel = SelectResults(session.query(MyClass))
    
    # construct a Mapper where the Query.select()/select_by() methods will return a SelectResults:
    mapper(MyClass, mytable, extension=SelectResultsExt())
    
    # globally configure all Mappers to return SelectResults, using the "selectresults" mod
    import sqlalchemy.mods.selectresults

SelectResults greatly enhances querying and is highly recommended.  For example, heres an example of constructing a query using a combination of joins and outerjoins:

    {python}
    mapper(User, users_table, properties={
        'orders':relation(mapper(Order, orders_table, properties={
            'items':relation(mapper(Item, items_table))
        }))
    })
    session = create_session()
    query = SelectResults(session.query(User))

    result = query.outerjoin_to('orders').outerjoin_to('items').select(or_(Order.c.order_id==None,Item.c.item_id==2))

For a full listing of methods, see the [generated documentation](rel:docstrings_sqlalchemy.ext.selectresults).

#### SessionContext

**Author:**  Daniel Miller

The `SessionContext` extension is still available in the 0.4 release of SQLAlchemy, but has been deprecated in favor of the [scoped_session()](rel:unitofwork_contextual) function, which provides a class-like object that constructs a `Session` on demand which references a thread-local scope.  

For docs on `SessionContext`, see the SQLAlchemy 0.3 documentation.
    
#### assignmapper

**Author:** Mike Bayer

The `assignmapper` extension is still available in the 0.4 release of SQLAlchemy, but has been deprecated in favor of the [scoped_session()](rel:unitofwork_contextual) function, which provides a `mapper` callable that works similarly to `assignmapper`.

For docs on `assignmapper`, see the SQLAlchemy 0.3 documentation.

#### ActiveMapper

**Author:** Jonathan LaCour

Please note that ActiveMapper has been deprecated in favor of [Elixir](http://elixir.ematia.de/), a more comprehensive solution to declarative mapping, of which Jonathan is a co-author.

ActiveMapper is a so-called "declarative layer" which allows the construction of a class, a `Table`, and a `Mapper` all in one step:

    {python}
    class Person(ActiveMapper):
        class mapping:
            id          = column(Integer, primary_key=True)
            full_name   = column(String)
            first_name  = column(String)
            middle_name = column(String)
            last_name   = column(String)
            birth_date  = column(DateTime)
            ssn         = column(String)
            gender      = column(String)
            home_phone  = column(String)
            cell_phone  = column(String)
            work_phone  = column(String)
            prefs_id    = column(Integer, foreign_key=ForeignKey('preferences.id'))
            addresses   = one_to_many('Address', colname='person_id', backref='person')
            preferences = one_to_one('Preferences', colname='pref_id', backref='person')
    
        def __str__(self):
            s =  '%s\n' % self.full_name
            s += '  * birthdate: %s\n' % (self.birth_date or 'not provided')
            s += '  * fave color: %s\n' % (self.preferences.favorite_color or 'Unknown')
            s += '  * personality: %s\n' % (self.preferences.personality_type or 'Unknown')
        
            for address in self.addresses:
                s += '  * address: %s\n' % address.address_1
                s += '             %s, %s %s\n' % (address.city, address.state, address.postal_code)
        
            return s


    class Preferences(ActiveMapper):
        class mapping:
            __table__        = 'preferences'
            id               = column(Integer, primary_key=True)
            favorite_color   = column(String)
            personality_type = column(String)


    class Address(ActiveMapper):
        class mapping:
            id          = column(Integer, primary_key=True)
            type        = column(String)
            address_1   = column(String)
            city        = column(String)
            state       = column(String)
            postal_code = column(String)
            person_id   = column(Integer, foreign_key=ForeignKey('person.id'))
            
More discussion on ActiveMapper can be found at [Jonathan LaCour's Blog](http://cleverdevil.org/computing/35/declarative-mapping-with-sqlalchemy) as well as the [SQLAlchemy Wiki](http://www.sqlalchemy.org/trac/wiki/ActiveMapper).