How to connect Oracle from python and load pandas Dataframe from oracle
- March 25, 2020
- By Pawan Prasad
- 0 Comments
In this post, we are going learn to connect the ORACLE database from python and access the data.
I will demonstrate:-
- How to create a user in oracle?
- Which library is required?
- How to make the connection in python for oracle?
- How to execute the query and access the data from Oracle DB using python?
- How to load a pandas dataframe by providing a query and oracle connection object?
I assume you have oracle and Python 3.x installed on your windows system because this is the configuration I am going to use.
Create a user in Oracle database
We are creating a user in oracle, using this user will connect to DB from python.
I have used the name "PYTHON" for the username, you can choose a different name just replace your username with "PYTHON" in the below SQL commands.
open command prompt and type "sqlplus / as sysdba" , this will open sqlplus utility and now execute the below command one by one in exact order.
CREATE USER PYTHON IDENTIFIED BY PYTHON;
GRANT CONNECT TO PYTHON;
GRANT CONNECT, RESOURCE, DBA TO PYTHON;
GRANT CREATE SESSION, GRANT ANY PRIVILEGE TO PYTHON;
GRANT UNLIMITED TABLESPACE TO PYTHON;
Connect to the user using command conn PYTHON, provide the password PYTHON as shown below
we will create a sample table with data, execute below SQL commands in prompt to create a table and insert data.
CREATE TABLE WORLD (NAME VARCHAR(255), CONTINENT VARCHAR(255), AREA INT, POPULATION INT, GDP INT);
TRUNCATE TABLE WORLD;
INSERT ALL
INTO WORLD (NAME, CONTINENT, AREA, POPULATION, GDP) VALUES ('AFGHANISTAN', 'ASIA', '652230', '25500100', '20343000000')
INTO WORLD (NAME, CONTINENT, AREA, POPULATION, GDP) VALUES ('ALBANIA', 'EUROPE', '28748', '2831741', '12960000000')
INTO WORLD (NAME, CONTINENT, AREA, POPULATION, GDP) VALUES ('ALGERIA', 'AFRICA', '2381741', '37100000', '188681000000')
INTO WORLD (NAME, CONTINENT, AREA, POPULATION, GDP) VALUES ('ANDORRA', 'EUROPE', '468', '78115', '3712000000')
INTO WORLD (NAME, CONTINENT, AREA, POPULATION, GDP) VALUES ('ANGOLA', 'AFRICA', '1246700', '20609294', '100990000000')
SELECT * FROM DUAL;
COMMIT;
SELECT * FROM WORLD;
now we are done with the Oracle part next, we will install the required python library.
Install cx_Oracle python library
we are going to use cx_Oracle library to connect python and oracle.
like all other libraries, we will install this using PIP command
like all other libraries, we will install this using PIP command
open command prompt execute the below pip command
pip install cx_Oracle
to see package information type, pip show cx_Oracle, it will list all the details about the package.
How to make the connection in python for oracle?
Import the required libraries
create the connection by providing username, password and server information
Create a cursor object from the connection
for now, we are using a simple query
execute the query and print the result
Result: -
Close the cursor and connection
import cx_Oracle import pandas as pd
create the connection by providing username, password and server information
conn = cx_Oracle.connect('PYTHON/PYTHON@localhost')
Create a cursor object from the connection
cursor = conn.cursor()
for now, we are using a simple query
query = 'SELECT * FROM WORLD'
execute the query and print the result
cursor.execute(query)
for row in cursor: print (row)
Result: -
('AFGHANISTAN', 'ASIA', 652230, 25500100, 20343000000)
('ALBANIA', 'EUROPE', 28748, 2831741, 12960000000)
('ALGERIA', 'AFRICA', 2381741, 37100000, 188681000000)
('ANDORRA', 'EUROPE', 468, 78115, 3712000000)
('ANGOLA', 'AFRICA', 1246700, 20609294, 100990000000)
Close the cursor and connection
cursor.close() conn.close()
How to load pandas Dataframe from oracle:
Now, load the data from the database into pandas dataframe directly using this code
import cx_Oracle import pandas as pd
conn = cx_Oracle.connect('PYTHON/PYTHON@localhost')
query = 'SELECT * FROM WORLD'
df_ora = pd.read_sql(query, con=conn)
print(df_ora)
conn.close()
