def book_api(isbn):
db_session()
book = db_session.execute(
"""
SELECT books.id, books.title, books.author, books.year,
COUNT(reviews.rating) AS total_voters,
AVG(reviews.rating) AS average_rating
FROM books
LEFT JOIN reviews ON books.id=reviews.book_id
WHERE books.isbn=:isbn
GROUP BY books.id
""", {
"isbn": isbn
}).fetchone()
if book is None:
return jsonify({"error": "Invalid book isbn"}), 422
else:
if book.average_rating is not None:
average_rating = round(float(book.average_rating), 2)
else:
average_rating = None
return jsonify({
"isbn": isbn,
"title": book.title,
"author": book.author,
"publication_date": book.year,
"total_voters": book.total_voters,
"average_rating": average_rating
})
def book(isbn):
user_id = session.get("user_id")
if user_id:
db_session()
book = db_session.execute("SELECT * FROM books WHERE isbn=:isbn;", {
"isbn": isbn
}).fetchone()
if book is None:
abort(404)
else:
if request.method == "POST":
db_session.execute(
"""
INSERT INTO reviews (message, rating, user_id, book_id)
VALUES (:message, :rating, :user_id, :book_id);
""", {
"message": request.form.get('message'),
"rating": request.form.get('rating'),
"user_id": user_id,
"book_id": book.id
})
db_session.commit()
return redirect(url_for("main.book", isbn=isbn))
else:
reviews = db_session.execute(
"""
SELECT user_id, message, rating, username FROM reviews
JOIN users ON users.id=reviews.user_id
WHERE book_id=:book_id;
""", {
"book_id": book.id
}).fetchall()
goodreads = {}
if current_app.config.get("GOODREADS_KEY"):
res = requests.get(
"https://www.goodreads.com/book/review_counts.json",
params={
"key": current_app.config.get("GOODREADS_KEY"),
"isbns": isbn
},
timeout=5)
if res.status_code != 200:
raise Exception(
"Request to goodreads was unsuccessful")
goodreads = (res.json())["books"][0]
return render_template("book.html",
book=book,
reviews=reviews,
user_id=int(user_id),
goodreads=goodreads)
else:
abort(403)
def index():
user_id = session.get("user_id")
username = "******"
if user_id:
db_session()
user = db_session.execute("SELECT * FROM users WHERE id=:user_id", {
"user_id": session.get('user_id')
}).first()
username = user.username
return render_template("index.html", username=username)
def search():
key = request.args.get("key")
value = request.args.get("value")
results = []
db_session()
if key in {"isbn", "author", "title"}:
results = db_session.execute(
"SELECT * FROM books WHERE %s LIKE :value" % key, {
"value": "%" + value + "%"
}).fetchall()
return render_template("search.html",
key=key,
value=value,
results=results)
def register():
userEmail = request.json.get("email", None)
userPassword = request.json.get("password", None)
with db_session() as s:
user = s.query(User).filter_by(email=userEmail).first()
if not user:
try:
user = User(email=userEmail, password=userPassword)
# insert the user
s.add(user)
s.commit()
# generate auth token
access_token = create_access_token(identity=userEmail)
# response data
responseObject = {
'status': 'success',
'message': 'Successfully registered.',
'access_token': access_token
}
return make_response(jsonify(responseObject)), 201
except Exception as e:
responseObject = {
'status': 'fail',
'message': 'Some error occurred. Please try again.'
}
return make_response(jsonify(responseObject)), 401
else:
responseObject = {
'status': 'fail',
'message': 'User already exists. Please Log in.',
}
return make_response(jsonify(responseObject)), 202
def login():
if not request.is_json:
return jsonify({"msg": "Missing JSON in request"}), 400
userEmail = request.json.get('email', None)
userPassword = request.json.get('password', None)
if not userEmail:
return jsonify({"msg": "Missing email parameter"}), 400
if not userPassword:
return jsonify({"msg": "Missing password parameter"}), 400
with db_session() as s:
user = s.query(User) \
.filter_by(
email=userEmail
) \
.first()
#password=userPassword
if not user:
return jsonify({"msg": "Failed to login"}), 403
#if userEmail != '*****@*****.**' or userPassword != 'password':
# return jsonify({"msg": "Bad email or password"}), 401
# Identity can be any data that is json serializable
ret = {
'access_token': create_access_token(identity=userEmail),
'refresh_token': create_refresh_token(identity=userEmail)
}
return jsonify(ret), 200
def add_claims_to_access_token(identity):
with db_session() as s:
user = s.query(User).filter_by(email=identity).first()
roles = ['user']
if user.admin:
roles += ['admin']
return {'id': user.id, 'roles': roles}
def login():
error = None
if request.method == 'POST':
if not request.form.get('username'):
error = "You must submit username"
elif not request.form.get('password'):
error = "You must submit password"
else:
db_session()
user = db_session.execute(
"SELECT * FROM users WHERE username=:username", {
"username": request.form.get('username')
}).fetchone()
if user and check_password_hash(user.password_hash,
request.form.get('password')):
session["user_id"] = user.id
return redirect(url_for('main.index'))
else:
error = "Login fail. Wrong username/password."
return render_template('auth/login.html', error=error)
def get_or_create_bank(bank):
session = db_session()
_bank = session.query(Bank).filter_by(name=bank.name, website=bank.website).first()
if not _bank:
session.add(bank)
session.commit()
session.close()
session.expunge_all()
return bank
session.expunge_all()
session.close()
return _bank
def get_or_create_type(data):
session = db_session()
t = session.query(Type).filter_by(name=data).first()
if not t:
new_type = Type(name=data)
session.add(new_type)
session.commit()
session.expunge_all()
session.close()
return new_type
session.expunge_all()
session.close()
return t
def get_or_create_city(city_name, region):
session = db_session()
city = session.query(City).filter_by(name=city_name, region_id=region.id).first()
if not city:
new_city = City(name=city_name, region_id=region.id)
session.add(new_city)
session.commit()
session.expunge_all()
session.close()
return new_city
session.expunge_all()
session.close()
return city
def get_or_create_country(country_name):
session = db_session()
country = session.query(Country).filter_by(name=country_name).first()
if not country:
new_country = Country(name=country_name)
session.add(new_country)
session.commit()
session.expunge_all()
session.close()
return new_country
session.expunge_all()
session.close()
return country
def get_or_create_region(region_name, country):
session = db_session()
session.add(country)
region = session.query(Region).filter_by(name=region_name, country_id=country.id).first()
if not region:
new_region = Region(name=region_name, country_id=country.id)
session.add(new_region)
session.commit()
session.expunge_all()
session.close()
return new_region
session.expunge_all()
session.close()
return region
def register():
error = None
if request.method == 'POST':
if not request.form.get('username'):
error = "You must submit username"
elif not request.form.get('password'):
error = "You must submit password"
elif request.form.get('password') != request.form.get('password2'):
error = "Passwords don't match"
else:
db_session()
user = db_session.execute(
"SELECT * FROM users WHERE username=:username", {
"username": request.form.get('username')
}).first()
if user:
error = "This username already exists. Choose another one."
else:
password_hash = generate_password_hash(
request.form.get('password'))
db_session.execute(
"""
INSERT INTO users (username, password_hash)
VALUES (:username, :password_hash)""", {
"username": request.form.get('username'),
"password_hash": password_hash
})
db_session.commit()
user = db_session.execute(
"SELECT * FROM users WHERE username=:username", {
"username": request.form.get('username')
}).fetchone()
session["user_id"] = user.id
return redirect(url_for('main.index'))
return render_template('auth/register.html', error=error)
def get_or_create_square(square):
session = db_session()
sq = session.query(Square).filter_by(common=square.common, living=square.living,
kitchen=square.kitchen, bathroom=square.bathroom,
toilet=square.toilet, wc=square.wc,
territory=square.territory).first()
if not sq:
session.add(square)
session.commit()
session.expunge_all()
session.close()
return square
session.expunge_all()
session.close()
return sq
def get_or_create_address(country, region, city, street='', other=''):
session = db_session()
session.add(country)
session.add(region)
session.add(city)
address = session.query(Address).filter_by(country_id=country.id, region_id=region.id, city_id=city.id, street=street, other=other).first()
if not address:
new_address = Address(country=country, country_id=country.id,
region=region, region_id=region.id,
city=city, city_id=city.id,
street=street, other=other)
session.add(new_address)
session.commit()
session.expunge_all()
session.close()
return new_address
session.expunge_all()
session.close()
return address
def write_to_db(lot, region, city, bank_obj):
global added
global updated
LOCK.acquire()
session = db_session()
lot_parsed = Lot()
lot_parsed.source_id = lot.get('source_id')
lot_parsed.link = lot.get('link')
lot_parsed.type = get_or_create_type(lot.get('type'))
country = get_or_create_country(lot.get('country'))
if lot.get('region'):
region = get_or_create_region(lot.get('region'), country)
else:
region = get_or_create_region(region, country)
session.add(region)
if lot.get('city'):
city = get_or_create_city(lot.get('city'), region)
else:
city = get_or_create_city(city, region)
session.add(city)
lot_parsed.address = get_or_create_address(country, region, city,
lot.get('street'),
lot.get('other'))
squares = Square()
session.add(squares)
squares.bathroom = lot.get('bathroom')
squares.common = lot.get('common')
squares.kitchen = lot.get('kitchen')
squares.living = lot.get('living')
squares.territory = lot.get('territory')
squares.toilet = lot.get('toilet')
squares.wc = lot.get('wc')
lot_parsed.square = get_or_create_square(squares)
lot_parsed.communications = lot.get('communications')
lot_parsed.rooms = lot.get('rooms')
lot_parsed.level = lot.get('level')
lot_parsed.build_year = lot.get('build_year')
lot_parsed.description = lot.get('description')
lot_parsed.photo = lot.get('photo')
lot_parsed.price = lot.get('price')
lot_parsed.price_credit = lot.get('price_credit')
lot_parsed.price_rent = lot.get('price_rent')
lot_parsed.date = datetime.utcnow()
lot_parsed.source = lot.get('source')
lot_parsed.bank = bank_obj
lot_db = session.query(Lot).filter_by(source_id=lot_parsed.source_id,
source=lot_parsed.source).first()
curr_date = datetime.utcnow()
if lot_db:
# print "lot %d updated" % lot_db.source_id
lot_db.date = curr_date
session.merge(lot_db)
session.commit()
updated += 1
else:
# print "lot %d added to db" % lot.source_id
lot_parsed.date = curr_date
session.add(lot_parsed)
session.commit()
added += 1
session.close()
LOCK.release()
