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.