How to Copy a Table in MySQL Using Python?
In this article, we will create a table in MySQL and will create a copy of that table using Python. We will copy the entire table, including all the columns and the definition of the columns, as well as all rows of data in the table.
To connect to MySQL database using python, we need PyMySql module. The cursor class allows python to execute SQL commands. Cursors are created by connection_name.cursor() method, where connection_name is the link made to SQL Database. Once the connection is established, cursor.execute() is used to run the SQL statements.
Let us understand the above by taking an example. Suppose, in MySQL we create a database test and it contains a table named geeksfoegeeks and has the below schema and following data:
To Copy a Table in MySQL we use the below query:
CREATE TABLE table-name SELECT * FROM table-name;
Now, below is the program to copy the entire table using python:
In the above figure, we could see the list of tables followed by all records from the geeksforgeekscopy table. The above output has also been confirmed by providing the output of MySQL database.
Here is another example which depicts how to create a new table from the data and schema of a previous table. Below is the previously existing table:
Now, using the below script to create a copy of the above table in the database:
Below is the new table whose data and schema are copied from the previous table:
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. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course