is is possible to run two sql inserts back to back in a loop?

Question

Say you have a loop of data that is similar to

for tree in trees:
    tree_type = [t_type]
    tree_owner = [t_owner]

So one result of the loop would look like

magnolia
Bill

Your two tables look like the following and the _id columns are auto increment PKs

tree_header

tree_header_id	tree_type
25	spruce

tree_detail

tree_detail_id	tree_id	tree_owner
2	25	Susan
3	25	Roger

Is it possible to use the loop to run two inserts in tandem to insert each result into both tables kind of like

Insert into tree_header(tree_type) values(tree_type)

last_id = last id inserted into header

Insert into tree_detail(tree_id, tree_owner) values(last_id, tree_owner)

Does this answer your question? [PostgreSQL function for last inserted ID](https://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id) — Klaus D., Jun 30 '21 at 06:41

uncrayon · Accepted Answer · 2021-07-02T10:24:05.430

This is the answer. This allows you to create a loop, set variables, and pass those in for values. So if you have a data set returned from wherever - just through this into a loop and assign your data values to variables and pass them into the inserts. This also separates the values out and makes it possible to have multiple rows go in under either of the inserts.

import psycopg2

class MyData():
    def __init__(self, host="10.0.80.85", db="hhh", user="postgres", password="5bebfakee11"):
        self.conn = psycopg2.connect(host=host, database=db, user=user, password=password)
        self.cur = self.conn.cursor() 
            
    def close(self):
        self.cur.close()
        self.conn.close()


db = MyData()
insert = "INSERT INTO hhh.header (col1, col2) VALUES (%s, %s) RETURNING header_id;"
db.cur.execute(insert, ('variant1', 'true' ,))

last_header_id = db.cur.fetchone()[0]

insert = "INSERT INTO hhh.detail (header_id) VALUES (%s)"
db.cur.execute(insert, (last_header_id,))

db.conn.commit()
db.close()

score 0 · Answer 2 · answered Jul 01 '21 at 19:02

You can actually do this is a single statement. The technique is create a CTE to receives the parameters. In this case tree type and an array of owners. Then a second CTE to insert the tree_header and return the generated id. Finally, join the parameters with the with the resulting header. See Demo.

with parms (tree, owners) as
     ( values ('<tree type>', array ['<Owner_Array>']) )
   , make_tree as
     ( insert into tree_header(tree_type)
         select tree from parms
         returning tree_id, tree_type
     )
insert into  tree_detail(owner_name, tree_id)
   select unnest (p.Owner_Array), mt.tree_id
     from parms p 
     join make_tree mt on (mt.tree_type = p.tree);

Now since I do not write your obsurfication language (Python?) I am not exactly how it translates. But I guess something like:

import psycopg2

class MyData():
    def __init__(self, host="10.0.80.85", db="hhh", user="postgres", password="5bebfakee11"):
        self.conn = psycopg2.connect(host=host, database=db, user=user, password=password)
        self.cur = self.conn.cursor() 
            
    def close(self):
        self.cur.close()
        self.conn.close()


 db = MyData()
 statement = "with parms (tree, owners) as                   \
                  ( values ('%',  array [%]) )               \
                , make_tree as                               \
                  ( insert into tree_header(tree_type)       \
                      select tree from parms                 \ 
                      returning tree_id, tree_type           \ 
                  )                                          \
             insert into  tree_detail(owner_name, tree_id)   \
                select unnest (p.owners), mt.tree_id         \
                  from parms p                               \ 
                  join make_tree mt on (mt.tree_type = p.tree);"

 db.cur.execute(statement, (last_header_id,list_owner_array))
 
 db.conn.commit()
 db.close()

Note: There is nothing returned from the statement, it either works of Postgres throws and exception.

My code is Python, yes. A CTE could work in some cases, but I am not entirely sure if it could in mine. I am doing this - 1. Pull data from API via streaming and putting it into a list() 2. Looping through the returned data and performing two inserts for every object. So basically, I have a loop that loops through every object returned from the api and in that loop I am inserting some values into header, then other values into detail. Detail ties back to header with a FK. Creating a one to many relationship between the tables. Make sense? I really appreciate your write up! Tons of good info! — uncrayon, Jul 01 '21 at 20:28
You would look through what is being pulled from the API, but only need 1 statement to accomplish bots inserts. That means 1 call to the server for each object instead of 2 (or 3 - not sure about *last_header_id = db.cur.fetchone()[0]*). Also, as a suggestion, just use 1 commit after all objects written not a commit after each obejct — Belayer, Jul 01 '21 at 20:36
I'll test it and let you know. If it works I'll have some questions on breaking it down. I don't know what you mean about a single commit? I am just doing a single commit already. — uncrayon, Jul 01 '21 at 21:19
I've e read you answer, and studied over it. Your solution would work if this were an SP. But when it comes to using it in Python, it makes it harder to have multiple rows being inserted into one of my two tables. Meaning, the detail table will have multiple rows tied to a single row of the header. So one insert into the detail will insert multiple rows at a time. You method also introduces SQL injection because you have the values directly in the query. Where as my solution breaks the values out. Your is a great solution for something like an SP though. — uncrayon, Jul 02 '21 at 10:23

is is possible to run two sql inserts back to back in a loop?

2 Answers2