How to Rename a MySQL Table in Python? (original) (raw)

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

import mysql.connector

mydb = mysql.connector.connect(

`` host = "localhost" ,

`` user = "username" ,

`` password = "geeksforgeeks" ,

`` database = "store"

)

mycursor = mydb.cursor()

query = "ALTER TABLE staff RENAME to employees"

mycursor.execute(query)

mycursor.execute( "SHOW TABLES" )

myresult = mycursor.fetchall()

for row in myresult:

`` print (row)

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.