title: Build dynamic INSERT query with psycopg2
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>Python 3.7.2</p>
  
  <p>Postgres 12</p>
  
  <p>Windows 10 64 Bit</p>
  
  <p>So essentially what i want, in pseudo code is something like this :</p>
  
  <pre><code>INSERT INTO {tableName} {columnList} VALUES {valueList}
  </code></pre>
  
  <p>After trying multiple variations of what was essentially string manipulation and referring to these resources/SO posts :</p>
  
  <p><a href="http://aklaver.org/wordpress/2018/04/21/building-dynamic-sql-using-psycopg2/" rel="nofollow noreferrer">http://aklaver.org/wordpress/2018/04/21/building-dynamic-sql-using-psycopg2/</a></p>
  
  <p><a href="https://www.psycopg.org/docs/usage.html#query-parameters" rel="nofollow noreferrer">https://www.psycopg.org/docs/usage.html#query-parameters</a></p>
  
  <p><a href="https://www.psycopg.org/docs/sql.html#module-psycopg2.sql" rel="nofollow noreferrer">https://www.psycopg.org/docs/sql.html#module-psycopg2.sql</a></p>
  
  <p><a href="https://stackoverflow.com/questions/13292218/build-sql-dynamic-query-with-psycopg2-python-library-and-using-good-conversion-t">build SQL dynamic query with psycopg2 python library and using good conversion type tools</a></p>
  
  <p>I came up with this code so far:</p>
  
  <pre><code># 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 --&gt; [[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
  </code></pre>
  
  <p>Trying to run this code yields the following error:
  <code>expected string or None as name, got 1</code>
  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.</p>
  
  <p>Thank you for your advice.</p>
  
  <h2>EDIT 1</h2>
  
  <p>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:</p>
  
  <pre><code># 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 --&gt; [[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
  </code></pre>
  
  <p>Now i get this error :</p>
  
  <pre><code>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(')')])
  </code></pre>