How to copy a table in MySQL using Python?

We can create a copy of an existing table in MySQL using Python. The entire table will be copied including the columns, column definitions, and all the rows of the table.

Syntax

CREATE TABLE table_name SELECT * FROM existing_table

Where table_name is the name of the new table to be created and existing_table is the name of the table to be copied.

Steps to Copy a Table

• Import MySQL connector

• Establish connection using connect()

• Create cursor object using cursor() method

• Create query using appropriate MySQL statements

• Execute the SQL query using execute() method

• Close the connection

Suppose we have a table named Students with the following data:

+--------+-------+-----------+-------+
| Name   | Class | City      | Marks |
+--------+-------+-----------+-------+
| Karan  |   4   | Amritsar  |  95   |
| Sahil  |   6   | Amritsar  |  93   |
| Kriti  |   3   | Batala    |  88   |
| Khushi |   9   | Delhi     |  90   |
| Kirat  |   5   | Delhi     |  85   |
+--------+-------+-----------+-------+

Example

Let's create a copy of the above table named CopyStudents:

import mysql.connector

# Establish connection
db = mysql.connector.connect(
    host="localhost",
    user="your_username", 
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# Copy table Students into CopyStudents
query = "CREATE TABLE CopyStudents SELECT * FROM Students"
cursor.execute(query)

# Select rows from the new table
query1 = "SELECT * FROM CopyStudents"
cursor.execute(query1)

# Print the contents of the copied table
for row in cursor:
    print(row)

# Close the connection
db.close()

Output

('Karan', 4, 'Amritsar', 95)
('Sahil', 6, 'Amritsar', 93)
('Kriti', 3, 'Batala', 88)
('Khushi', 9, 'Delhi', 90)
('Kirat', 5, 'Delhi', 85)

Alternative Method: Copy Structure Only

If you want to copy only the table structure without data, use:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password", 
    database="your_database"
)

cursor = db.cursor()

# Copy only table structure
query = "CREATE TABLE EmptyStudents LIKE Students"
cursor.execute(query)

print("Table structure copied successfully!")
db.close()

Key Points

• CREATE TABLE ... SELECT * copies both structure and data

• CREATE TABLE ... LIKE copies only the table structure

• Primary keys and indexes may need to be recreated separately

• Always close database connections after operations

Conclusion

Copying MySQL tables in Python is straightforward using the CREATE TABLE ... SELECT statement. This method preserves all data, columns, and column definitions from the original table.