You can pull data directly from Oracle database to python using the library cx_Oracle and sqlalchemy.
Reading data from database is a two step process listed below
- Create a connection string: This is a single string which contains the database credentials and it is used to establish the connection with the database.
- SQL query: Once the connection has been established, you can run any valid SQL query on the database. It can be a simple query like “select * from employee” or it can be an sql query involving joins on multiple tables.
Below snippet installs the required libraries. You can run these commands either in command prompt or in the jupyter notebook using an exclamation symbol “!”. e.g. !pip install cx_Oracle.
|
1 2 3 |
# installing the required libraries to connect with Oracle Database pip install cx_Oracle pip install sqlalchemy |
Below code snippet connects to the oracle database by first generating a connection variable “conn” which includes the login details to the database and then firing an sql query using the connection variable inside function read_sql_query() present in the pandas library.
You just have to change the database credentials to connect to your own oracle database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
import cx_Oracle import sqlalchemy import pandas as pd # Use your database credentials below DATABASE = "DB" SCHEMA = "DEV" PASSWORD = "password" # Generating connection string connstr = "oracle://{}:{}@{}".format(SCHEMA, PASSWORD, DATABASE) conn = sqlalchemy.create_engine(connstr) # Fetching the data from the selected table using SQL query RawData= pd.read_sql_query("select * from [EMP].[Employee_table] where countryCode='US'", conn) RawData.head() |
Sample Output:
Author Details
Lead Data Scientist
Farukh is an innovator in solving industry problems using Artificial intelligence. His expertise is backed with 10 years of industry experience. Being a senior data scientist he is responsible for designing the AI/ML solution to provide maximum gains for the clients. As a thought leader, his focus is on solving the key business problems of the CPG Industry. He has worked across different domains like Telecom, Insurance, and Logistics. He has worked with global tech leaders including Infosys, IBM, and Persistent systems. His passion to teach inspired him to create this website!