Python MySQL - Drop Table

A connector is employed when we have to use MySQL with other programming languages. The work of MySQL-connector is to provide access to MySQL Driver to the required language. Thus, it generates a connection between the programming language and the MySQL Server.

Drop Table Command

Drop command affects the structure of the table and not data. It is used to delete an already existing table. For cases where you are not sure if the table to be dropped exists or not DROP TABLE IF EXISTS command is used.

Syntax:

DROP TABLE tablename;
DROP TABLE IF EXISTS tablename;

• DROP TABLE tablename: Removes the specified table from the database.
• DROP TABLE IF EXISTS tablename: Drops the table only if it exists, avoiding an error if the table is not present.

Below is a table for example, the following examples will operate on this:

Example 1: Drop Table If Exists

In this example, we will demonstrate how to use the DROP TABLE IF EXISTS command. This ensures that no error is raised if the table to be dropped does not exist.

import mysql.connector

# Connecting to the Database
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="your_password"
  database="College"
)

cs = mydb.cursor()

# Drop table if it exists
statement = "DROP TABLE IF EXISTS Employee"

# Executing the query
cs.execute(statement)
mydb.commit()

# Disconnecting from the database
mydb.close()

Database after running the program:

Example 2: Drop Table Without Condition

This example shows how to drop a table directly using the DROP TABLE command without checking if it exists. If the table does not exist, an error will be raised.

import mysql.connector

# Connecting to the Database
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="your_password",
  database="College",
  
)

cs = mydb.cursor()

# Drop table Employee
statement = "DROP TABLE Employee"

# Executing the query
cs.execute(statement)
mydb.commit()

# Disconnecting from the database
mydb.close()

Output in the terminal:

Explanation:

As we have already dropped the Employee table, running the above code gives error- "mysql.connector.errors.ProgrammingError: 1051 (42S02): Unknown table 'college.employee'".