Open In App

How to Rename a MySQL Table in Python?

Last Updated : 08 Dec, 2020
Improve
Improve
Like Article
Like
Save
Share
Report

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:



Previous Article
Next Article

Similar Reads

Rename column name in MySQL using Python
In this article, we are going to see how we can rename or alter the name of a column table using Python. Python allows the integration of a wide range of database servers with applications. A database interface is required to access a database from Python. MySQL Connector-Python module is an API in python for communicating with a MySQL database. Ap
1 min read
Connect MySQL database using MySQL-Connector Python
While working with Python we need to work with databases, they may be of different types like MySQL, SQLite, NoSQL, etc. In this article, we will be looking forward to how to connect MySQL databases using MySQL Connector/Python.MySQL Connector module of Python is used to connect MySQL databases with the Python programs, it does that using the Pytho
2 min read
Rename multiple files using Python
Prerequisite: OS module in PythonIn Python3, rename() method is used to rename a file or directory. This method is a part of the os module and comes in extremely handy. Syntax for os.rename() : os.rename(src, dst) : src is source address of file to be renamed and dst is destination with the new name. Now say given n images in a folder having random
2 min read
Python | Pandas Dataframe.rename()
Python is a great language for doing data analysis, primarily because of the fantastic ecosystem of data-centric python packages. Pandas is one of those packages and makes importing and analyzing data much easier. Pandas rename() method is used to rename any index, column or row. Renaming of column can also be done by dataframe.columns = [#list]. B
3 min read
Python | Pandas TimedeltaIndex.rename
Python is a great language for doing data analysis, primarily because of the fantastic ecosystem of data-centric python packages. Pandas is one of those packages and makes importing and analyzing data much easier. Pandas TimedeltaIndex.rename() function set new names on index. It defaults to returning new index. Syntax : TimedeltaIndex.rename(name,
2 min read
Rename all file names in your directory using Python
Given multiple files in a directory having different names, the task is to rename all those files in sorted order. We can use OS module in order to do this operation. The OS module in Python provides functions for interacting with the operating system and provides a portable way of using operating system-dependent functionality. We can go to the cu
1 min read
Python | Pandas Series.rename()
Pandas series is a One-dimensional ndarray with axis labels. The labels need not be unique but must be a hashable type. The object supports both integer- and label-based indexing and provides a host of methods for performing operations involving the index. Pandas Series.rename() function is used to alter Series index labels or name for the given Se
2 min read
Python MongoDB- rename()
MongoDB is a cross-platform, document-oriented database that works on the concept of collections and documents. It stores data in the form of key-value pairs and is a NoSQL database program. The term NoSQL means non-relational. Refer to MongoDB and Python for an in-depth introduction to the topic. Now let's understand the use of rename() function i
2 min read
Build a Bulk File Rename Tool With Python and PyQt
In this tutorial, we will learn how to create a file renaming tool using Python and PyQt5. PyQt5 is a set of Python bindings for the cross-platform application framework Qt, which is used to create graphical user interfaces (GUIs). We will use the OS module to interact with the file system, and the QFileDialog and QMessageBox classes from PyQt5 to
7 min read
Rename Nested Field in Spark Dataframe in Python
In this article, we will discuss different methods to rename the columns in the DataFrame like withColumnRenamed or select. In Apache Spark, you can rename a nested field (or column) in a DataFrame using the withColumnRenamed method. This method allows you to specify the new name of a column and returns a new DataFrame with the renamed column. Requ
3 min read
Article Tags :
Practice Tags :