I’m not a fan of cURL, so I usually build demos or write blog posts using a GUI like Insomnia or Postman. However, cURL is ubiquitous. When it comes to examples online, or vendor documentation, it’s almost always cURL.
But YOUR solutions aren’t going to be built with cURL. Your application isn’t going to HOST out to the OS to run a cURL command, hopefully.
You might see some automation stuff done via jobs/tasks with cURL.
Python on the other hand feels like ‘magic!’ It’s ridiculously simple and user-friendly. I can pick it up after 6 months and be happy again in a few minutes.
As most of these blogs posts work, I was asked a question.
Can you show how to POST a CSV file to Oracle API that batch loads a table
So let’s do that, first with cURL (on Windows), and then using a simple Python program.
cURL
The HTTP request will be for a REST API. So we’ll need:
- Oracle Database with a simple table
- REST API to batchload said table from CSV, via HTTP POST, provided by ORDS
The nice thing I CAN say about cURL is that if I show demonstrate using a REST API with it, then most any developer should be able to repeat the same outcome using the language or framework of their choice.
Our Oracle Database AUTOREST feature gives you these APIs, for free. And our REST workshop in SQL Developer Web even gives you a swagger tooling like page to test our your endpoints, including a POST!
So a quick detour back to our GUI…
If I look at the POST for /batchload on my table, I can put in some data, and hit the ‘Execute’ button…I’ll paste in 5 rows of CSV with the headers:
And further down the page we can see the results.
Now, how do I go from here to using a file to send the data on the POST?
Your TABLE DDL:
We’re going to create a really simple table –
CREATE TABLE CSV_2M ( COL1 VARCHAR2(20) , COL2 VARCHAR2(20) , COL3 VARCHAR2(20) ) ;
Your DATA
cURL code
Let’s look at the cURL snippet the tool gave us, which assume a Mac or Linux environment:
curl -X 'POST' \
'http://localhost:8080/ords/hr/csv_2m/batchload' \
-H 'accept: application/json' \
-H 'Content-Type: text/csv' \
-d 'COL1,COL2,COL3
"e40a9db6","1b","27531"
"6182c817","73","5355332"
"5ed9e437","d2","4834758"
"d77868a1","64","2129797"
"09bfaa73","89","3294896"'Since I’m on Windows, SPOILER ALERT, that’s not going to work.
Why my obsession with Windows? Because I know that for all of the people I’m talking to, about 90% of you are on Windows.
cURL code, for Windows and the file
We’re NOT doing a FORM file upload POST. We’re sending the CONTENTS of a file, as the BODY of a HTTP POST request, with the Content-Type header set to text/csv.
You can copy/paste this directly to windows CMD prompt –
curl --write-out '%{time_total}' ^
-X POST --data-binary "@2M.csv" ^
-H "Content-Type: text/csv" ^
http://localhost:8080/ords/hr/csv_2m/batchload?batchRows=5000
And the response –
#INFO Number of rows processed: 2,097,148
#INFO Number of rows in error: 0
#INFO Last row processed in final committed batch: 2,097,148
SUCCESS: Processed without errors
'8.320995'It’ll look like this:
Mind the “quoted” text! Windows doesn’t like it when I quote the URIs for my REST requests, but Macs and Linux are cool with it, mostly? It’s annoying.
Also, I’m happy to claim ignorance or ‘doing it wrong’ because I haven’t done my homework. Leave me a comment!
Python
So I’m going to write some code, but maybe you still want to cheat! I know, I’ll just have my GUI (Insomnia) generate the Python code for me!
Setting up the POST, with the FILE/Binary option on the BODY, is super easy:
It won’t generate code to do a file based POST, it puts the data in the, into your code…which for big files, is a no-go.
When you attempt to generate the code, it’ll hang the app, as it’s trying to put 60MB+ of text data into the GUI.
I mean, this isn’t completely unreasonable. It works JUST FINE instead I have 5 lines of CSV in the post body vs having a BINARY file of CSV. It then generates this:
Let’s find out.
Eureka!
It works, so awesome. But again, I’m using a file. I want to send the file, or at least, the contents of the file!
Python code for sending the file contents
import json
import requests
import sys
import logging
from colorama import Fore
from colorama import Style
url = 'http://localhost:8080/ords/hr/csv_2m/batchload?batchRows=5000'
h = {'Content-Type' : 'text/csv'}
with open('c:\\users\\jdsmith\\downloads\\2M.csv','rb') as payload:
headers = {'content-type': 'application/x-www-form-urlencoded'}
r = requests.post(url,data=payload, headers=h)
print(r.text)
print (Fore.GREEN + ' Elapsed:', r.elapsed.total_seconds(), 'seconds')Notes:
- we need to use the OPEN routine on our file, and send the contents of that file as the body of the POST request, using the ‘data’ option on the requests.post
- you need to use \\ in Python for Windows paths
Assuming your file location and REST API are good, it should look like this when you run it –
This technique is demonstrated on StackOverflow, here. If you use this code, please give our SO author a +1, they’ve earned it!
Did you know that 97% of Python code originates rom StackOverflow (SO?) #joke. SO is a great resource for Python troubleshooting and examples.
The wrong way to do it, I tried this first and got hung up
If you instead try to treat the file as a MULTI-FORM file upload with a POST, you’re gonna see something like this. Note I’m not giving you the code so you can’t accidentally waste 20 minutes of your time like I did.
If you do run that, you’ll see something weird like this in the response –
> & python3.10.exe c:/Users/JDSMITH/Downloads/python/autorest-csv-batchload.py
#ERROR Column in header row --1d09edf8bbb634bd13ad03e83baac8eb is not defined for table.
#INFO Number of rows processed: 0
#INFO Number of rows in error: 0
#INFO No rows committed
SEVERE: Load terminatedI’m mostly including this ‘wrong way’ to do it in case someone tries and can’t find the answer but ‘googles the errors message.’
