SQLObject FAQ

SQLExpression

In SomeTable.select(SomeTable.q.Foo > 30) why doesn’t the inner parameter, SomeTable.q.Foo > 30, get evaluated to some boolean value?

q is an object that returns special attributes of type sqlbuilder.SQLExpression. SQLExpression is a special class that overrides almost all Python magic methods and upon any operation instead of evaluating it constructs another instance of SQLExpression that remembers what operation it has to do. This is similar to a symbolic algebra. Example:

SQLExpression(“foo”) > 30

produces SQLExpression(“foo”, “>”, 30) (well, it really produces SQLExpression(SQLExpression(“foo”)…))

How does the select(…) method know what to do?

In short, select() recursively evaluates the top-most SQLExpression to a string:

SQLExpression(“foo”, “>”, 30) => “foo > 30”

and passes the result as a string to the SQL backend.

The longer but more detailed and correct explanation is that select() produces an instance of SelectResults class that upon being iterated over produces an instance of Iteration class that upon calling its next() method (it is iterator!) constructs the SQL query string, passes it to the backend, fetches the results, wraps every row as SQLObject instance and passes them back to the user.

For the details of the implementation see sqlobject/main.py for SQLObject, sqlobject/sqlbuilder.py for SQLExpression, sqlobject/dbconnection.py for DBConnection class (that constructs the query strings) and Iteration class, and different subdirectories of sqlobject for concrete implementations of connection classes - different backends require different query strings.

Why there is no __len__?

There are reasons why there is no __len__ method, though many people think having those make them feel more integrated into Python.

One is that len(foo) is expected to be fast, but issuing a COUNT query can be slow. Worse, often this causes the database to do essentially redundant work when the actual query is performed (generally taking the len of a sequence is followed by accessing items from that sequence).

Another is that list(foo) implicitly tries to do a len first, as an optimization (because len is expected to be cheap – see previous point). Worse, it swallows all exceptions that occur during that call to __len__, so if it fails (e.g. there’s a typo somewhere in the query), the original cause is silently discarded, and instead you’re left with mysterious errors like “current transaction is aborted, commands ignored until end of transaction block” for no apparent reason.

How can I do a LEFT JOIN?

The short: you can’t. You don’t need to. That’s a relational way of thinking, not an object way of thinking. But it’s okay! It’s not hard to do the same thing, even if it’s not with the same query.

For these examples, imagine you have a bunch of customers, with contacts. Not all customers have a contact, some have several. The left join would look like:

SELECT customer.id, customer.first_name, customer.last_name,
       contact.id, contact.address
FROM customer
LEFT JOIN contact ON contact.customer_id = customer.id

Simple

for customer in Customer.select():
    print customer.firstName, customer.lastName
    for contact in customer.contacts:
        print '   ', contact.phoneNumber

The effect is the same as the left join – you get all the customers, and you get all their contacts. The problem, however, is that you will be executing more queries – a query for each customer to fetch the contacts – where with the left join you’d only do one query. The actual amount of information returned from the database will be the same. There’s a good chance that this won’t be significantly slower. I’d advise doing it this way unless you hit an actual performance problem.

Efficient

Lets say you really don’t want to do all those queries. Okay, fine:

custContacts = {}
for contact in Contact.select():
    custContacts.setdefault(contact.customerID, []).append(contact)
for customer in Customer.select():
    print customer.firstName, customer.lastName
    for contact in custContacts.get(customer.id, []):
        print '   ', contact.phoneNumber

This way there will only be at most two queries. It’s a little more crude, but this is an optimization, and optimizations often look less than pretty.

But, say you don’t want to get everyone, just some group of people (presumably a large enough group that you still need this optimization):

query = Customer.q.firstName.startswith('J')
custContacts = {}
for contact in Contact.select(AND(Contact.q.customerID == Customer.q.id,
                                  query)):
    custContacts.setdefault(contact.customerID, []).append(contact)
for customer in Customer.select(query):
    print customer.firstName, customer.lastName
    for contact in custContacts.get(customer.id, []):
        print '   ', contact.phoneNumber

SQL-wise

Use LEFTJOIN() from SQLBuilder.

How can I define my own intermediate table in my Many-to-Many relationship?

Note

In User and Role, SQLRelatedJoin is used with createRelatedTable=False so the intermediate table is not created automatically. We also set the intermediate table name with intermediateTable=’user_roles’. UserRoles is the definition of our intermediate table. UserRoles creates a unique index to make sure we don’t have duplicate data in the database. We also added an extra field called active which has a boolean value. The active column might be used to activate/deactivate a given role for a user in this example. Another common field to add in this an intermediate table might be a sort field. If you want to get a list of rows from the intermediate table directly add a MultipleJoin to User or Role class.

We’ll expand on the User and Role example and define our own UserRoles class which will be the intermediate table for the User and Role Many-to-Many relationship.

Example:

>>> class User(SQLObject):
...     class sqlmeta:
...         table = "user_table"
...     username = StringCol(alternateID=True, length=20)
...     roles = SQLRelatedJoin('Role',
...         intermediateTable='user_roles',
...         createRelatedTable=False)

>>> class Role(SQLObject):
...     name = StringCol(alternateID=True, length=20)
...     users = SQLRelatedJoin('User',
...         intermediateTable='user_roles',
...         createRelatedTable=False)

>>> class UserRoles(SQLObject):
...     class sqlmeta:
...         table = "user_roles"
...     user = ForeignKey('User', notNull=True, cascade