There may come a time you need to rename one or more columns in an existing MySQL table for a variety of reasons. Using ALTER TABLE, to rename a column is an easy enough command. But, suppose there are multiple tables in the same database/schema that have the same-named column and all of those columns need to be renamed. That could be a lot of ALTER TABLE statements to type out. Save your energy and time, avoiding all of those ALTER TABLE commands all-together. If you are lucky enough to be working with a MySQL version > 8.0 then the Shell is your salvation. With just a few lines of Python code in \py mode, all of your trouble(s) and headache(s) are no more…
OS, Software, and DB used:
- OpenSuse Leap 15.1
- MySQL 8.0.20
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
Credit where credit is due.
This blog post is inspired by a video I watched on YouTube demonstrating how to rename a same-named column across multiple tables using MySQL Workbench. I adapted the concepts presented in the video to work in MySQL Shell Python (\py) mode. Be sure and check out,
How to Rename Column in all tables in MySQL Database – MySQL Developer Tutorial! It’s a helpful and informative video.
Suppose I have these 3 tables:
1 2 3 4 5 6 7 8 9 | MySQL localhost:33060+ ssl practice SQL > SHOW TABLES IN practice; +--------------------+ | Tables_in_practice | +--------------------+ | ages_1 | | ages_2 | | ages_3 | +--------------------+ 3 rows in set (0.0029 sec) |
Each table has a single column, horribly named ‘some_age’ of datatype INTEGER:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl practice SQL > DESC ages_1; +----------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------+------+-----+---------+-------+ | some_age | int | YES | | NULL | | +----------+------+------+-----+---------+-------+ 1 row in set (0.0039 sec) |
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl practice SQL > DESC ages_2; +----------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------+------+-----+---------+-------+ | some_age | int | YES | | NULL | | +----------+------+------+-----+---------+-------+ 1 row in set (0.0039 sec) |
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl practice SQL > DESC ages_3; +----------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------+------+-----+---------+-------+ | some_age | int | YES | | NULL | | +----------+------+------+-----+---------+-------+ 1 row in set (0.0039 sec) |
I want to rename each of the columns to something even more meaningless such as, ‘an_age’, and could easily type out the individual ALTER TABLE statements like this:
1 | ALTER TABLE table_name_here RENAME COLUMN some_age TO an_age; |
Granted 3 ALTER TABLE statements for 3 tables is not that much work. But, what if there were 10 tables? Or 20? In that instance, I would have to type out those ALTER TABLE commands individually. More-so than I care to do. Luckily, I don’t have to. Here is one such situation where the power of MySQL Shell Python mode (\py) shines.
First, I’ll create a Python list of table name objects using the get_tables() method in the current working ‘practice’ database/schema:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl practice Py > tables = db.get_tables() MySQL localhost:33060+ ssl practice Py > tables [ <table:ages_1>, <table:ages_2>, <table:ages_3> ] |
Since there is no MySQL Shell ALTER TABLE command or method (to my knowledge at the time of writing), we have to use the .sql() method for this type of MySQL statement:
1 2 3 | MySQL localhost:33060+ ssl practice Py > session = db.get_session() MySQL localhost:33060+ ssl practice Py > session <Session:j2112o@localhost:33060> |
Visit the online .sql() documentation for more information on this method.
Each element in the ‘tables’ list is actually an shell.Object as shown below by calling the type() function on an element (the 1st element in this example):
1 2 | MySQL localhost:33060+ ssl practice Py > type(tables[0]) <class 'shell.Object'> |
That being said, if I try and use that shell.Object name directly in the ALTER TABLE statement, I get an error:
1 2 3 4 5 | MySQL localhost:33060+ ssl practice Py > for table in tables: -> session.sql('ALTER TABLE {} RENAME COLUMN some_age TO an_age'.format(table)).execute() Traceback (most recent call last): File "<string>", line 2, in <module> mysqlsh.DBError: MySQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<table:ages_1> RENAME COLUMN some_age TO an_age' at line 1 |
Which makes total sense as there is no table named ‘ALTER TABLE statements out by hand and be doomed to misery? Script it out in another language/environment? Absolutely not.
As I learned while creating this post, the get_name() Table class method is used to extract the actual name from a table shell.Object.
Shown below, chaining on the get_names() method to the 1st ‘tables’ list element (0-eth index), returns the actual str (string) name of that object:
1 2 3 4 | MySQL localhost:33060+ ssl practice Py > tables[0].get_name() ages_1 MySQL localhost:33060+ ssl practice Py > type(tables[0].get_name()) <class 'str'> |
Using get_name() we can construct the Python code to execute the ALTER TABLE statement for each table, including the actual table name in the command instead of the object name, as in the previous shown failed attempt:
1 2 3 4 5 6 | MySQL localhost:33060+ ssl practice Py > for table in tables: -> table_name = table.get_name() -> session.sql('ALTER TABLE {} RENAME COLUMN some_age TO an_age'.format(table_name)).execute() Query OK, 0 rows affected (0.1524 sec) Records: 0 Duplicates: 0 Warnings: 0 |
Once execution is complete, reviewing the tables’ structures we can see those multiple ALTER TABLE commands were successful. Each tables’ single column has been renamed:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl practice SQL > DESC ages_1; +--------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------+------+-----+---------+-------+ | an_age | int | YES | | NULL | | +--------+------+------+-----+---------+-------+ 1 row in set (0.0027 sec) |
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl practice SQL > DESC ages_2; +--------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------+------+-----+---------+-------+ | an_age | int | YES | | NULL | | +--------+------+------+-----+---------+-------+ 1 row in set (0.0027 sec) |
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl practice SQL > DESC ages_3; +--------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------+------+-----+---------+-------+ | an_age | int | YES | | NULL | | +--------+------+------+-----+---------+-------+ 1 row in set (0.0027 sec) |
No more typing out many ALTER TABLE statements individually to rename same-named columns across multiple tables. Just use the powerful MySQL Shell in Python mode, along with the necessary methods and you are good to go. Easy as can be!
Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
One thought on “MySQL Shell Python mode for multiple ALTER TABLE statements – easily”