How to create table, insert data and fetch data in Oracle Database using Python

Hello coder! we have already learned how to connect the Oracle database to our python program. In this tutorial, we will learn how to create tables, insert data and fetch data in the Oracle database using Python program.

Create a table in Oracle Database using Python

First of all, we will create the cursor() object cur which is used to execute the SQL commands in our program. Here, co is a connection object.

cur = co.cursor()

The cur.execute() method is used to execute any command that includes DML(Data Manipulation Language) or DDL(Data Definition Language).

So, we will pass the SQL CREATE TABLE command to this method. Since it is a DDL command, it gets committed automatically.

cur.execute('''CREATE TABLE
                       CodeSpeedy(employee_id number(10),employee_name varchar2(10))''')

After executing the commands, it is a best practice to close the cursor object.

cur.close()

Python program to create a table in Oracle database

import cx_Oracle
try:
    with cx_Oracle.connect('Username/password')as co:
        print("Connected")
        cur=co.cursor()
        cur.execute('''CREATE TABLE
                       CodeSpeedy(employee_id number(10),employee_name varchar2(10))''')
        print("Table Created")
        cur.close()
                
except Exception as e:
    print("Error: ",str(e))

Output:

Connected
Table Created

We can verify our result by using the desc CodeSpeedy command in the SQL command line.

Insert data into a table in Oracle Database using Python

We will insert data into the table using the cur.execute() method. The SQL INSERT INTO command is passed to this method to insert the data in our table.

cur.execute('''INSERT INTO
               CodeSpeedy values(101,'Ravi')''')
cur.execute('''INSERT INTO
               CodeSpeedy values(102,'Ramu')''')
cur.execute('''INSERT INTO 
               CodeSpeedy values(103,'Rafi')''')

As it is a DML command, we need to commit, so as to reflect the changes in our database. To achieve this, the co.commit() method is used.

co.commit()

Python program to insert data into a table in Oracle database

import cx_Oracle
try:
    with cx_Oracle.connect('Username/passsword')as co:
        print("Connected")
        cur=co.cursor()
        
        cur.execute('''INSERT INTO
                       CodeSpeedy values(101,'Ravi')''')
        cur.execute('''INSERT INTO
                       CodeSpeedy values(102,'Ramu')''')
        cur.execute('''INSERT INTO 
                       CodeSpeedy values(103,'Rafi')''')
        co.commit()
        cur.close()
        print("Data Inserted")
                
except Exception as e:
    print("Error: ",str(e))

Output:

Connected
Data Inserted

We can verify our result by using the SELECT * FROM CodeSpeedy command in the SQL command line which displays the inserted records in our table.

Fetch data from a table in Oracle Database using Python

There are three methods used to fetch data from a table:

  • fetchone( ) – used to fetch a single record or row from a table.
  • fetchmany(n) – used to fetch n number of records or rows from a table.
  • fetchall( ) – used to fetch all the records or rows from a table.

All these methods return a list of rows stored in our table. These rows are in the form of Python tuples.

At first, we need to execute SQL SELECT command using the cur.execute() method.

cur.execute("SELECT * FROM CodeSpeedy")

Then, using any one of the above fetching methods, store the returned list into a variable.

x=cur.fetchone()

Here, I have used the fetchone() method which returns a single row as a list. We can directly print the list x or we can iterate over it.

Python program to fetch data from a table in Oracle database using various fetching methods

import cx_Oracle
try:
    with cx_Oracle.connect('Username/password')as co:
        print("Connected")
        cur=co.cursor()
        
        cur.execute("SELECT * FROM CodeSpeedy")
        x=cur.fetchone()
        print(x)

        cur.execute("SELECT * FROM CodeSpeedy")
        y=cur.fetchmany(2)
        print(y)
        
        cur.execute("SELECT * FROM CodeSpeedy")
        z=cur.fetchall()
        print(z)
        
        print("Data Fetched")
        cur.close()
                
except Exception as e:
    print("Error: ",str(e))

Output:

Connected
(101, 'Ravi')
[(101, 'Ravi'), (102, 'Ramu')]
[(101, 'Ravi'), (102, 'Ramu'), (103, 'Rafi')]
Data Fetched

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

Leave a Reply

Your email address will not be published.