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 Python

Clean up a .CSV file with Regular Expressions, Pandas, and Python

Let us begin

I log into Database Actions as my newly created “Python Developer” and navigate directly to the “Data Load” page (found under the Data Tools section of the Launchpad). I choose to “Load Data” from a “Local File.” I click “next,” click the pencil icon (see the arrow in the image), and navigate to the “File” tab. I scroll to the “RESTAURANTOPENDATE” column and see this:

CSV file in data load, Database Actions, Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Actions

In a previous post (if you’re new, you can read the end-to-end process), I discussed how this time (“04:00:00+00” or “05:00:00+00”) wasn’t necessary for me. At that time, I used the “Find and Replace” function in Excel (I’m sure you can do the same with Numbers, Sheets, or Calc) to replace all occurrences of time with “” (i.e., nothing).

But in the spirit of doing things in five days, when they could have taken but five minutes, I opted to see if I could achieve a similar result in python.

Goal

Create a python script that will allow you to remove part of a value in a .CSV file.

WARNING: I thought this would be a simple task. I should have known better. My approach may not be ideal for your situation, but hopefully you’ll learn something. Or at the very least maybe you can bookmark this post, along with the resources (at the end of the post) I'm including for later use.

Regular Expressions

I am confident there is a way to achieve this goal with the .CSV library in python. There is probably a way to do this with python out of the box. I couldn’t figure it out.

I’m also reasonably confident that my approach is on the verge of ridiculous. Nevertheless, Regular Expressions, and the Pandas library, in python are what worked for me.

What are Regular Expressions?

Good question. I still don’t know, but here is what I found on Wikipedia:

“A regular expression (shortened as regex or regexp; also referred to as rational expression) is a sequence of characters that specifies a search pattern in text. Usually, such patterns are used by string-searching algorithms for “find” or “find and replace” operations on strings, or for input validation. It is a technique developed in theoretical computer science and formal language theory.”

That is so painful to read, but the way I understand it is that we use Regular Expressions in pattern-matching. Essentially you create a pattern and then tell your application (or script) to search for it. From there, you can include more code to perform more actions. In my case, those actions would be to find a pattern and replace that pattern with nothing.

So what is the pattern?

One of the benefits of having zero formal training in application development (in this case, computer science and formal language theory) is that occasionally, you might take an approach that, while unintuitive, works well enough.

And after many, many hours of trial and error, parsing through Stack Overflow, reviewing hours of YouTube, reading pages of blogs, and occasional use of the “I’m Feeling Lucky” button on Google, it occurred to me that my pattern was:

Pattern for Regular Expressions, Database Actions, Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Actions

After reviewing more documentation and other various resources (I have an entire section at the end), I more clearly identified a pattern:

A more formal pattern for Regular Expressions, Database Actions, Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Actions

Coming up with a clearly-defined pattern helped inform me as to how I could create the following Regular Expression:

'\s\d+:\d+:\d+[^a-c6]\d+$'

I then did some more stuff, and that was it! Follow me for more…

I’m kidding.

Deep Dive

I can’t teach you everything, because I’m probably only ahead of you by a week at this point. But I can explain, in the hopes that it will make sense. Let me continue with the above Regular Expression (again, you’ll want to spend some time in the resources section I’ve included to understand better how this all fits together).

But the above Regular Expression can be defined like this:

Regular Expression definition for this script, Database Actions, Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Actions

I then took the above and plugged it into this nine (12 with sections, slightly more with comments) line script:

from pickle import NONE, TRUE
#This doesn't seem work without pickle, still not sure why
import pandas as pd
#You need this to do anything related to dataframes (i.e. "df"); 
# if you're a data scientist and you use python, you may already 
# be familiar with this
import re 
#You need this for Regular Expressions to work

d = '/Users/choina/Documents/untitled folder/Restaurants_in_Wake_County.csv'
#I'm assigning the file to "d"
df = pd.read_csv(d)
#df means dataframe. Here I'm telling panda to read the .CSV file 
# that I just created ("d"), and consider that the "dataframe".

print(df.dtypes)
#This is optional, but when I do this I can see what the various 
# datatypes are (according to pandas). The RESTAURANTOPENDATE column 
# is an 'object'.
for RESTAURANTOPENDATE in df:
    df['RESTAURANTOPENDATE'] = df['RESTAURANTOPENDATE'].str.replace('\s\d+:\d+:\d+[^a-c6]\d+$', '', regex=TRUE)
#This is a "For Loop" that says set the RESTAURANTOPENDATE column 
# contents equal to the following: FIRST, consider the values in the 
# column as string and replace the contents using this process: 
# find a pattern that matches this Regular Expression, replace
# it with NOTHING, then take that column,along with the other 
# columns and...
df.to_csv('newrest.csv')
#Save it to a new .CSV file called "newrest.csv"

Code as an image for reference:

Python script to clean CSV file for data load, Database Actions, Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Actions

In short, (I’ve left detailed comments for reference), these nine lines of code search for a pattern and replace it with nothing when found. We then take these results (including the rest of the .CSV file) and save them to a new file called “newrest.csv.”

Please, reference this script. Tweak it as needed. Or drop me a note if you need an outside perspective. Good luck!

One more thing

Pickle, Pandas, and RE are all required for this script to work.

Note: While I understand Pickle is a library that deals with the serialization of objects in python. I've no idea what that means, and reading about it makes my brain melt. Additionally, I’m not sure if this is expected behavior, but when I ran an earlier version of this script, this "Pickles" library just appeared at the very top. If this has happened to you, please leave a comment, because I'm scared there may be an actual ghost in my machine.

Moving on

The rest is pretty straightforward.

I then went back into Database Actions (it had been so long that I’d expected a new version had been released already), loaded my data, and inspected the column in question:

Updated CSV file in data load, Database Actions, Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Actions

Looks good to me!

Magic

Something I noticed was that my Autonomous Database automatically recognizes the “RESTAURANTOPENDATE” column as a data type of “Date.” I didn’t have to do anything special here, and I thought this was pretty cool. Take a look:

Database Actions automatically recognizes datatypes Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Actions

Addendum to the addendum

Once imported, you can also use Regular Expressions in your database (with SQL). But if you’d like to clean your data before it reaches your ADB, the approach I’m writing about here is also a great option!

Resources

As promised, here are all the resources I’ve curated that might assist you should you choose to take the road less traveled…

  • Important/relevant documentation:
  • Stack Oveflow – this is a series of “bookmarked” questions about Python and Regular Expressions, that helped me. They might be of use. They aren’t intended to teach you, but rather aide you in gaining a better perspective.
  • YouTube – a playlist I created that might be worth reviewing (at a minimum watch Case Digital’s “How to Remove Characters From a Pandas Dataframe In Python” video).
  • Regular Expression Editors:
    • Pythex (make sure you click the “Regular expression cheatsheet” button to expand/see a valuable quick reference – also, apparently a more complete version can be found here)
    • Regular Expressions 101 – this includes an enormous library of previously created Regular Expressions. There is a “debugger” too!

An even simpler solution

I discovered an even simpler solution to this problem. As I was reviewing the Pandas documentation I noticed there was functionality built directly into Pandas. This can be done with the “datatime” function.

And you can take it one step further with what is referred to as a “.dt accessor.” (you’ll see this in the updated code I’m including). This accessor allows you to manipulate a Series (aka a column) within a Pandas dataframe (df).

There are quite a few time-date properties you can manipulate in Pandas, all of which can be found here. The “Time Zone Operations” section is where the “.dt accessor”, is briefly mentioned. For more context, I’d recommend reviewing this section on the .dt accessor and how they interplay with a Pandas Series.

Don’t stop there though, the main python documentation discusses the basics of date and time operations. Without further ado, on to the new code:

import pandas as pd 
import numpy as np 

#You don't have to do it like this. You could have a single "d" and then just comment/uncomment each line as needed. 

d1 = '/Users/choina/Downloads/Food_Inspections.csv'
d2 = '/Users/choina/Downloads/Food_Inspection_Violations copy.csv'
d3 = '/Users/choina/Downloads/Restaurants_in_Wake_County copy.csv'

#The same thing here, you could just have a single "d" and then just comment out the other two, when not needed.

df_1 = pd.read_csv(d1)
df_2 = pd.read_csv(d2)
df_3 = pd.read_csv(d3)

#Same goes for this too. This is either very slick of me, or incredibly lazy. 

df_1['DATE_'] = pd.to_datetime(df_1['DATE_']).dt.date
df_2['INSPECTDATE'] = pd.to_datetime(df_2['INSPECTDATE']).dt.date
df_3['RESTAURANTOPENDATE'] = pd.to_datetime(df_3['RESTAURANTOPENDATE']).dt.date

#Same, same here as well. You could do one at a time. But it works, I double-checked my work. 

df_1.to_csv('newninspect.csv')
df_2.to_csv('newviolate.csv')
df_3.to_csv('newrest.csv')
New python script to change date time in csv files, Chris Hoina, Senior Product Manager, ORDS, Database Tools, Oracle Autonomous Database

About the update

This code is certified fresh – meaning, it works. I triple-checked. The only thing I’ve noticed is that lines 12-14 must-have “.dt.date” at the end. From what I am reading (and am inferring from the documentation), it looks as though you need to first “treat” the Series with the “to_datatime” function. After that, the entire dataframe is in limbo (not quite written out to a new .CSV), waiting. Before the next step, we can strip the time portion out using the .dt accessor (i.e. the “.dt.date” portion located at the end of lines 12-14).

From there it is simply a matter of writing out these updated dataframes to the three new expectant .CSV files.

Find Me

Please come back for more. I’m going to be working on views in my Autonomous Database, and then later, I’ll REST-enable them with the help of ORDS!

Categories
Autonomous Database Python

GET requests with Python, ORDS, and the Oracle Autonomous Database

Problem

I wanted to take the Objects,

Objects in SQL Worksheet in Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools,
Objects

In my Admin schema and copy them into a newly created Python Developer database user/schema. I have been meaning to do this for a while now since I’d to approach all future development work as a typical [developer] user might.

I previously created a “developer” user, but the recommendation was to create a user specifically for python development. This recommendation makes sense because I’d like to explore other developer frameworks — JavaScript/React, Derelicte, Golang, Hoobastank, Swift, Smashmouth.

DISCLAIMER: Some of the aforementioned languages/frameworks are entirely fictional.

So in creating this user and populating the schema, I could have just copied these objects over in seconds. I’m sure some already existing would have enabled me to do this. A less-elegant option would be to do a local upload. I’m confident I could export the tables of my Admin schema and then upload them as a developer in Database Actions. Doing so here:

Data Load in Database Actions Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools

But those options were too straightforward, effortless, and expeditious. I needed an approach that would make me question my life decisions. Something that would force me to rethink what it meant to be human.

Mostly, I wanted an excuse to tinker with ORDS some more. While I didn’t start with a well-defined use case, I was able to retcon (a portmanteau of the words retroactive and continuity) one.

Scenario

The scenario is that we have REST-enabled schema objects (in this case, various tables), and maybe we want to execute GET requests on that table. Perhaps we want all the data from that table (having been returned in JSON). And for whatever reason, we want to save this information in a JSON file. Plausible? Not likely. Possible? If I can do it, then yes, 100% possible.

Solution

NOTE: I've since disabled REST for these tables. So none of these URLs currently work. Also for security reasons you probably want to a require authentication to access these. The below screenshots and code snippets are for demonstration purposes.  In this case, probably acceptable, but please (PLEASE) exercise caution if you are recreating at your workplace.

REST-enabling

When you REST-enable a table, you’re provided a URL (aka a REST Endpoint) for viewing. It will look something like this:

https://gf641ea24ecc468-dbmcdeebyface.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/scores/

The response looks like this:

First 25 results via ORDS Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools,

You are seeing the first 25 results from my “Scores” table. I’ve also created an alias name for that table as an extra level of protection. The table’s actual name is not “Scores,” but rather something else.

If you collapse the “items,” it’s easier to see the rest of the “name: value” pairs:

Collapsed results via ORDS Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools,

I can do my best to explain what we see here:

  • ‘items: […]” are the individual entries in my table (the columns would be the items you see in the black bold). ORDS includes links for calling that single entry only
  • ‘hasMore”: true,” essentially means that there are more pages with information to follow
  • “limit: 25” refers to the fact that ORDS is only returning 25 results at a time. I didn’t set this; it was the default.
  • “offset: 0” this is tricky. From what I can tell, the offset only becomes a consideration after the first page. You begin with no offset (this makes no sense, I know, but I will explain this shortly)
  • “count: 25” – we have entries 1-25 on this page
  • “links: […]” these are all pretty self-explanatory. Two links, in particular, are pretty interesting. They are:
    • “describedby” and
    • “next”

If I click the “describedby” link, you’ll see something that looks like this:

Described by URL ORDS Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools,

On this page, you can review valuable information without ever logging into Database Actions. I found this extremely helpful; from here, I could see the table name, Primary Key (if it exists), and the column names and types for the table.

Another link I wanted to highlight was “next.” If you look closely, you’ll see that the “next” URL includes an offset of zero:

Initial 25 results via ORDS Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools,

However, if I click that URL (I’ve collapsed all the items on this page as well), I can see that “offset” has increased to 25, and the new “next” URL shows “offset=50”. This next part will do a better job of explaining what is happening.

Next 25 results via ORDS Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools,

Since we are on the second page of results, ORDS knows that we began with an offset of zero (the beginning of the table) and a limit of 25. Each “next” URL will increment by 25 (the offset) and return 25 results at a time. And it will keep producing “next” URLs until the “hasMore: false” condition is finally satisfied. We can modify a lot of this, but this is what ORDS does for you automatically/by default. Great news for me because at my current level of experience, the more steps that I can automate, the better!

Python

Prepare yourself for a bit of a leap. At this point, I was pretty confident I could copy/paste my way through some python code and pull down this data, and then save it in some format.

Finally, after about a week, I was able to duct-tape something together. Courtesy of Google, StackOverflow, so many YouTube video tutorials (so, so many), here is what I’ve arrived at:

import requests
import json 

# requests is a python library that allows you to send GET, 
# PUT, DELETE, HEAD, and OPTIONS requests. I'm just doing 
# GET requests though. The json library comes with python, 
# but I need it here to be able to work with the json that # is returned. I'll also need it to create the json file 
# (at the end).

offset = 0

# I'm basically telling this little script that we should 
# expect to start with an offset of zero. If you recall, 
# this just means we are beginning on the first page. 
# Hence, there is no offset. 

url = 'https://gf641ea24ecc468-dbmcdeebyface.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/places/?offset={}'

# I've added in this modifier {}, which, as you'll see in a # bit, allows me to increment by 25 with each subsequent 
# GET request.

hasMore = True

# Read this as "hasMore is equal to True". In other words, 
# if there is more STUFF, then we should see "true" (go 
# back and look at the screenshots if you don't know what 
# this means.)

while hasMore:

# We are saying, "While 'hasMore is equal to true, then..."

    response = requests.get(url.format(offset))
    
#...recognize that "response" is a GET request that 
# consists of the URL (from above) plus whatever the offset # is (we'll indicate the offset below).

    data = response.json()

#data is just whatever the results are from the above 
# response.

    filename = 'places.json'

# "filename" is the json file we are going to create to 
# put all these results into.

    if data.get('hasMore'):
        offset += 25
        with open(filename, 'a') as json_file:
            json.dump(data, json_file)
    else:
        break 

# I'm going to switch to narrating here, this one is just 
# too verbose. In lay terms, we are saying: if the GET 
# request "data" (which is essentially a composite variable # at this point) has "hasMore: true" on the page, then keep # doing this next thing. 

# With the next "thing" being: take the results from that 
# URL and save it into a file called "places.json". Once 
# you've done that, keep doing it, but add 25 to the offset # for that URL for subsequent GET requests. And whenever 
# you have results, append it (that's what that "a" is on 
# line 44) to the file you created, and just keep going 
# until "hasMore" doesn't satisfy that condition on line 
# 20. 

# If it doesn't satisfy that condition then that means 
# "hasMore" is equal to false. In other words, there is 
# nothing more to request, so stop (that is what the "else: # break" means). 

I’m going to include an image to see the nesting better.

python script for ORDS, Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools,

And once I run this, I’m left with a JSON file (“places.json”) that has all my entries saved à la:

json file output from ORDS, Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools,

Now what?

At this point, I have a few options. I could:

  • include code that would convert this JSON file to a . CSV file
  • include code to indent the JSON file, making it more readable, or
  • drag and drop it into my Autonomous Database (via Data Load in Database Actions) with some slight tweaking (I just tried, so I already know I need to do more massaging)

The End

So what have we learned here? Even somebody with very basic skills can interact with an Autonomous Database via ORDS.

If you have an internet connection and are eager enough, the sky is the limit. Putting things into perspective, I wouldn’t have been able to do this about a week and a half ago.

Truthfully, you’re not that far behind! I encourage you to give it a go.

Find me

Categories
Autonomous Database

Updating VARCHAR2 fields in Oracle Database Actions

Update

I’ve run into more problems with this python/flask/ords/adb app idea of mine. Turns out I’m too [developer] dumb to do some of the things that I’d originally envisioned. Now that I’ve become more familiar with python, flask, the Oracle Autonomous Database, and our Database Tools (like ORDS and Database Actions), I’m realizing that I need to rethink my approach.

First off, I’ve updated the (not trying to be pretentious here) User Journey for this project.

SIDE NOTE: The User Journey is something that I really should pay more attention to (especially considering it was one of my concentrations in my MBA program, and it was a huge initiative in my previous role at Big Blue) and you should too!

Ch-ch-ch-ch-changes

I’m leaning towards (after a recent discussion with that jeff smith) more of a drop-down style of search. I’d like to present a user with all the available cities (having taken these from my “Restaurants” table) in Wake County.

I think I can populate a list of cities to choose from with the help of REST APIs (via ORDS). But I need to do some clean-up first.

Side note

In about a minute, you’ll probably come to the realization that my approach is less than elegant. Remember, I’m not a developer. I’m kind of stumbling through this, and I’ll need to make some sacrifices along the way (we call those “trade-offs” in business-speak). Unfortunately, there are simply some techniques/skills that I do not know yet. For a visual representation of these deficits, I draw your attention to:

Me the develper versus an actual developer, Chris Hoina, Senior Product Manager, ORDS, Database Tools, Oracle Autonomous Database, Database Actions

Back to your regularly scheduled program

On to clean-up. The cities in this table are a bit all over the place. Let’s take a look.

From Database Actions, I can navigate to a SQL Worksheet. From there I can inspect my tables (Right-click > Edit), or jump straight into the SQL. Since I know my “CITIES” column is the problem, I’ll run this:

SELECT DISTINCT CITY
FROM ADMIN.RESTAURANTS
ORDER BY 1

Some screenshots of what this looks like in Database Actions:

NOTE: The "DISTINCT" parameter drops any duplicates, "ORDER BY" returns results in ascending order.

I suspect that the program used to input health inspections allows for some level of free text.

Tangent / Story

I’ve used an application similar to this in a previous life, but it was a Computer Aided Dispatch (CAD) application. You could enter in free-text, or select from users’ previous entries. There was a lot of flexibility, but because of that, there were many variations on things like street names, cities, locations, parking lots, person names, etc. For the user, awesome, for the developer or database administrator, less so.

At this point, it is pretty clear the amount of normalization I’ll have to do to all these city names. Now I just need to figure out:

  • case to use (Upper, Sentence, Lower, SaRCaSTic, etc.)
  • city name variations to use

I randomly chose a city with the following SQL command:

UPDATE Restaurants SET CITY = 'Holly Springs'
WHERE UPPER(CITY) = 'HOLLY SPRING'

In this example, I searched for all instances of “Holly Spring” and replaced them with the correct “Holly Springs”. I can then repeat the process for all other cities. Again, the abridged walk-through of this is here.

I actually know this data well, since I grew up in the area. So deciding on the correct city names shouldn’t be too challenging. But familiarizing yourself with your own data might not be such a bad idea. For instance, in this table, one of the cities displays as “Research Triangle Park”. I don’t think it’s common to refer to it like this; we usually just call it “RTP”. I think little details like that really help to elevate your application.

Next steps

I’ll keep this brief. But what I think I’m going to have to do next is create a REST API based on this Restaurant table. I should have a better idea in a few days. But the idea is, that once a user selects a city, it will pass that city as a parameter to a GET request (again, courtesy of ORDS) and then return additional data. A user would then be able to further filter on that returned data, eventually ending on their target restaurant.

Okay, stay tuned…as I feel like I’m on the precipice of something exciting!

Catch me if you can


Abridged walk-through

Select Worksheets in Database Actions Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools

From Database Actions, navigate to the “SQL” feature. You’ll see this screen appear.

NOTE: I had a previous SQL Worksheet saved, but you can enter SQL statements directly into the default worksheet. 
Before city field has been updated in Database Actions Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools

*If you expand, you can see the statement that I used.

SELECT DISTINCT CITY
FROM ADMIN.RESTAURANTS
ORDER BY 1

This will produce a list of all the cities in their current form. Notice the four different variations of “Fuquay-Varina”.

Updating fields in City Column Database Actions Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools

I’ll comment out lines 1-3 and enter the following SQL statement to update Fuquay-Varina:

UPDATE Admin.Restaurants SET CITY = 'Fuquay-Varina'
WHERE UPPER(CITY) = 'FUQUAY VARINA'

I use the “DISTINCT” parameter to drop any duplicates in the results. “ORDER BY” returns results in ascending order.

You’ll see that 77 rows have been updated.

After Fuquay Varina has been updated in Database Actions Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools

I’ll un-comment lines 1-3, and comment lines 5-6.

Based on the results, we’ve reduced the variations of “Fuquay-Varina”. I’ll continue this work until I have a smaller set of more consistent cities.

Categories
Autonomous Database

Create a user in Oracle Autonomous Database [for Dummies]

An OCI user does not ≠ ADB user

Creating an additional user in your Oracle Cloud Infrastructure (OCI) is not the same thing as creating an additional user in your Autonomous Database (ADB). I spent about two days (on and off) last week, wrapping my head around this. Maybe you knew this…I did not.

Chris Hoina – 2022

If however, you want to create an additional OCI user; one that would be able to access various resources in OCI, then please bookmark this page. This is all you need to get started.

However, you may not even need to create this additional user (as was the case with me). While I followed the steps in that documentation (very simple/straightforward actually), it wasn’t till after I finished, did I realize that doing it was completely unnecessary. What I was really trying to do was create a new database user, not an OCI user. The steps are different, simpler.

Federations

Another area that I got really hung up was with “Federations”. This was a relatively new concept for me. And I’m not sure if I should even do a deep dive on how Federations work. However, let it be known, if I receive even one (legitimate) comment on this subject, I will put together a treatise on Federations in OCI.

It took me about a day and a half to educate myself on how Federations and Identity Providers work in OCI. So, if you are reading this, and you think you’d benefit from a standalone article, then let me know. Otherwise, this page and this page are both very helpful for learning more about Federations in OCI.

Are you me?

If so, then you are acting as an administrator in your Always Free OCI account, as well as the database administrator. Being both is confusing for me, and maybe it is for you too. Since I don’t consider myself a traditional user (I’m blurring the lines when it comes to the different roles), I’m tasked with managing my OCI tenancy and also setting up my development environment. All this so I can begin to work on some applications/proofs of concepts (POCs) for interacting with my Autonomous Database via ORDS (Oracle REST Database Services aka our REST APIs).

Don’t be like me and allow yourself to get too weighed down with all the technical jargon thrown your way. Naturally, our docs read like they are geared toward the System or Database Administrator. And this makes sense; remember when you are in your tenancy you’re the de-facto admin for everything. At a larger organization/enterprise a developer would probably never do any of this setup. You’d just sign in to the database directly or connect via a command line.

Workflow for the “Every Person”

But for me (with my limited experience), the workflow looks something like this:

Start here

Sign-up for an Oracle Cloud Account. You’ll be provided a Cloud Account Name* and credentials.

*You’ll also hear this account being referred to as your Tenancy name (you can modify the name later if you want).

When you sign in, you can quickly tell if you are the administrator by seeing if the "Service User Console" option is available. This is the administrator for the Oracle Cloud Infrastructure. Your tenancy.

Next, create an Autonomous Database and create the “ADMIN” credentials* for said database. You can see how to do that in Lab 1 of this workshop

REMINDER: This administrator is different that the OCI Tenancy administrator.

Add a new user by:

Navigating to your Autonomous Database under "Oracle Database".  It might be in your recently viewed.

Navigating to your Autonomous Database under “Oracle Database”. It might be in your recently viewed.

Once you've created your Autonomous Database, navigate to Database Actions. This is where you need to go to create a new user.

After you click the database, you’ll see this screen. Click “Database Actions”. You’ll link out to the Database Actions dashboard.

Navigate to the Administration tile, and click it. It will take you to a dashboard of all current users, along with an option to create a new user.

Select the Database Users option in the Administration section.

Create a new user. Afterward, you'll be provided an URL that allows a user to directly log into the Oracle Autonomous Database Actions Launchpad.


Select the “Create User” option.

Toggling the Web Access switch will automatically grant the user the CONNECT and RESOURCE roles.

At a minimum, you’ll want to enable “Web Access” for this user. This will automatically grant two roles for the user:

  1. CONNECT
  2. RESOURCE

Both are needed for the developer though.

Here you can see all the available roles. The CONNECT and RESOURCE roles are already checked because you will have selected "Web Access" in the previous tab.

If you want to take a look at the different roles available, click this tab. Scrolling through, you can see the CONNECT and RESOURCE roles are checked.

HINT: You can always go back and edit the roles if more roles need to be granted. Both are needed for the developer though.

Finish (kind of)

You can see the URLs in this image.  This, along with the login credentials are what should be provided to the user.  They can log in to the Database Actions console directly.

Once complete, you’ll see a URL in that user’s newly-created tile. This URL will link you out to a login page, for accessing the Database Actions console. Since you are the only user, you’ll just want to document the URL. Otherwise, as the admin, I’m assuming you’d share this with the respective recipient.

Next Up

From here on out, I’ll do all my development work with this account (I called mine “appdev”), to mimic what a typical user might encounter in a practical setting.

Right now, I’ m wrapping up a python + flask + database course on LinkedIn Learning. So far, its been pretty informative. If you are interested, you can take a look as well (to see the direction I’m headed). My goal will be to use the templates in this course as a resource for connecting to my database, but with ORDS.

I hope to have something small in the next week or so. And I’ll be sharing here, and on my GitHub as well. So stay tuned.

Helpful resources

What I did here was very simplified, a distillation for creating a new user in your Autonomous Database. But I’ll include some of the resources that helped get me to this level of understanding:

Find me

And that’s it for now. But if you want to follow along then check me out at these places…