Build dynamic INSERT query with psycopg2

Question

I am currently coding up a small tool to move data from a SQL Sever DB into a postgres DB. Connection an basic querys all work fine so far.

I find it very difficult to creat queries dynamically. It needs to be dynamic, since there many tables and many columns which need to be moved. Also the order and the names of the tables and the colums are not necessarily matched from the getgo. I have already matched old and new columns with the data, the last thing i need to do is build the insert statements. Doing it columnwise is not an option either, since not null constraints dont allow to only insert one value at a time.

Python 3.7.2

Postgres 12

Windows 10 64 Bit

So essentially what i want, in pseudo code is something like this :

INSERT INTO {tableName} {columnList} VALUES {valueList}

After trying multiple variations of what was essentially string manipulation and referring to these resources/SO posts :

http://aklaver.org/wordpress/2018/04/21/building-dynamic-sql-using-psycopg2/

https://www.psycopg.org/docs/usage.html#query-parameters

https://www.psycopg.org/docs/sql.html#module-psycopg2.sql

build SQL dynamic query with psycopg2 python library and using good conversion type tools

I came up with this code so far:

# wfData -- List of Lists which contain data, of mixed data types(int,str,NULL), every list is a row from #the database
# tableName -- Name of the table i want to insert into, as a string
# columnMap -- List of lists, which contain the matched column names --> [[col1_old,col2_old,..],[col1_new,col2_new,...]]
def addData(self, wfData, tableName, columnMap ):
    cursor = self.cursor
    for row in wfData:
        insert_str = sql.SQL("INSERT INTO {tableName} ({}) VALUES ({})").format(
        sql.SQL(",").join(map(sql.Identifier, columnMap[1])),
        sql.SQL(",").join(map(sql.Placeholder, row)),
        sql.Identifier(tableName)
        )
        try:
            cursor.execute(insert_str)
        except Exception as e:
            print(e)
        return

Trying to run this code yields the following error: expected string or None as name, got 1 Which leaves me lost, because i dont really know if this comes from some type conversion issues, which i suspect, oder if what i am trying to cook up here ist just wrong.

Thank you for your advice.

EDIT 1

I have changed the order of my parameters but no avail. Error is still the same. But i have made an interesting discovery, upong revisiting the sources mentioned above i have managed to get this whole thing to at least procude a SQL string, which does still not work, but progres is proges. Based of the changes i made it seems that my data structure caused the error until now data is organised in lists. I have now changed it into a dictionary named test:

# wfData -- List of Lists which contain data, of mixed data types(int,str,NULL), every list is a row from #the database
# tableName -- Name of the table i want to insert into, as a string
# columnMap -- List of lists, which contain the matched column names --> [[col1_old,col2_old,..],[col1_new,col2_new,...]]
    def addData(self, wfData, tableName, columnMap ):
        cursor = self.cursor
        test = {"id": 1}
        for row in wfData:
            try:
                insert_str = sql.SQL("INSERT INTO {} ({}) VALUES ({})").format(
                sql.Identifier(tableName),
                sql.SQL(",").join(map(sql.Identifier, test)),
                sql.SQL(",").join(map(sql.Placeholder, test))
                )
                cursor.execute(insert_str)

            except Exception as e:
                print(e)
            exit()
        return

Now i get this error :

ERROR:  Syntaxerror at »%«
LINE 1: INSERT INTO "gr_akt_a" ("id") VALUES (%(id)s)
#The printed sql string :
Composed([SQL('INSERT INTO '), Identifier('graves_cur_h'), SQL(' ('), Composed([Identifier('id')]), SQL(') VALUES ('), Composed([Placeholder('id')]), SQL(')')])

Answer 1

I have done it. Problem was my data structure. I was trying to handle everything with lists and mixed up some of list handling with some of the dictionary handling, in short it was a mess, but i have now managed to get things working:

def addData(self, wfData, tableName, columnMap ):
    cursor = self.cursor      
    for row in wfData:
        test = dict(zip(columnMap[1],row)) #this is where the magic starts
        try:
            insert_str = sql.SQL("INSERT INTO {} ({}) VALUES ({})").format(
            sql.Identifier(tableName),
            sql.SQL(",").join(map(sql.Identifier, test)),
            sql.SQL(",").join(map(sql.Placeholder, test))
            )
            print(type(insert_str))
            cursor.execute(insert_str, test)# the 'test' here is crucial i just forgot it
        except Exception as e:
            print(e)
        exit()
    return

Now the insert query gets executed, it still crashes but this is some data related issue. As of now im trying to move a geometry vom sql server to postgis without any processing. Any advice for these sort of things is welcome.