import csv
import sys
import os
import sqlalchemy
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy import create_engine
from flask import Flask, render_template, request, session
from flask_sqlalchemy import SQLAlchemy
from flask_session import Session

engine = create_engine(os.getenv("DATABASE_URL")) # database engine object from SQLAlchemy that manages connections to the database,# DATABASE_URL is an environment variable that indicates where the database lives

def main():
    f = open("movies.csv","r")
    reader = csv.reader(f, delimiter=';')
    for i, row in enumerate(reader): # loop gives each column a name
        if i == 0:
            continue
        title = row[0]
        year = int(row[1])
        runtime = int(row[2])
        imdbID = row[3]
        imdbRating = float(row[4])
        insert_statement = sqlalchemy.text("INSERT INTO movies(title, year, runtime, "imdbID", "imdbRating") VALUES (:title, :year, :runtime, :imdbID, :imdbRating)")
        engine.execute(insert_statement, title=title, year=year, runtime=runtime, imdbID=imdbID, imdbRating=imdbRating)
    engine.commit() # transactions are assumed, so close the transaction finished
if __name__ == "__main__":
    main()