Connecting to Oracle Database using Python Script

Photo by Mika Baumeister on Unsplash

Python script can be used to fetch data from Oracle Database.

This post will be useful for the beginners who want to work with Python and Oracle Database.

Firstly, you need to set up the environment for Python and Oracle database.

You can check below my post for how to set up python environment.

Connecting to SQL Server Database using Python

Also, you can setup Oracle environment by using below steps.

Oracle Express setup

1. Download and install Oracle Express and set up a database. https://www.oracle.com/database/technologies/xe-downloads.html
2. Configure the database using Database configuration assistant.You can configure the database by launching dbca interface using command prompt.

C:\>dbca

Connecting to Oracle

You can connect to Oracle using localhost to manage users.

https://localhost/5500/em

UserName: sys

Password: Password@123(Password you entered while creating database)

Connecting to an Oracle Database

Once database is configured in Database configuration assistant, connect to database through command prompt.

C:\WINDOWS\system32>sqlplus

Enter user name and password.

Note: You should enter password in quotes if you have any special characters in your password.

This will connect you to the Oracle database.

Creating a new table

You can create/modify tables using SQL command line tool. For instance,

SQL> CREATE TABLE customers

2 ( customer_id number(10) NOT NULL,

3 customer_name varchar2(50) NOT NULL,

4 city varchar2(50)

5 );

Inserting values into a table

You can use insert query to insert values into a table.

Get Velammal Ayyappan’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

SQL> INSERT INTO customers

Values (100, ‘ABC’, ‘Sydney’);

Connecting Database to Python Application

You can install Oracle plugin into Python environment.

Pip install cx_Oracle

Once the plugin is installed, you can use it in python application to connect to the database.

Before that, you need to know host name, port number and service name (database name) to successfully connecting to the Oracle database.

To know the above details, in command prompt, type the below.

C:\WINDOWS\system32>lsnrctl status LISTENER

This will list out all the available endpoints. Here, you can check your Oracle connection details.

Sample Python script to fetch data from Oracle database

import cx_Oracle

dsn_tns = cx_Oracle.makedsn(‘127.0.0.1’, ‘1522’, service_name=’test100')

conn = cx_Oracle.connect(user=’sys’, password=’Password@123', dsn=dsn_tns, mode=cx_Oracle.SYSDBA)

# For example, if your user name contains ‘\’, you’ll need to place ‘r’ before the user name: user=r’User Name’

c = conn.cursor()

c.execute(‘select * from customers’)

# use triple quotes if you want to spread your query across multiple lines

print(“ — — — — Id — — — — — Name — — — — — City — — — — “)

for customer in c:

print(“ %d %s %s “ %(customer[0],customer[1],customer[2]))

print(“ — — — — — — — — — — — — — — — — — — — — — — — -”)

conn.close()

Output

To run the application:

1. From Solution Explorer → select the python file you want to execute.
2. Right-click and select Start without Debugging option.

You can see the output in a specified format in command prompt window.

Related posts:

Connecting to Oracle Database using IronPython

Connecting to SQL Server using IronPython