Python and Oracle – Exporting Data (JSON)

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. JSON or JavaScript Object Notation files are an alternative file format to CSV, that can sometimes be used for both of these scenarios.

Below is an example of how Python can be used to export data to a JSON file called ‘personexport.json’, from an Oracle database table called ‘person’, which was used in the examples for selecting, inserting, updating, deleting, importing and exporting data (text and XML).

Firstly, the JSON 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 data is then formatted and written out to a file.

A rolling seven day backup is also included. This makes a copy of the JSON file that has just been created, giving it a name that includes the index number for the day of the week, along with the day itself, for example, 'personexport-1-monday.json', for the backup on a Monday. Here, Sunday is classed as the first day of the week, with an index value of zero. Note that the backup is only done for the first time that this is run in a given day. Backups are then overwritten each week.

Finally, confirmation of a successful export is provided. A ‘try-except-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 datetime
import json
import oracledb
import os
import shutil

# File path and name.
filePath = '/home/demouser/Documents/'
fileName = 'personexport.json'

# Database connection variable.
connect = None

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

    try:

        # Connect to database.
        connect = oracledb.connect('DemoUN/DemoPW@localhost:1521/freepdb1')

    except oracledb.Error 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, title, dob 
        FROM person 
        ORDER BY id"""

    try:

        # Execute query.
        cursor.execute(sqlSelect)

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

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

        # Extract the data.
        data = [dict(zip(headers, row)) for row in results]

        # Convert the data to JSON and format.
        json = json.dumps({"person":data}, indent=2, 
                default=lambda o: o.isoformat() if isinstance(o, datetime.datetime) else None)

        # Open JSON file for writing.
        with open(filePath + fileName, 'w') as jsonFile:

            # Add the JSON to the file.
            jsonFile.write(json)

        # Today's date.
        today = datetime.datetime.now().date()

        # Construct the backup file name.
        fileNameBackup = fileName[0:-5] + "-" + \
                         today.strftime("%w") + "-" + \
                         today.strftime("%A").lower() + ".json"

        # Check if the backup file does not exist, or if it does, check that
        # today's date is different from the last modified date.
        if not(os.path.isfile(filePath + fileNameBackup)) or \
           (os.path.isfile(filePath + fileNameBackup) and \
           today != datetime.date.fromtimestamp(os.stat(filePath + fileNameBackup).st_ctime)):

            # Copy the JSON export.
            shutil.copyfile(filePath + fileName, filePath + fileNameBackup)

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

    except oracledb.Error as e:

        # Message stating export unsuccessful.
        print("Data export unsuccessful.")
        quit()

    finally:

        # Close the cursor and database connection.
        cursor.close()
        connect.close()

else:

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

The JSON file produced contains the following data.

{
  "person": [
    {
      "ID": 1,
      "FIRSTNAME": "Bob",
      "LASTNAME": "Smith",
      "TITLE": "Mr",
      "DOB": "1980-01-20T00:00:00"
    },
    {
      "ID": 3,
      "FIRSTNAME": "Fred",
      "LASTNAME": "Bloggs",
      "TITLE": "Mr",
      "DOB": "1975-05-07T00:00:00"
    },
    {
      "ID": 4,
      "FIRSTNAME": "Alan",
      "LASTNAME": "White",
      "TITLE": "Mr",
      "DOB": "1989-03-20T00:00:00"
    },
    {
      "ID": 5,
      "FIRSTNAME": "Fiona",
      "LASTNAME": "Bloggs",
      "TITLE": "Mrs",
      "DOB": "1985-05-19T00:00:00"
    },
    {
      "ID": 6,
      "FIRSTNAME": "Zoe",
      "LASTNAME": "Davis",
      "TITLE": "Miss",
      "DOB": "1979-07-11T00:00:00"
    },
    {
      "ID": 7,
      "FIRSTNAME": "Tom",
      "LASTNAME": "Ingram",
      "TITLE": "Mr",
      "DOB": "1971-10-04T00:00:00"
    },
    {
      "ID": 8,
      "FIRSTNAME": "Karen",
      "LASTNAME": "Thomas",
      "TITLE": "Mrs",
      "DOB": "1969-03-08T00:00:00"
    },
    {
      "ID": 9,
      "FIRSTNAME": "Samantha",
      "LASTNAME": "Yates",
      "TITLE": "Miss",
      "DOB": "1995-08-27T00:00:00"
    }
  ]
}

More