How to Fetch Data from Oracle Database in Python

In this tutorial, we will learn how to fetch data from the oracle database in python. Also, we will cover these topics.

• Pre Requisites – Database details
• How to Fetch Data from Oracle Database in Python
• How to Read Data from Oracle Database in Python
• How to Retrieve Data from Oracle Database in Python
• How to Fetch Large Data from Oracle Database in Python
• How to Fetch Data from Oracle Database in Python without cx_oracle
• How to Extract Data from Oracle Database Using Python
• How to Retrieve Data from Oracle Database using Python

Pre Requisites – Database details

In this tutorial, we are going to use a table gift_store that has 6 columns with different data types.

• Python provides a module cx_Oracle using which we can connect oracle database with python.
• Use below code to create a gift_store table in oracle database, insert few records in it so that you can fetch data from oracle database using python.
• In the upcoming sections, we will write code in python on vscode editor.

create table gift_store(
product_id   INTEGER NOT NULL,
product_name VARCHAR(20),
product_qty INTEGER,
cost_price FLOAT,
sell_price FLOAT,
shop_status VARCHAR(20)

);

Here is the data that we inserted in the gift_store table.

(1, 'toy gun', 1, 18.0, 20.0, 'open')
(2, 'doll', 1, 10.0, 12.0, 'open')
(3, 'drum', 3, 30.0, 40.0, 'open')
(4, 'guitar', 1, 150.0, 155.0, 'open')
(5, 'skipping rope', 5, 90.0, 100.0, 'open')
(6, 'toy crossbow', 1, 150.0, 170.0, 'open')
(7, 'chess board', 1, 7.0, 10.0, 'open')
(8, 'no sale', 0, 0.0, 0.0, 'closed')

Also, check: Oracle 19c installation on Windows 10

How to Fetch Data from Oracle Database in Python

In this section, we will learn how to fetch data from the oracle database in python.

• There are mainly three steps involved to fetch data from oracle database in python.
  • import oracle module
  • establish connection by providing necessary information
  • execute query to fetch data
• Install oracle module using command pip install cx_Oracle . Once installation is complete import the oracle module import cx_Oracle .
• We need username, password and dsn (host & sid) to connect with the python with oracle database.
  • username : provide the name of the user to which you want to connect. In our case, we are connecting using common user ‘c##sqlserverguides’.
  • password : Provide the password for the entered user name.
  • dsn : It comprises of two things, host information and sid. Host information in our case is localhost but in case you are connecting with server then enter the IP address of that server here. Default sid is ‘orcl’, incase you have changed it or created a new one than enter that in place of orcl.
• In the below syntax we have created a connected and then displayed that connection. If the connection established successfully then you will see the message as below.

# module
import cx_Oracle

# establish connection
con = cx_Oracle.connect(
    user="c##sqlserverguides", 
    password="sqlserver",
    dsn="localhost/orcl"
)

# print connection status
print(con)

• Cursor is the database object that helps in retrieving the data from the oracle database. Once object is created you use execute method of cursor to put the query.
• To fetch data from oracle database we can either pass the query 'select * from table_name' or we can use fetchone() , fetchmany(), fetchall() methods. These methods are explained in detail later in this tutorial.

Source code:

In this code, we have connected to the oracle database using python where the username is ‘c##sqlserverguides’. The table name is gift_store and we have executed a query to display all the records from the table. All of this code is written in Python version 3 and the information is fetched from the oracle database.

import cx_Oracle

# establish connection
connection = cx_Oracle.connect(
    user="c##sqlserverguides", 
    password="sqlserver",
    dsn="localhost/orcl")


# create cursor object
cursor = connection.cursor()

# query to display all the data
cursor.execute("""SELECT * FROM gift_store""")

# print each row in the cursor
for i in cursor:
    print(i)

Output:

In this output, All the data is fetched from the oracle database using python.

Read: How to Get List all Tables in Oracle Database

How to Read Data from Oracle Database in Python

In this section, how to read data from the oracle database in python.

• In order to read the data we have to fetch it first. We have explained how to fetch the data from oracle database using python.
• We will perform the same here but this time we will be use fetchall(), fetchmany() and fetchone() method in python.

fetchall()

Using fetchall() method in python, we can fetch all the records from the oracle database. In our example, we have put the condition to fetch all the records where product quantity is greater than 1.

# module
import cx_Oracle


# establish connection
connection = cx_Oracle.connect(
    user="c##sqlserverguides", 
    password="sqlserver",
    dsn="localhost/orcl")

# create cursor
cursor = connection.cursor()
qry = 'select * from gift_store where product_qty > 1'
cursor.execute(qry)

# fetch all the rows 
res = cursor.fetchall()
for row in res:
    print(row)

Output:

In this output, only 2 records were having a product quantity greater than 1. Only those records have been displayed here.

fetchmany()

In fetchmany(n), we have to provide the number of rows we want to display from the database. It accepts n as a parameter where n is the total number of records to fetch. In the below example, we have fetched 4 records only.

# module
import cx_Oracle

# establishg connection
connection = cx_Oracle.connect(
    user="c##sqlserverguides", 
    password="sqlserver",
    dsn="localhost/orcl")

# create cursor object
cursor = connection.cursor()

# query to fetch all records
qry = 'select * from gift_store'

# pass the query in the cursor execute function
cursor.execute(qry)

# use fetchmany() method to display 4 records
res = cursor.fetchmany(4)

# print records in a row
for row in res:
    print(row)

Output:

In this output, the oracle database has a total of 8 records but only 4 are displayed because we have passed 4 as the parameter in fetchmany(4) method.

fetchone()

Using the python fetchone() method we can retrieve just 1 record from the oracle database using python.

# module
import cx_Oracle


# establish connection
connection = cx_Oracle.connect(
    user="c##sqlserverguides", 
    password="sqlserver",
    dsn="localhost/orcl")

# create cursor object
cursor = connection.cursor()

# query to fetch all data
qry = ''' Select * from  gift_store '''

# execute query using cursor

cursor.execute(qry)

# using fetchone() method
res = cursor.fetchone()

# displying each item in a row (optional)
for row in res:
    print(row)

Output:

In this output, only one record is displayed but the query was passed to fetch all the data.

Read: Connect Excel to Oracle Database

How to Retrieve Data from Oracle Database in Python

In this section, we how to retrieve data from the oracle database in python.

• In our previous two sections, we have displayed the way how to retrieve data from oracle database in python.
• fetch, read, retrieve and extract data, all of these have similar or close to similar meaning. and they simply implies the use of select command.
• Using the python module cx_Oracle we can work on connecting the oracle database with python. In the earlier sections, we explained it with an example.
• In our example, we have retrieved data with condition to display records where shop is open.

# module
import cx_Oracle

# establish connection

connection = cx_Oracle.connect(
    user="c##sqlserverguides", 
    password="sqlserver",
    dsn="localhost/orcl")

# create cursor object
cursor = connection.cursor()

# query to retrieve records with shop status as open

qry = '''select * from gift_store 
where shop_status='open' '''


# execute cursor query
cursor.execute(qry)

# fetall the records
res = cursor.fetchall()

# print data in rows
for row in res:
    print(row)

Output:

In this output, data is retrieved from the oracle database using python. since we applied a condition to display data with shop status as open.

Also, check: How to Check Oracle Database Version

How to Fetch Large Data from Oracle Database in Python

In this section, we will learn how to fetch large data from the oracle database in python.

• Oracle database is capable of handeling data of all sizes. But when we fetch large data from oracle database using python then the process becomes time taking.
• Large data has 1000+ columns and millions of rows. Fetching large data from oracle database using python is no
• Usse python method fetchmany(n) to fetch the data in chunks. What every number you will provide in place of n that many records will be fetched in one go.
• Apply this with the for loop to fetch the huge amount of data in an optimized manner.

Read: How to create table in Oracle

How to Fetch Data from Oracle Database in Python without cx_oracle

In this section, we will learn how to fetch data from the oracle database in python without the cx_oracle module.

• Python cx_oracle module is important to work with the oracle databse using python.
• It is not possible to connect with oracle database with python without using cx_oracle module.
• Alternative way could we could be you can add cx_oracle module to the environment. This way we don’t have to install the cx_oracle module for using it.

How to Extract Data from Oracle Database Using Python

In this section, how to extract data from an oracle database using python.

• The detailed information is already shared in the above sections. Here we will display another example of extracting data from oracle database using python.
• In our example, we we will fetch all the records from gift_store table where shop status is open and quantity is 1.

# module
import cx_Oracle

# establish connection
connection = cx_Oracle.connect(
    user="c##sqlserverguides", 
    password="sqlserver",
    dsn="localhost/orcl")

# create cursor object
cursor = connection.cursor()

# query with condition
qry = ''' Select * from  gift_store where shop_status='open' and product_qty=1 '''

# execute cursor
cursor.execute(qry)

# using fetchall method
res = cursor.fetchall()

# print records in a row
for row in res:
    print(row)

Output:

In this output, all the records are fetched using python from the oracle database. These records have shop status as open and product purchase quantity as 1.

Read Alter Table Add Column Oracle

How to Retrieve Data from Oracle Database using Python

In this section, we will learn how to retrieve data from the oracle database using python

• In our earlier sections, we have explained how to retrieve data from oracle database using python.
• In our previous two sections, we have displayed the way how to retrieve data from oracle database in python.
• fetch, read, retrieve and extract data, all of these have similar or close to similar meaning. and they simply implies the use of select command.
• Using the python module cx_Oracle we can work on connecting the oracle database with python. In the earlier sections, we explained it with an example.
• In our example, we have retrieved data with condition to calculate total profit made by gift_store. Total profit will equals to selling price – cost price.

# module
import cx_Oracle


# establish connection
connection = cx_Oracle.connect(
    user="c##sqlserverguides", 
    password="sqlserver",
    dsn="localhost/orcl")

# create cursor object
cursor = connection.cursor()

# calculating total pofit
qry = '''SELECT SUM(sell_price - cost_price) AS "Total profit" FROM gift_store  '''

# executing cursor
cursor.execute(qry)

# using fetchall() method
res = cursor.fetchall()

# printing data in a row
for row in res:
    print(row)

Output:

In this output, data is retrieved from the oracle database using python and the profit of gift_store is calculated.

In this tutorial, we have learned how to fetch data from the oracle database in python. Also, we have covered these topics.

• Pre Requisites – Database details
• How to Fetch Data from Oracle Database in Python
• How to Read Data from Oracle Database in Python
• How to Retrieve Data from Oracle Database in Python
• How to Fetch Large Data from Oracle Database in Python
• How to Fetch Data from Oracle Database in Python without cx_oracle
• How to Extract Data from Oracle Database Using Python
• How to Retrieve Data from Oracle Database using Python