How can I iterate through mysql tables to alter character set?

mickt
mickt used Ask the Experts™
on
I tried the following.


from alembic import op

    # Convert character set to utf8
    for table in op.execute("SHOW TABLES"):
        print tables
        op.execute("alter table " + table + " convert to character set utf8 collate utf8_general_ci")

throws the following error:
TypeError: 'NoneType' object is not iterable

I also tried

    tables = op.execute("SHOW TABLES")
    for table in tables():
        print tables
        op.execute("alter table " + table + " convert to character set utf8 collate utf8_general_ci")

throws the following error:
TypeError: 'NoneType' object is not callable
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Owner
Top Expert 2014
Commented:
I don't think MySQL supports such query structure.  You have to first use a query against the table names to generate those ALTER statements, and then run those statements.
This will generate the required ALTER statements ("convert to" is not needed):
SELECT CONCAT('ALTER TABLE _your_database_name_.', table_name, ' CHARACTER SET utf8 COLLATE utf8_general_ci;') stmt FROM information_schema.TABLES WHERE table_schema='_your_database_name_' AND table_type='BASE TABLE';

Open in new window

and then run those statements (Don't forget to replace _your_database_name_ with your actual database name).

You can use PHP to write the whole thing, or just use your favorite MySQL tools to generate and run those statements.
Most Valuable Expert 2011
Top Expert 2016

Commented:
This does not look like a PHP script, since the variable names do not start with dollar signs.  Suggest you request attention and ask a moderator to put this into the correct zones for the languages you're using.

Author

Commented:
Thanks.  It's useful and I'll use it elsewhere.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial