jbKJci31

1import time
2import csv
3import pyodbc
4
5SERVER = 'nmct-arne.database.windows.net'
6DATABASE = 'nmct-cloud'
7USERNAME = 'arned'
8PASSWORD = '-Azerty2016'
9DRIVER = '{ODBC Driver 13 for SQL Server}'
10
11# movieId uniqueidentifier PRIMARY KEY: Experimentale code voor maken van unieke ID's in Python zonder int IDENTITY(1,1)
12
13# --------------------
14# Movies tabel SQL
15# --------------------
16def createTableMovies():
17    createTable = """
18    DROP TABLE IF EXISTS Movies;
19    CREATE TABLE Movies(
20    movieId int IDENTITY(1,1) PRIMARY KEY,
21    title nvarchar(150) NOT NULL,
22    IMDBLink nvarchar(150) NOT NULL,
23    IMDBScore float NOT NULL
24    );"""
25    connectionString = 'DRIVER=' + DRIVER + ';PORT=1433;SERVER=' + SERVER + ';PORT=1443;DATABASE=' + DATABASE + ';UID=' + USERNAME + ';PWD=' + PASSWORD
26    connection = pyodbc.connect(connectionString)
27    cursor = connection.cursor()
28    result = cursor.execute(createTable)
29    cursor.commit()
30
31# --------------------
32# Actors tabel SQL
33# --------------------
34def createTableActors():
35    createTable = """
36    DROP TABLE IF EXISTS Actors;
37    CREATE TABLE Actors(
38    actorId int IDENTITY(1,1) PRIMARY KEY,
39    actor_name nvarchar(150) NOT NULL
40    );"""
41    connectionString = 'DRIVER=' + DRIVER + ';PORT=1433;SERVER=' + SERVER + ';PORT=1443;DATABASE=' + DATABASE + ';UID=' + USERNAME + ';PWD=' + PASSWORD
42    connection = pyodbc.connect(connectionString)
43    cursor = connection.cursor()
44    result = cursor.execute(createTable)
45    cursor.commit()
46
47# --------------------
48# Directors tabel SQL
49# --------------------
50def createTableDirectors():
51    createTable = """
52    DROP TABLE IF EXISTS Directors;
53    CREATE TABLE Directors(
54    directorId int IDENTITY(1,1) PRIMARY KEY,
55    director_name nvarchar(150) NOT NULL
56    );"""
57    connectionString = 'DRIVER=' + DRIVER + ';PORT=1433;SERVER=' + SERVER + ';PORT=1443;DATABASE=' + DATABASE + ';UID=' + USERNAME + ';PWD=' + PASSWORD
58    connection = pyodbc.connect(connectionString)
59    cursor = connection.cursor()
60    result = cursor.execute(createTable)
61    cursor.commit()
62
63# --------------------
64# Genres tabel SQL
65# --------------------
66def createTableGenres():
67    createTable = """
68    DROP TABLE IF EXISTS Genres;
69    CREATE TABLE Genres(
70    genreId int IDENTITY(1,1) PRIMARY KEY,
71    genre_name nvarchar(150) NOT NULL
72    );"""
73    connectionString = 'DRIVER=' + DRIVER + ';PORT=1433;SERVER=' + SERVER + ';PORT=1443;DATABASE=' + DATABASE + ';UID=' + USERNAME + ';PWD=' + PASSWORD
74    connection = pyodbc.connect(connectionString)
75    cursor = connection.cursor()
76    result = cursor.execute(createTable)
77    cursor.commit()
78
79# --------------------
80# MoviesActors tabel SQL
81# --------------------
82def createTableMoviesActors():
83    createTable = """
84    DROP TABLE IF EXISTS MoviesActors;
85    CREATE TABLE MoviesActors(
86    movieactorId int IDENTITY(1,1) PRIMARY KEY,
87    movieId int NOT NULL,
88    actorId int NOT NULL
89    );"""
90    connectionString = 'DRIVER=' + DRIVER + ';PORT=1433;SERVER=' + SERVER + ';PORT=1443;DATABASE=' + DATABASE + ';UID=' + USERNAME + ';PWD=' + PASSWORD
91    connection = pyodbc.connect(connectionString)
92    cursor = connection.cursor()
93    result = cursor.execute(createTable)
94    cursor.commit()
95
96# --------------------
97# MoviesGenres tabel SQL
98# --------------------
99def createTableMoviesGenres():
100    createTable = """
101    DROP TABLE IF EXISTS MoviesGenres;
102    CREATE TABLE MoviesGenres(
103    moviegenreId int IDENTITY(1,1) PRIMARY KEY,
104    movieId int NOT NULL,
105    genreId int NOT NULL
106    );"""
107    connectionString = 'DRIVER=' + DRIVER + ';PORT=1433;SERVER=' + SERVER + ';PORT=1443;DATABASE=' + DATABASE + ';UID=' + USERNAME + ';PWD=' + PASSWORD
108    connection = pyodbc.connect(connectionString)
109    cursor = connection.cursor()
110    result = cursor.execute(createTable)
111    cursor.commit()
112
113# =====================
114# Maken van tabellen
115# =====================
116createTableMovies()
117createTableActors()
118createTableDirectors()
119createTableGenres()
120createTableMoviesActors()
121createTableMoviesGenres()
122
123def readActors(fileName):
124    reader = csv.DictReader(open(fileName, encoding="utf8"), delimiter=';')
125
126    counter = 1
127
128    connection = pyodbc.connect('DRIVER=' + DRIVER + ';PORT=1433;SERVER=' + SERVER + ';PORT=1443;DATABASE=' + DATABASE + ';UID=' + USERNAME + ';PWD=' + PASSWORD)
129    cursor = connection.cursor()
130
131    for line in reader:
132        insertSql = "INSERT INTO Actors VALUES (?)"
133        result = cursor.execute(insertSql,
134                                line['actor_name'])
135        cursor.commit()
136        print(counter)
137        counter += 1
138    cursor.close()
139    print("CSV data successfully imported in table Actors")
140
141def readDirectors(fileName):
142    reader = csv.DictReader(open(fileName, encoding="utf8"), delimiter=';')
143    counter = 1
144
145    connection = pyodbc.connect('DRIVER=' + DRIVER + ';PORT=1433;SERVER=' + SERVER + ';PORT=1443;DATABASE=' + DATABASE + ';UID=' + USERNAME + ';PWD=' + PASSWORD)
146    cursor = connection.cursor()
147
148    for line in reader:
149        insertSql = "INSERT INTO Directors VALUES (?)"
150        result = cursor.execute(insertSql,
151                                line['director_name'])
152        cursor.commit()
153        print(counter)
154        counter += 1
155    cursor.close()
156    print("CSV data successfully imported in table Directors")
157
158# ------------------
159# Inlezen csv
160# ------------------
161
162readDirectors("movie_metadata_directors.csv")
163time.sleep(1)
164readActors("movie_metadata_actors.csv")