Solved

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

Posted on 2016-10-04
3
78 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
ID: 41828093
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 109

Expert Comment

by:Ray Paseur
ID: 41828145
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
ID: 41839657
Thanks.  It's useful and I'll use it elsewhere.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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 …

809 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