gistfile1.txt

import csv
import MySQLdb
from pprint import pprint

mydb = MySQLdb.connect(host='127.0.0.1',
    user='root',
    passwd='pa55word',
    db='metro')


with mydb:
    cur = mydb.cursor(MySQLdb.cursors.DictCursor)
    cur2 = mydb.cursor()
    cur.execute('SELECT * FROM rides ORDER BY id, ts')
    numrows = int(cur.rowcount)

    oldtime = None
    oldid = None
    trip = 1
    for i in range(numrows):
        row = cur.fetchone()
        pprint(row)
        if oldtime:
            dif = row['ts']-oldtime
            if dif.total_seconds() > 30*60 and oldid == row['id']:
                trip = trip+1
            if oldid != row['id']:
                trip = 1
        pprint(trip) 
        cur2.execute("UPDATE rides SET trip=%d WHERE id='%s' AND ts='%s'" % (trip, row['id'], row['ts']))
        oldtime=row['ts']
        oldid=row['id']

print "Done"