How to Automate Oracle SQL Query Exports to CSV with Python

The real-world scenario

Imagine you are a Data Analyst or Database Administrator who needs to generate a massive audit report every Monday morning at 8:00 AM. Instead of logging into **SQL Developer**, manually running a query, and clicking **Export** while waiting for the UI to freeze, you can delegate this task to a script. It is like setting up a conveyor belt in a factory: once you pull the lever, the data flows from the **Oracle Database** directly into a neatly labeled **CSV file** without any human intervention.

The solution

We use the modern **python-oracledb** driver, which is the successor to **cx_Oracle**. This script connects to your **Oracle instance**, executes a provided SQL statement, and uses the **csv** module to stream the results into a file. By utilizing **pathlib**, we ensure the script works seamlessly across **Windows**, **macOS**, and **Linux** environments.

Prerequisites

• Install the Oracle Python driver: pip install oracledb
• Ensure you have the **Database Credentials** (host, port, service name, username, and password).
• The script uses **Thin Mode**, meaning you do not need the **Oracle Instant Client** installed for basic connections.

The code

"""
-----------------------------------------------------------------------
Authors: Sharanam & Vaishali Shah
Recipe: Oracle Data Porter
Intent: Automate the extraction of SQL query results to a CSV file.
-----------------------------------------------------------------------
"""
import oracledb
import csv
from pathlib import Path

def export_oracle_to_csv(user, password, dsn, sql_query, output_file):
    # Define the output path using pathlib
    target_path = Path(output_file)
    
    try:
        # Establish connection in Thin Mode
        conn = oracledb.connect(user=user, password=password, dsn=dsn)
        cursor = conn.cursor()
        
        # Execute the query
        cursor.execute(sql_query)
        
        # Fetch metadata for headers
        headers = [col[0] for col in cursor.description]
        
        # Open file and write data
        with target_path.open(mode='w', newline='', encoding='utf-8') as f:
            writer = csv.writer(f)
            
            # Write the header row
            writer.writerow(headers)
            
            # Fetch and write data in batches to save memory
            while True:
                rows = cursor.fetchmany(numRows=1000)
                if not rows:
                    break
                writer.writerows(rows)
                
        print(f"Success: Data exported to {target_path.absolute()}")

    except oracledb.Error as e:
        print(f"Database error: {e}")
    except Exception as e:
        print(f"An unexpected error occurred: {e}")
    finally:
        if 'conn' in locals():
            conn.close()

if __name__ == "__main__":
    # Configuration - Replace with your actual credentials
    DB_USER = **MY_DB_USER**
    DB_PASS = **MY_DB_PASSWORD**
    DB_DSN = **localhost:1521/xe**
    
    QUERY = **SELECT * FROM HR.EMPLOYEES**
    FILENAME = **employee_report.csv**
    
    export_oracle_to_csv(DB_USER, DB_PASS, DB_DSN, QUERY, FILENAME)

Code walkthrough

The script begins by importing **oracledb** for database connectivity and **pathlib** for robust file path handling. The function **export_oracle_to_csv** takes your credentials and the **SQL query** as input.

First, it creates a **Path** object for the **output_file**, which ensures the file path logic is consistent across operating systems. When **oracledb.connect** is called, it defaults to **Thin Mode**, which is lightweight and fast. Once the query is executed, we access **cursor.description** to grab the column names; these become the first row in our **CSV**.

To handle large datasets without crashing your computer’s memory, we use **cursor.fetchmany** in a loop. This processes the data in chunks of 1,000 rows at a time. Finally, the **finally** block ensures that the database connection is closed properly, even if the script encounters an error.

Sample output

When you run the script in your **Terminal** or **PowerShell**, you will see the following confirmation:

Success: Data exported to /Users/sharanam/projects/reports/employee_report.csv

Conclusion

Automating data extraction is a foundational skill for any modern engineer. By replacing manual exports with this Python script, you reduce the risk of human error and free up your time for more complex architectural tasks. You can now wrap this script in a **Cron job** or a **Windows Task Scheduler** to fully automate your reporting pipeline.