Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to copy a table in MySQL using Python?
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.
2K+ Views
