Skip to content

@miratcan /fix_database_to_utf8.py
Last active

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
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]")
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 tables;")
tables_to_update = [t[0] for t in cur.fetchall()]
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"
@b987123

error in line 22 - should be:
cur.execute(query)

@miratcan
Owner

fixed

@cappie

to support tables with views, change line 16 to:

cur.execute("show full tables where table_type = 'BASE TABLE'")

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.