[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2016-10-04
3
Medium Priority
?
131 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 2000 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 111

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
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 …
Suggested Courses
Course of the Month19 days, 7 hours left to enroll

873 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