Search code examples
pythonselectinner-joinpython-2.4sqlobject

Possible to get a generator over a custom SQLObject Select call?

I'm using the SQLObject ORM in Python, and I want to be able to iterate over a generator (of the row objects) in the same way I would Table.select(...), but what I can get from doing this method call is too restrictive, even using filter().

I want the same as I would get from a SQL query in this manner:

SELECT TableB.column_x, TableA.*
FROM TableA
INNER JOIN TableB
ON TableA.column_y = TableB.column_z;

I know how to use SQLBuilder to get this exact query: 

columns = [TableB.q.column_x, TableA.q.id]  + 
  [getattr(TableA.q, col.name) for col in TableA.sqlmeta.columnList]
inner_join = sqlbuilder.INNERJOINOn(
  table1=TableA,
  table2=TableB,
  on_condition=(TableA.column_y==TableB.column_z),
)
select = sqlbuilder.sqlbuilder.Select(items=columns, join=inner_join)

From there I can execute this query:

conn = TableA._connection
rows = conn.queryAll(conn.sqlrepr(select))

But this returns a list of tuples, not an equivalent generator as you would get by doing Table.select(...).filter(...)

This is part of a "restore_defaults" endpoint, I need to revert (the default, and only the default) rows of TableA and I need to see which of the default rows still exist and delete them where they do before inserting all the default rows into TableA while not overwriting/deleting any extra custom rows that may have been created by the users. In other words, this it's a specific requirement and I genuinely do need to use the augmented table from the SQL query above.

I realise that I can use the returned tuples (even making temporary dicts out of them) to those checks and update the dictionary, but is there a better way of getting the direct interactivity from SQLObject you get by doing a regular select() call?

(We're limited to Python 2.4, but I don't think that makes much of a difference)

Solution

    1. The high-level API (SQLObject-inherited classes) can query multiple tables at once (joins) but the result is always a list of SQLObject instances so only attributes from one class are immediately available. This is explained in the FAQ. You can declare One-to-Many or Many-to-Many Relationships and access attributes of the other tables; SQLObject will fetch rows and convert them to SQLObject instances automatically.

    2. Correction: SQLObject.select() doesn't return a generator, it returns at iterator, an instance of Iteration class. To be even more precise SQLObject.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!) fetches the results and passes them back to the user.

    3. And alas, Iteration only works with SelectResults. It doesn't work with SQLBuilder's queries.