Testing Flask-to-Oracle DB Connectivity: A Simple Example

 Before you build any real feature, it's a good idea to check that Flask can actually talk to your Oracle database. Here's a tiny app that does just that — no tables, no forms, just a connectivity check.

The Test App

from flask import Flask, jsonify
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text
import oracledb

oracledb.init_oracle_client(lib_dir=r"C:\instantclient_21_11")

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'oracle+oracledb://apps:devr12app@ebsdevdb01.nrb.inside:1522/?service_name=ebs_R122DEV'
db = SQLAlchemy(app)


@app.route('/test-db')
def test_db():
    try:
        result = db.session.execute(text("SELECT 1 FROM dual")).scalar()
        return jsonify(status="success", message="Connected to Oracle DB!", result=result)
    except Exception as e:
        return jsonify(status="error", message=str(e)), 500


if __name__ == '__main__':
    app.run(debug=True)

Why It Works

The key line is:

SELECT 1 FROM dual

dual is a special one-row table that exists in every Oracle database by default. Querying it doesn't touch any real data — it just proves the connection is alive. If this query runs, Flask successfully reached the database.

The try/except block catches any connection error (wrong password, unreachable host, missing Instant Client, etc.) and returns it as readable JSON instead of crashing the app.

Running It

1. Save the file as test_db_connection.py
2. Run python test_db_connection.py
3. Open http://127.0.0.1:5000/test-db in your browser

A successful connection looks like this:

{
  "status": "success",
  "message": "Connected to Oracle DB!",
  "result": 1
}

If something's wrong, you'll get a clear error message instead — telling you exactly what to fix (credentials, host, port, or driver path) before you build the rest of the app.