def list(self, qry: Select, limit=None, offset=None, cls=None):
"""
Performs a query with offset and limit
Returns a tuple with total record count for the query, and the rows returned by applying offset and limit
The original query object is left intact
:param qry: query to use
:param limit:
:param offset:
:param cls: optional Record class to use for rows
:return: (total_rows, selected_row_list)
"""
total = 0
qry = copy.deepcopy(qry)
sql, values = qry.assemble()
qry_count = Select(self._dialect).from_(
{Literal(sql): "qry"}, cols={Literal('COUNT(*)'): 'total'})
if limit:
qry.limit(limit, offset)
with self._db.cursor() as c:
# count total rows
sql, _ = qry_count.assemble()
count_record = c.fetchone(sql, values)
if count_record:
total = count_record['total']
# fetch rows
rows = self.fetch(qry, cls=cls)
return total, rows
def fetch_one(self, qry: Select) -> Optional[object]:
"""
Retrieve a single row
:param qry: query to execute
:return: record object of self._record or None
Example:
r.fetch_one(r.select().where('login', '=', 'gandalf@lotr')) # fetch record if exists, else returns None
"""
with self._db.cursor() as c:
sql, values = qry.limit(1).assemble()
return c.fetchone(sql, values, cls=self._record)