how to use mysqls SQL_CALC_FOUND_ROWS or how to know what number of rows a query would return.

[robert rottermann]

Hi there,

I would like to create a batching functionality for a web based aplication that
uses a mysql database.

mysql offeres SQL_CALC_FOUND_ROWS for this purpose.
how can I use it, or is there a generic way to have sqlalchemy return the number
of rows a query would have returned without limit.

thanks
robert

[Michael Bayer]

use the "prefixes" argument to select() for this.

[robert rottermann]

Michael Bayer schrieb:

> use the "prefixes" argument to select() for this.
>

thanks very much.
it it possible to use this somehow with session.query?

I do not use select at all, but do construct the queries dynamically like so:

q = session.query(tblNewsletteremail)
c = tblNewsletteremail.__table__.c
for k,v in info.items():
if isinstance(v, tuple):
op, v = v
if op == 'like':
q = q.filter(c[k].like('%' + v + '%'))
if op == 'in':
q = q.filter(c[k].in_(v))
else:
q = q.filter(c[k] == v)
if order_by:
for e in order_by:
q = q.order_by(c[e])
if limit:
q = q.limit(limit)
result = q.all()
now I would like to "apply" SQL_CALC_FOUND_ROWS to the query object before
executing it. is this possible?

thanks again
robert

[Michael Bayer]

its not built into Query() at the moment so the closest you could get
would be query.from_statement(some_statement). you can probably go
from query->select()->back to query again doing something like this:

q = query.<build up your query>

session.query(...).from_statement(q.statement.prefix_with
("SQL_CALC_FOUND_ROWS..."))

[robert rottermann]

thanks
robert

Michael Bayer schrieb: