Q:

Other way on Inserting large amount of JSON data to database without using loop

I call an API using request to get a JSON data and store it in one variable like this:
r = requests.post(url=endpoint , data=payload, headers=headers, params=parameter)
all = json.loads(r.text)

then I used loop to insert data row by row like this: 
for row in all:
   sql = "INSERT INTO t VALUES ({},{},{});".format(all['id'],all['num'],all['type'])
   cur.execute(sql)

The real one has more columns not just 3 columns like the example. 
This code work totally fine but my questions here is that, are there any other way to insert JSON data to the table? Because I need to insert like 4-5 thousand rows per 1 request which will take very long time (comparing with copy_expert on CSV file) since it insert row by row. Are there a ways without using loop or anything that might help this insert process to be faster?
I used PosgreSQL database with Python here

A:

This technique should be the fastest since it doesn't involve any client side loops or parsing, but it requires that the keys in your json structure match your column names.
INSERT INTO t 
SELECT * from json_populate_recordset(null::t, your_json);

Here's an example:
create table t (a int, b int, c int);
insert into t 
select * from json_populate_recordset(null::t, 
             '[{"a": 1, "b": 2}, {"c": 3, "a": 1}, {"b": 4, "a": 7, "c": 9}, {"d": 5}]'
);
select * from t;
 a | b | c
---+---+---
 1 | 2 |
 1 |   | 3
 7 | 4 | 9
   |   |
(4 rows)

Note that the last row is all null because it only had "d" in it, which is not a column in t.
In python, this would translate to something like this:
r = requests.post(url=endpoint , data=payload, headers=headers, params=parameter)
all = r.text # No need to parse this, unless you want to check it's valid
cur.execute('insert into t select * from json_populate_recordset(null::t, %s)', [all])

