def reviews(book_id):
"""Reviews route with AJAX"""
# initialize reviews variable
reviews = None
# user Post review to server
if request.method == "POST":
# check if user has already posted a review
double_post_query = "SELECT * FROM reviews WHERE user_id = :user_id AND book_id = :book_id"
double_post_data = {'user_id': session['user_id'], 'book_id': book_id}
result = db.execute(double_post_query, double_post_data).first()
print(result)
if result is not None:
flash("You've already reviewed this book.")
return redirect("/books/{}".format(book_id))
# create review and into database
query = "INSERT INTO reviews (rating, review, user_id, book_id) VALUES (:rating, :review, :user_id, :book_id)"
data = {
'rating': request.form.get("rating"),
'review': request.form.get("review"),
'user_id': session['user_id'],
'book_id': book_id
}
# prevent errors during async connection
try:
db.execute(query, data)
db.commit()
flash("Review Submitted")
except Exception as e:
flash("Review Submission Failed:")
finally:
db.close()
return redirect("/books/{}".format(book_id))
# update reviews on page
if request.method == "GET":
# retrieve reviews if any from database
if book_id is not None and book_id != "undefined":
query = "SELECT * FROM reviews WHERE book_id = :book_id ORDER BY updated_at DESC"
data = {'book_id': book_id}
# prevent errors during async connection
try:
reviews = db.execute(query, data).fetchall()
finally:
db.close()
return render_template("partials/review.html", reviews=reviews)
def setUp(self):
db.query(self.model_class).delete()
db.commit()
for x in range(5):
p = self.model_class(
username=u'rodrigocesar.savian%s' % x,
facebook_id='100003194166055%s' % x,
name=u'Rodrigo Cesar Savian%s' % x,
gender=u'male')
db.add(p)
db.commit()
self.object_list = db.query(self.model_class).all()
self.object = self.object_list[0]
def setUp(self):
db.query(self.model_class).delete()
db.commit()
self.object_list = []
for x in range(1, 5):
p = self.model_class(
id=x,
username=u"rodrigocesar.savian%s" % x,
facebook_id="100003194166055%s" % x,
name=u"Rodrigo Cesar Savian%s" % x,
gender=u"male",
)
self.object_list.append(p)
self.object = self.object_list[0]
self.serializer_object = self.serializer_class(self.object)
def get_app(self):
# first clear all
db.query(self.model_class).delete()
db.commit()
for x in range(5):
p = self.model_class(
username=u'rodrigocesar.savian%s' % x,
facebook_id='100003194166055%s' % x,
name=u'Rodrigo Cesar Savian%s' % x,
gender=u'male')
db.add(p)
db.commit()
self.object_list = db.query(self.model_class).all()
self.object = self.object_list[0]
return app.make_app_test()
def process_item(self, item, spider):
if item.get('song_name') is None:
# 分页完
raise DropItem('ajax page over.')
singer = db.query(
Singer.pk).filter_by(face=item['singer_face']).first()
if singer is None:
singer = Singer(name=item['singer'], face=item['singer_face'])
db.add(singer)
album_name = item.get('album_name')
if album_name is not None:
cover = item.get('album_cover')
album = db.query(Album.pk).filter_by(cover=cover).first()
if album is None:
album = Album(
name=album_name,
intro=item.get('album_intro'),
rdt=item['release_date'],
cover=cover)
db.add(album)
else:
album = Empty()
db.commit()
lrc = item.get('lrc')
song = db.query(Song).filter_by(
name=item['song_name'], singer=singer.pk).first()
if song is None:
song = Song(
name=item['song_name'],
singer=singer.pk,
album=album.pk,
lrc=lrc)
db.add(song)
db.commit()
elif None not in (lrc, song.lrc):
song.lrc = lrc
tag_objs = []
for tag in item['tags']:
t = db.query(Tag.pk).filter_by(name=tag).first()
if t is None:
t = Tag(name=tag)
db.add(t)
tag_objs.append(t)
db.commit()
for tag in tag_objs:
db.merge(SongTag(sid=song.pk, tid=tag.pk))
db.commit()
return item
def tearDown(self):
db.query(self.model_class).delete()
db.commit()
del self.object
del self.object_list
def sql(sql_talk):
try:
row = cursor.execute(sql_talk)
db.commit()
except:
db.rollback()
def create_cache(col1, col2):
cache = Cache(start_date=col1, end_date=col2)
cache.pages = []
pages = db.query(Collection).filter(Collection.date == col1).all()
parameters = [
'status_code', 'robots_txt', 'redirects', 'b_home_footer',
'description', 'b_footer_search_also', 'h2', 'h3', 'title',
'canonical', 'robots', 'b_descr_blocks_item',
'p_gsarticle_promo_aside', 'b_left', 'headers', 'b_descr_text',
'keywords', 'error', 'h1', 'load_time', 'b_similar', 'size'
]
for page1 in pages:
page2 = db.query(Collection).filter(
Collection.date == col2,
CollectionItem.url == page1.url).one_or_none()
# Main data
if page1 and page2:
for key in page1.__dict__:
if key.startswith('_'):
continue
if getattr(page1, key, None) != getattr(page2, key, None):
if getattr(cache, key, None):
cache.__dict__[key] += 1
else:
setattr(cache, key, 1)
# URLs data
keys = []
for key in parameters:
if page1 and page2:
if getattr(page1, key, None) != getattr(page2, key, None):
keys.append(key)
if page1.status_code == 404:
keys.append('404')
elif page1.robots_txt == 'Disallowed':
keys.append('rb_txt')
elif page1.robots == 'noindex, nofollow':
keys.append('rb_meta')
elif page1.redirects != '301':
keys.append('redirects')
cache_page = CachePage(url=page1.url, status_code=page1.status_code)
for k in keys:
setattr(cache_page, k, True)
cache.pages.append(cache_page)
re_redirects = re.compile(r'^301', re.IGNORECASE)
cache.errors_1 = \
db.query(Collection).filter(Collection.items == col1, CollectionItem.status_code == 404).count()
cache.disallowed_1 = \
db.query(Collection).filter(Collection.items == col1, CollectionItem.robots_txt == 'Disallowed').count()
cache.noindex_1 = \
db.query(Collection).filter(Collection.items == col1, CollectionItem.robots == 'noindex, nofollow').count()
cache.redirects_1 = \
db.query(Collection).filter(Collection.items == col1, CollectionItem.redirects == re_redirects).count()
cache.errors_2 = \
db.query(Collection).filter(Collection.items == col2, CollectionItem.status_code == 404).count()
cache.disallowed_2 = \
db.query(Collection).filter(Collection.items == col2, CollectionItem.robots_txt == 'Disallowed').count()
cache.noindex_2 = \
db.query(Collection).filter(Collection.items == col2, CollectionItem.robots == 'noindex, nofollow').count()
cache.redirects_2 = \
db.query(Collection).filter(Collection.items == col2, CollectionItem.redirects == re_redirects).count()
db.add(cache)
db.commit()
return cache
def create_cache(col1, col2):
cache = Cache(start_date=col1, end_date=col2)
cache.pages = []
pages = db.query(Collection).filter(Collection.date == col1).all()
parameters = ['status_code', 'robots_txt', 'redirects', 'b_home_footer', 'description',
'b_footer_search_also', 'h2', 'h3', 'title', 'canonical', 'robots', 'b_descr_blocks_item',
'p_gsarticle_promo_aside', 'b_left', 'headers', 'b_descr_text', 'keywords', 'error', 'h1',
'load_time', 'b_similar', 'size']
for page1 in pages:
page2 = db.query(Collection).filter(Collection.date == col2, CollectionItem.url == page1.url).one_or_none()
# Main data
if page1 and page2:
for key in page1.__dict__:
if key.startswith('_'):
continue
if getattr(page1, key, None) != getattr(page2, key, None):
if getattr(cache, key, None):
cache.__dict__[key] += 1
else:
setattr(cache, key, 1)
# URLs data
keys = []
for key in parameters:
if page1 and page2:
if getattr(page1, key, None) != getattr(page2, key, None):
keys.append(key)
if page1.status_code == 404:
keys.append('404')
elif page1.robots_txt == 'Disallowed':
keys.append('rb_txt')
elif page1.robots == 'noindex, nofollow':
keys.append('rb_meta')
elif page1.redirects != '301':
keys.append('redirects')
cache_page = CachePage(url=page1.url, status_code=page1.status_code)
for k in keys:
setattr(cache_page, k, True)
cache.pages.append(cache_page)
re_redirects = re.compile(r'^301', re.IGNORECASE)
cache.errors_1 = \
db.query(Collection).filter(Collection.items == col1, CollectionItem.status_code == 404).count()
cache.disallowed_1 = \
db.query(Collection).filter(Collection.items == col1, CollectionItem.robots_txt == 'Disallowed').count()
cache.noindex_1 = \
db.query(Collection).filter(Collection.items == col1, CollectionItem.robots == 'noindex, nofollow').count()
cache.redirects_1 = \
db.query(Collection).filter(Collection.items == col1, CollectionItem.redirects == re_redirects).count()
cache.errors_2 = \
db.query(Collection).filter(Collection.items == col2, CollectionItem.status_code == 404).count()
cache.disallowed_2 = \
db.query(Collection).filter(Collection.items == col2, CollectionItem.robots_txt == 'Disallowed').count()
cache.noindex_2 = \
db.query(Collection).filter(Collection.items == col2, CollectionItem.robots == 'noindex, nofollow').count()
cache.redirects_2 = \
db.query(Collection).filter(Collection.items == col2, CollectionItem.redirects == re_redirects).count()
db.add(cache)
db.commit()
return cache
def register():
"""Registration page"""
# Get register page
if request.method == 'GET':
return render_template("register.html")
# Post to register page
if request.method == 'POST':
# set a boolean for validation
valid = True
# check if username or password was provided
if not request.form.get("username") or not request.form.get(
"password"):
flash("must provide a username or password")
valid = False
return redirect(url_for("register"))
# check if email is valid email
if not EMAIL_REGEX.match(request.form.get("email")):
flash("must provide an email")
valid = False
return redirect(url_for("register"))
# check if user is already registered
email_query = "SELECT * FROM users WHERE email = :email"
data = {'email': request.form.get("email")}
result = db.execute(email_query, data).first()
# if user exists alreay redirect
if result:
flash("user already exists")
valid = False
return redirect(url_for("register"))
# check for dangerous input from user
if re.search(r";|'|-", request.form.get("username")) or re.search(
r";|'|-", request.form.get("password")) or re.search(
r";|'|-", request.form.get("email")):
flash("ILLEGAL!!!")
valid = False
return redirect(url_for("register"))
# final check of validations
if not valid:
return redirect(url_for("register"))
# register user oncee valid
elif valid:
password = generate_password_hash(request.form.get("password"))
query = "INSERT INTO users (username, email, password) VALUES (:username, :email, :password)"
data = {
'username': request.form.get("username"),
'email': request.form.get("email"),
'password': password
}
# Catch and handle errors
try:
db.execute(query, data)
flash("Registration was successful")
except InternalServerError:
return
finally:
db.commit()
return redirect(url_for("login"))
def tearDown(self):
super(type(self), self).tearDown()
db.query(self.model_class).delete()
db.commit()
