How to Copy a Table in MySQL Using Python

In this article, we show how to copy a table in MySQL using Python.

What is meant by copying a table is exactly what's implied. We copy the entire table, including all the columns, the definition of the columns, as well as all rows of data in the table.

So, everything gets transferred over.

Thus, if we have a table with 2 columns, of firstname VARCHAR(50), lastname VARCHAR(50), and phonenumber INT(10), the new table will have all these same exact columns with the same exact definitions. Plus, if there are rows of data in the table, these get copied over as well. Thus, if there are entries, Robert Smith with a phone number of (000) 123-4567, Kelly Walkers with a phone numbers (000) 000-0000, and Rachel Morris with a phone number (111) 111-1111, these rows of data will appear in the new table.

So, we would copy a table, simply if we just want a duplicate, such as a backup copy.

So, the general code to copy a MySQL table using Python is shown below.

cursor.execute("CREATE TABLE Table_new AS SELECT * FROM Table_original")

The full code that copies a MySQL table using Python is shown below. In this code, we copy the table Students1 and create the new table Students2.

import pymysql pymysql.install_as_MySQLdb() import MySQLdb db= MySQLdb.connect("hostname", "username", "password", "database_name") cursor= db.cursor() cursor.execute("CREATE TABLE Students2 AS SELECT * FROM Students1") db.close()

So, this is all the code that is needed to copy a MySQL table with Python.

So, the first thing we have to do is import the MySQLdb.

Once we have MySQLdb imported, then we create a variable named db. We set db equal to the MySQLdb.connect() function. This function will allow us to connect to a database. In side of this MySQLdb.connect() function are 4 parameters. These are, MySQLdb.connect("hostname", "username", "password", "database_name"). This line establishes connection with the database that you want.

We then create a variable named cursor, which we set equal to db.cursor(). This establishes a cursor for the database.

Next, we execute our function to copy a table definition using the cursor.execute() function. Inside of this function, we place in the line, "CREATE TABLE Students2 AS SELECT * FROM Students1". So the whole line of code, is, cursor.execute("CREATE TABLE Students2 AS SELECT * FROM Students1"). Students1 is the original table, while Students2 is the new table created.

If the function executes and the table definition is successfully copied, a number will be output, which represents the number of rows that have been copied. Thus, if 2 rows were copied over, a '2' will be output.

And this is how you can copy a table in MySQL using Python.

Related Resources

How to Show All Tables of a MySQL Database in Python

How to Count the Number of Rows in a MySQL Table in Python