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

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
micktAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian TaoSenior Business Solutions ConsultantCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
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.
0
micktAuthor Commented:
Thanks.  It's useful and I'll use it elsewhere.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.