change_collation

import MySQLdb

# MySQL Connection Parameters
MYSQL_USER=''
MYSQL_PASSWORD=''
MYSQL_DB=''
MYSQL_HOST=''

# Collation Information
OLD_COLLATION=''
NEW_COLLATION=''
NEW_CHARACTER_SET=''

# Let's start with the MySQL connection first
try:
    # The DB connection and cursor global variables
    db = MySQLdb.connect(host=MYSQL_HOST,
                         user=MYSQL_USER,
                         passwd=MYSQL_PASSWORD,
                         db=MYSQL_DB)
    db.autocommit(True)
    cursor = db.cursor()
except MySQLdb.OperationalError:
    print("Could not connect to database. Please check the database configuration.")
    exit(1)


def main():
    """Script for fetching list of tables with old collation and updating it to a new one."""
    old_collation_tables = get_tables()
    change_table_collation(old_collation_tables)
    modify_database()
    print("Done.")


def get_tables():
    """Get the list of tables that match OLD_COLLATION."""
    SELECT_TABLE_QUERY="SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA=%s AND TABLE_COLLATION=%s"
    cursor.execute(SELECT_TABLE_QUERY, (MYSQL_DB, OLD_COLLATION))
    tables=cursor.fetchall()
    return tables


def change_table_collation(tables):
    """Change collation from OLD_COLLATION to NEW_COLLATION for the given tables."""
    # iterate through the tuple of tables once by one and update collation for each table
    try:
        cursor.execute("SET FOREIGN_KEY_CHECKS=0")
    except:
        print("Cannot disable foreign key check.")
        exit(2)
    for table in tables:
        # FIXME: table[0] is a hack because we get tuple of tuples in the previous query.
        COLLATION_UPDATE_QUERY="ALTER TABLE {0} CONVERT TO CHARACTER SET {1} COLLATE {2}".format(table[0], NEW_CHARACTER_SET, NEW_COLLATION)
        try:
            cursor.execute(COLLATION_UPDATE_QUERY)
        except MySQLdb.Error, e:
            print("Updating collation for table {0} failed.".format(table[0]))
            print("Your query: {}".format(cursor._last_executed))
            print("MySQL Error: {}".format(str(e)))
    try:
        cursor.execute("SET FOREIGN_KEY_CHECKS=1")
    except:
        print("Cannot enable foreign key check.")
        exit(3)


def modify_database():
    """Change default collation for database."""
    CHANGE_DB_COLLATION_QUERY="ALTER DATABASE {0} DEFAULT COLLATE {1}".format(MYSQL_DB, NEW_COLLATION)
    try:
        cursor.execute(CHANGE_DB_COLLATION_QUERY)
    except:
        print("Updating collation for database {0} failed.".format(MYSQL_DB))


if __name__ == "__main__":
    main()