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!

Don’t forget to follow

Be sure to share or follow if you aren’t already:

Leave a Comment