How to Copy a Table Definition in MySQL Using Python

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

What is meant by copying a table definition is that we copy the exact structure of a table. This means the new table will have all of the columns as the copied table and each column will have the same exact definitions.

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.

None of the entries in the original table will appear in the new table. Only the structure and definitons are copied. If the original table has any records in it, none will appear in the new table.

So, we would copy a table definition if we want a table exactly like one we already have in our database or one that is close that we can easily modify to what we want.

So, the general code to copy the definition of a MySQL table in Python is shown below.

cursor.execute("CREATE TABLE NewTable AS SELECT * FROM Table_Original WHERE 1=0")

The full code that copies the table definition of 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 WHERE 1=0") db.close()

So, this is all the code that is needed to copy the table definition of a MySQL table in 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 WHERE 1=0". So the whole line of code, is, cursor.execute("CREATE TABLE Students2 AS SELECT * FROM Students1 WHERE 1=0"). Students1 is the original table, while Students2 is the new table created that has the copied structure and definition of Students1.

If the function executes and the table definition is successfully copied, a '0' will be output.

And this is how you can copy a table definition 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