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:
- Establish a connection with the database server and create a cursor object.
- Use the cursor object to execute the CREATE-SELECT or CREATE-LIKE-INSERT statements to copy a table.
- Check if the table definition has been copied.
Let’s see some examples for better understanding.
Database in use:
We will use a store database with a products table describing the products and the available stock.
Example 1: Copy table definition using CREATE-SELECT statement
Use the connect() function to establish a connection with the database server and use the cursor() function to create a cursor object. With this cursor object, execute the CREATE-SELECT statement for creating a copy of the products table new inventory table using the execute() 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.
Python
# Import required packages import mysql.connector # Establish connection to MySQL database db = mysql.connector.connect( host = "localhost", user = "username", password = "neveropen", database = "store") # Create a cursor object cursor = db.cursor() # MySQL query for copying existing table, # selecting new table data and # describing new table structure queries = "CREATE TABLE inventory1 AS SELECT * FROM products;\ DESC inventory1;" # Execute the query results = cursor.execute(queries, multi = True) # Print data and description of newly created table for result in results: if result.with_rows: for row in result: print(row) # Close database connection db.close() |
Output:
All the values from the products table are copied to the inventory table. The structure, that is, the data types and columns are retained. However, the database objects like the primary key constraint of prod_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.
Python
# Import required packages import mysql.connector # Establish connection to MySQL database db = mysql.connector.connect( host="localhost", user="username", password="neveropen", database="store") # Create a cursor object cursor = db.cursor() # MySQL queries for copying existing table # without copying its data, # selecting new table data and # describing new table structure queries = "CREATE TABLE inventory2 AS \ SELECT * FROM products \ WHERE 1=0; \ DESC inventory2;" # Execute the query results = cursor.execute(queries, multi=True) # Print data and description of newly created table for result in results: if result.with_rows: for row in result: print(row) # Close database connection db.close() |
The 1 = 0 condition in the CREATE-SELECT query always evaluates false. So, the query returns an empty table, creating a new empty inventory 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.
Python
# Import required packages import mysql.connector # Establish connection to MySQL database db = mysql.connector.connect( host="localhost", user="username", password="neveropen", database="store") # Create a cursor object cursor = db.cursor() # MySQL queries for copying existing table, # selecting new table data and # describing new table structure queries = "CREATE TABLE inventory3 LIKE products; \ INSERT inventory3 SELECT * FROM products;\ DESC inventory3;" # Execute the query results = cursor.execute(queries, multi = True) # Print data and description of newly created table for result in results: if result.with_rows: for row in result: print(row) # Close database connection db.close() |
Output:
Notice that the primary key constraint of prod_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.
Python
# Import required packages import mysql.connector # Establish connection to MySQL database db = mysql.connector.connect( host="localhost", user="username", password="neveropen", database="store") # Create a cursor object cursor = db.cursor() # MySQL queries for copying existing table # without copying its data, # selecting new table data and # describing new table structure queries = "CREATE TABLE inventory4 LIKE products; \ DESC inventory4;" # Execute the query results = cursor.execute(queries, multi=True) # Print data and description of newly created table for result in results: if result.with_rows: for row in result: print(row) # Close database connection db.close() |
Output:
