How to delete a table from Oracle Database in Python

By Shubhodh Amaravadi

Hello Geek! we have already learned how to create tables, insert data and fetch data from the Oracle Database table using Python. In this tutorial, we will see how to delete a table from Oracle Database in Python.

This is a part of our Oracle Database tutorial in Python. Before diving into this tutorial, you may refer to the following articles for better understanding:

• Oracle Database connection in Python
• How to create tables, insert data and fetch data from the Oracle Database table using Python

Delete a table from Oracle Database in Python

Before deleting a table, we need to make sure that the table must exist in our database. Otherwise, the program raises the DatabaseError exception.

In our program, we will use the CodeSpeedy table, which is an existing table in our Database.

At first, we will create a connection object using cx_Oracle.connect( ) method and we will open this connection using with statement. Here con is a reference to the connection object.

with cx_Oracle.connect('Username/password')as con:

Now, create a con.cursor( ) object which is used to execute the SQL commands. Here cur is a reference to the cursor object.

cur=con.cursor()

Generally, in SQL the DROP command is used to delete a table from Oracle Database.

Using the cur.execute( ) method we will execute the SQL DROP command by passing the command to it.

cur.execute("DROP TABLE CodeSpeedy")

Python program to delete a table from Oracle Database

We will enclose our code in try & except block to handle the exceptions like DatabaseError.

import cx_Oracle
try:
    with cx_Oracle.connect('Username/password')as con:
        print("Connected")
        cur=con.cursor()
        cur.execute("DROP TABLE CodeSpeedy")
        print("Table Deleted")
                
except Exception as e:
    print("Error: ",str(e))

Output:

Connected
Table Deleted

If we try to access our CodeSpeedy table in the SQL command prompt, we will get the following error as we deleted it.

That’s it! Hope you understood the tutorial.

If you face any difficulties, feel free to post them below.