Categories
Autonomous Database Flask Python

Working with Flask, WTForms, the Oracle Autonomous Database, and Oracle REST Database Services (ORDS) APIs

Welcome Back

The title pretty much speaks for itself (wordy, I know), but if you’ve been following along, you I’m working on a demo application that uses Flask (a web application microframework) connected to my Oracle Autonomous Database (ADB) via Oracle REST Data Services (ORDS) APIs.

One of Flask’s strengths is that it allows you to extend functionality and allows for a good bit of customization. But…

By default, Flask does not include a database abstraction layer, form validation or anything else where different libraries already exist that can handle that. Instead, Flask supports extensions to add such functionality to your application as if it was implemented in Flask itself.

Flask Docs

Why ADB and ORDS?

I’m primarily using this specific configuration (with ORDS and ADB) for obvious reasons. But secondarily, I haven’t come across anything that actually references the Oracle ADB or ORDS (in the context of Flask, WTForms, and python of course).

Most of the online tutorials I’ve seen and books I’ve read reference SQLlite or (in one case) MongoDB for the database. SQLite does seem like a good entry point, especially if you are putting together a proof of concept. But as things scale, I suspect you’ll reach diminishing performance returns with SQLite. In fact, the Flask documentation mentions exactly that:

Python comes with built-in support for SQLite in the sqlite3 module. SQLite is convenient because it doesn’t require setting up a separate database server and is built-in to Python. However, if concurrent requests try to write to the database at the same time, they will slow down as each write happens sequentially. Small applications won’t notice this. Once you become big, you may want to switch to a different database.

Flask Docs

That last sentence is significant. I know ORDS can be set up with High Availability in mind, so why not take advantage of that from the beginning? Also, with my ADB, the effort to create a database schema and tables is very minimal (fun even).

Regarding, MongoDB, obviously I’m baised but I’m also disorganized. And I’m still a “technical toddler.” So having to download, install, and configure MongoDB Community Edition plus the MongoDB Compass GUI would end up being an necessary step for me (I actually ended up doing this for a previous tutorial).

Regardless, I already have my OCI tenancy and my Autonomous Database, and I continue to get more and more comfortable with our ecosystem.

Sign-up for an Oracle Cloud Always Free account now. Do it. Chris Hoina, Senior Product Manager, ORDS, Database Tools
Also: You should sign up for an 
Always Free OCI account.

Moving on…I also want to use Database Actions, and the REST API features provided by ORDS. Not to mention, from everything I’ve seen in the Flask tutorials you have to first create database models before you can do anything really meaningful. I’m not entirely sure how to go about that (please check back in 2023).

Bottomline, ORDS is straightforward – just give me the URI for the API endpoint and I’ll take it from there. If I need something additional we can modify the API accordingly.

API GET Request response (via ORDS)

Speaking of which, here is what the JSON looks like from my ORDS-powered API GET request:

ORDS REST API response from Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

I won’t spend too much time explaining this image (you can read about it in detail, here), but I’m most interested in the city: (found under items). There are 18 cities, and I’ll need all of them for Flask. More specifically, I need them for WTForms.

About WTForms

I won’t give a crash course on WTForms, but bottomline its another way to extend the capabilities of Flask.

WTForms is a flexible forms validation and rendering library for Python web development. It can work with whatever web framework and template engine you choose. It supports data validation, CSRF protection, internationalization (I18N), and more.

WTForms Docs

In my scenario, WTForms allows me to easily make and handle (as in routing) forms in the Flask application. More specifically, there is a SelectFields() form that I need to use (this is just how I initially designed the app, you can review here).

Choices

The forms have fields. Here are details on the form I’m most interested in:

WTForms Flask Python ORDS REST APIs Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools

You’ll notice there are various parameters I can include, but all I care about are the choices[]. You can either hard (static) code in a list of choices, or (as I just successfully did as of this week), pass a list from an API GET request to the choices[] parameter.

If you’ve looked at the documentation, then you’ve seen that choices[] can be”passed” in one of three ways:

  1. a list of (value, label) pairs (it can also be a list of only values, in which case the value is used as the label)
  2. a dictionary of {label: list} pairs defining groupings of options, or
  3. a function taking no argument, and returning either a list or a dictionary

My approach used the first option. Actually, the values only approach, in my case.

The breakdown

First, I’ll show the dropdown list; it will look like this in my application (FYI this actually works):

Flask application in action

I know that video is fast, but you should be able to see the dropdown list (at around the :15-:17 mark) and how it renders.

This dropdown list is made possible from an initial GET request to my ORDS-enabled “Cities” table. And the code in its entirety (including the Flask bits), looks like this:

Flask WTForms Python ORDS REST APIs Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools
NOTE: I'm working off the assumption that you may have some familiarity with flask and WTForms. And also that your focus is the REST API - to - choices part of this problem. 

By the numbers

Okay, a quick overview:

  • Lines 27-29: This is pretty standard for Flask, no explanation needed
  • Lines 31-33: Is where I build out the form:
    • You’ll see that city is the field (to select from)
    • The dropdown will have a heading that says, “Let’s begin by choosing a city” (you can see this in the video)
    • And the choices will be a list []
    • Submit on line 33 is just how you’d create the submit button
  • Lines 37-39: Are also pretty standard for any GET request using the Requests library in python
NOTE: But here, you'll see that my URL is actually the URI provided by ORDS (remember, I REST-enabled my cities table - that is the "endpoint" we are seeing) 
  • Line 40: I make a list (it’s empty at this step, so sad)
  • Lines 41-43: This is the pièce de résistance.
    • Here we perform a loop through the items: in the JSON response, and since this is a nested array, you have to specify what array you want and what exactly in that array you want to pull out (in this case the “city”)
    • Line 43 was a suggestion by thatjeffsmith, to display the list in alphabetical order (I must confess, it does better)
  • Lines 45-48: Are pretty standard for Flask as well. You need to set city (in line 32) equal to the city_list so WTForms knows where the SelectField(choices=[]) comes from

The Code

With the brief explanation out of the way, here is the code. Please, help yourself:

#For the GET request portion, you'll at a minimum need requests and json. If you are working in Flask already, then you know the rest. 
import requests
import json 
from wtforms import SelectField, SubmitField
from flask import Flask, json, jsonify, render_template, request
from flask_wtf import FlaskForm

#Creating the Flask instance.
app = Flask(__name__)
app.config['SECRET_KEY'] = 'password1234'

#Creating the Form.
class CitiesForm(FlaskForm):
  city = SelectField("Let's begin by choosing a city", choices=[])
  submit = SubmitField('Submit')

#Routes, used for displaying the html on your web app.
@app.route('/', methods = ['GET', 'POST'])
def index():

#This is the section where you perform the GET request to retrieve the JSON from your ORDS endpoint.
    URL = '[your ORDS endpoint]'
    response = requests.get(URL)
    r = json.loads(response.text)

#Creating the list of cities; which will be populated shortly.
city_list = []

#Looping through the nested array (from the inital JSON / API response). 
    for nestedArr in r['items']:
      city_list.append(nestedArr['city'])
      city_list.sort()

#Finishing touches so WTForms, and Flask know where to look. And also setting up how/where the html on your web application renders.

    city=city_list
    form = CitiesForm()
    form.city.choices = city_list 
    return render_template('index.html', form=form)

Obviously you’ll want to change up some of the names. But the ORDS pieces should remain pretty similar.

Next up

My next steps are to create a second and third dependent dropdown for the app. What I mean is that, based off a user’s city selection, that city would then be used as a query parameter for a subsequent GET request (to my ADB). And ORDS would respond with a list that includes restaurant information for the target city (everything else would be excluded).

If you’ve made it this far and you have any questions, please send me a note.

And if you think this is worth sharing, and that it might help others, please pass it around!

Cash me outside

As always, you can find me on:

Categories
Autonomous Database Flask Python

Project Overview: python – flask – ORDS – Autonomous Database

Update

For the past week and a half I’ve been immersed in learning how python and flask interact with each other so a developer can create a quick/crude web application. I’ve also spent time becoming more familiar with virtual environments (as they pertain to python + flask), jinja, and WTForms (I’m sure I’m forgetting other stuff too). But if you are like me (circa two weeks ago), then most of what I just wrote means nothing. But that’s okay, you have to start somewhere.

I’m trying to learn all I can because I want to share my ideas (and code) with the Oracle community in the hopes that it will lead to more creative and innovative ways to connect with our Autonomous Databases.

If you recall, as of this writing, I currently have my Autonomous Database (ADB) set up; and my three tables are REST enabled (with the help of ORDS). I also have a newly created “appdev” user (for accessing the database remotely).

And what I’m attempting to do is develop a very basic web/mobile-friendly application in python. An application that will transform the JSON (provided via ORDS) coming from the ADB, into a readable and helpful tool.

Which prompts the first question: how do I intend to do this?

I think the best way to tell the story is by starting at the end, briefly, then going back to the beginning, and then periodically returning to the end, maybe giving different characters’ perspectives throughout. Just to give it a bit of dynamism, otherwise, it’s just sort of a linear story.

David Ershon (as portrayed by the legendary Steve Coogan in the critically acclaimed 2010 film The Other Guys

First off, its ridiculously easy to REST enable tables in your ADB. Simple mouse clicks really, take a look:

Now that I’ve REST enabled my tables, I can take the provided URL (see below in the image) and paste it in a separate browser window. Here you see the default is that the first 25 entries in the target table are returned.

JSON output from my Oracle Autonomous Database, courtesy of ORDS.

Of course, with some manipulation I can do much more than what is seen here. But the point is that everything works, thanks to ORDS (huge time saver). Although, we still need to clean up the presentation (or what a typical user might expect).

The actual application

So what I’d like to do is take this…word salad, and clean it up a bit; make it more presentable. Up until now I haven’t stumbled across anything that takes our JSON (via ORDS) and presents it in such a way using python + flask. I think it would be really neat, and a good chance for me to contribute something more than just funny memes and GIFs.

If you recall, I have three datasets:

  1. Wake County Restaurants
  2. Restaurant Health Code Inspections
  3. Restaurant Inspection Violations

All of which share the same primary key (a restaurant ID). So I thought I would produce something that would use all three of these tables in some combination.

SHOUT OUT: Thanks to Wake County, NC for providing people with these openly-available datasets.

The direction I’m headed is to develop a Minimum Viable Product (MVP), share the code via GitHub, then move onto the next ORDS-inspired project! After spending a few days researching and learning, I’ve cobbled together what I think might work:

A simple mock-up of what this python, flask, autonomous database app might look like.
A simple mock-up of what this python, flask, autonomous database + ORDS app might look like.

This needs explanation. Let me discuss what is going on here.

  1. A user would begin on the landing page (name yet to be determined). This page of course is displayed in html.
  2. Next a user would enter in a restaurant of interest (I’ll have to begin with Wake County, NC as that is all I have access to at the moment). From there the python application would use ORDS to request information from my autonomous database.
  3. Skipping several steps for brevity, eventually this information would then be transmitted back to the user (in html).
NOTE: I intend to follow-up with subsequent posts detailing my development progress. So this should become less opaque as we near the finish line! 

More explanation

Right, so what are flask, jinja, and WTForms?

  • As I understand it, Flask is a web framework that allows python developers to more easily create web applications.
  • Jinja. Apparently writing html is annoying, so jinja is an “engine” that allows developers to write in python-like code and have that code “transformed” into html. Seems like it makes it that much easier to stay in your native language (python in this case).
  • WTForms is used for form “input handling and validation”. My interpretation of this is that python developers aren’t expected to know how to communicate with databases. Much like they wouldn’t be expected to be fluent in html. So it is a way of extended python to handle requests to the ADB.

Learning resources

No single person is an island. Having said that, what follows (in no specific order) are resources that have helped me get to this level of understanding/comprehension. Maybe they’ll help you too.

I’m more of a visual and practical learner, so I’ve primarily been working through tutorials on YouTube and LinkedIn Learning. I found a course on LinkedIn Learning; one that I actually did.

Meanwhile on YouTube, I did a lot of pausing/reviewing/rewinding so I could figure out how everything works together. Luckily, python and flask seem to be very formulaic; not too dissimilar to COBOL.

LinkedIn Learning

Full Stack Web Development with Flask (LinkedIn Learning)

Note: I did sections 1-4. When I got to section 5, I passively followed along for about ¾ of the section, but then reached diminishing returns. I skipped section 6; twas irrelevant.  

YouTube

The two channels I found helpful:

  • Codemy.com (channel) – you’ll want to use the keywords “flask Fridays”; they go as far back as 2021 and are still on-going
  • Corey Schafer (playlist) – a great start-to-finish series for building a flask/python-based web application (although not Autonomous Database + ORDS specific)

Mac set-up

Bookmark this guide if you want to set up your Mac for anything development-related. In my case the following sections were extremely important for this project:

  • xCode (for some reason doesn’t come preinstalled on Mac)
  • Homebrew
  • Visual Studio Code
  • Python, along with:
    • Pip
    • Virtualenv (where I learned more about virtual environments)

Up next

Next up, I’ll be taking what I’ve learned so far and applying it to this project. Since a user wouldn’t log in, update, or modify anything in this application, the complexity should be low.

That isn’t to say it won’t have its challenges. But in a couple weeks I hope to have the front end connected (via ORDS) to the ADB. I’ve got a good start on what the landing page might look like. And of course once it is all polished up and properly commented, I’ll be sure to share the code.

Screen shot of the landing page for this python, flask, ORDS, Oracle Autonomous Database application
Screen shot of what the landing page currently looks like; running locally in a virtual environment (venv). Next up is to add a search bar and then connect to my Autonomous Database via ORDS.

The dig continues

Time to hit the books.

If you want to learn more about ORDS check out these resources.

And as always, you can find me here:

Categories
Social

Hello World :)

WARNING: Turn back now.  You have been warned. 

Who am I?

I’m Chris Hoina. Senior Product Manager at Oracle as (still not sure why they hired me, most likely a clerical error).

I’m a recent “Big Blue” defector; at IBM for nearly three years. There I worked in IBM Z (the mainframe, Big Iron, or Z). Specifically, I was a Product Manager for three different pieces of software focused on application development and delivery. It was a great first step. I’m thankful for my time there and everything I learned (so much brain trust). But a challenging, new opportunity presented itself, and I couldn’t say no.

Now, I’ll be focused on Oracle’s REST Data Services (ORDS). Actually, I’ll be heavily involved what you can do with ORDS (aka Database Management REST APIs) and all the ways you can interact with your Oracle databases.

“These are my confessions…”

Up until this point, my experiences with databases and transaction processing have largely been in Db2, CICS, IMS, and Adabas. And my experiences with SQL have been working with SQL Stored Procedures (both Native and External). But in this new role, I’ll be using various application frameworks to create some simple/quick proofs of concept for talking to your database via ORDS.

Now what?

Today, I’m intrigued by python and the flask framework, so I think I’ll begin there. I’ve seen a lot of good reference material so it should be easy going. But first, part of my work here will be to fully immerse myself in the Oracle ecosystem and learn as much as I can before I begin pumping out more technical content. 

DISCLAIMER: I’m by no means an expert, so this should be a fun learning experience for us all. I’m hoping folks reading this will appreciate my self-deprecating humor and suffering. Regardless, enjoy the ride.

To start, I wanted to do a little exploration. I’m sure exploration will be ongoing, but I may not always post about it, because this won’t be my focus moving forward (and expectations for me are so low right now, that this is probably the only time I can get away with something like this). 

And my first foray into the Oracle Cloud Infrastructure (OCI) ecosystem was with this “Load and analyze data in Autonomous Database” workshop. The tutorial says it takes about 90 mins, but it kept me occupied for an entire afternoon. You can find all the available workshops here. To date, there are 291 available!

I came away with some initial impressions and takeaways from the workshop. And I thought they might be helpful or interesting for some. So, if you care to learn more, then stay tuned as I’ll be posting my write-up first thing next week (I’ll be sure to update and link here too).

Fin

Want to stay up to date with my progress? Then…

“Would you be mine, could you be mine…Won’t you be my neighbor?”

Find me on: