Connect to Oracle DB via JDBC Driver Using Python

A Python developer may need to connect to an Oracle database in order to save and manipulate data efficiently. In Python, a wide range of powerful tools are available to establish a connection with a particular database. One of them is JayDeBeApi, which enables Python developers to connect to relational databases via the JDBC driver.

In this article, we will explore the steps involved in using the JDBC driver to connect to an Oracle database via Python Script.

Prerequisites

Before establishing a connection to Oracle, make sure that you have the following prerequisites on your machine:

Java Installation
JAVA_HOME Path
Python Installation

Connect to Oracle DB via JDBC Driver Using Python

In order to make the connection, you need to set the environment by doing the following steps:

Download JayDeBeApi Python Module
Download JDBC Driver

Step 1: Download Python Module

Download the Python module name “JayDeBeApi” by typing the following command in the CMD:

pip install jaydebeapi

Output

The output showed that the “JayDeBeApi” is installed in the system.

Step 2: Download JDBC Driver

In order to download the JDBC driver simply go to the Oracle Database JDBC driver and Companion Jars Downloads page and download the driver according to the java installed in your system:

After the download, create a folder and open it in any code editor:

Create a new file with the “.py” extension, for example, “connect.py”:

First, import the “JayDeBeApi” and “jpype” Python modules by typing the following code:

import jaydebeapi
import jpype

In the above code,

JayDeBeApi is used to make the connection to Oracle database using Python code and JDBC driver.
Jpype provides the full access to Java classes from Python code.

Set the path of the Oracle JDBC driver JAR file as follows:

driver_path = "/path/to/oracle/driver/ojdbc<version>.jar"

Set the JDBC connection string for your database:

conn_string = "jdbc:oracle:thin:@//<hostname>:<port>/<SID>"

For this post:

<hostname> is localhost
<port> is 1521
<SID> is xe

It can be changed according to your requirements.

Set the <username> and <password> of the Oracle database:

username = "<username>"
password = "<password>"

Start the JVM with the JDBC driver class:

jpype.startJVM("-Djava.class.path={}".format(driver_path))

Establish a JDBC connection using the JayDeBeApi module:

conn = jaydebeapi.connect("oracle.jdbc.driver.OracleDriver", conn_string, [username, password])

Check if the connection was successful or not:

if conn:
print("Connected to Oracle database successfully!")
else:
print("Failed to connect to Oracle database.")

Close the connection and stop the JVM:

conn.close()
jpype.shutdownJVM()

After typing the complete code, save the file, open a terminal, and type the following command to run the code:

python.exe .\connect.py

Output

The output displayed a successful connection to the Oracle database.

Conclusion

To connect to the Oracle database via JDBC driver using Python, download the JDBC driver and install a Python module named JayDeBeApi. Open a folder in any code editor, create a new Python file, import the modules, and provide the database credentials. Save the file and execute the script to establish the connection. This article explained how to connect to Oracle DB via JDBC driver using Python.

Connect to Oracle DB via JDBC Driver Using Python

Connect to Oracle DB via JDBC Driver Using Python

Conclusion

About the author

Danish Ghafoor