Tag: ORDS

  • Python POST requests three ways with Oracle REST Data Services (ORDS)

    Python POST requests three ways with Oracle REST Data Services (ORDS)

    The simple POST request

    It was bugging me that I couldn’t perform a simple Python POST request to an ORDS REST-enabled table.

    I wanted to send HTTPS POST requests to this table in my Oracle Autonomous Database, ORDS, Oracle REST APIs, Chris Hoina, Product Manager, Database Actions, Database Tools
    This one actually…

    I don’t mean to convey that this isn’t possible. Up until very recently, I wasn’t able to do this. Luckily I had a few hours free, so I took to the docs to do some reading. And wouldn’t you know it, like most things in tech, reading the documentation was a practical and valuable use of my time.

    Side note

    Let me pause here for a minute or two. I should add this disclaimer that none of what I share here uses OAuth 2.0 authentication. Of course, I wish it did, but I’m just not there yet (technical proficiency). In the future, I’d like to update this with security in mind1:

    1We have an API security section in our ORDS Best Practices guide.

    The other thing I’ll mention is that I’m going to include the SQL for creating this table along with all the Python code in my GitHub repo (I’ll also add any code updates to my repo! will also add any updates I make to this code to the repo!).

    Also, did you know that saving DDL with Database Actions is just a mouse click away?

    New User speed run

    This section is a bit of an aside, but I also created a new Python Developer user in one of my Autonomous Databases. It’s straightforward to do as the admin. Here is a “speed run”:

    POST haste

    After creating my new user, I created a “Python_Post” table. Super imaginative, right? And I kept things simple, naming the four columns (wait for it): "col1", "col2", "col3", and "col4".

    Columns in POST table in Oracle Autonomous Database to test requests in Python, ORDS, Oracle REST APIs, Chris Hoina, Product Manager, Database Actions, Database Tools
    Damn…he actually did that.

    Cheat codes

    I auto-REST enabled my table and reviewed a Bash cURL command so I could remind myself of the expected data (aka payload).

    Reviewing a Bash POST cURL command for reference in my Oracle Autonomous Database, ORDS, Oracle REST APIs, Chris Hoina, Product Manager, Database Actions, Database Tools
    A Bash cURL command for POST requests; can be used as later reference in your editor.

    I’ve noticed that if I don’t specify a ‘rowid’ the Autonomous Database automatically does this. SQL-newbies (like me) might appreciate this since we still don’t know all the best practices for database design (or DDL, for that matter)!

    My process might differ from yours, but I’ve used the cURL commands in Database Actions as a cheat. I’ve been copying/pasting this cURL command into my working Python file, so I don’t have to switch screens. Additionally, it helps to remind me what the {‘key’:‘value’} pairs are (even though I just created all this, I STILL can’t remember what I did).

    In this case, I’m referencing a POST request, but you could do this for the other HTTPS methods too:

    • GET ALL
    • GET
    • POST
    • BATCH LOAD
    • PUT
    • DELETE

    Moving on…

    I could omit the ‘rowid’ when making these POST requests. I don’t know if this is typical when working with databases, but this seems to work consistently (at least with the testing I did) with Python and the Requests library.

    If you weren’t taken aback by my imaginative table name and column names, then get ready because I’m about to blow your mind with this next bit. I created a payload for each of these POST request variations:

    Oracle Autonomous Database, ORDS, Oracle REST APIs, Chris Hoina, Product Manager, Database Actions, Database Tools
    And I’m just going to be as literal as possible to avoid confusion…is it lame? Yes. Does it work? Undecided, you tell me.

    In case you didn’t catch it, they were: payload1, payload2, and payload3.

    On feedback…

    Lack of communication is disturbing, Oracle Autonomous Database, ORDS, Oracle REST APIs, Chris Hoina, Product Manager, Database Actions, Database Tools

    I also included some feedback for myself. And I feel like this is a good practice because if you successfully POST something, how would you know? Conversely, the same would be true if you unsuccessfully POSTed something. If I were smart, I’d design a REST API in Database Actions that automatically includes some feedback. But I am not.

    If you want to read about this Implicit Parameter and others, click here.

    Luckily, the Python Requests library includes various feedback methods. I’ve included the following in my Python file:

    • status_code – returns the response status code
    • raise_for_status() – this will display the error message (if applicable); displays "None" if the request was successful
    • headers – returns the server’s response headers as a Python dictionary

    Different payloads

    My Python code is simple enough. It looks more than it is because I include three POST request variations. I’ve also tested payloads, and it seems like we can send a "payload" as:

    • params – query parameters (e.g. https://httpbin.org/get?key2=value2&key1=value1)
    • json – JSON data
    • data – a Python dictionary (in this use case)

    Oracle REST Data Services has a ton of documentation on filtering with query parameters… I’m still learning about it, but they are powerful. Check them out here.

    You can even send payloads consisting of nested items/objects, too (e.g., an array or list as one of your values). I’m sure this violates normalization rules, but it’s possible. In my case, I didn’t do this; I just stuck to my four columns.

    Executing the code

    After I executed the code, I received the following feedback for these POST requests:

    Feedback from post to my Oracle Autonomous Database, ORDS, Oracle REST APIs, Chris Hoina, Product Manager, Database Actions, Database Tools
    If you squint, you can see three separate responses. I’ve only boxed one, but you should be able to point out the other two.

    You should see:

    • a 201 status code, which indicates “the request has been fulfilled and has resulted in one or more new resources being created2.
    • None – which if there were a 400 error, that would show me the error message
    • {key: value} pairs displaying all header details

    2About the 201 Status Code in Hypertext Transfer Protocol (HTTP/1.1): Semantics and Content

    Double checking

    While in VS Code, the POST requests appeared successful. But since I had access, I logged into Database Actions and manually inspected my table. Success!

    Double checking the post requests in database actions, Oracle Autonomous Database, ORDS, Oracle REST APIs, Chris Hoina, Product Manager, Database Actions, Database Tools
    Reviewing the new updates in Database Actions.

    And that’s it…zero to POST request in my Oracle Autonomous Database + ORDS in no time!

    Was this helpful?

    I have this code in my GitHub repository, so feel free to fork, download, or add comments. But I’ll include here too:

    import requests 
    
    url = "[Your ORDS URL goes here]"
    
    payload1 = {'col1': 'test1', 'col2':'test1', 'col3':'test1', 'col4': 'test1'}
    
    r1 = requests.post(url, json=payload1)
    
    print(r1.status_code)
    print(r1.raise_for_status())
    print(r1.headers) 
    
    payload2 = {'col1': 'test2', 'col2':'test2', 'col3':'test2', 'col4': 'test2'}
    
    r2 = requests.post(url, data=payload2)
    
    print(r2.status_code)
    print(r2.raise_for_status())
    print(r2.headers) 
    
    payload3 = {'col1': 'test3', 'col2':'test3', 'col3':'test3', 'col4': 'test3'}
    
    r3 = requests.post(url, params=payload3)
    
    print(r3.status_code)
    print(r3.raise_for_status())
    print(r3.headers) 

    And if you are still here — was this helpful? Want to see more or something else that is Python + ORDS-related? Let me know here in the comments, on Twitter, or by email!

  • Flask, Python, ORDS, and Oracle CloudWorld 2022

    Flask, Python, ORDS, and Oracle CloudWorld 2022

    Python, Flask, and ORDS

    Restaurants Web Application update

    If you’ve been following along, then you are probably aware of the python/flask/ORDS journey that I’ve embarked on. If not, you can read up on the overview here. The idea was to take local restaurant health inspection scores (aka Sanitary grades) and present the data to a user in a visualization. The web framework I’m using is based on Flask, a popular Python web application framework. And we’ll interact with our Oracle Autonomous Database using our Oracle REST Data Services APIs (for both GET and POST requests).

    That progress is moving along, and I’ve been toying with the Folium library for map visualizations.

    If you are familiar with Leaflet.js then you basically know Folium – a python wrapper used for displaying map renderings from Leaflet. You can get a taste of what I’m working towards in this latest post.

    However, this project is on hold (very, very) temporarily while I become familiar with another Python / Flask / ORDS web application.

    eCommerce/Retail Web Application [NEW]

    Currently titled “Frank’s Franks”, this application is what I’m referring to as an eCommerce type application. You are presented with product information, you then make a product selection, and create an order. There is no credit card gateway, but the application does actually POST order information to a live Autonomous Database.

    This application is the brainchild of our very own Brian Spendolini. But I’m taking a crack at making some additions to it with what I’ve learned from Folium and Bootstrap (HTML and CSS). I’m not sure of the exact final state, but you can follow along as we continue iterating on it (more on this in the GitHub section).

    And here is a very crude example of the index (aka “home”) page:

    And here is a nice screenshot of the python code along with some in-progress HTML:

    Our Flask application is on the left, with the two additional right panes showing the base.html and index.html pages.

    I’m showing this because this will be one of the practical labs that our team will be facilitating in October. Why October you ask? Great question…

    Oracle CloudWorld

    Because that is when Oracle CloudWorld is happening. Queue gratituous copy/paste in 3…2…1…

    Join us at CloudWorld, the new global conference where our customers and partners can share ideas, develop in-demand skills, and learn about cloud infrastructure and applications solutions that serve their unique roles and business needs. At CloudWorld, you’ll experience first-look product demos by Oracle experts, access hands-on training and certification opportunities, and shape the cloud innovation conversation alongside your peers and industry leaders.

    Now I’m not sure who on the team will be attending, but rest assured, one of us will be there.

    GitHub

    Finally, GitHub. Why am I including an ominous “GitHub” section? For two reasons. First, if you want to follow along with the progress of this CloudWorld lab, I encourage you to peruse my repo, which can be found here.

    Second, I’ve started to build out a python-themed library that will document code snippets and frequent code patterns when working with popular libraries + ORDS. The obvious ones are things like “requests” and “pandas”. And a lot of it is things that I have encountered personally. But I’m discovering there is so much more out there for python folks, so hopefully, this will continue to expand.

    If you do visit the “library” you’ll see that I have created some ORDS endpoints based on historical U.S. home sale data. You can check it out here.

    Current state:

    GitHub repo of python ORDS library code patterns, code snippets, Chris Hoina, Database Tools, Oracle Autonomous Database
    Those endpoints are actually enabled, so you can work with them without having to authenticate. Now of course you could totally mess things up and POST or delete whatever...but lets try to avoid that. 

    But wait, there’s more

    You have both an invitation and a request.

    An invitation to follow along with both the lab progress and the python library project.

    And a request to

    • submit ideas and possible areas of improvement (or cool code patterns) for this CloudWorld application,
    • and submit any requests you might have for the python library that I’ve started to build-out

    This is all voluntary of course, but if you think any of this might benefit please do engage and share!

    Finally, I’ll be back to regularly-scheduled technical posts soon. In the meantime…

    Keep following

  • Python or Filtering in Queries: sorting JSON responses from an Oracle Database

    Python or Filtering in Queries: sorting JSON responses from an Oracle Database

    In a recent post, I discussed taking the JSON response from my REST-enabled database table (which looked like this):

    URI from ORDS REST API enabled table, Chris Hoina, ORDS, Database Tools, Oracle Autonomous Database

    And sorting those cities after I performed a GET request (using some python code). However, after a recent discussion and a suggestion from thatjeffsmith, I think I should share another way you can sort this data from the source.

    Background

    If you recall, the portion of my python code that:

    1. Performs a GET request
    2. Extracts the cities to a list, and
    3. Sorts them in ascending order,

    …looks like this:

    def city_choices_python(): 
        import requests
        import json 
        city_list = []
    
        URL = 'https://gf641ea24ecc468-dbmcdeebyface.adb.us-ashburn-1.oraclecloudapps.com/ords/pythondev/cities/'
       
        response = requests.get(URL)
        r = json.loads(response.text)
        for nestedArr in r['items']:
            city_list.append(nestedArr['city'])
        return sorted(city_list)

    As you can see, in this version the URI provided by ORDS remains untouched. I’m just using that simple “sorted(city_list)” function to return a list of sorted cities in ascending order (FYI: its the last line in this code block).

    Filtering in queries

    This approach worked fine in my case, however I am now…enlightened…

    Meaning, I am now aware that I have other options. It hadn’t really occurred to me that I could filter in queries at the time of the get request.

    Filtering is the process of limiting a collection resource by using a per-request dynamic filter definition across multiple page resources, where each page contains a subset of items found in the complete collection. Filtering enables efficient traversal of large collections.

    ORDS Docs

    What I like about this is that filtering in queries doesn’t require any changes to the the original URI. Meaning, you wouldn’t need to go back into (in my case I’m using…) Database Actions to modify anything. You simply include filters along with the URI.

    In my case it would look like something like this:

    https://gf641ea24ecc468-dbmcdeebyface.adb.us-ashburn-1.oraclecloudapps.com/ords/pythondev/cities/?q={"$orderby":{"city":"asc"}}

    Time-out!

    This will screw with your head, so listen up. Quotation marks (e.g. ”
    “) can’t be transmitted over the internet, so they need to be converted to the ASCII character-set.

    Quotations in HTML-5 will look like “%22” in a URL. So if you take that above URL copy it, and then paste it again, it may end up looking like this in your browser’s address bar:

    https://gf641ea24ecc468-dbmcdeebyface.adb.us-ashburn-1.oraclecloudapps.com/ords/pythondev/cities/?q={%22$orderby%22:{%22city%22:%22asc%22}}

    Where you’ll start out with UTF-8 encoding (quotation marks), at some point you’ll see ASCII encoding (which is a combination of the % and hexadecimal encoding).

    I only mention this because I noticed when I took the updated URL and copied it into my VS Code editor it ended up looking like this:

    def city_choices_ords(): 
        import requests
        import json 
        city_list = []
    
        URL = 'https://gf641ea24ecc468-dbmcdeebyface.adb.us-ashburn-1.oraclecloudapps.com/ords/pythondev/cities/?q={%22$orderby%22:{%22city%22:%22asc%22}}'
    
        response = requests.get(URL)
        r = json.loads(response.text)
        for nestedArr in r['items']:
            city_list.append(nestedArr['city'])
        return(city_list) 

    Don’t be alarmed, everything still works. But the query will look like this:

    ?q={%22$orderby%22:{%22city%22:%22asc%22}}

    What is this saying?

    The query above, requests that the cities table be ordered (“orderby”) in ascending (“asc”) order according to the “city” column. You can see this in action if you point your browser to it. It will display like this:

    URI with filtering in query from ORDS REST API enabled table, Chris Hoina, ORDS, Database Tools, Oracle Autonomous Database

    Ordering isn’t the only type of filtering you can do, the combinations are pretty much infinite…

    Levar Burton, in no way endorses ORDS, but if he did anything database or API-related, I like to think that he would, Chris Hoina, Senior Product Manager, Oracle, Database Tools, ORDS

    Take a look.

    Comparing performance

    One last thing…I conducted a very crude A/B performance test, comparing the two code versions.

    In short, there was no appreciable difference. I should remind that I’m working with a very small database object (a table with 2 columns and 18 cities). So if there is a difference one way or another, that difference is negligible.

    I still recorded the test for fun. And you’ll see across the ten requests (five for each version), the elapsed times were essentially the same. I know this isn’t statistically sound, but this was done to satisfy my own curiosity. Our documentation actually states that filtering is advantageous for traversing large objects, so this is probably a terrible example. And as requests scale, I have to image filtering in queries would begin to pull ahead.

    Nonetheless, see the results for yourself:

    The End

    Now you’ve got a couple options for sorting when working with a REST-enabled table (or other object). The nice thing is that setting up these endpoints with ORDS can still be accomplished with simple mouse clicks.

    Also, remember, sorting isn’t the only thing that can be accomplished when filtering in queries. There are TONs of other capabilities, too many to list here. But I would bookmark this page so you can reference later on.

    I don’t want to say you can filter everything, but it sure looks like it. And for a python newbie like myself, this might be a better approach moving forward.

    Funny story, the day I added that “sort()” function in my code was literally the same day (earlier that morning in fact) that I watched a “python tips” video, where sorting was mentioned. It was pure luck.

    Had it not been for that video, I would have been stuck for at least another day trying to figure out how to sort my list.

    Hey, at least now I know I won’t be restricted to what I can filter based on the python functions I do or don’t know. I can’t be the only one….right?

    Follow

    Be sure to follow along for more fun:

  • Working with WTForms and Oracle REST Database Services (ORDS) APIs

    Working with WTForms and Oracle REST Database Services (ORDS) APIs

    Welcome Back

    The title pretty much speaks for itself, and if you’ve been following along, you’d know that 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 biased 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 unnecessary 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 Always Free OCI account, Oracle Cloud Infrastructure, Chris Hoina, Senior Product Manager, Database Tools, ORDS
    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).

    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 bottom line it’s 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 it 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 is needed
    • Lines 31-33: Are 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: These 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 on 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:

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

    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

  • Updating VARCHAR2 fields in Oracle Database Actions

    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.

  • Add Primary and Foreign Keys to your Oracle Autonomous Database

    Add Primary and Foreign Keys to your Oracle Autonomous Database

    I’m not a Database Administrator, but I feel as though I may be slowly evolving into one…

    In a previous post, I left myself the task of correcting the html on a proposed Python/Flask/ORDS/Autonomous Database application. My then next step was to add a search bar (which I did):

    Search bar added to application for python, flask, ords, oracle autonomous database application. Chris Hoina, Oracle Senior Product Manager.
    Search bar added to the html for searching my Autonomous Database.

    Although this was a small step forward it led to some serious questions. While having a search feature like this is good in theory, in practice it is totally unusable.

    The Problem

    If a user wants to search for a specific restaurant, they’d need to know a lot of information beforehand. At a minimum, the name of their target restaurant. But even that isn’t enough. Look at when I query for a single restaurant (randomly chosen – I’m not sponsored by Wendy’s btw):

    SELECT FROM LIKE SQL statement showing a single restaurant name. Chris Hoina, ORDS, Oracle Autonomous Database
    SQL statement and the output (a single restaurant in this case).

    Some of these restaurants come back with additional store numbers (probably a reference to either a corporate- or franchise-owned property). So a search like this simply will not do.

    That’s problem number one. The second problem is that in the original datasets, while HSISID and Permit ID (read about them here) are unique in the Restaurants table. That isn’t the case with the Violations and Inspections tables. There are a TON of entries for each restaurant in these tables, and the HSISID and Permit IDs are used repeatedly for subsequent inspections. Just take a look at this example (they’re all like that though!):

    Viewing the HSISID in my table; unique identifier complications, Chris Hoina, ORDS, Oracle Autonomous Database
    Using the HSISID over and over again make it challenging to gather necessary information across all tables.

    A Closer look

    Above, is the Violations table, but the Inspection table looks similar. From what I observer, each time a violation or inspection is recorded that same HSISID is used (Permit IDs too). But in these two tables an Entry ID is used as the unique identifier. I know the image is greyed out, but if you look closely you can see the “Object ID” in that screenshot.

    But these Object IDs don’t mean anything outside of this table. And it underscores the point that my original approach to searching (i.e search bar) using a single identifier won’t work. Across the three tables these restaurants are uniquely identified in different ways.

    At a minimum, these tables should be linked so that when an HSISID is searched for in the Restaurant table, it gathers all associated data from the Violations and Inspections table too. Meaning, if I search for a specific “Wendy’s” only that target restaurant comes back along with all historical inspection and inspection violation information as well.

    Now that we understand the problem, I’ll attempt a solution. For my next feat, I’ll relate the three tables to one another in a logical way. Continue on dear reader…


    Navigating

    Navigate to your Autonomous Database, then Database Actions. Once there, look under the “Development” section, and choose “SQL”.

    Database Actions Development SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS
    NOTE: I'm sure there are other ways to accomplish this, but this is what worked for me. 

    Once in that SQL Worksheet, look at the the left side of your browser, you’ll see the Navigator tab. Of the two drop-down lists, look at the second one. That is the “Object” selector; for my purposes I made sure that Tables was selected.

    Navigator tab in Oracle Database Actions, Chris Hoina, Senior Product Manager, Database Tools, OCI, Autonomous Database
    Navigator tab in Oracle Database Actions in my Autonomous Database Always Free OCI account.

    I have the three tables; two of which (Inspections and Violations) need Foreign Keys while the third table (Restaurants) will need a Primary Key.

    My three tables in Oracle Database Actions, Chris Hoina, Senior Product Manager, Database Tools, OCI, Autonomous Database
    My three tables in Oracle Database Actions: Inspections, Restaurants, Violations
    TL;DR - The Restaurant table is like my Parent table, whereas the other two tables are like children (or dependents). Establishing these Primary and Foreign keys is a way for me to easily establish interdependence/relation among these three tables. 

    Establishing a Primary Key

    I’ll use the Restaurant table as an example. Here is the step-by-step…

    Editing my table in Oracle Database Actions, Chris Hoina, Senior Product Manager, Database Tools, OCI, Autonomous Database

    Right-click on the table (Restaurants), then select “Edit”.

    Table Properties my table in Oracle Database Actions, Chris Hoina, Senior Product Manager, Database Tools, OCI, Autonomous Database

    If a Primary Key has already been established, then you’ll see a Column (which is actually displayed as a row in this Table Properties table) that already has a check in the “PK” column.

    If there isn’t one, continue by clicking the Primary Key option (see the left-most red arrow).

    Primary Keys section in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

    Once in the Primary key option, you’ll see the following boxes with checks:

    • Enabled
    • Initially Immediate,
    • Validate
    Choosing HSISID as the Primary Key in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

    I’m using the HSISID (found in the “Available Columns” list) as my Primary Key. Once I select that, the “Add Selected Columns” arrow will illuminate (from white to gray). Click it once to move your selection over to the right in the “Selected Columns” list.

    Once your column has been moved, you can click “Apply”.

    Output from establishing the Primary Key in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

    You’ll see this “Output” screen appear. Here you can see that my table was successfully altered. If there are errors, it’ll let you know, and there will be A LOT of red (trust me, I saw tons of red prior to writing this post).

    The DDL from a table in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

    Bonus points, you can always find the Data Definition Language (DDL) in the DDL option (read up on this and the other types of SQL statements here).

    As I see it, it looks like everything since the inception of this table has been recorded. I would imagine you could take this and build some sort of automation/orchestration script with it (along with the DDL from the other tables too).

    Sample Code

    Table as a Primary Key

    CREATE TABLE ADMIN.RESTAURANTS 
        ( 
         OBJECTID           NUMBER , 
         HSISID             NUMBER , 
         NAME               VARCHAR2 (4000) , 
         ADDRESS1           VARCHAR2 (4000) , 
         ADDRESS2           VARCHAR2 (4000) , 
         CITY               VARCHAR2 (4000) , 
         STATE              VARCHAR2 (4000) , 
         POSTALCODE         VARCHAR2 (4000) , 
         PHONENUMBER        VARCHAR2 (4000) , 
         RESTAURANTOPENDATE DATE , 
         FACILITYTYPE       VARCHAR2 (4000) , 
         PERMITID           NUMBER , 
         X                  NUMBER , 
         Y                  NUMBER , 
         GEOCODESTATUS      VARCHAR2 (4000) 
        ) 
        TABLESPACE DATA 
        LOGGING 
    ;
    
    
    CREATE UNIQUE INDEX ADMIN.RESTAURANTS_PK ON ADMIN.RESTAURANTS 
        ( 
         HSISID ASC 
        ) 
        TABLESPACE DATA 
        LOGGING 
    ;
    
    ALTER TABLE ADMIN.RESTAURANTS 
        ADD CONSTRAINT RESTAURANTS_PK PRIMARY KEY ( HSISID ) 
        USING INDEX ADMIN.RESTAURANTS_PK ;

    That is literally all did to set this up. Very straightforward, next I’ll walk through how to set up Foreign keys. Then I’ll show you what it looks like in the Data Modeler so you can visually see the newly-established relationships of the tables.


    Establishing a Foreign Key

    We’re still in the SQL worksheet (found in Database Actions). Both Inspections and Violations tables need a Foreign key; I’ll demonstrate with Inspections.

    Foreign Key in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

    Right-click on the table and select “Edit”, just like I did when establishing a Primary Key. Next, navigate to the “Foreign Keys” option in the Table Properties.

    Adding a Foreign Key in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

    It’ll be empty, but you’ll want to click the
    “+” key (see the arrow). You’ll see “NEW_FK_1” appear under the Foreign Key table. If you click on that you can change the name to whatever your heart desires.

    I then changed the Foreign Key name to something that I could remember (and something that would make sense at a later date); “Inspections_FK”. I then selected “HSISID” as the Local Column.

    And automatically, the schema recognizes the Primary Key I previously established in the Restaurants table. I didn’t do that, that was all done for me automatically! Very cool.

    Output of the Foreign Key name change in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

    After I click “Apply” you’ll see the output from this change. I can then return to the Foreign Keys option and voilà, the Foreign Key name has been changed. I’ll proceed to do the same to my “Violations” table as well.

    Sample Code

    Table as a Foreign Key

    CREATE TABLE ADMIN.INSPECTIONS 
        ( 
         OBJECTID    NUMBER , 
         HSISID      NUMBER , 
         SCORE       NUMBER , 
         DATE_       DATE , 
         DESCRIPTION VARCHAR2 (4000) , 
         TYPE        VARCHAR2 (4000) , 
         INSPECTOR   VARCHAR2 (4000) , 
         PERMITID    NUMBER 
        ) 
        TABLESPACE DATA 
        LOGGING 
    ;
    
    ALTER TABLE ADMIN.INSPECTIONS 
        ADD CONSTRAINT INSPECTIONS_FK FOREIGN KEY 
        ( 
         HSISID
        ) 
        REFERENCES ADMIN.RESTAURANTS ( HSISID ) 
        ON DELETE CASCADE 
        NOT DEFERRABLE 
    ;

    Data Modeler Primer

    This isn’t a “How-to” on the Data Modeler (Documentation here though), but rather just me taking a minute to share how you can visually see the relationships between tables. It definitely helped me better understand why I needed to establish Primary and Foreign Keys.

    Andiamo!

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

    From the Database Actions Launchpad, select “Data Modeler”.

    Navigator Tab in Data Modeler in Database Actions in Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

    Once there, you’ll see two drop-down menus. You’ll see your current Schema (Admin for me, in this case). In the second drop-down menu, you’ll see a list of available “Objects”. I’ll want to make sure that I have “Tables” selected.

    Adding Objects to the Diagram Editor in Data Modeler in Database Actions in Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

    You can drag these over one at a time, or select them all and drag all at once. They’ll appear in the Diagram Editor, with the relationships already included (since I already set up the Primary and Foreign Keys).

    New Diagram of tables in the Data Modeler Diagram Editor in Database Actions in Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

    Expand these diagrams and you’ll reveal additional information. In this image, I’ve expanded the Inspections table to reveal Foreign Key details. I’ll do the same by expanding the bottom of the Violations table to reveal its Foreign Key. That little red arrow signifies that there is additional information hidden.

    Okay, but what is this?

    What we are looking at is a visual representation of the relationships between these tables. Everything is “linked” through the HSISID. My interpretation of this is that everything begins with the Restaurant table, and then the other two tables act as subordinates.

    For a single Restaurant (which has a unique HSISID) there are many entries in both Inspections and Violations that use the HSISID repeatedly. In those cases, that HSISID is no longer unique. It is still important, necessary information.

    And now that everything is linked, I’ll want to create a single query (SQL presumably) that when executed will return all relevant information from a single entry in the Restaurant table with all associated (HSISID) entries from the Inspections and Violations table, nested underneath it.

    Clear as mud? Good. Hope this helps more than it hurts.

    Now what

    Next I’ll need to come up with something (in SQL perhaps) that I believe I’ll use in tandem with ORDS. From there I’ll work on building the html portion of this python + flask application so that the search is more intuitive (to the user) and elegant (for the Autonomous Database). Believe it or not, but it took about a week to wrap my head around all this key business. Needless to say, I’m slightly behind schedule. But expect more updates as I progress.

    Hope you learned something.

    Find me

    HMU on:

  • Project Overview: python – flask – ORDS – Autonomous Database

    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:

    https://twitter.com/chrishoina/status/1499829222315118596

    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:

  • Hello World :)

    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?”

    Mr. Rogers

    Find me on: