January 31, 2023 By Matthew Rathbone

The python-oracledb library is a Python interface for accessing Oracle databases. It allows Python programmers to execute SQL statements and interact with Oracle databases in a simple and efficient manner. In this quick guide, we will cover the basics of using the python-oracledb library to connect to an Oracle database and execute some basic SQL statements.

Before we start, it is important to note that the python-oracledb library requires the Oracle client libraries and the cx_Oracle Python package to be installed on your system. You can download the Oracle client libraries from the Oracle website and install them according to the instructions provided.

Once you have the Oracle client libraries and ‘python-oracledb’ installed, you can start using the python-oracledb library in your Python code. The first step is to establish a connection to the Oracle database. You can do this using the following code:

import oracledb

# Connect to the database
conn = oracledb.connect(user='user', password='password', dsn='dsn_name')

Here, you need to replace ‘user’ and ‘password’ with the username and password for your Oracle database, and ‘dsn_name’ with the name of the database service name. You can also specify additional connection parameters, such as the hostname and port number, if necessary.

Once you have established a connection to the database, you can execute SQL statements using the cursor object. The cursor object allows you to execute SQL statements and retrieve the results. You can create a cursor object using the following code:

# Create a cursor
cursor = conn.cursor()

To execute an SQL statement, you can use the execute() method of the cursor object. For example, to execute a SELECT statement, you can use the following code:

# Execute a SELECT statement
cursor.execute('SELECT * FROM table_name')

# Fetch the results
results = cursor.fetchall()

# Loop through the results
for row in results:
    print(row)

You can also use the execute() method to execute other types of SQL statements, such as INSERT, UPDATE, and DELETE. For example, to insert a new row into a table, you can use the following code:

# Execute an INSERT statement
cursor.execute("INSERT INTO table_name (column1, column2) VALUES (value1, value2)")

# Commit the changes to the database
conn.commit()

It is important to remember to call the commit() method after executing any SQL statements that modify the database. This ensures that the changes are saved to the database.

Once you are finished working with the database, you should close the connection and cursor objects to free up resources. You can do this using the following code:

# Close the cursor
cursor.close()

# Close the connection
conn.close()

This is a quick guide to using the python-oracledb library to connect to an Oracle database and execute SQL statements in Python. With this basic understanding, you can start using the python-oracledb library to interact with your Oracle database in your Python applications.

Beekeeper Studio Is A Free & Open Source Database GUI

Best SQL query & editor tool I have ever used. It provides everything I need to manage my database. - ⭐⭐⭐⭐⭐ Mit

Beekeeper Studio is fast, intuitive, and easy to use. Beekeeper supports loads of databases, and works great on Windows, Mac and Linux.

Beekeeper's Linux version is 100% full-featured, no cut corners, no feature compromises.