Python - Database Create & Drop Table

In this application, we will create a table named employees in the Oracle Database.

The employees table contains the following columns:

• eno → Employee Number
• ename → Employee Name
• esal → Employee Salary
• eaddr → Employee Address

This application also demonstrates how to use:

• try
• except
• finally
• cursor()
• execute()
• rollback()
• close()

These concepts help us perform Database operations safely.

Python Program
      │
      ▼
Connect to Oracle
      │
      ▼
Create Cursor
      │
      ▼
Execute CREATE TABLE
      │
      ▼
Create employees Table

The program creates the following employees table:

Column	Data Type
eno	number
ename	varchar2(10)
esal	number(10,2)
eaddr	varchar2(10)

employees
+---------------------------+
| eno    : number           |
| ename  : varchar2(10)     |
| esal   : number(10,2)     |
| eaddr  : varchar2(10)     |
+---------------------------+

The purpose of each column is:

Column	Purpose	Data Type
eno	Stores employee number	number
ename	Stores employee name	varchar2(10)
esal	Stores employee salary	number(10,2)
eaddr	Stores employee address	varchar2(10)

The complete program to create the employees table in Oracle Database is:

The program performs the following operations:

Import cx_Oracle
      │
      ▼
Connect to Oracle
      │
      ▼
Create Cursor
      │
      ▼
Execute CREATE TABLE
      │
      ▼
Create employees Table
      │
      ▼
Display Success Message
      │
      ▼
Close Resources

If an Oracle Database error occurs, the program handles it using the except block.

Table created successfully

The first statement of the program is:

import cx_Oracle

The cx_Oracle module is imported to communicate with the Oracle Database.

Python
  │
  ▼
cx_Oracle
  │
  ▼
Oracle Database

The Database operations are written inside a try block:

try:
    ...

The try block contains the code that may generate an Oracle Database error. In this application, the following operations are performed inside try:

• Connect to Oracle Database
• Create the Cursor object
• Execute the CREATE TABLE statement
• Display the success message

try
 │
 ├── Connect
 ├── Create Cursor
 ├── Execute SQL
 └── Print Success Message

The connection is created using:

con = cx_Oracle.connect('scott/tiger@localhost')

A Connection object is created using the following details:

Detail	Value
Username	scott
Password	tiger
Database	localhost

cx_Oracle.connect(
'scott/tiger@localhost'
)
        │
        ▼
Connect to Oracle
        │
        ▼
Connection Object
        │
        ▼
       con

After establishing the Database connection, the program creates a Cursor object:

cursor = con.cursor()

The Cursor object is required to execute SQL statements.

Connection Object
      │
      ▼
con.cursor()
      │
      ▼
Cursor Object
      │
      ▼
cursor

In Part 4, we only printed the Database version, so a Cursor was not required. In this application, however, we need to execute an SQL statement:

CREATE TABLE employees

Therefore, a Cursor object is required.

Need to Execute SQL?
        │
        ▼
       Yes
        │
        ▼
Create Cursor Object
        │
        ▼
cursor = con.cursor()

The program executes the following SQL statement:

cursor.execute(
    "create table employees("
    "eno number,"
    "ename varchar2(10),"
    "esal number(10,2),"
    "eaddr varchar2(10))"
)

The execute() method executes the SQL statement that creates the employees table.

If we write the SQL statement separately, it looks like this:

create table employees(
    eno number,
    ename varchar2(10),
    esal number(10,2),
    eaddr varchar2(10)
)

CREATE TABLE is an SQL statement used to create a new table in a Database.

General Syntax

CREATE TABLE table_name(
    column1 datatype,
    column2 datatype,
    ...
)

In this Program

CREATE TABLE employees(
    eno number,
    ename varchar2(10),
    esal number(10,2),
    eaddr varchar2(10)
)

The Cursor object sends the SQL statement to Oracle Database for execution:

cursor
   │
   ▼
execute(SQL)
   │
   ▼
CREATE TABLE employees
   │
   ▼
Oracle Database
   │
   ▼
employees Table Created

After executing the CREATE TABLE statement, the program executes:

print("Table created successfully")

If the SQL statement executes successfully, the following message is displayed:

Table created successfully

1. eno - Employee Number

eno number

eno represents the Employee Number. Its data type is number. Therefore, it is used to store numeric employee numbers.

2. ename - Employee Name

ename varchar2(10)

ename represents the Employee Name. Its data type is varchar2(10). It stores character data with a maximum size of 10 characters in the table definition used by the tutorial.

3. esal - Employee Salary

esal number(10,2)

esal represents Employee Salary. The source program uses number(10,2). This allows numeric salary values with decimal precision according to the Oracle numeric definition.

4. eaddr - Employee Address

eaddr varchar2(10)

eaddr represents Employee Address. Its data type is varchar2(10). It stores character data with the size specified in the source table definition.

The program uses try, except, and finally for safe Database programming.

             try
              │
              ▼
       Database Operation
              │
              ▼
          Error?
          /            No      Yes
        │        │
        ▼        ▼
     Success   except
        │        │
        └───┬────┘
            ▼
          finally
            │
            ▼
      Close Resources

The exception block is:

except cx_Oracle.DatabaseError as e:

If an Oracle Database error occurs inside the try block, execution moves to this except block. The error object is stored in e.

Part	Meaning
cx_Oracle.DatabaseError	Oracle Database-related exception class used in the program
as e	Stores the exception object in variable e

Inside the except block, the program checks:

if con:
    con.rollback()

rollback() is used to roll back temporary Database changes when an error occurs.

Database Error
      │
      ▼
Is con Available?
      │
      ▼
     Yes
      │
      ▼
con.rollback()
      │
      ▼
Rollback Changes

The condition checks whether a usable Connection object is available before attempting to call rollback():

if con
  │
  ├── True  → con.rollback()
  │
  └── False → Skip rollback()

After rollback, the program executes:

print("There is a problem with sql", e)

This displays the SQL-related error information stored in e. The general output structure is:

There is a problem with sql <Oracle Database error>

For example, if the employees table already exists, executing the CREATE TABLE statement again can result in an Oracle Database error:

CREATE TABLE employees
        │
        ▼
Does employees already exist?
       /      No   Yes
     │     │
     ▼     ▼
 Create  DatabaseError
 Table      │
            ▼
          except

The exact Oracle error depends on the Database environment.

The program contains:

finally:

The finally block executes whether an exception occurs or not. It is used here to close Database resources.

try
 │
 ├── Success ───┐
 │              │
 └── Error ─────┤
                ▼
             finally
                │
                ▼
        Close Resources

Inside the finally block, the Cursor is closed first:

if cursor:
    cursor.close()

If the Cursor object exists, it is closed using cursor.close(). This releases the Cursor resource.

After closing the Cursor, the program closes the Connection:

if con:
    con.close()

The Database connection is closed using con.close(). This releases the Database connection resource.

The Cursor was created after the Connection, and resources are normally closed in the reverse order:

Connection        Cursor
   │                │
   ▼                ▼
Cursor         Connection

Therefore, the program performs:

cursor.close()
con.close()

The following methods are used in the program:

Method	Purpose
connect()	Creates a Database connection
cursor()	Creates a Cursor object
execute()	Executes the SQL query
rollback()	Cancels temporary changes if an error occurs
cursor.close()	Closes the Cursor
con.close()	Closes the Database connection

The important objects and variables used are:

Object / Variable	Purpose
con	Stores the Oracle Connection object
cursor	Stores the Cursor object used to execute SQL
e	Stores the Oracle Database exception object

CREATE TABLE is the SQL statement that defines and creates the table, while cursor.execute() is the Python Database API operation used to send the SQL statement for execution:

Python
  │
  ▼
cursor.execute()
  │
  ▼
CREATE TABLE employees(...)
  │
  ▼
Oracle Database
  │
  ▼
employees Table

The complete try-except-finally flow of the program is:

START
  │
  ▼
Import cx_Oracle
  │
  ▼
try
  │
  ▼
Create Connection
  │
  ▼
Create Cursor
  │
  ▼
Execute CREATE TABLE
  │
  ▼
SQL Successful?
  │
 ┌┴──────────────┐
 │               │
Yes              No
 │               │
 ▼               ▼
Print          DatabaseError
"Table created    │
successfully"     ▼
 │             except
 │               │
 │          if con:
 │          rollback()
 │               │
 │          Print Error
 │               │
 └───────┬───────┘
         ▼
       finally
         │
         ▼
    Close Cursor
         │
         ▼
  Close Connection
         │
         ▼
        END

In the previous application, we created an employees table in Oracle Database. Now we will learn how to drop the employees table from Oracle Database using a Python program.

To remove an existing table, SQL provides the:

DROP TABLE

command.

DROP TABLE is an SQL command used to remove an existing table from a Database.

General Syntax

DROP TABLE table_name

For the employees table:

DROP TABLE employees

After this statement is executed successfully, the employees table is removed from the Database.

SQL Command	Purpose
CREATE TABLE	Creates a new table
DROP TABLE	Removes an existing table

CREATE TABLE employees
         │
         ▼
 employees Created


DROP TABLE employees
         │
         ▼
 employees Removed

This application demonstrates connecting Python with Oracle Database, creating a Cursor object, executing a DROP TABLE SQL statement, handling Database errors, using try-except-finally, using rollback(), and closing Cursor and Connection resources.

Python Program
      │
      ▼
Connect to Oracle
      │
      ▼
Create Cursor
      │
      ▼
Execute DROP TABLE
      │
      ▼
employees Table Removed

The program follows the same Database-programming structure used in the previous application. The important SQL statement in this application is drop table employees.

The program contains four major sections:

1. Import the Oracle module
2. Perform Database operations inside try
3. Handle Database errors inside except
4. Close resources inside finally

import cx_Oracle
       │
       ▼
      try
       │
       ├── Connect
       ├── Create Cursor
       ├── DROP TABLE
       └── Print Success
       │
       ▼
     except
       │
       ├── rollback()
       └── Print Error
       │
       ▼
     finally
       │
       ├── Close Cursor
       └── Close Connection

Table dropped successfully

The program starts with:

import cx_Oracle

This imports the Oracle Database module used throughout the source tutorial. It provides the functionality required to:

• Connect to Oracle Database
• Create Cursor objects
• Execute SQL statements
• Handle Oracle Database errors
• Manage transactions

Python Program
      │
      ▼
cx_Oracle
      │
      ▼
Oracle Database

The Database operations are placed inside a try block, because the code may generate a Database-related exception:

try:
    con = cx_Oracle.connect(...)

    cursor = con.cursor()

    cursor.execute("drop table employees")

    print("Table dropped successfully")

The program connects to Oracle Database using:

con = cx_Oracle.connect('scott/tiger@localhost')

The connection information is:

Part	Value
Username	scott
Password	tiger
Database / Location	localhost

cx_Oracle.connect(
'scott/tiger@localhost'
)
        │
        ▼
Oracle Database
        │
        ▼
Connection Established
        │
        ▼
Connection Object
        │
        ▼
       con

After creating the Connection object, the program creates a Cursor object:

cursor = con.cursor()

The Cursor is required because the SQL command DROP TABLE employees must be sent to Oracle Database for execution:

Connection
    │
    ▼
con.cursor()
    │
    ▼
Cursor Object
    │
    ▼
cursor

Therefore, cursor = con.cursor() is required before calling execute().

The most important statement in this application is:

cursor.execute("drop table employees")

The execute() method sends the SQL statement to Oracle Database. Its purpose is to remove the employees table.

Part	Meaning
cursor	Cursor object
execute()	Method used to execute an SQL statement
drop table employees	SQL statement being executed

cursor
   │
   ▼
execute()
   │
   ▼
"drop table employees"
   │
   ▼
Oracle Database
   │
   ▼
Locate employees Table
   │
   ▼
Drop Table
   │
   ▼
employees Removed

Before DROP TABLE

Oracle Database
      │
      ▼
+----------------------+
| employees            |
+----------------------+
| eno                  |
| ename                |
| esal                 |
| eaddr                |
+----------------------+

After DROP TABLE

Oracle Database
      │
      ▼
employees table
no longer exists

DROP TABLE removes the table definition and its associated table data according to Oracle's DROP TABLE behavior.

If the SQL statement executes successfully, the next statement is:

print("Table dropped successfully")

Therefore, the success message is:

Table dropped successfully

This indicates that execution reached the success statement after the DROP operation.

If an Oracle Database-related error occurs, the program uses:

except cx_Oracle.DatabaseError as e:

The exception object is stored in e. cx_Oracle.DatabaseError is the exception class used by the source program for Database-related problems:

try
 │
 ▼
Database Operation
 │
 ▼
Database Error?
 │
 ├── No  → Continue
 │
 └── Yes
        │
        ▼
cx_Oracle.DatabaseError
        │
        ▼
        e

One possible problem is attempting to drop the employees table when it does not exist:

DROP TABLE employees
        │
        ▼
Does employees exist?
      /         Yes      No
     │        │
     ▼        ▼
   Drop    Database
   Table    Error

When such a Database error occurs, control moves to the except block. The except block contains:

if con:
    con.rollback()

If a Connection object is available, the program calls con.rollback():

DatabaseError
      │
      ▼
if con
      │
      ▼
con.rollback()

After the rollback check, the program executes:

print("There is a problem with sql", e)

This prints the message There is a problem with sql along with the actual Oracle Database exception information stored in e.

Important Oracle DDL Note

DROP TABLE is a DDL (Data Definition Language) operation. Oracle performs implicit transaction handling around many DDL statements. Therefore, although the source program calls con.rollback() inside its general Database-error handler, you should not interpret this as meaning that a successfully executed Oracle DROP TABLE can normally be undone using rollback().

Operation Type	Examples	Typical Transaction Handling
DML	INSERT, UPDATE, DELETE	commit() / rollback()
DDL	CREATE TABLE, DROP TABLE	Oracle normally performs implicit commits around successful DDL

Finally, the program uses the finally block for resource cleanup. It executes whether the table is dropped successfully or a Database error occurs:

             try
              │
       ┌──────┴──────┐
       │             │
    Success        Error
       │             │
       │           except
       │             │
       └──────┬──────┘
              ▼
           finally
              │
              ▼
      Close Resources

The program checks whether the Cursor exists and closes it:

if cursor:
    cursor.close()

If it exists, the Cursor is closed and the Cursor resource is released.

The Connection is then closed:

if con:
    con.close()

If a Connection object exists, con.close() releases the Database connection.

The resources were created in the order Connection, then Cursor, and they are closed in reverse order:

Opening	Closing
Connection	Cursor
Cursor	Connection

Both applications follow the same Database-programming structure; they differ mainly in the SQL statement they execute.

Feature	App2	App3
Purpose	Create employees table	Drop employees table
SQL	CREATE TABLE	DROP TABLE
Connection	connect()	connect()
Cursor	cursor()	cursor()
Execute SQL	execute()	execute()
Error Handling	DatabaseError	DatabaseError
Cleanup	finally	finally

The important methods and statements used by both applications are:

Method / Statement	Purpose
cx_Oracle.connect()	Connects Python to Oracle Database
con.cursor()	Creates the Cursor
cursor.execute()	Executes the SQL statement
con.rollback()	Used by the source's Database-error handling pattern
cursor.close()	Closes the Cursor
con.close()	Closes the Database connection

The line-by-line explanation of the App3 program is:

Statement	Purpose
import cx_Oracle	Imports Oracle Database support
try:	Starts protected Database operations
con = cx_Oracle.connect(...)	Establishes the Database connection
cursor = con.cursor()	Creates a Cursor object
cursor.execute("drop table employees")	Drops the employees table
print("Table dropped successfully")	Displays the success message
except cx_Oracle.DatabaseError as e:	Handles Database errors
con.rollback()	Performs rollback as part of the source's general error-handling pattern
print(..., e)	Displays error information
finally:	Runs resource-cleanup code
cursor.close()	Closes the Cursor
con.close()	Closes the Connection

Here is a quick summary of both applications:

Topic	App2 - Create	App3 - Drop
Purpose	Create employees table	Drop employees table
SQL Command	CREATE TABLE employees	DROP TABLE employees
Connection	cx_Oracle.connect()	cx_Oracle.connect()
Cursor	con.cursor()	con.cursor()
Execution	cursor.execute()	cursor.execute()
Exception	cx_Oracle.DatabaseError	cx_Oracle.DatabaseError
Rollback	rollback()	rollback()
Cleanup	finally	finally
Close Resources	cursor.close() then con.close()	cursor.close() then con.close()

The important points to remember from App2 are:

• The program creates a table named employees.
• The table contains four columns: eno, ename, esal, and eaddr.
• The cx_Oracle module is used to communicate with Oracle Database.
• Use connect() to establish the Database connection.
• Use cursor() before executing SQL statements.
• The execute() method executes the CREATE TABLE query.
• The program uses try-except-finally for safe execution.
• cx_Oracle.DatabaseError handles Oracle Database-related errors.
• If an SQL error occurs, rollback() is executed when a connection is available.
• The error object is stored in e.
• The finally block executes whether an exception occurs or not.
• The Cursor and Connection are closed inside the finally block, with the Cursor closed before the Connection.

The important points to remember from App3 are:

1. DROP TABLE is used to remove an existing table.
2. The source application drops the employees table.
3. A Database connection is established before executing the SQL statement.
4. A Cursor object is required to execute the DROP TABLE statement.
5. If the operation succeeds, the program prints Table dropped successfully.
6. DROP TABLE is DDL. In Oracle, successful DDL normally involves implicit commit behavior, so a successful DROP should not be treated like ordinary DML that can simply be undone with rollback().
7. The finally block is used to release Database resources.
8. Dropping a table removes the table itself, so this operation should be performed carefully.