title: Insert foreign keys in mysql with Python
body: <p>I need to get data from an API and insert it into mysql with Python. The request and the insertion is fine and works well but I try to insert foreign keys from db and insert it to tickets_facts table. The only problem is that it inserts 1 for every foreign key in every row. If i look in my db, I notice that there are a lot of other ID's and not just 1. I tried a few times and i tried another code to insert it but that gives me the same output. I work with Pycharm and i tried another editor but i get the same output</p>
  <p>Can anyone help me with the following code:</p>
  <pre><code>import requests
  import json
  import pymysql
  
  # Definieer alle db en api paramenters
  host = &quot;&quot;
  username = &quot;&quot;
  password = &quot;&quot;
  database = &quot;&quot;
  api_url = &quot;https://my.api.mockaroo.com/cinemavisits?key=ed7c6150&quot;
  
  # data ophalen van apiresponse = requests.get(api_url)
  processed_response = response.json()
  
  # Connectie maken met db
  db = pymysql.connect(host=host, user=username, password=password, database=database)
  cursor = db.cursor()
  
  # elke record die we krijgen uit API loopen
  for purchase in processed_response:
  
  # Ticket facts# vergelijk de data in de db met die van API om de foreign key te achterhalen
  original_ticket_price = str(purchase[&quot;original_ticket_price&quot;])
  purchasing_ticket_price = str(purchase[&quot;purchasing_price_tickets&quot;])
  discount_ticket = str(purchase[&quot;discount_on_tickets&quot;])
  purchase_ticket_date = str(purchase[&quot;purchase_date&quot;])
  group_size = str(purchase[&quot;group_size&quot;])
  original_food_price = str(purchase[&quot;original_food_price&quot;])
  purchasing_food_price = str(purchase[&quot;purchasing_price_food&quot;])
  discount_food = str(purchase[&quot;discount_on_food&quot;])
  purchase_food_date = str(purchase[&quot;purchase_date&quot;])
  purchase_food_amount = str(purchase[&quot;group_size&quot;])
  app_version = str(purchase[&quot;app_version&quot;])
  
  movie = 
  cursor.execute(&quot;&quot;&quot;select movieID from movies_dimensionwhere movie_name = '&quot;&quot;&quot;+movie_name+&quot;&quot;&quot;'AND distributor = '&quot;&quot;&quot;+distributor+&quot;&quot;&quot;'&quot;&quot;&quot;)
  
  cinema = 
  cursor.execute(&quot;&quot;&quot;select cinemaID from cinema_dimensionwhere cinema_name = '&quot;&quot;&quot;+cinema_name+&quot;&quot;&quot;'AND cinema_location = '&quot;&quot;&quot;+cinema_location+&quot;&quot;&quot;'&quot;&quot;&quot;)
  
  booker = 
  cursor.execute(&quot;&quot;&quot;select bookerID from customer_dimensionwhere booker_first_name = '&quot;&quot;&quot;+booker_first_name+&quot;&quot;&quot;'AND booker_last_name = '&quot;&quot;&quot;+booker_last_name+&quot;&quot;&quot;'AND booker_age = '&quot;&quot;&quot;+booker_age+&quot;&quot;&quot;'AND booker_email = '&quot;&quot;&quot;+booker_email+&quot;&quot;&quot;'AND booker_gender = '&quot;&quot;&quot;+booker_gender+&quot;&quot;&quot;'AND booker_address = '&quot;&quot;&quot;+booker_address+&quot;&quot;&quot;'AND booker_city = '&quot;&quot;&quot;+booker_city+&quot;&quot;&quot;'&quot;&quot;&quot;)
  
  popcorn = 
  cursor.execute(&quot;&quot;&quot;select popcornID from popcorn_dimensionwhere popcorn_size = '&quot;&quot;&quot;+popcorn_size+&quot;&quot;&quot;'AND popcorn_taste = '&quot;&quot;&quot;+popcorn_taste+&quot;&quot;&quot;'&quot;&quot;&quot;)
  
  # Controlleer of record al in tabel voorkomt, om dubbele data te voorkomen
  cursor.execute(&quot;&quot;&quot;select * from tickets_facts 
  where movieID = '&quot;&quot;&quot;+str(movie)+&quot;&quot;&quot;'
  AND cinemaID = '&quot;&quot;&quot;+str(cinema)+&quot;&quot;&quot;'
  AND bookerID = '&quot;&quot;&quot;+str(booker)+&quot;&quot;&quot;'
  AND original_ticket_price = '&quot;&quot;&quot;+original_ticket_price+&quot;&quot;&quot;'
  AND purchasing_ticket_price = '&quot;&quot;&quot;+purchasing_ticket_price+&quot;&quot;&quot;'
  AND discount_ticket = '&quot;&quot;&quot;+discount_ticket+&quot;&quot;&quot;'
  AND purchase_ticket_date = '&quot;&quot;&quot;+purchase_ticket_date+&quot;&quot;&quot;'
  AND group_size = '&quot;&quot;&quot;+group_size+&quot;&quot;&quot;'
  AND popcornID = '&quot;&quot;&quot;+str(popcorn)+&quot;&quot;&quot;'
  AND original_food_price = '&quot;&quot;&quot;+original_food_price+&quot;&quot;&quot;'
  AND purchasing_food_price = '&quot;&quot;&quot;+purchasing_food_price+&quot;&quot;&quot;'
  AND discount_food = '&quot;&quot;&quot;+discount_food+&quot;&quot;&quot;'
  AND purchase_food_date = '&quot;&quot;&quot;+purchase_food_date+&quot;&quot;&quot;'
  AND purchase_food_amount = '&quot;&quot;&quot;+purchase_food_amount+&quot;&quot;&quot;'
  AND app_version = '&quot;&quot;&quot;+app_version+&quot;&quot;&quot;'&quot;&quot;&quot;)
  
  # Aantal rijen tellen die worden opgehaald uit db
  resultcount = cursor.rowcount
  
  # Zodra de record niet bestaat wordt die ingevoerd in dbif resultcount == 0:sql = &quot;&quot;&quot;Insert into tickets_facts
  (movieID, cinemaID, bookerID, original_ticket_price, purchasing_ticket_price, discount_ticket, purchase_ticket_date, group_size,popcornID, original_food_price, purchasing_food_price, discount_food, purchase_food_date, purchase_food_amount, app_version)
  VALUES ('&quot;&quot;&quot;+str(movie)+&quot;&quot;&quot;', '&quot;&quot;&quot;+str(cinema)+&quot;&quot;&quot;', '&quot;&quot;&quot;+str(booker)+&quot;&quot;&quot;','&quot;&quot;&quot;+original_ticket_price+&quot;&quot;&quot;', '&quot;&quot;&quot;+purchasing_ticket_price+&quot;&quot;&quot;', '&quot;&quot;&quot;+discount_ticket+&quot;&quot;&quot;','&quot;&quot;&quot;+purchase_ticket_date+&quot;&quot;&quot;', '&quot;&quot;&quot;+group_size+&quot;&quot;&quot;', '&quot;&quot;&quot;+str(popcorn)+&quot;&quot;&quot;', '&quot;&quot;&quot;+original_food_price+&quot;&quot;&quot;', '&quot;&quot;&quot;+purchasing_food_price+&quot;&quot;&quot;','&quot;&quot;&quot;+discount_food+&quot;&quot;&quot;', '&quot;&quot;&quot;+purchase_food_date+&quot;&quot;&quot;', '&quot;&quot;&quot;+purchase_food_amount+&quot;&quot;&quot;', '&quot;&quot;&quot;+app_version+&quot;&quot;&quot;')&quot;&quot;&quot;
  
  try:
  # Voer sql command uit en commit in db
  cursor.execute(sql)
  db.commit()
  
  except Exception as e:
  # Als iets fout gaat, niets veranderen
  print(e)
  db.rollback()
  
  db.close()
  </code></pre>