Script to change database column names on Sqlite3 databases.

sqlite_rename_column.py

#!/usr/bin/env python

import os, sys, subprocess, re

if len(sys.argv)==1:
	print "Usage:"
	print "   %s <databasefile> <tablename,tablename2,...> <origcolumn> <destcolumn> [--apply]"%sys.argv[0]
	print
	print "Options (must appear at the end):"
	print "   --apply   Apply the changes directly to database file"
	print
	sys.exit(0)

databasefile=sys.argv[1]
tablenames=sys.argv[2]
origcolumn=sys.argv[3]
destcolumn=sys.argv[4]
apply_process=None


if len(sys.argv)==6 and sys.argv[5]=='--apply':
	print '**** applying to database ***'
	apply_process=subprocess.Popen(["sqlite3", databasefile], stdin=subprocess.PIPE)
	sys.stdout.close()
	sys.stdout=apply_process.stdin


print 'BEGIN TRANSACTION;'

for tablename in tablenames.split(','):
	output=subprocess.check_output(["sqlite3", databasefile, ".schema %s"%tablename], stderr=sys.stderr)

	state='notattable'
	rows=[]
	output=output[output.index('(')+1:output.index(');')]
	lrows=[x.strip() for x in output.split(',')]
	
	extras=[]
	rows=[]
	addto=rows
	for l in lrows:
		if l.startswith('UNIQUE'):
			addto=extras
		addto.append(l)

	# remove , at the end if any, and empty lines, if any
	rows=[x[:-1] if x[-1]==',' else x for x in rows if x]
	keys=[x[1:x[1:].index('"')+1] if x[0]=='"' else x.split()[0] for x in rows]
	
	
	print '\n-- Rename old to tmp'
	print 'ALTER TABLE "%s" RENAME TO "%s_old";'%(tablename,tablename)

	print '\n-- Create new table'
	print 'CREATE TABLE "%s" ('%tablename
	columnmark='"%s" '%origcolumn # mark with the name of the column to change.
	frows=[]
	origcolumn_re=re.compile('"%s"'%origcolumn)
	for r in rows:
		row='\t'+origcolumn_re.sub('"%s"'%destcolumn, r)
		frows.append(row)
	for r in extras:
		row='\t'+origcolumn_re.sub('"%s"'%destcolumn, r)
		frows.append(row)

	print ',\n'.join(frows)
	print ');'

	print '\n-- Copy data'
	print 'INSERT INTO "%s" (%s)\n     SELECT %s\n       FROM "%s_old";'%(
		tablename, 
		', '.join('"%s"'%destcolumn if k==origcolumn else '"%s"'%k for k in keys),
		', '.join('"%s"'%k for k in keys), 
		tablename)

	print '\n-- Drop old'
	print 'DROP TABLE "%s_old";'%tablename


print 'COMMIT;'