home     documentation     download

Spyce integrates an advanced database module to make reading and modifying your data faster and less repetitive.

Just initialize the db reference in your Spyce config file following the examples given there, and you're all set.

The general idea is, db.tablename represents the tablename table in your database, and provides hooks for reading and modifying data in that table in pure Python, no SQL required. We find this gives 90% of the benefits of an object-relational mapping layer, without making developers learn another complex tool. And since the Spyce db module is part of SQLAlchemy, probably the most advanced database toolkit in the world, the full ORM approach is available to those who want it.

Here's a quick example of reading and inserting data from a table called todo_lists:

<spy:parent title="To-do demo" />

[[!
def list_new(self, api, name):
    if api.db.todo_lists.selectfirst_by(name=name):
        raise HandlerError('New list', 'a list with that description already exists')
    api.db.todo_lists.insert(name=name)
    api.db.flush()
]]

(This is an self-contained example using the same database as the
<a href=/demos/to-do/index.spy>to-do demo</a>.)

<h2>To-do lists</h2>

[[ lists = db.todo_lists.select(order_by=db.todo_lists.c.name) ]]
<spy:ul data="[L.name for L in lists]" />

<h2>New list</h2>
<f:form>
<f:submit value="New list" handler=self.list_new />:
<f:text name=name value="" />
</f:form>

Full SqlSoup documentation follows.

Loading objects

Loading objects is as easy as this:

    >>> users = db.users.select()
    >>> users.sort()
    >>> users
    [MappedUsers(name='Joe Student',email='student@example.edu',password='student',classname=None,admin=0), 
     MappedUsers(name='Bhargan Basepair',email='basepair@example.edu',password='basepair',classname=None,admin=1)]

Of course, letting the database do the sort is better (".c" is short for ".columns"):

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

Field access is intuitive:

    >>> users[0].email
    u'student@example.edu'

Of course, you don't want to load all users very often. The common case is to select by a key or other field:

    >>> db.users.selectone_by(name='Bhargan Basepair')
    MappedUsers(name='Bhargan Basepair',email='basepair@example.edu',password='basepair',classname=None,admin=1)

Select variants

Modifying objects

Modifying objects is intuitive:

    >>> user = _
    >>> user.email = 'basepair+nospam@example.edu'
    >>> db.flush()

(SqlSoup leverages the sophisticated SqlAlchemy unit-of-work code, so multiple updates to a single object will be turned into a single UPDATE statement when you flush.)

To finish covering the basics, let's insert a new loan, then delete it:

    >>> db.loans.insert(book_id=db.books.selectfirst(db.books.c.title=='Regional Variation in Moss').id, user_name=user.name)
    MappedLoans(book_id=2,user_name='Bhargan Basepair',loan_date=None)
    >>> db.flush()

    >>> loan = db.loans.selectone_by(book_id=2, user_name='Bhargan Basepair')
    >>> db.delete(loan)
    >>> db.flush()

You can also delete rows that have not been loaded as objects. Let's do our insert/delete cycle once more, this time using the loans table's delete method. (For SQLAlchemy experts: note that no flush() call is required since this delete acts at the SQL level, not at the Mapper level.) The same where-clause construction rules apply here as to the select methods:

    >>> db.loans.insert(book_id=book_id, user_name=user.name)
    MappedLoans(book_id=2,user_name='Bhargan Basepair',loan_date=None)
    >>> db.flush()
    >>> db.loans.delete(db.loans.c.book_id==2)

You can similarly update multiple rows at once. This will change the book_id to 1 in all loans whose book_id is 2:

    >>> db.loans.update(db.loans.c.book_id==2, book_id=1)
    >>> db.loans.select_by(db.loans.c.book_id==1)
    [MappedLoans(book_id=1,user_name='Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0))]

Joins

Occasionally, you will want to pull out a lot of data from related tables all at once. In this situation, it is far more efficient to have the database perform the necessary join. (Here we do not have "a lot of data," but hopefully the concept is still clear.) SQLAlchemy is smart enough to recognize that loans has a foreign key to users, and uses that as the join condition automatically.

    >>> join1 = db.join(db.users, db.loans, isouter=True)
    >>> join1.select_by(name='Joe Student')
    [MappedJoin(name='Joe Student',email='student@example.edu',password='student',classname=None,admin=0,
     book_id=1,user_name='Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0))]

You can compose arbitrarily complex joins by combining Join objects with tables or other joins.

    >>> join2 = db.join(join1, db.books)
    >>> join2.select()
    [MappedJoin(name='Joe Student',email='student@example.edu',password='student',classname=None,admin=0,
     book_id=1,user_name='Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0),
     id=1,title='Mustards I Have Known',published_year='1989',authors='Jones')]

If you join tables that have an identical column name, wrap your join with "with_labels", and all the columns will be prefixed with their table name:

    >>> db.with_labels(join1).select()
    [MappedUsersLoansJoin(users_name='Joe Student',users_email='student@example.edu',
                          users_password='student',users_classname=None,users_admin=0,
                          loans_book_id=1,loans_user_name='Joe Student',
                          loans_loan_date=datetime.datetime(2006, 7, 12, 0, 0))]

Advanced usage

You can access the SqlSoup's engine attribute to compose SQL directly. The engine's execute method corresponds to the one of a DBAPI cursor, and returns a ResultProxy that has fetch methods you would also see on a cursor.

    >>> rp = db.engine.execute('select name, email from users order by name')
    >>> for name, email in rp.fetchall(): print name, email
    Bhargan Basepair basepair+nospam@example.edu
    Joe Student student@example.edu

You can also pass this engine object to other SQLAlchemy constructs; see the SQLAlchemy documentation for details.

You can access SQLAlchemy Table and Mapper objects as db.tablename._table and db.tablename._mapper, respectively.