miratcan / fix_database_to_utf8.py
Last active

Embed URL

HTTPS clone URL

SSH clone URL

You can clone with HTTPS or SSH.

Download Gist

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)

View fix_database_to_utf8.py
Raw
File suppressed. Click to show.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 
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 commented

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

Owner
miratcan commented

fixed

cappie commented

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.