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.
👋 Check out our easy to use desktop GUI for SQL
Beekeeper Studio is a truly cross-platform SQL GUI with a clean, uncluttered interface. Write SQL, edit data, alter tables, and more!
Available for MacOS, Linux, and Windows.
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.