dumping a table from a sqlite database

While trying to extract the text messages from an iPhone backup, I found a need to extract a table to a text file to make it easier to work with. This extraction ended up being the final result, since it was good enough and could be imported into a spreadsheet for easy viewing.

The table extraction just does a grid escape of the results of a select statement.

#!/usr/bin/env python
import sys, sqlite3

if len(sys.argv) != 3:
  print "expected database file name and table name"
  sys.exit()
file = sys.argv[1]
table = sys.argv[2]

def grid_escape(x):
  if x == None:
    return "\\N"
  x = unicode(x)
  return x.replace("\\", "\\\\").replace("\t", "\\t").replace("\n","\\n")

conn = sqlite3.connect(file)
c = conn.cursor()

c.execute('select * from ' + table)
print "\t".join([col[0] for col in c.description]).encode('utf-8')
for row in c:
  print "\t".join(map(grid_escape,row)).encode('utf-8')

conn.close()

the grid escape function demonstrates how simple it is to encode that format, which is part of why I like it, it is trivial to produce in many languages.