How to Connect Oracle Database With Python

1. Connect Oracle Database With Python
2. Execute Oracle Query With Python
3. Retrieve Data From Oracle Database With Python

Oracle is an advanced relational database management system. It is very useful to add a database to the software you have developed to keep a lot of information together.

This article explains how to connect the Oracle database with the cx_Oracle library of the Python programming language.

Connect Oracle Database With Python

You must first install the cx-Oracle library to connect to the Oracle database in a program written in Python. If you are Linux, use the pip install cx-Oracle command.

Using Windows, use the py -m pip install cx-Oracle command.

Before you start performing operations on the database, you must establish a connection with Oracle. This connection is established with the connect() function.

Provide the required username, password, host, and port information as parameters to the function. Use the close() function to close the connection after all operations are finished.

In the example below, the Oracle database running on port 1521 on localhost is connected to the user with the username admin and the password password. After the operation is done, the connections are closed with the close() function.

import cx_Oracle

connection = cx_Oracle.connect("admin/password@localhost:1521")
connection.close()

Execute Oracle Query With Python

Once the connection is established, use the cursor() function to be able to execute SQL queries. After this function, you can perform a single query with the execute() method.

If you want to perform a query with multiple bind variables, use the executemany() function. Use the commit() function to reflect the queries you execute to the database.

In the example below, after the connection is established, the users table is created, and a value named user is added to the table with the execute() method. After all the operations are done, the connections are closed with the close() function.

import cx_Oracle

connection = cx_Oracle.connect("admin/password@localhost:1521")
cursor = connection.cursor()

cursor.execute("create table users(id integer primary key, username varchar2(20))")
cursor.execute("insert into users values(1,'user')")
connection.commit()

cursor.close()
connection.close()

Retrieve Data From Oracle Database With Python

Use the fetchone() method to fetch a single row from the top of the result set. If you want to fetch all the rows in the result set, you can use the fetchall() function.

You can use the fetchmany(number) function to fetch a limited number of rows based on the argument passed in it.

All the values in the users table are fetched with the fetchall() method in the example below.

import cx_Oracle

connection = cx_Oracle.connect("admin/password@localhost:1521")
cursor = connection.cursor()

cursor.execute("select * from users")
rows = cursor.fetchall()
print(rows)

cursor.close()
connection.close()