Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Script to change database column names on Sqlite3 databases.
#!/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;'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment