How to copy a table in MySQL using Python?

PythonServer Side ProgrammingProgramming

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

Syntax

CREATE TABLE table_name SELECT * FROM existing_table

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

Steps to copy a table using MySQL in python

  • import MySQL connector

  • establish connection with the connector using connect()

  • create the cursor object using cursor() method

  • create a query using the appropriate mysql statements

  • execute the SQL query using execute() method

  • close the connection

Suppose, we have a table named “Students” as follows

+----------+---------+-----------+------------+
|    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

We want to create copy of the above table . Let the name of the copied table be “CopyStudents”.

import mysql.connector
db=mysql.connector.connect(host="your host", user="your username", password="your
password",database="database_name")

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)
db.close()

Output

(‘Karan’, 4 ,’Amritsar’ , 95)
(‘Sahil’ , 6 , ‘Amritsar’ ,93)
(‘Kriti’ , 3 , ‘Batala’ ,88)
(‘Amit’ , 9 , ‘Delhi’ , 90)
(‘Priya’ , 5 , ‘Delhi’ ,85)

All the rows, columns and column definitons from table “Students” are copied into the “CopyStudents” table.

raja
Published on 10-Jun-2021 13:11:53
Advertisements