title: Python: Set param for columns and values pypyodbc - executemany
body: <p>I have this situation where I created a method that will insert rows in database. I provide to that method columns, values and table name.</p>
  
  <blockquote>
    <p>COLUMNS = [['NAME','SURNAME','AGE'],['SURNAME','NAME','AGE']]<br>
    VALUES = [['John','Doe',56],['Doe','John',56]]<br> 
    TABLE = 'people'</p>
  </blockquote>
  
  <p>This is how I would like to pass but it doesn't work:</p>
  
  <pre><code>db = DB_CONN.MSSQL() #method for connecting to MS SQL or ORACLE etc.
  cursor = db.cursor()
  sql = "insert into %s (?) VALUES(?)" % TABLE 
  cursor.executemany([sql,[COLUMNS[0],VALUES[0]],[COLUMNS[1],VALUES[1]]])
  db.commit()
  </code></pre>
  
  <p>This is how it will pass query but problem is that I must have predefined column names and that's not good because what if the other list has different column sort? Than the name will be in surname and surname in name.</p>
  
  <pre><code>db = DB_CONN.MSSQL() #method for connecting to MS SQL or ORACLE etc.
  cursor = db.cursor()
  sql = 'insert into %s (NAME,SURNAME,AGE) VALUES (?,?,?)'
  cursor.executemany(sql,[['John','Doe',56],['Doe','John',56]])
  db.commit()
  </code></pre>
  
  <p>I hope I explained it clearly enough.
  Ps. COLUMNS and VALUES are extracted from json dictionary </p>
  
  <blockquote>
    <p>[{'NAME':'John','SURNAME':'Doe','AGE':56...},{'SURNAME':'Doe','NAME':'John','AGE':77...}]</p>
  </blockquote>
  
  <p>if that helps.</p>
  
  <p><strong>SOLUTION:</strong></p>
  
  <pre><code>class INSERT(object):
  
      def __init__(self):
          self.BASE_COL = ''
  
      def call(self):
          GATHER_DATA =  [{'NAME':'John','SURNAME':'Doe','AGE':56},{'SURNAME':'Doe','NAME':'John','AGE':77}]
          self.BASE_COL = ''
          TABLE = 'person'
  
          #check dictionary keys
          for DATA_EVAL in GATHER_DATA:
  
              if self.BASE_COL == '': self.BASE_COL = DATA_EVAL.keys()
              else:
                  if self.BASE_COL != DATA_EVAL.keys():
                      print ("columns in DATA_EVAL.keys() have different columns")
                      #send mail or insert to log  or remove dict from list
                      exit(403)
  
          #if everything goes well make an insert
          columns = ','.join(self.BASE_COL)
          sql = 'insert into %s (%s) VALUES (?,?,?)' % (TABLE, columns)
          db = DB_CONN.MSSQL()
          cursor = db.cursor()
          cursor.executemany(sql, [DATA_EVAL.values() for DATA_EVAL in GATHER_DATA])
          db.commit()
  
  
  if __name__ == "__main__":
      ins = INSERT()
      ins.call()
  </code></pre>