|
How to Connect to Oracle ODBC Database with Python and pyodbc |
|
|
|
How to Connect to Oracle ODBC Database with Python and pyodbc |
|
Python is a popular general purpose scipting language that is also becoming popular among web developers. If you want to use an Oracle database as a data storage for your Pyhon app, this tutorial teaches how to connect Python to an Oracle database using ODBC driver, code samples included. There are many ways to connect to Oracle database from Python, including cx_Oracle, a Python extension module that enables access to Oracle database, most of the applications though use either ODBC or JDBC driver to connect to Oracle. One of the most convenient methods to connect to an external database or access cloud data from Python is using the pyodbc Python module that implements the Python DB specification and allows you to easily connect Python appplications to data sources with the Devart ODBC driver for Oracle.
If you don't have Python installed on your machine, go to the Python official website, download the installer for your system and run it. You will also need to install the pyodbc module — the easiest way to do that is by using the pip install pyodbc command in the Python interactive mode. Oracle database storage comes with ODBC support, but in order to connect to Oracle from Windows, macOS, or Linux, you must install ODBC driver for Oracle, otherwise the pyodbc module will not be able to establish a connection to the database.
Next, you need to download the ODBC Driver for Oracle. To use the ODBC driver as a translation layer between the application and the database, you need to configure it by following the installation instructions.
Below is a Python code example that connects to Oracle using ODBC driver. First we import the pyodbc module, then create a connection to the database, insert a new row, and read the contents of the EMP table while printing each row to the Python interactive console. If you have ever connected to any relational database from Python, you will easily understand the code as we describe each line. To execute the script, you can type this code directly in the interactive console or add the code to a file with the .py extension and run the file from the command prompt.
Step 1: Connect
The pyodbc module is imported to provide the API for accessing Oracle database. The code uses the driver named "Devart ODBC Driver for Oracle" to connect to the remote database. Once a connection is established, you can perform CRUD operations on the database.
import pyodbc
cnxn = pyodbc.connect('DRIVER={Devart ODBC Driver for Oracle};Direct=True;Host=myhost;Service Name=myservicename;User ID=myuserid;Password=mypassword')
Step 2: Insert a row into Oracle table
Here's a simple example of how to execute an insert statement to test the connection to the database. The script inserts a new record to the EMP table.
cursor = cnxn.cursor()
cursor.execute("INSERT INTO EMP (EMPNO, ENAME, JOB, MGR) VALUES (535, 'Scott', 'Manager', 545)")
Step 3: Retrieve data from Oracle table
The cursor.execute() function retrieves rows from the select query on a dataset. The cursor.fetchone() function iterates over the result set returned by cursor.execute() while the print() function prints out all records from the table to the console.
cursor = cnxn.cursor()
cursor.execute("SELECT * FROM EMP")
row = cursor.fetchone()
while row:
print (row)
row = cursor.fetchone()
We have shown you how to access Oracle database using Python, pyodbc and Devart ODBC driver. This tutorial uses only a handful of lines of code to establish a connection and demonstrate basic operations, but you can use the pyodbc module and our driver for Oracle in a enterprise application in the production environment. Feel free to download Devart ODBC driver for Oracle for free to evaluate its features. You can use the same steps to connect to any data source using a Devart ODBC driver: basically, all you have to do is modify the connection string, the pyodbc functions used in this tutorial work for any database.