-
Notifications
You must be signed in to change notification settings - Fork 0
/
TableCreator.py
132 lines (105 loc) · 3.73 KB
/
TableCreator.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
# !/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Tue Sep 25 16:50:19 2018
@author: rohith
"""
import time
import sys
from connectorHelper import connectorHelper
class TableCreator():
def create_cursor(conn):
cur = conn.cursor()
print("connection esatblished")
return cur
def colse_cursor(curObj):
curObj.close()
print("Cursor closed")
def create_tables():
cnt =0
print("Creating Tables ")
start_time = time.time()
curObj = create_cursor(conn)
try:
dropQuery = "DROP TABLE IF EXISTS movies"
curObj.execute(dropQuery)
# Movies table creation
sqlQuery = """ CREATE TABLE movies(
movie_id INT PRIMARY KEY,
movie_name text,
runtime INT,
budget INT,
revenue BIGINT,
language text,
popularity NUMERIC(5,2),
releasedate DATE,
votes INT,
voteAvg INT
)"""
curObj.execute(sqlQuery)
# Actor table actor movie relational table
actorQuery = """CREATE TABLE actors(
actorId INT PRIMARY KEY,
actorName text,
gender INT)
"""
curObj.execute(actorQuery)
actorMovieQuery = """CREATE TABLE actor_movie(
actor_id INT REFERENCES actors(actorID),
movie_id INT REFERENCES movies(movie_id),
characterName text )
"""
curObj.execute(actorMovieQuery)
# Genres table created
genresQuery = """
CREATE TABLE genres(
genres_id INT PRIMARY KEY,
genres_name text
)
"""
curObj.execute(genresQuery)
# Genres movie relational table
genresMovieQuery = """CREATE TABLE genres_movie(
genres_id INT REFERENCES genres(genres_id),
movie_id INT REFERENCES movies(movie_id)
)
"""
curObj.execute(genresMovieQuery)
# Crew table and crew movie relational table
crewQuery = """ CREATE TABLE crew(
crewId INT PRIMARY KEY,
crewName text,
gender INT)
"""
curObj.execute(crewQuery)
crewMovieQuery = """CREATE TABLE crew_movie(
crew_id INT REFERENCES crew(crewID),
movie_id INT REFERENCES movies(movie_id),
dept text )
"""
curObj.execute(crewMovieQuery)
# Production details
prodQuery = """ CREATE TABLE production(pId INT PRIMARY KEY,
pName text
)
"""
curObj.execute(prodQuery)
# Production and Movies relational table
prodMovieQuery = """CREATE TABLE prod_movie(
pId INT REFERENCES production(pId),
movie_id INT REFERENCES movies(movie_id)
)
"""
curObj.execute(prodMovieQuery)
except psycopg2.ProgrammingError as err:
print(err)
except:
print("Error in the creating tables")
print("Unexpected error:", sys.exc_info()[0])
curObj.close()
conn.commit()
print("Time taken to execute %s" % (time.time() - start_time))
print("problem with %s", cnt)
if __name__ == "__main__":
tbCreatorIns = TableCreator()
tbCreatorIns.create_tables()