Solved

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

Posted on 2016-10-04
3
47 Views
Last Modified: 2016-10-12
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
0
Comment
Question by:mickt
3 Comments
 
LVL 9

Accepted Solution

by:
Brian Tao earned 500 total points
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 

Author Closing Comment

by:mickt
Comment Utility
Thanks.  It's useful and I'll use it elsewhere.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will show the steps for installing Python on Ubuntu Operating System. I have created a virtual machine with Ubuntu Operating system 8.10 and this installing process also works with upgraded version of Ubuntu OS. For installing Py…
The purpose of this article is to demonstrate how we can use conditional statements using Python.
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now