TurboGears (SQLObject) makes accessing your database seem very much like using any other Python objects. Check Data model overview for a general overview on how to use a database with TurboGears. See below under “Using the model” for basic database operations in TurboGears.
The model should be pretty self explanatory, this Python code will create three tables in your database: one for books, one for authors, and a join table for the many-to-many relationship between them:
class Book(SQLObject):
isbn = StringCol(length=13, alternateID=True)
title = StringCol(length=100)
description = StringCol()
authors = RelatedJoin('Author')
class Author(SQLObject):
last_name = StringCol(length=40)
first_name = StringCol(length=40)
books = RelatedJoin('Book')
Your model classes inherit from the SQLObject class and then you define class attributes by specifying the column types and supply some parameters for them.
Note
If you have more model code than will comfortably fit in one module, you can always break it up into multiple modules and import into the model module.
After you have created the model, you can insert a new row into your database by just instantiating an object from the appropriate class in your model. SQL queries are being run behind the scenes as you aceess and change your data object.
SQLObject does well against a wide variety of databases. Many aspects of how the database is used, such as column names, table names and join table names can be customized as needed to fit existing databases.
Your model objects should not be dumb data containers. They are full Python objects and can have methods of their own to perform more complex calculations and operations.
There are many subclasses of the Col class. These are used to indicate different types of columns, when SQLObject creates your tables.
A string (character) column. Extra keywords:
You could check column types for detail.
Column objects share some common attributes with which you can influence their behaviour and how they map to database columns.
The default value for this column. Used when creating a new row. If you give a callable object or function, the function will be called, and the return value will be used.
For example, you can specify datetime.now to make the default value be the current time. Or you can use sqlbuilder.func.NOW() to have the database use the NOW() function internally.
If you don’t give a default there will be an exception if this column isn’t specified in when the data object is instantiated:
You can check Specifying Columns for details.
The relational databases allow you relating tables with each other. Most common relationships are One-to-One, One-to-Many, and Many-to-Many.
SQLObject uses MultipleJoin and ForeignKey column-pairs to define a One-to-Many relationship.
For example, Persons can have multiple books, so we can extend the Book class from the initial example:
class Book(SQLObject):
....
owner = ForeignKey('Person')
class Person(SQLObject):
books = MultipleJoin('Book')
Note the column definition person = ForeignKey("Person"). This adds a reference to the Person object to the Book object. Related classes are referred to by name (with a string).
Note
As a result of defining this relation, in the database there will be an additional column person_id, type INT in the books table, which points to the id column of the person table.
Check One-to-Many Relationships for details.
Several keyword arguments are allowed to the MultipleJoin constructor:
Check MultipleJoin and SQLMultipleJoin: One-to-Many for details.
SQLObject use ‘SingleJoin’ and ‘ForeignKey’ column-pair to define a One-to-One relationship.
SingleJoin has all the keyword arguments of MultipleJoin, but returns just one object, not a list.
SQLObject use ‘RelatedJoin’ to define a Many-to-Many relationship.
For example, books can be edited by many authors, and authors could wrotte multiple books, so the quick example showed us:
class Book(SQLObject):
....
authors = RelatedJoin('Author')
class Author(SQLObject):
....
books = RelatedJoin('Book')
RelatedJoin has all the keyword arguments of MultipleJoin, plus:
Check RelatedJoin and SQLRelatedJoin: Many-to-Many for detail.
There’s a special class ‘sqlmeta’ could be defined to specify the table name that would be created in database:
class Book(SQLObject):
class sqlmeta:
table = "tg_book"
The ‘tg_book’ table will be created while you create the databse with ‘tg-admin sql create’ command. check Class sqlmeta for detail.
Assuming you have a Book model defined, here’s an example.
TurboGears provide the tg-admin shell command session for interactive database manipulation with debug logging turned on by default:
>>> Book(isbn="1234567890", title="A Fistful of Yen",
description="An evocative look at Japanese currency "
"surrounded by hands.")
1/QueryIns: INSERT INTO book (isbn, description, title)
VALUES ('1234567890',
'An evocative look at Japanese currency surrounded by
hands.', 'A Fistful of Yen')
1/QueryIns- > 1
1/COMMIT : auto
1/QueryOne: SELECT isbn, title, description FROM book WHERE id = 1
1/QueryR : SELECT isbn, title, description FROM book WHERE id = 1;
1/QueryOne- > (u'1234567890', u'A Fistful of Yen',
'An evocative look at Japanese currency surrounded by
hands.')
1/COMMIT : auto
<Book 1 isbn='1234567890' title='A Fistful of Yen'
description="'An evocative loo...'">
SQLObject works best in its default setup where each table has an integer primary key though there are ways to have keys handled differently.
You can get at any SQLObject’s primary key through the id attribute. SQLObject makes it really easy to retrieve by ID:
>>> Book.get(1)
<Book 1 isbn='1234567890' title='A Fistful of Yen'
description="'An evocative loo...'">
When you specify that a column is an alternateID, as we did for the isbn field, SQLObject automatically creates a classmethod so that you can use to search on those values:
>>> Book.byIsbn("1234567890")
1/QueryOne: SELECT id, isbn, title, description FROM book
WHERE isbn = '1234567890'
1/QueryR : SELECT id, isbn, title, description FROM book
WHERE isbn = '1234567890'
1/QueryOne- > (1, u'1234567890', u'A Fistful of Yen',
'An evocative look at Japanese currency surrounded by
hands.')
1/COMMIT : auto
<Book 1 isbn='1234567890' title='A Fistful of Yen'
description="'An evocative loo...'">
Of course, there are plenty of times when we need to do searches beyond just simple ID lookups. SQLObject provides a select() classmethod that lets you specify many queries in more Python-like terms.
Your class has a special q attribute that gives you access to a placeholder for a real attribute to use in queries.
For example, to query on the isbn column, you would use Book.q.isbn. Here’s a sample query:
>>> list(Book.select(AND(LIKE(Book.q.title, "%Fistful%"),
Book.q.isbn=="1234567890")))
1/Select : SELECT book.id, book.isbn, book.title, book.description
FROM book
WHERE ((book.title LIKE '%Fistful%') AND
(book.isbn = '1234567890'))
1/QueryR : SELECT book.id, book.isbn, book.title, book.description
FROM book
WHERE ((book.title LIKE '%Fistful%') AND
(book.isbn = '1234567890'))
1/COMMIT : auto
[<Book 1 isbn='1234567890' title='A Fistful of Yen'
description="'An evocative loo...'">]
In the example above, you’ll note the call to list() around the Book.select() call. The select classmethod returns a SelectResults object. check Selecting Multiple Objects for detail.
An alternative to .select is .selectBy. It works like:
>>> peeps = Book.selectBy(title = "%Fistful%", isbn = "1234567890")
The neat thing about SelectResults is that until you start pulling data out of it, it’s just a placeholder for the results.
Rather than converting the results to a list, we could have added .count() to the end of the select call in order to just retrieve the number of matching rows.
Updates are very easy: just change the class attribute!
Every time you change an attribute, SQLObject will run an UPDATE SQL statement. Sometimes, though, you may need to change several columns at once and don’t want to run individual updates for each.
Your instances have a set() method that lets you set them all at once. Here are examples of both styles:
>>> book.title = "A Fistful of Foobar"
1/Query : UPDATE book SET title = 'A Fistful of Foobar' WHERE id = 1
1/QueryR : UPDATE book SET title = 'A Fistful of Foobar' WHERE id = 1
1/COMMIT : auto
>>> book.set(title="A Fistful of Yen 2: Electric Boogaloo", isbn="37")
1/Query : UPDATE book SET isbn = '37',
title = 'A Fistful of Yen 2: Electric Boogaloo' WHERE id = 1
1/QueryR : UPDATE book SET isbn = '37',
title = 'A Fistful of Yen 2: Electric Boogaloo' WHERE id = 1
1/COMMIT : auto
Deleting a row is also pretty simple. If you are using the object directly you would make a call to objname.destroySelf()
Note that by default destroySelf only removes the object in question. It does not follow references and remove them.
To add this behavior you will have to override the destroySelf method of your class and add in the behavior you want. Here is a sample class that does this:
class SomeCol(SQLObject):
...
def destroySelf(self):
for x in self.related_join:
x.destroySelf()
SQLObject.destroySelf()
Where related_join is the name of the join you would like to follow.
TurboGears makes it easy to use transactions, via the “connection hub”.
The connection hub automatically connects to the database as needed, and also gives you methods to begin, commit, rollback or end transactions. Here’s an example of transactions at work:
>>> book.title
'A Fistful of Yen 2: Electric Boogaloo'
>>> hub.begin()
>>> book.title = "A Fistful of Yen 3: The Sequel That Shouldn't Be"
1/Query : UPDATE book SET
title = 'A Fistful of Yen 3: The Sequel That Shouldn''t Be'
WHERE id = 1
1/QueryR : UPDATE book SET
title = 'A Fistful of Yen 3: The Sequel That Shouldn''t Be'
WHERE id = 1
>>> hub.rollback()
1/ROLLBACK:
>>> hub.end()
>>> book.title
"A Fistful of Yen 3: The Sequel That Shouldn't Be"
>>> book.sync()
1/QueryOne: SELECT isbn, title, description FROM book WHERE id = 1
1/QueryR : SELECT isbn, title, description FROM book WHERE id = 1
1/QueryOne- > (u'37', u'A Fistful of Yen 2: Electric Boogaloo',
'An evocative look at Japanese currency surrounded by hands.')
1/COMMIT : auto
>>> book.title
'A Fistful of Yen 2: Electric Boogaloo'
Notice that, unlike in the previous examples, there was no COMMIT : auto for these queries. That’s because we turned on transactions, so autocommit was automatically turned off. You can also specify that you don’t want autocommit by adding an autoCommit=0 parameter to your connection URI.
It is also worth noting that the book object that we had in memory did not revert to its database state automatically on rollback. By calling sync(), the values are reloaded from the database.
Once you’ve had enough of “A Fistful of Yen 2”, you can delete it from the database by using the destroySelf() method.
There is quite a bit more information about defining your data model with SQLObject in the SQLObject documentation, SQLObject builder, and SQLObject module index.