Convert all tables in a db to "utf8mb4" character set & "utf8mb4_general_ci" collate in a MySQL 8.0 server

convert-tables-to-utf8mb4.py

#!/usr/bin/env python3
from mysql.connector import connection
from mysql.connector.errors import IntegrityError, OperationalError

HOST = 'server'
USER = 'user'
PASSWORD = 'password'
DB = 'db'

QUERY_GET_TABLES_MYISAM = "SELECT DISTINCT TABLE_NAME FROM COLUMNS WHERE TABLE_SCHEMA='{DATABASE}' AND (CHARACTER_SET_NAME != 'utf8mb4' or COLLATION_NAME != 'utf8mb4_general_ci');".format(DATABASE=DB)

QUERY_CONVERT_TABLE = 'ALTER TABLE {TABLE_NAME} CONVERT TO CHARACTER SET "utf8mb4" COLLATE "utf8mb4_general_ci";'

connection = connection.MySQLConnection(
  user=USER,
  passwd=PASSWORD,
  host=HOST,
  db='information_schema'
)
cursor = connection.cursor()

cursor.execute(QUERY_GET_TABLES_MYISAM)
rows = cursor.fetchall()
cursor.close()

if not rows:
  print('There is not tables to convert')
  exit()

connection.cmd_init_db(DB)
cursor = connection.cursor()

for row in rows:
  print('Converting the table: "{}"...'.format(row[0]), end='')
  try:
    cursor.execute(QUERY_CONVERT_TABLE.format(TABLE_NAME=row[0]))
  except (IntegrityError, OperationalError) as error:
    print('\nError converting table "{}": {}'.format(row[0], error))
  else:
    print(' OK')

connection.commit()
cursor.close()
connection.close()

print('Number of tables converted: {}'.format(len(rows)))

Dependencies:

1. mysql-connector-python