Click here to Skip to main content
15,506,865 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a problem with the primary and foreign key. I want the primary key from the cinema table "Cinema_ID" into the fact_purchase table as foreign key.

I get an error
Quote:
name 'cinema_id' is not defined


How can i fix this code, so that the foreign key is working.

####
#
# 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()


What I have tried:

I tried this code that i get from school, but it isnt working for me. I don't know what i am doing wrong.

# 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()
Posted
Updated 9-Jun-22 0:33am

1 solution

You have the following code:
Python
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']

But the sql variable still contains (as far as I can make out) an INSERT statement.

So you really need to think much more carefully about the logical steps needed in your application. As it stands I cannot see much structure to your program.

The second issue you have is that you are using string concatenation to build your SQL statements. This is dangerous and leaves your database open to destruction by hackers. Go to MySQL :: MySQL Connector/Python Developer Guide[^] and work through some of the tutorials and examples. You will then see the correct way to add variable values to your MySQL statements.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900