I would like to retrieve every Nth row of a SQL table, and I would like this accessed via a python slice function.<\/p>\n
A python slice allows access to a list (or any object that implements a __getitem__ method) by a start, stop, and step — for example,<\/p>\n
>>> foo = range(100)\n>>> foo[5]\n5\n>>> foo[5:10]\n[5, 6, 7, 8, 9]\n>>> foo[5:10:2]\n[5, 7, 9]\n>>> \n<\/pre>\nThe parameters of a slice behave identically to the builtin range() function,<\/p>\n
>>> range(5,10,2)\n[5, 7, 9]\n>>> \n<\/pre>\nGiven a python class that accesses a database table, you may want to implement slice functionality directly into that class. This can be done by implementing a __getitem__ method, a very simple __getitem__ looks something like this:<\/p>\n
def __getitem__(self, key):\n curs = self.db.cursor()\n return list(curs.execute('SELECT * FROM %s WHERE ROWID = %s' %(self.table, key+1)).fetchall()[0])\n<\/pre>\nHowever, this fails to account for slices and will only work for single item retrievals. When only a single key is provided python will pass that key to __getitem__, and when slice parameters are provided python will pass a slice object. In other words, inspect the key to figure out if a slice is passed in, e.g.,<\/p>\n
def __getitem__(self, key):\n if isinstance( key, slice ):\n ... slice acccess, e.g., foo[1:5], foo[:5], foo[0:10:2]\n else:\n ... single element access, e.g., foo[5]\n<\/pre>\nA slice object will have three parameters: slice.start, slice.stop, and slice.step; corresponding to object[start:stop:step]<\/p>\n
Going back to the SQL case, we’ll need to dynamically construct a SQL statement that will access every Nth row between a start and stop.<\/p>\n
The simplest way to do this is using a modulo operator (available in all major relational databases), for example, object[5:10:2] in sqlite<\/p>\n
SELECT * \nFROM table\nWHERE ROWID > 5 AND ROWID <= 10\n AND ROWID % 2 = 5%2\n<\/pre>\nThis approach allow you to retrieve every Nth row between an arbitrary start and stop, to put this into practice in python, a __getitem__ may be implemented as follows:<\/p>\n
def __getitem__(self, key):\n curs = self.db.cursor()\n if isinstance( key, slice ):\n start = 0 if key.start is None else key.start\n stop = len(self) - key.start if key.stop is None else key.stop\n qstep = ''\n if key.step is not None:\n qstep = ' AND ROWID %% %s = %s ' %(key.step, (start+1) % key.step)\n res = curs.execute('SELECT * FROM %s WHERE ROWID > %s AND ROWID <= %s %s'\n %(self.table, start, stop, qstep)).fetchall()\n else:\n res = list(curs.execute('SELECT * FROM %s WHERE ROWID = %s' %(self.table, key+1)).fetchall()[0])\n curs.close()\n return res\n<\/pre>\n