Click here to Skip to main content
15,437,689 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How can i insert the next SQL statement in my python script. In SQL is it working allready but not in Python.

INSERT INTO fact_sales (cinema_ID, movie_id, booking_id, date_id) 
SELECT cinema_id, movie_id, booking_id, date_id
FROM dim_cinema, dim_movie, dim_booking, dim_date;
SELECT * FROM fact_sales


this is my database:
DROP DATABASE IF EXISTS Bioscoop_MERU;
CREATE SCHEMA IF NOT EXISTS Bioscoop_MERU DEFAULT CHARACTER SET utf8 ;
USE Bioscoop_MERU;

CREATE TABLE Dim_Cinema (
  Cinema_ID INT(6) NOT NULL AUTO_INCREMENT,
  Cinema_Name VARCHAR(50) NOT NULL,
  Cinema_Location VARCHAR(50) NOT NULL,
  PRIMARY KEY (Cinema_ID)
);

CREATE TABLE Dim_Movie (
  Movie_ID INT(6) NOT NULL AUTO_INCREMENT,
  Movie_Distributor VARCHAR(50) NOT NULL,
  Movie_Title VARCHAR(50) NOT NULL,
  PRIMARY KEY (Movie_ID)
);

CREATE TABLE Dim_Booking (
  Booking_ID INT(6) NOT NULL AUTO_INCREMENT,
  Booking_Size INT(4) NOT NULL,
  Booking_Age INT(2) NOT NULL,
  PRIMARY KEY (Booking_ID)
);

CREATE TABLE Dim_Date (
  Date_ID INT(6) NOT NULL AUTO_INCREMENT,
  Date_Purchase DATE NOT NULL,
  Date_Vacation VARCHAR(50) NOT NULL,
  Date_Week INT(2) NOT NULL,
  Date_Month INT(2) NOT NULL,
  PRIMARY KEY (Date_ID)

);

CREATE TABLE fact_sales (
  PK_Sales_ID int NOT NULL AUTO_INCREMENT,
  Cinema_ID int NOT NULL,
  Movie_ID int NOT NULL,
  Booking_ID int NOT NULL,
  Date_ID int NOT NULL,
  PRIMARY KEY (`PK_Sales_ID`),
  KEY Cinema_ID (`Cinema_ID`),
  KEY Movie_ID (`Movie_ID`),
  KEY Booking_ID (`Booking_ID`),
  KEY Date_ID (`Date_ID`),
  CONSTRAINT Cinema_ID_1 FOREIGN KEY (Cinema_ID) REFERENCES dim_cinema (Cinema_ID),
  CONSTRAINT Movie_ID_1 FOREIGN KEY (Movie_ID) REFERENCES dim_movie (Movie_ID),
  CONSTRAINT Date_ID_1 FOREIGN KEY (Date_ID) REFERENCES dim_date (Date_ID),
  CONSTRAINT Booking_ID_1 FOREIGN KEY (Booking_ID) REFERENCES dim_booking (Booking_ID)
);


What I have tried:

Tried to insert it in python. The value cinema_ID value from dim_cinema, needs to insert into cinema_ID in fact_sales.
Posted
Updated 10-Jun-22 2:24am

1 solution

See Python MySQL[^].

Quote:
The value cinema_ID value from dim_cinema, needs to insert into cinema_ID in fact_sales.
It is not clear what that means. But you have already written some SQL statements in Python, as shown in your previous questions.
 
Share this answer
 
Comments
Megan van Bommel 10-Jun-22 8:36am    
Yes but this wasnt working, so can you help me to write a python script that will working or not.
Richard MacCutchan 10-Jun-22 8:47am    
What wasn't working? You need to provide us with full details of your problem, it is impossible for anyone here to guess. Please use the Improve question link above, and add complete details of the problem.
Megan van Bommel 10-Jun-22 8:51am    
I want to insert the SQL statement above in python. But how can i do that. Because i tried the statement in SQL and that works. But i need that statement in python. How can i covert de SQL statement to a python statement?
Richard MacCutchan 10-Jun-22 8:56am    
Exactly the same way you have already done it in your previous questions. I am sorry but I still do not understand what your problem is.
Megan van Bommel 10-Jun-22 9:35am     CRLF
The code is working now in Python, but there were no values loaded. So id is not loading in an another table. This is the code: # Fact_purchase for fact_sales in processed_response: cursor.execute(""" SELECT cinema_id, movie_id, booking_id, date_id FROM dim_cinema, dim_movie, dim_booking, dim_date WHERE cinema_name = '"""+cinema_name+"""' AND cinema_location = '"""+cinema_location+""""' OR Movie_Distributor = '"""+movie_distributor+"""' OR Movie_Title = '"""+movie_title+""""' OR booking_size = '"""+booking_size+"""' OR booking_age = '"""+booking_age+""""' OR date_purchase = '"""+date_purchase+"""' OR date_vacation = '"""+date_vacation+""""' OR date_week = '"""+date_week+""""' OR date_month = '"""+date_month+""""' """) try: cursor.execute(sql) for result in cursor: bioscoop_id = result['bioscoop_id'] cinema_id= dim_cinema['cinema_id'] movie_id= dim_movie['movie_id'] booking_id= dim_booking['booking_id'] movie_id= dim_movie['movie_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_sales (cinema_id, movie_id, booking_id, date_id) VALUES ('"+str(cinema_id)+"', '"+str(movie_id)+"', '"+str(booking_id)+"', '"+str(date_id)+"'") db.commit() except Exception as e: print(e) db.rollback()

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