Cacher is the code snippet organizer for pro developers

#!/usr/bin/python27
import sys
import csv
import time
import sqlite3
from datetime import time as Time # used only for generating data if file is absent
from random import randint # used only for generating data if file is absent

def get_the_data():

    msg = """
        Thanks to http://opendata.toronto.ca/TTC/routes/
        OpenData_TTC_Schedules.zip/stop_times.txt
        for the data.
        """
    print msg

    """ Returns a list with this:
    trip_id,arrival_time,departure_time,stop_id,stop_sequence,stop_headsign,pickup_type,drop_off_type,shape_dist_traveled
    19985180,6:15:00,6:15:00,14146,1,,0,0,
    19985180,6:16:41,6:16:41,3807,2,,0,0,0.5355
    19985180,6:17:41,6:17:41,6904,3,,0,0,0.8543
    ...
    (many lines later)
    ...
    20064514,25:41:41,25:41:41,7531,65,,0,0,17.6094
    20064514,25:42:00,25:42:00,13118,66,,1,1,17.6794
    """

    data=[]
    start = time.clock()
    try:
        with open(r'D:\OpenData_TTC_Schedules\stop_times.txt') as fi:
            for row in csv.reader(fi, delimiter=','):
                data.append(tuple(row)) # SQLite's "executemany()" needs tuples.
        data.pop(0) # get rid of headers.
    except IOError:
        print "\tfile not found - generating data..."
        data=syntheticData()

    end = time.clock()
    wall_time = end - start

    print "\tFetched %d lines of data, or %.3f Mb, in %.3f seconds" %(len(data), sys.getsizeof(data)/float(10**6), wall_time)
    print "\t(Reading csv from disk = %d rows per second)." %(len(data)/wall_time)
    return data

def syntheticData(length=4*10**5):
    alist=[]
    for i in range(length):
        trip_id = 19985180+i
        arrival_time = Time(randint(0,23),randint(0,59),randint(0,59)).isoformat()
        departure_time = Time(randint(0,23),randint(0,59),randint(0,59)).isoformat()
        stop_id = randint(1,15000)
        stop_sequence = randint(1,66)
        stop_headsign = ''
        pickup_type = randint(0,1)
        drop_off_type = randint(0,1)
        shape_dist_traveled = round(float(randint(5000,25000)/1000),4)
        row=tuple([trip_id,arrival_time,departure_time,stop_id,stop_sequence,stop_headsign,pickup_type,drop_off_type,shape_dist_traveled])
        alist.append(row)
    return alist

def transaction(data, DB, SQL_create_table, SQL, PRAGMA="ON", JOURNAL="", BATCH=True):

    con = sqlite3.connect(DB)
    cur = con.cursor()

    if PRAGMA=="OFF":
        cur.execute("PRAGMA synchronous = OFF")
    if JOURNAL!="":
        cur.execute("PRAGMA journal_mode = %s" %(JOURNAL))

    cur.execute(SQL_create_table) # Create the table.

    start = time.clock()
    if BATCH==True:
        cur.executemany(SQL, data) # Do the insert test
        # Save (commit) the changes
        con.commit() ###
    else:
        for row in data[:100000]: # We have patience for 100k rows.
            cur.execute(SQL, row)
            # Save (commit) the changes
            con.commit() ###
            if time.clock()-start > 300:
                break
    end = time.clock()

    cur.execute("SELECT IFNULL(MAX(id),1) FROM TTC;")
    rows=cur.fetchone()[0]

    wall_time = (end-start)
    if JOURNAL=="":
        JOURNAL="(default)"
    if BATCH==True:
        MODE="EXECUTEMANY"
    else:
        MODE="EXECUTE"
    print "\t%.2f rows per second with PRAGMA=%s and JOURNAL=%s using %s in %s" %(int(rows/wall_time),PRAGMA, JOURNAL, MODE, DB)

    SQL = "DROP TABLE IF EXISTS TTC;" # wipe the junk
    cur.execute(SQL)
    con.commit()
    con.close()

if __name__ == '__main__':

    data = get_the_data()

    print "\n\tUsing SQLite %s and pysqlite version %s" %(sqlite3.sqlite_version, sqlite3.version)

    SQL_create_table = "CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY, trip_id TEXT, arrival_time TEXT, departure_time TEXT, stop_id INTEGER, stop_sequence INTEGER, stop_headsign TEXT, pickup_type TEXT, drop_off_type TEXT, shape_dist_traveled REAL);"
    SQL_data_operation = "INSERT INTO TTC VALUES(Null,?,?,?,?,?,?,?,?,?)" # the Null keyword permits SQLite to autoincrement.

    DB = 'sqlite3_speedtest.db'
    print "\tSingle Transactions using EXECUTE To DISK"
    transaction(data, DB, SQL_create_table, SQL_data_operation, BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "DELETE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "TRUNCATE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "MEMORY", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "WAL", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "OFF", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", BATCH=False) 
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "DELETE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "TRUNCATE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "MEMORY", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "WAL", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "OFF", BATCH=False)
    print "\tBatched Transactions using EXECUTEMANY To DISK"
    transaction(data, DB, SQL_create_table, SQL_data_operation)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "DELETE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "TRUNCATE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "MEMORY")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "WAL")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "OFF")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "DELETE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "TRUNCATE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "MEMORY")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "WAL")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "OFF")

    DB = ':memory:'
    print "\tSingle Transactions using EXECUTE To MEMORY"
    transaction(data, DB, SQL_create_table, SQL_data_operation, BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "DELETE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "TRUNCATE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "MEMORY", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "WAL", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "OFF", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", BATCH=False) 
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "DELETE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "TRUNCATE", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "MEMORY", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "WAL", BATCH=False)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "OFF", BATCH=False)
    print "\tBatched Transactions using EXECUTEMANY To MEMORY"
    transaction(data, DB, SQL_create_table, SQL_data_operation)
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "DELETE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "TRUNCATE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "MEMORY")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "WAL")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "ON", "OFF")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "DELETE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "TRUNCATE")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "MEMORY")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "WAL")
    transaction(data, DB, SQL_create_table, SQL_data_operation, "OFF", "OFF")

    print "\tTest Completed"