escape mysql characterset hell (converts text to sanitised utf8)

escape_mysql_charset_hell.py

#!/usr/bin/env python

# inspired by this post:
# http://www.bluebox.net/news/2009/07/mysql_encoding

import MySQLdb

DB_NAME="example"
DB_USER="root"
DB_HOST="locahost"
DB_PASS="password"
TMP_TABLE="fixcharsettmp"

db = MySQLdb.connect(user=DB_USER, host=DB_HOST, db=DB_NAME, passwd=DB_PASS, charset='utf8')
c = db.cursor(MySQLdb.cursors.DictCursor)

c.execute("SELECT table_name, column_name, column_type "
    "FROM information_schema.columns "
    "WHERE table_schema = '%s' " 
    "AND (column_type like '%%char%%' OR column_type like '%%text%%') "
    "AND table_name != '%s' " % (DB, TMP_TABLE)
    ) 

for row in c.fetchall():
    col = row['column_name']
    tab = row['table_name']
    type = row['column_type']
    c.execute("drop table if exists %s", % TMP_TABLE);
    c.execute("create table %s (select * from %s where LENGTH(%s) !=  CHAR_LENGTH(%s))" % (TMP_TABLE, tab, col, col))
    c.execute("alter table %s modify %s.%s %s  character set latin1" % (TMP_TABLE, col, type))
    c.execute("alter table %s modify %s.%s blob" % (TMP_TABLE, col))
    c.execute("alter table %s modify %s.%s %s character set utf8" % (TMP_TABLE, col, type))
    c.execute("delete from  %s where LENGTH(%s) =  CHAR_LENGTH(%s)" % (TMP_TABLE, col, col))
    c.execute("replace into %s (select * from %s)" % (tab, TMP_TABLE))
    c.execute("drop table if exists %s" % (TMP_TABLE,));