Python requires an interface to access a database server. Python supports a wide range of interfaces to interact with various databases. To communicate with a MySQL database,MySQL Connector Python module, an API written purely in Python, is used. This module is self-sufficient meaning that it does not have dependencies and only requires the standard Python library. 

Copying a table definition in SQL:

With the CREATE and SELECT statements, one can copy the definition and data from an existing table to a new table.

Syntax:

CREATE TABLE new_table AS SELECT * FROM original_table;

Here, the contents of an existing table (original_table) are copied to a new table (new_table). The CREATE statement creates a new table having the structure defined by the SELECT statement and populates the new table with the selected columns (* indicates all columns are selected). 

Note:The database objects associated with the original table like indexes, key constraints etc., are not duplicated.

To copy a table along with its dependent database objects, the CREATE, LIKE and INSERT statements are used.

Syntax:

CREATE TABLE new_table LIKE original_table;INSERT new_table SELECT * FROM original_table;

First, the CREATE statement creates a new table (new_table) having the same structure and dependent objects as the existing table (original_table). Then the INSERT statement populates the new table with the values selected from the original table. Simply using the CREATE statement without the INSERT will create an empty table having the structure and dependent objects of the existing table.

Steps using Python:

1. Establish a connection with the database server and create a cursor object.
2. Use the cursor object to execute the CREATE-SELECT or CREATE-LIKE-INSERT statements to copy a table.
3. Check if the table definition has been copied.

Let's see some examples for better understanding.

Database in use:

We will use astore database with aproducts table describing the products and the available stock.

Example 1:Copy table definition using CREATE-SELECTstatement

Use theconnect() function to establish a connection with the database server and use thecursor() function to create a cursor object. With this cursor object, execute the CREATE-SELECT statement for creating a copy of theproducts table newinventory table using theexecute() function. To check if the newly created table has the same table definition as the original, use the DESC statement to describe the structure and the SELECT statement to check the table contents.

# Import required packagesimportmysql.connector# Establish connection to MySQL databasedb=mysql.connector.connect(host="localhost",user="username",password="geeksforgeeks",database="store")# Create a cursor objectcursor=db.cursor()# MySQL query for copying existing table,# selecting new table data and# describing new table structurequeries="CREATE TABLE inventory1 AS SELECT * FROM products;\           DESC inventory1;"# Execute the queryresults=cursor.execute(queries,multi=True)# Print data and description of newly created tableforresultinresults:ifresult.with_rows:forrowinresult:print(row)# Close database connectiondb.close()

Output:

All the values from theproducts table are copied to theinventory table. The structure, that is, the data types and columns are retained. However, the database objects like the primary key constraint ofprod_id is not retained. 

Example 2:Copy table definition without table content using CREATE-SELECT statement

Use the same code for establishing a connection and verification as the above example. To simply copy the table definition and not the contents, add a WHERE clause to the SELECT statement such that it returns an empty set and no values are copied as follows.

# Import required packagesimportmysql.connector# Establish connection to MySQL databasedb=mysql.connector.connect(host="localhost",user="username",password="geeksforgeeks",database="store")# Create a cursor objectcursor=db.cursor()# MySQL queries for copying existing table# without copying its data,# selecting new table data and# describing new table structurequeries="CREATE TABLE inventory2 AS\           SELECT * FROM products\           WHERE 1=0;\           DESC inventory2;"# Execute the queryresults=cursor.execute(queries,multi=True)# Print data and description of newly created tableforresultinresults:ifresult.with_rows:forrowinresult:print(row)# Close database connectiondb.close()

The1 = 0 condition in the CREATE-SELECT query always evaluates false. So, the query returns an empty table, creating a new emptyinventory table with the same structure as the existing products table.

Output:

Example 3:Copy table definition and dependent database objects using CREATE-LIKE statement

Use the same code for establishing a connection and verification as Example 1. To copy a table with all its dependent objects and contents, use the CREATE-LIKE-INSERT statement as shown below.

# Import required packagesimportmysql.connector# Establish connection to MySQL databasedb=mysql.connector.connect(host="localhost",user="username",password="geeksforgeeks",database="store")# Create a cursor objectcursor=db.cursor()# MySQL queries for copying existing table,# selecting new table data and# describing new table structurequeries="CREATE TABLE inventory3 LIKE products;\           INSERT inventory3 SELECT * FROM products;\           DESC inventory3;"# Execute the queryresults=cursor.execute(queries,multi=True)# Print data and description of newly created tableforresultinresults:ifresult.with_rows:forrowinresult:print(row)# Close database connectiondb.close()

Output:

Notice that the primary key constraint ofprod_id is retained.

Example 4:Copy table definition and dependent objects without table data using CREATE-LIKE statement

Use the same code for establishing a connection and verification as Example 1. To simply copy the table definition and not the contents, remove the INSERT statement as shown below.

# Import required packagesimportmysql.connector# Establish connection to MySQL databasedb=mysql.connector.connect(host="localhost",user="username",password="geeksforgeeks",database="store")# Create a cursor objectcursor=db.cursor()# MySQL queries for copying existing table# without copying its data,# selecting new table data and# describing new table structurequeries="CREATE TABLE inventory4 LIKE products;\           DESC inventory4;"# Execute the queryresults=cursor.execute(queries,multi=True)# Print data and description of newly created tableforresultinresults:ifresult.with_rows:forrowinresult:print(row)# Close database connectiondb.close()

Output:

Similar Reads