Yes, that is the basis of what I am suggesting.
There is not currently such a thing; there is something called 'select_raw'
implemented in the GoogleDataStore adapter, but not in anything else, and
it isn't exactly what I am proposing.
To elaborate:
Assume the table is defined as follows:
reftable = db.define_table('reftable', Field('a', string))
table = db.define_table('table', Field('b', reftable))
In my case, I need to pull all the records (60,000) from the database to
compute some aggregation which I cannot compute using sql. There are two
alternatives here:
r1 = db().select(table.ALL) # takes > 6 seconds
r2 = db.executesql(db._select(table.ALL)) # takes ~0.1sec
The records returned in the first instance are much richer; they have
record chasing (e.g. I can do r1[0].b.a to select through the foreign key),
they have methods like r1[0].update_record() and r1[0].delete_record(), and
other nice stuff.
However, for this use, I don't need the additional records, and I do need
the speed, so I would rather use r2. However, r2 is not a direct
replacement -- it doesn't have the column names. If I use
r3 = db.executesql(db._select(table.ALL), as_dict=True) # still takes
~0.1sec
I can do r3[0]['b'] but I cannot do r3[0].b; and it takes a lot more memory
than r2.
A suggestion: add another parameter, processor=... which, if available,
will be called with the db.connection.cursor, returning a function, through
which each routine will be passed; example
def named_tuple_process(name, description):
from collections import namedtuple
fields = ' '.join([x[0] for x in description])
return namedtuple(name, fields)
r4 = db.executesql(db._select(table.ALL), process=lambda x:
named_tuple_process('tablerec', x))
r4[0].b # will now work; not a full replacement, but good enough for many
uses.
In fact, you can do that externally -
r4 = db.executesql(db._select(table.ALL))
f = named_tuple_process('tablerec', db._adapter.cursor.description)
r4 = [f(x) for x in r4]
But this requires reaching into the internals of the db adapter.
Finally, I propose to define x.raw_select(*args) to do:
db.executesql(x._select(*args))
which would make this a relatively clean replacement.
