def showRest(self):
mysql = connectToMySQL('books')
query = 'SELECT COUNT(*) from books'
count = mysql.query_db(query)
if count[0]['COUNT(*)'] <= 3:
return count
else:
show = count[0]['COUNT(*)'] - 3
mysql = connectToMySQL('books')
query = 'SELECT * from books ORDER BY books.created_at ASC LIMIT %(count)s'
data = {'count': show}
rest = mysql.query_db(query, data)
return rest
def checkBook(self):
mysql = connectToMySQL('books')
query = 'SELECT * FROM books WHERE title = %(title)s;'
data = {'title': request.form['title']}
result = mysql.query_db(query, data)
print(result)
return result
def addauthor2(self):
mysql = connectToMySQL('books')
query = 'INSERT INTO authors (author, created_at, updated_at) VALUES (%(author)s,NOW(),NOW())'
data = {
'author': request.form['new_author'],
}
result = mysql.query_db(query, data)
session['result'] = result
return result
def addauthor1(self):
mysql = connectToMySQL('books')
query = 'SELECT * FROM authors WHERE authors.author=%(author)s'
data = {
'author': request.form['author'],
}
result = mysql.query_db(query, data)
session['result'] = result[0]['id']
return result
def addReview(self):
mysql = connectToMySQL('books')
query = 'INSERT INTO reviews (review, created_at, updated_at,book_id, user_id) VALUES (%(review)s,NOW(),NOW(),%(book_id)s, %(user_id)s)'
data = {
'review': request.form['review'],
'book_id': request.form['book_id'],
'user_id': session['user_id']
}
mysql.query_db(query, data)
return request.form['book_id']
def addbook(self):
mysql = connectToMySQL('books')
query = 'INSERT INTO books (title, rating, created_at, updated_at, author_id, user_id) VALUES (%(title)s,%(rating)s,NOW(),NOW(),%(author_id)s, %(user_id)s)'
data = {
'title': request.form['title'],
'rating': request.form['rating'],
'author_id': session['result'],
'user_id': session['user_id'],
}
book_id = mysql.query_db(query, data)
mysql = connectToMySQL('books')
query = 'INSERT INTO reviews (review, created_at, updated_at,book_id, user_id) VALUES (%(review)s,NOW(),NOW(),%(book_id)s, %(user_id)s)'
data = {
'review': request.form['review'],
'book_id': book_id,
'user_id': session['user_id'],
}
result = mysql.query_db(query, data)
return result
def newUser(self, pw_hash):
mysql = connectToMySQL('books')
query = 'INSERT INTO users (first_name,last_name, email, password, birthday, created_at, updated_at) VALUES (%(first_name)s, %(last_name)s,%(email)s, %(password)s, %(birthday)s, NOW(), NOW())'
data = {
'first_name': request.form['first_name'],
'last_name': request.form['last_name'],
'email': request.form['email'],
'password': pw_hash,
'birthday': request.form['birthday']
}
mysql.query_db(query, data)
return redirect('/registration_success')
def showThree(self):
mysql = connectToMySQL('books')
query = 'SELECT * from books JOIN authors ON books.author_id = authors.id JOIN reviews ON reviews.book_id = books.id JOIN users ON reviews.user_id = users.id ORDER BY books.created_at DESC LIMIT 3'
result = mysql.query_db(query)
return result
def showBooks(self):
mysql = connectToMySQL('books')
query = 'SELECT COUNT(*) from books'
result = mysql.query_db(query)
return result
def checkUser(self):
mysql = connectToMySQL('books')
query = 'SELECT * FROM users WHERE email = %(email)s;'
data = {'email': request.form['email']}
result = mysql.query_db(query, data)
return result
def allReviews(self, id):
mysql = connectToMySQL('books')
query = 'SELECT title FROM users JOIN reviews on reviews.user_id=users.id JOIN books on reviews.book_id=books.id WHERE users.id=%(id)s'
data = {'id': id}
result = mysql.query_db(query, data)
return result
def countReviews(self, id):
mysql = connectToMySQL('books')
query = 'SELECT first_name, count(users.id) FROM users JOIN reviews ON reviews.user_id=users.id JOIN books ON reviews.book_id=books.id WHERE users.id=%(id)s'
data = {'id': id}
result = mysql.query_db(query, data)
return result
def showUser(self, id):
mysql = connectToMySQL('books')
query = 'SELECT * FROM users WHERE users.id=%(id)s'
data = {'id': id}
result = mysql.query_db(query, data)
return result
def authors_list(self):
mysql = connectToMySQL('books')
query = 'SELECT * from authors'
result = mysql.query_db(query)
return result
def showReviews(self, id):
mysql = connectToMySQL('books')
query = 'SELECT * FROM books JOIN reviews ON books.id = reviews.book_id JOIN users ON reviews.user_id=users.id WHERE books.id=%(id)s'
data = {'id': id}
result = mysql.query_db(query, data)
return result
def showOne(self, id):
mysql = connectToMySQL('books')
query = 'SELECT * from books JOIN authors ON books.author_id = authors.id WHERE books.id = %(id)s'
data = {'id': id}
result = mysql.query_db(query, data)
return result
