Python 4 | Create a Python Function to Read Oracle Table

To read data from an Oracle database in Python, you can use the `cx_Oracle` or sqlachemy library. Here's an example of how to connect to the database and retrieve data using cx_oracle library:

 1. Install the `cx_Oracle` library using `pip` if you haven't already:

 pip install cx_Oracle

 2. Import the `cx_Oracle` library in your Python script:

 import cx_Oracle

 3. Connect to the Oracle database by specifying the connection details (host, port, service name, username, and password):

 connection = cx_Oracle.connect(

    user="your_username",

    password="your_password",

    dsn="your_host:your_port/service_name"

)

 Replace `"your_username"`, `"your_password"`, `"your_host"`, `"your_port"`, and `"service_name"` with the appropriate values for your Oracle database.

 4. Create a cursor object to execute SQL statements:

 cursor = connection.cursor()

 5. Execute an SQL query using the `execute()` method of the cursor object:

 query = "SELECT column1, column2 FROM your_table"

cursor.execute(query)

 

Replace `"your_table"` with the name of the table you want to retrieve data from, and `"column1"` and `"column2"` with the names of the columns you want to select.

 6. Fetch the result set using the `fetchall()` or `fetchone()` method of the cursor object:

 result_set = cursor.fetchall()

 # Alternatively, you can use fetchone() to retrieve one row at a time:

# row = cursor.fetchone()

 7. Process the fetched data as required:

  

for row in result_set:

    column1_value = row[0]  # Access values by index

    column2_value = row[1]

    print(column1_value, column2_value)

 

8. Close the cursor and the database connection when you're done:

 cursor.close()

connection.close()

This is a sample example of how to read data from an Oracle database in Python using the `cx_Oracle` library. You can expand on this example to handle different types of queries and process the data according to your needs.

 

Below is the complete python code.

 

 import cx_Oracle

def read_from_oracle_table(connection_string, query):

    try:

        # Establish a connection to the Oracle database

        connection = cx_Oracle.connect(connection_string)

 

        # Create a cursor object to execute SQL queries

        cursor = connection.cursor()

 

        # Execute the SQL query to fetch data from the table

        cursor.execute(query)

 

        # Fetch all the rows from the query result

        rows = cursor.fetchall()

 

        # You can process and manipulate the 'rows' data as needed here

        for row in rows:

            print(row)  # Print each row to the console as an example

 

    except cx_Oracle.Error as error:

        print(f"Error: {error}")

    finally:

        # Close the cursor and the database connection

        if cursor:

            cursor.close()

        if connection:

            connection.close()

 

# Usage example:

if __name__ == "__main__":

    connection_string = "your_username/your_password@your_database_url"

    query = "SELECT * FROM your_table_name"

    read_from_oracle_table(connection_string, query)