Python connect with Oracle Autonomous Database

Oracle Database

On request, a quick example of how to connect with an Oracle Autonomous Database from Python and create a flask REST endpoint in Python.

Even though Oracle Autonomous Database provides a native solution for building REST endpoints in the form of ORDS (Oracle Rest Data Services) there are a lot of cases where you would like to externalise the logic behind the REST interface from the database. In cases where more complex logic is needed, or the REST interface needs to be part of a bigger construct the choice to externalize the logic is very valid.

Upon a request to share the most basic example of a Python Flask application which connects with an Oracle Autonomous Database by using a wallet the below code is now available.

import oracledb
from flask import Flask, jsonify

app = Flask(__name__)

@app.route('/query', methods=['GET'])
def query_db():

    conn = oracledb.connect(
        user="admin",
        password="mySecretPassword",
        dsn="geotest2_low",
        config_dir="./Wallet_geotest2",
        wallet_location="./Wallet_geotest2",
        wallet_password="mySecretWalletPassword")

    # Execute the query and fetch the results
    cursor = conn.cursor()
    cursor.execute("SELECT sysdate from dual")
    rows = cursor.fetchall()

    # Convert the query results to a list of dictionaries
    results = []…