Find the existing values of the given text file and check if those records present or not in the Oracle database table

This post helps to understand the basic syntax to opening a file and reading from it along with how we can fetch the data from the database and compare the details. 

We can follow 4 simple steps to find the existing values of the text file and check if those records present in the Oracle database table or not.

Reding Text file from the local system

Reading or writing a file in python can be done in different ways. Most of the time we use Open or Open with statements to open file and read the file. open() is the inbuilt function used to open the file. The open() function returns a file object, which has a read() method for reading the content of the file.

When we use Open() function we need to close it manually when we complete our work but with statement provides a way for ensuring that we didn’t have to close the file manually it takes care by with statement automatically.

We have tried to find the word which is present in a given text file and save it into the list for further processing.

Code to read the file from local system

Create a table using the below script if the table does not exist in the database.

Below SQL  statement helps to create a table named pythonic_test in oracle database with 2 columns names id and text with datatype as number and varchar2 respectively.
 
Create Table with 2 column 
create table pythonic_test(id number(10),text varchar2(50));
 
The following  SQL statements insert the different values into the pythonic_test  table
 
Insert the value in the  created table using the below script 
insert into pythonic_test values(1,’My name is nitesh’);
insert into pythonic_test values(2,’My name is Athang’);
insert into pythonic_test values(15,’My name is Akshay’);
insert into pythonic_test values(16,’My name is akshay’);
insert into pythonic_test values(17,’My name is Prashant’);
 

Execute the below snippet to fetch the record from the table.

To select the data from the Oracle database in python we need to follow some steps which are given below :

1.Importing cx_Oracle :  This is a Python extension module that enables access to Oracle Database.

By the below command, you can install cx_Oracle package but it is required to install Oracle database to use those functions.
pip install cx_Oracle

2.Creating Connection: Establish a connection between Python program and Oracle database by using connect()  method present in cx_Oracle package. Which consists of different connection credentials such as username, password, IP address, port, and database name.

3.Executing query:  To execute sql query cursorobject required which help to hold the connection details and execute single or multiple query.

Fetching or Reading of data can be done using a different method

fetchone: To fetch a single row from a result set we can use cursor.fetchone(). This method returns a single record and it can return a none if no rows are available.

fetchmany: This method returns the number of rows specified by size as an argument the default value of size is 1. We can use as many we want. It returns an empty list when no rows available.

fetchall: It fatches all the rows from the table as a list of tuples. An empty list is returned if there is no record to fetch from the table.

4.Commit the transaction: If any DML(Data Manipulation Language) operation perform on database we need to commit in order to reflect the changes in database.

5.Closing Connection: We write the close function within finally block so whenever any error occurs then also it close the connection along with database operation.

Let see the below snippet for a better understanding of the above concept.

Fetching value from the given table

Compare the fetched record from the created table with the details present in a text file and return the matched values.

Below python query help to split and store each word which is got by our resultset in list object name as Final_list. Using for loop we compare the text word with the word extracted from the table and return the exact matched word.

Checking the data with Oracle database to return thee matched values

I think this post helps to understand some basic operations on files along with dealing with oracle database to fetch the records. If you have any suggestions for this post then let us know by writing comments in below comment box.

About the Author: Nitesh

I am a software engineer and Enthusiastic to learn new things

Leave a Reply

Your email address will not be published. Required fields are marked *