Documentation - Modules
3.8.1. DB (implicit)
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:
Full SqlSoup documentation follows.
Loading objects is as easy as this:
>>> users = db.users.select() >>> users.sort() >>> users [MappedUsers(name='Joe Student',firstname.lastname@example.org',password='student',classname=None,admin=0), MappedUsers(name='Bhargan Basepair',email@example.com',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',firstname.lastname@example.org',password='basepair',classname=None,admin=1), MappedUsers(name='Joe Student',email@example.com',password='student',classname=None,admin=0)]
Field access is intuitive:
>>> users.email firstname.lastname@example.org'
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@example.com',password='basepair',classname=None,admin=1)
Select variantsAll the SqlAlchemy Query select variants are available. Here's a quick summary of these methods: - get(PK): load a single object identified by its primary key (either a scalar, or a tuple) - select(Clause, **kwargs): perform a select restricted by the Clause argument; returns a list of objects. The most common clause argument takes the form "db.tablename.c.columname == value." The most common optional argument is order_by. - select_by(**params): the *_by selects allow using bare column names. (columname=value)This feels more natural to most Python programmers; the downside is you can't specify order_by or other select options. - selectfirst, selectfirst_by: returns only the first object found; equivalent to select(...) or select_by(...), except None is returned if no rows are selected. - selectone, selectone_by: like selectfirst or selectfirst_by, but raises if less or more than one object is selected. - count, count_by: returns an integer count of the rows selected. See the SqlAlchemy documentation for details: - general info and examples - details on constructing WHERE clauses
Modifying objects is intuitive:
>>> user = _ >>> user.email = 'firstname.lastname@example.org' >>> 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))]
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@example.com',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',firstname.lastname@example.org',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',email@example.com', 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))]
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 firstname.lastname@example.org Joe Student email@example.com
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.