Python MYSQL primary key insert as foreign key in table

Quote:

name 'cinema_id' is not defined

####
#
# Requesting data from an API and processing this with Python3.
#
####

# Import required packages
import requests
import json
import pymysql
import datetime

# Setting all required parameters
host = "localhost"
username = "root"
password = "root"
database = "bioscoop_meru"
api_url = "https://my.api.mockaroo.com/cinemavisits?key=ed7c6150"

# Retrieve data from API
response = requests.get(api_url)

# Print(json.dumps(response.json() ,  sort_keys=True, indent=4))
processed_response = response.json()

# Establish the connection to the Database
db = pymysql.connect(host='localhost',
                     user='root',
                     password='root',
                     database='bioscoop_meru')

cursor = db.cursor()
# Dim_cinema
for dim_cinema in processed_response:

    if dim_cinema["cinema"] == None:
        continue
    if dim_cinema["cinema_location"] == None:
        continue

    # "Cinema" dimension
    cinema_name = str(dim_cinema["cinema"])
    cinema_location = str(dim_cinema["cinema_location"])
    
    print(cinema_name, cinema_location)

    # Check whether this record already exists in the dimension we created.
    # We do this, because we don't have to insert it twice.
    cursor.execute("""
    SELECT * FROM dim_cinema
    WHERE cinema_name = '"""+cinema_name+"""'
    AND cinema_location = '"""+cinema_location+""""'
    """)
    
    resultcount = cursor.rowcount
    
    if resultcount == 0:
        # We don't have this combination in our dimension. Lets insert it!
        sql = """
        INSERT INTO dim_cinema (cinema_name, cinema_location)
        VALUES ('"""+cinema_name+"""', '"""+cinema_location+"""')
        """
        
      # End of table
    try:
        # Execute the SQL command and commit our changes to the database...
            cursor.execute(sql)
            db.commit()
            
    except:
            # Unless something goes wrong, in which case we do a rollback on our changes.
            db.rollback()

# Fact_purchase
for fact_purchase in processed_response:

    if fact_purchase["original_ticket_price"] == None:
        continue
    if fact_purchase["app_version"] == None:
        continue 

    # "Cinema" dimension
    ticket_price = str(fact_purchase["original_ticket_price"])
    ticket_discount = str(fact_purchase["discount_on_tickets"])

    print(ticket_price, ticket_discount)
        
cursor.execute("""SELECT cinema_id FROM dim_cinema WHERE cinema_name = '"""+cinema_name+"""' AND cinema_location = '"""+cinema_location+"""'""")

try:
    cursor.execute(sql)
    for result in cursor:
        cinema_id = result['cinema_id']

    cursor.execute("INSERT INTO fact_purchase (cinema_id, ticket_price, ticket_discount) VALUES ('"+(cinema_id)+"', '"+(ticket_price)+"', '"+(ticket_discount)+"'")
    db.commit()

except Exception as e:
    print(e)
    db.rollback()


db.close()

# Retrieve foreign key from dimension table(s)
# We first retrieve the foreign key ID from the dim_bioscoop
# We put this foreign key in the variable bioscoop_id
# Do this for all the dimension tables you have, so look up all the foreign keys,
# then insert all retrieved values in the database.
cursor.execute("SELECT cinimea_id FROM dim_cinema WHERE cinema_name = "+cinema_name+"")
try:
    cursor.execute(sql)
    for result in cursor:
        cinema_id= result['cinema_id']
    db.commit()

except Exception as e:
    print(e)
    db.rollback()

# Then, we insert the new record in the fact table, together with the values
# Revenue & Costs come from the API call
# Bioscoop_id was retrieved from the dimension table
try:
    cursor.execute("INSERT INTO fact_purchase(cinema_id, revenue, costs) VALUES ('"+str(cinema_id)+"', '"+str(revenue)+"', '"+str(costs)+"'")
    db.commit()

except Exception as e:
    print(e)
    db.rollback()