Python and Oracle – Exporting Data (Text)

Sometimes it can be useful to export data from a database, so that it can be analysed, or, to import in to another computer system. Text files are one such file format that allows for both of these scenarios. These text files are often called delimited text files because each piece of data is separated by a delimiter.

Below is an example of how Python can be used to export data to a text file called ‘personexport.txt', from an Oracle database table called ‘person’, which was used in the example for selecting data. The delimiter used to separate each piece of data in this example is the pipe (|) symbol.

Firstly, the text file path and name are set and a check is made to see if the path actually exists. If it does, a connection to the database is established and a query is executed to extract the data from the database. The text file is then opened for writing and the table headers are added to it, followed by the rows of data. Finally, confirmation of a successful export is provided. A ‘try-catch-finally’ block is used to catch any errors that may occur, as well as close the database connection, regardless of whether the export is successful or not.

import csv
import cx_Oracle
import os

# File path and name.
filePath = 'c:\\demo\\'
fileName = 'personexport.txt'

# Database connection variable.
connect = None

# Check if the file path exists.
if os.path.exists(filePath):

    try:

        # Connect to database.
        connect = cx_Oracle.connect('DemoUN/DemoPW@localhost')

    except cx_Oracle.DatabaseError as e:

        # Confirm unsuccessful connection and stop program execution.
        print("Database connection unsuccessful.")
        quit()

    # Cursor to execute query.
    cursor = connect.cursor()

    # SQL to select data from the person table.
    sqlSelect = \
        "SELECT id, firstname, lastname, dob \
         FROM person \
         ORDER BY id"

    try:

        # Execute query.
        cursor.execute(sqlSelect)

        # Fetch the data returned.
        results = cursor.fetchall()

        # Extract the table headers.
        headers = [i[0] for i in cursor.description]

        # Open text file for writing.
        textFile = csv.writer(open(filePath + fileName, 'w', newline=''),
                              delimiter='|', lineterminator='\r\n',
                              quoting=csv.QUOTE_NONE, escapechar='\\')

        # Add the headers and data to the text file.
        textFile.writerow(headers)
        textFile.writerows(results)

        # Message stating export successful.
        print("Data export successful.")

    except cx_Oracle.DatabaseError as e:

        # Confirm error retrieving person information and stop program execution.
        print("Error retrieving person information.")
        quit()

    finally:

        # Close database connection.
        connect.close()

else:

    # Message stating file path does not exist.
    print("File path does not exist.")

The text file produced contains the following data.

ID|FIRSTNAME|LASTNAME|DOB
1|Bob|Smith|1980-01-20 00:00:00
2|George|Jones|1997-12-15 00:00:00
3|Fred|Bloggs|1975-05-07 00:00:00
4|Alan|White|1989-03-20 00:00:00