google_history_extract.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys
import datetime
import csv
import StringIO
import urlparse

con = None

try:
	con = lite.connect('History')
	
	cur = con.cursor()	
	cur.execute('SELECT V.visit_time, U.url FROM visits as V JOIN urls as U on V.url=U.id order by V.visit_time DESC;')
	
	rows = cur.fetchall()
	for visit_time, url in rows:

		if not 'google' in url:
			continue

		url = url.replace('#', '&')

		parsed = urlparse.urlparse(url)
		query = urlparse.parse_qs(parsed.query).get('q', None)

		if not query:
			continue

		query = query[0].encode('ascii','ignore')
		output = StringIO.StringIO()
		csv_writer = csv.writer(output, delimiter=',')
		date = datetime.datetime( 1601, 1, 1 ) + datetime.timedelta( microseconds=visit_time)
		date_string = date.strftime("%Y-%m-%d %H:%M:%S")
		csv_writer.writerow([date_string, query])

		print output.getvalue().strip('\n')
	
except lite.Error, e:
	
	print "Error %s:" % e.args[0]
	sys.exit(1)
	
finally:
	
	if con:
		con.close()