In this article, we will create a table in MySQL and will create a copy of that table using Python. We will copy the entire table, including all the columns and the definition of the columns, as well as all rows of data in the table.
To connect to MySQL database using python, we needPyMySql module. The cursor class allows python to execute SQL commands. Cursors are created byconnection_name.cursor() method, whereconnection_name is the link made to SQL Database. Once the connection is established, cursor.execute() is used to run the SQL statements.
Let us understand the above by taking an example. Suppose, in MySQL we create a databasetest and it contains a table namedgeeksforgeeksand has the below schema and following data:
SQL DatabaseTo Copy a Table in MySQL we use the below query:
CREATE TABLE table-name SELECT * FROM table-name;
Now, below is the program to copy the entire table using python:
Python3# import required modulesimportpymysql# establish connection to SQL databaseconnection=pymysql.connect(# specify hostnamehost="localhost",# specify user of mysql databaseuser="root",# specify password for above userpassword="1234",# default port number for mysql is 3306port=3306,# specify database name on which you want to workdb="test")# make a cursormycursor=connection.cursor()# create a new table geeksforgeekscopy and copy all# records from geeksforgeeks into the newly created tablemycursor.execute("create table geeksforgeekscopy select * from geeksforgeeks")# list all the tablesmycursor.execute("Show tables")# fetchall() will store all the names# of tables into query1query1=mycursor.fetchall()# print name of tablesforiinquery1:print(i)# read all records from copy tablemycursor.execute("Select * from geeksforgeekscopy")# fetchall() will store all the records# of copy table into query2query2=mycursor.fetchall()# print all recordsforiinquery2:print(i)Output:
Python outputIn the above figure, we could see the list of tables followed by all records from thegeeksforgeekscopy table. The above output has also been confirmed by providing the output of MySQL database.
MySQL OutputHere is another example which depicts how to create a new table from the data and schema of a previous table. Below is the previously existing table:
Now, using the below script to create a copy of the above table in the database:
Python3# import required modulesimportpymysql# connect python with mysql with your hostname,# username, password and databaseconnection=pymysql.connect("localhost","root","","geek")# make a cursormycursor=connection.cursor()# create a new table and copy all records from# previous table into the newly created tablemycursor.execute("create table geeksdemocopy select * from geeksdemo")# list all the tablesmycursor.execute("Show tables")# fetchall() will store all the names of tables into query1query1=mycursor.fetchall()# print name of tablesforiinquery1:print(i)# read all records from copy tablemycursor.execute("Select * from geeksdemocopy")# fetchall() will store all the records of copy table into query2query2=mycursor.fetchall()# print all recordsforiinquery2:print(i)Output:
Below is the new table whose data and schema are copied from the previous table:
