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" |
This comment has been minimized.
This comment has been minimized.
fixed |
This comment has been minimized.
This comment has been minimized.
cappie
commented
Jan 15, 2015
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
This comment has been minimized.
user1007017 commentedJan 23, 2013
error in line 22 - should be:
cur.execute(query)