title: Python script inserting into MySQL Table
body: <p>I've been working on a python script that pulls and organizes data from a website, and then attempts to reproduce that data in a MySQL table.  For the purposes of this question, this table's name is "capacitors", but I intend to eventually have more tables for more categories.  </p>
  
  <p>The one thing I can't seem to get around is inserting the data into the MySQL table.  My script runs properly (at least I think so) until the sql execute statement in which it only adds one row to the table.  It should add a total of 5 rows according to the for loop at the top of the script.  <strong>EDIT:</strong> <strong>What I'm really looking for here is to be able to update existing rows with new information from the website, as well as add entirely new rows.</strong></p>
  
  <p>Can I have some advice as to what I'm missing here?  Let me know if you need anything other than the script, which is below.  Thanks!!</p>
  
  <pre><code>import stock_checker, json, urllib.request, pprint, os, csv, re, time
  import mysql.connector as my
  
  # Create list of parts that will be looked up in Octopart 
  parts=[]
  for k in range(len(stock_checker.d)):
      parts.append((stock_checker.d[k]["product_id"]))
  
  # THIS NEXT PART NEEDS TO HAPPEN FOR EACH PIECE
  for i in range(5):
      print ("Generating detailed part information on: %s" % parts[i])
  
      url = 'http://octopart.com/api/v3/parts/match?'
      url += '&amp;queries=[{"mpn":"' + parts[i] + '"}]'
      url += '&amp;apikey=1b0d5753'
      url += '&amp;include[]=specs'
  
      data = urllib.request.urlopen(url).read()
      response = json.loads(data)
  
      #print request time (in milliseconds)
      # print response['msec']
  
      debug=False
      if debug:
          pprint.pprint(response)
  
      # print mpn's
      mylisth= ['part_number'] #list of headers
      mylistv= [parts[0]] #list of values
      mydict = {} #dictionary of pairs
      headers = []
      for result in response['results']:
          for item in result['items']:
              for key,value in item['specs'].items():
                  try:
                      mydict.update({key:value['display_value']})
                      mylisth.append(key)
                      mylistv.append(value['display_value'])
  
                      print ("%s: %s" % (key, value['display_value']))
                      #print("%s" % (value['display_value']))
  
                  except KeyError as e:
                      print ("WARNING: Could not find Key for %s in key \"%s\". Value is %s" % (e, key, value))
  
      print ("")
  
      #vector of what all the headers should look like
      try:
          if mylisth[1] == 'capacitance':
              cap_headers = ['part_number','capacitance','capacitance_tolerance','case_package','case_package_si','dielectric_characteristic',
                          'dielectric_material', 'lead_free_status','lifecycle_status','mounting_style','operating_temperature','packaging','pin_count',
                         'reach_svhc_compliance','rohs_status','size_height','size_length','size_thickness','size_width','voltage_rating_dc']
  
              missing = list(set(cap_headers)-set(mylisth))
  
              for i in range(len(missing)):   
                  mylisth.insert(cap_headers.index(missing[i]), missing[i])
                  mylistv.insert(cap_headers.index(missing[i]), None)
          if mylisth[2] == 'inductance':
              ind_headers = ['part_number','case_package','inductance','lead_free_status','mounting_style','packaging',
                             'pin_count','rohs_status']
      except:
          print("ERROR: BYE! HAVE A GOOD TIME! ;)")
  
      #Remove Units
      #if mylisth[1]=='capacitance':
          #mylistv[2]=re.sub("[^0-9]","",mylistv[2])
  
      time.sleep(1)
  
      #######################################################################################################
  
      #connect to database
      cnx = my.connect(host='localhost',user='root',password='',database='mydb')
      cursor = cnx.cursor()
  
      #create string input of mylisth
      heads = ','.join(str(i) for i in mylisth)
  
      #create string input of %s corresponding to number of entries in mylisth
      placelst = ['%s' for i in mylisth]
      placestr = ','.join(str(i) for i in placelst)
  
      #add_capacitor -&gt; SQL query line // data_capacitor -&gt; values to be inserted using query line
      add_capacitor = ("INSERT IGNORE INTO capacitors ("+heads+") VALUES ("+placestr+")")
      data_capacitor = mylistv
  
      cursor.execute(add_capacitor, data_capacitor)
      emp_no = cursor.lastrowid
  
      cnx.commit()
  
      cursor.close()
      cnx.close()
  </code></pre>