Skip to content

Instantly share code, notes, and snippets.

@ibnIrshad
Forked from miratcan/fix_database_to_utf8.py
Last active February 24, 2018 17:45
Show Gist options
  • Save ibnIrshad/fcd7c367737fd9bc311732c0f3b22bab to your computer and use it in GitHub Desktop.
Save ibnIrshad/fcd7c367737fd9bc311732c0f3b22bab to your computer and use it in GitHub Desktop.
Small python script that converts character sets to utf8 in all databases and tables. My solution for "Illegal mix of collations" errors. (http://stackoverflow.com/questions/3029321/how-to-solve-illegal-mix-of-collations-in-mysql)
from MySQLdb import connect
conn = connect(user="[user]", passwd= "[password]", host="[host]")
cur = conn.cursor()
cur.execute("show databases;")
dbs_to_update = filter(
lambda db: db not in ('information_schema', 'mysql', 'performance_schema'),
[dbname[0] for dbname in cur.fetchall()])
for db_to_update in dbs_to_update:
print "Updating %s db" % db_to_update
print "-" * (12 + len(db_to_update))
cur.execute("use %s" % db_to_update)
cur.execute("show full tables where table_type = 'BASE TABLE'")
tables_to_update = [t[0] for t in cur.fetchall()]
cur.execute("SET FOREIGN_KEY_CHECKS=0;")
for table_to_update in tables_to_update:
query = "ALTER TABLE %s CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" % table_to_update
cur.execute(query)
print table_to_update, "updated"
cur.execute("SET FOREIGN_KEY_CHECKS=1;")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment