Script to change database column names on Sqlite3 databases.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 "Options (must appear at the end):" | |
print " --apply Apply the changes directly to database file" | |
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