Skip to content
Related Articles

Related Articles

How to Rename a MySQL Table in Python?
  • Last Updated : 08 Dec, 2020

MySQL Connector-Python module is an API in python used to communicate with a MySQL database server. It only requires the standard Python libraries and has no additional dependencies. There are various other modules in Python like PyMySQL and mysqlclient which can be used to access a database server. In this article, we will use MySQL Connector-Python to execute MySQL queries, particularly renaming a table, through Python. 

Renaming a Table in SQL

ALTER and RENAME statements are used to rename a table. While renaming a table, one must keep in mind the other database objects like views, stored procedures, triggers, etc., that might be referenced to the table and adjust them manually.

Syntax of RENAME statement:

RENAME TABLE table_name to new_table_name

Here, table_name is the existing table that needs to be renamed and the new_table_name is the new name to be given to the existing table. Also, this new name should not be a duplicate of any other existing tables.



Syntax of ALTER statement:

ALTER TABLE table_name
RENAME to new_table_name

This statement is similar to RENAME statement. However, unlike RENAME, it can rename temporary tables as well.

Implementation:

First, establish a connection with the database server and create a cursor object with connect() and cursor() functions of the MySQL Connector-Python module in Python. Then use the RENAME or ALTER statements to change the name of a table. Below are some examples for better understanding.

Database in use:

We will use a sample database store with a products table, buyers table and staff table for the examples.

Example 1: Rename using ALTER statement



  • Step 1: Establish connection to store database using connect() function.
  • Step 2: Create a cursor object to interact with the database using cursor() function.
  • Step 3: Use the ALTER statement to rename the staff table as employees.
  • Step 4: To check if the table has been renamed, execute SHOW TABLES command. This displays the names of all tables in the database.

Python3

filter_none

edit
close

play_arrow

link
brightness_4
code

# Import required packages
import mysql.connector
  
# Establish connection to MySQL database
mydb = mysql.connector.connect(
    host="localhost",
    user="username",
    password="geeksforgeeks",
    database="store"
)
  
# Create a cursor object
mycursor = mydb.cursor()
  
# MySQL query for for renaming a table
query = "ALTER TABLE staff RENAME to employees"
# Execute the query
mycursor.execute(query)
  
# Print names of all tables in the database
mycursor.execute("SHOW TABLES")
myresult = mycursor.fetchall()
for row in myresult:
    print(row)
  
# Close database connection
mydb.close()

chevron_right


Output:

Example 2: Rename using RENAME statement

Follow the same steps as the above example for establishing a connection with the database server and creating a cursor object. Then execute the SQL query with RENAME statement. This command allows renaming multiple tables at once. This example renames the products table to inventory and the buyers table to customers.

Python3

filter_none

edit
close

play_arrow

link
brightness_4
code

# Import required packages
import mysql.connector
  
# Establish connection to MySQL database
mydb = mysql.connector.connect(
    host="localhost",
    user="username",
    password="geeksforgeeks",
    database="store"
)
  
# Create a cursor object
mycursor = mydb.cursor()
  
# MySQL query for renaming a table
query = "RENAME TABLE products to inventory,\
                      buyers to customers"
# Execute the query
mycursor.execute(query)
  
# Print names of all tables in the database
mycursor.execute("show tables")
myresult = mycursor.fetchall()
for row in myresult:
    print(row)
  
# Close database connection
mydb.close()

chevron_right


Output:


Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course.

My Personal Notes arrow_drop_up
Recommended Articles
Page :