-
Notifications
You must be signed in to change notification settings - Fork 2
/
db.py
executable file
·148 lines (126 loc) · 4.56 KB
/
db.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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
import sys
import os
import sqlite3
from contextlib import closing
from objects import Category
from objects import Movie
conn = None
def connect():
global conn
if not conn:
DB_FILE = "db/movies.sqlite"
conn = sqlite3.connect(DB_FILE)
conn.row_factory = sqlite3.Row
def close():
if conn:
conn.close()
def make_category(row):
return Category(row["categoryID"], row["categoryName"])
def make_movie(row):
return Movie(row["movieID"], row["name"], row["year"], row["minutes"],
make_category(row))
def get_categories():
query = '''SELECT categoryID, name as categoryName
FROM Category'''
with closing(conn.cursor()) as c:
c.execute(query)
results = c.fetchall()
categories = []
for row in results:
categories.append(make_category(row))
return categories
def get_category(category_id):
query = '''SELECT categoryID, name AS categoryName
FROM Category WHERE categoryID = ?'''
with closing(conn.cursor()) as c:
c.execute(query, (category_id,))
row = c.fetchone()
if row:
return make_category(row)
else:
return None
def get_movies_by_category(category_id):
query = '''SELECT movieID, Movie.name, year, minutes,
Movie.categoryID as categoryID,
Category.name as categoryName
FROM Movie JOIN Category
ON Movie.categoryID = Category.categoryID
WHERE Movie.categoryID = ?'''
with closing(conn.cursor()) as c:
c.execute(query, (category_id,))
results = c.fetchall()
movies = []
for row in results:
movies.append(make_movie(row))
return movies
def get_movies_by_all_categories():
query = '''SELECT movieID, Movie.name, year, minutes,
Movie.categoryID as categoryID,
Category.name as categoryName
FROM Movie JOIN Category
ON Movie.categoryID = Category.categoryID'''
with closing(conn.cursor()) as c:
c.execute(query)
results = c.fetchall()
movies = []
for row in results:
movies.append(make_movie(row))
return movies
def get_movie_by_id(movieId):
query = '''SELECT movieID, Movie.name, year, minutes,
Movie.categoryID as categoryID,
Category.name as categoryName
FROM Movie JOIN Category
ON Movie.categoryID = Category.categoryID
WHERE movieID = ?'''
with closing(conn.cursor()) as c:
c.execute(query, (movieId,))
results = c.fetchall()
return make_movie(results[0])
def get_movies_by_year(year):
query = '''SELECT movieID, Movie.name, year, minutes,
Movie.categoryID as categoryID,
Category.name as categoryName
FROM Movie JOIN Category
ON Movie.categoryID = Category.categoryID
WHERE year = ?'''
with closing(conn.cursor()) as c:
c.execute(query, (year,))
results = c.fetchall()
movies = []
for row in results:
movies.append(make_movie(row))
return movies
def get_movies_by_all_year():
query = '''SELECT movieID, Movie.name, year, minutes,
Movie.categoryID as categoryID,
Category.name as categoryName
FROM Movie JOIN Category
ON Movie.categoryID = Category.categoryID'''
with closing(conn.cursor()) as c:
c.execute(query)
results = c.fetchall()
movies = []
for row in results:
movies.append(make_movie(row))
return movies
def add_movie(movie):
sql = '''INSERT INTO Movie (categoryID, name, year, minutes)
VALUES (?, ?, ?, ?)'''
with closing(conn.cursor()) as c:
c.execute(sql, (movie.category.id, movie.name, movie.year,
movie.minutes))
conn.commit()
def edit_movie(movie_id, name, year, minutes, category_id):
sql = '''UPDATE Movie SET name = ?, year = ?, minutes = ?, categoryID = ? WHERE movieID = ?'''
print (sql, movie_id, name, year, minutes, category_id)
with closing(conn.cursor()) as c:
c.execute(sql, (name, year, minutes, category_id, movie_id))
test = conn.commit()
print("Test", test)
def delete_movie(movie_id):
sql = '''DELETE FROM Movie WHERE movieID = ?'''
with closing(conn.cursor()) as c:
c.execute(sql, (movie_id,))
test = conn.commit()
print("Test", test)