sqail

#!/usr/bin/python

# sqail by Jamie Matthews jamie.matthews@gmail.com
# 20th February 2009

# "tail -f" an sqlite table - monitor all inserts

# Usage: ./sqail dbfile tablename

# Prints the rowid of the row followed by a colon, then a
# comma-separated list of values for each column.

# Note, for some reason count(*) locks the database. So, if you're trying to 
# INSERT at *exactly* the same time that sqail is updating its rowcount, sqlite
# will return SQLITE_BUSY and your insert will fail. The bigger your table,
# the longer the count operation is going to take. On my system the count
# operation takes about two-tenths of a millisecond on a table with ~100 rows,
# but it takes about 0.2 seconds on a table with a million rows. Depending on your
# application, this may be a problem. But anyway, make sure you've got a busy
# handler and you should be fine.

# Number of seconds to wait between checks for new rows
sleep_time = 1

import sqlite3
import sys
from time import sleep

def usage():
	print """
Usage:
sqail dbfile tablename
"""

# Set up the database connection
def init_db(filename):
	db = sqlite3.connect(filename);
	c = db.cursor()		
	return db
	
# Return the current number of rows in the table
def get_count(db, table_name):
	c = db.cursor()
	c.execute("select count(*) from " + table_name)
	return c.fetchone()[0]
	
def print_info(db, db_filename, table_name, row_count):
	
	print
	print "Welcome to sqail. Press Ctrl+C to exit."
	print
	print "Monitoring inserts on table \"" + table_name + "\"",
	print "from database \"" + db_filename + "\"."
	print "Table currently has " + str(row_count) + " rows."
	
	print "Columns are: ",
	c = db.cursor()
	c.execute("pragma table_info(" + table_name + ")");
	rows = c.fetchall()
	for row in rows[:-1]: print str(row[1]) + ",",
	print str(rows[-1][1])
	print

# Print all rows in the table which have been inserted 
# after the supplied rowid
def print_diff(db, table_name, row_count):
	c = db.cursor()
	c.execute("select rowid, * from " + table_name + " where rowid > " + str(row_count))
	for row in c.fetchall(): print_row(row)

# Print a row, format "ROWID: column_1_val, column_2_val, column_3_val, ..."
def print_row(row):
	print str(row[0]) + ":",
	for column in row[1:-1]:
		print str(column) + ",",
	print row[-1]

# Main method
def main(argv):
	try:
		db_filename, table_name = argv
	except:
		usage()
		sys.exit(2)
	
	# Set up the database	
	db = init_db(db_filename)
	
	# Get the initial rowcount
	row_count = get_count(db, table_name)
	
	# Print some nice information about the table
	print_info(db, db_filename, table_name, row_count)
	
	# Forever
	while (True):	
		new_row_count = get_count(db, table_name)
		
		if (new_row_count > row_count):
			print_diff(db, table_name, row_count)
			row_count = new_row_count
		sleep(sleep_time)
			
if __name__ == "__main__":
	main(sys.argv[1:])