How to Rename a MySQL Table in Python?

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

# 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()

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

# 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()

Output: