def api_isbn(isbn):
book = db.execute(
"SELECT title, author, year, isbn FROM books WHERE isbn = :isbn", {
"isbn": isbn
}).fetchone()
# non existent isbn return 404
if book is None:
return jsonify({"Error": "Unknown ISBN number"}), 404
# turn result into dictionary
book = dict(book)
# 2 calculation queries to add to api call
review_count = dict(
db.execute("SELECT COUNT(*) FROM reviews WHERE book_isbn = :isbn", {
"isbn": isbn
}).fetchone())
average_score = dict(
db.execute(
"SELECT to_char(AVG(rating), '9D99') as average_score FROM reviews WHERE book_isbn = :isbn",
{
"isbn": isbn
}).fetchone())
book['average_score'] = average_score["average_score"]
book['review_count'] = review_count["count"]
# return ok
return jsonify(book), 200
def new_product():
if request.method == POST:
if request.form['submit'] == 'create_new_product':
return redirect(url_for("single_product", product_id='new'))
if request.form['submit'] == 'add_existing_product':
product_id = request.form.get("productChoice")
for row in db.execute(
"SELECT s.* FROM stores s WHERE business_id=:id",
id=session["business_id"]):
db.execute(
"INSERT INTO product_store (product_id, store_id, price, stock) VALUES (:product_id, :store_id, 0, 0)",
product_id=product_id,
store_id=row["id"])
return redirect(
url_for("single_product_store", product_id=product_id))
products = []
hasProduct = False
#retrieve all products that business doesn't own (!business_id in product table) AND that are not selled yet (not in product_store)
for row in db.execute(
"SELECT * FROM products WHERE business_id !=:id AND id NOT IN (SELECT product_id FROM product_store WHERE store_id IN (SELECT id FROM stores WHERE business_id=:id))",
id=session["business_id"]):
product = Product(row["id"], False, row["name"], row["description"],
"")
products.append(product)
hasProduct = True
return render_template(NEW_PRODUCT_PAGE,
products=products,
hasProduct=hasProduct)
def product():
products = []
hasproduct = False
for row in db.execute(
"SELECT DISTINCT p.* FROM products p INNER JOIN product_store ps ON (ps.product_id = p.id) INNER JOIN stores s ON (s.id=ps.store_id) WHERE s.business_id=:id",
id=session["business_id"]):
hasproduct = True
id = row["id"]
name = row["name"]
description = row["description"]
if row["business_id"] == session["business_id"]:
isOwner = True
else:
isOwner = False
imgs = db.execute(
"SELECT file FROM imgs i INNER JOIN product_img pi ON (i.id = pi.img_id AND pi.product_id = :id)",
id=id)
if len(imgs) >= 1:
main_img = Picture('', imgs[0]["file"], '')
main_img.name_thumbnail()
else:
main_img = Picture('', IMG_DEFAULT, IMG_DEFAULT)
product = Product(id, isOwner, name, description, main_img.thumbnail)
products.append(product)
return render_template(PRODUCT_PAGE,
products=products,
hasproduct=hasproduct)
def registers():
if request.method == "POST":
name = request.form.get("name")
username = request.form.get("username")
password = request.form.get("password")
confirm = request.form.get("confirm")
# secure_password=sha256_crypt.encrypt(str(password))
user_data = db.execute(
"SELECT username FROM users WHERE username=:username",
{"username": username},
).fetchone()
if user_data is not None:
flash("username already available please use some other", "danger")
else:
if password == confirm:
db.execute(
"INSERT INTO users(name,username,password) VALUES(:name,:username,:password)",
{"name": name, "username": username, "password": password},
)
# db.execute()
db.commit()
flash("you can login now", "success")
return redirect(url_for("login"))
else:
flash("password does not match", "danger")
return render_template("registers.html")
return render_template("registers.html")
def register():
form = RegistrationForm()
if form.validate_on_submit():
user_check = db.execute(
"SELECT * FROM users WHERE username = :username", {
"username": form.username.data
}).fetchone()
# check on duplicate username
if user_check:
flash('User already exists')
return redirect(url_for('register'))
email_check = db.execute("SELECT * FROM users WHERE email = :email", {
"email": form.email.data
}).fetchone()
# check on duplicate email
if email_check:
flash('Email already exists')
return redirect(url_for('register'))
# create user
username = form.username.data
email = form.email.data
password_hash = generate_password_hash(form.password.data)
db.execute(
"INSERT INTO users (username, email, password_hash) VALUES (:username, :email, :password_hash)",
{
"username": username,
"email": email,
"password_hash": password_hash
})
db.commit()
flash('Congratulations, you are now a registered user!')
return redirect(url_for('login'))
return render_template('register.html', title='Register', form=form)
def add_address():
# validate user
form = AddressAccountForm()
if form.validate_on_submit():
db.execute(
f"INSERT INTO addresses (street, number, zip_code, city, region, country) VALUES (:street, :number, :zip_code, :city, :region, :country)",
street=form.street.data,
number=form.number.data,
zip_code=form.zip_code.data,
city=form.city.data,
region=form.region.data,
country=form.country.data)
address_id = db.execute(
f"SELECT id FROM addresses WHERE street = :street AND number = :number AND zip_code = :zip_code AND city = :city AND region = :region AND country = :country",
street=form.street.data,
number=form.number.data,
zip_code=form.zip_code.data,
city=form.city.data,
region=form.region.data,
country=form.country.data)
db.execute(
f"UPDATE customers SET address_id = :address WHERE user_id = :user",
address=address_id[0]['id'],
user=session['user_id'])
return redirect(url_for("customer_account"))
return render_template("address.html", form=form)
def edit_address():
# validate user
if request.method == POST:
form = AddressAccountForm()
if form.validate():
db.execute(
f"UPDATE addresses SET street = :street, number = :number, zip_code = :zip_code, city = :city, region = :region, country = :country WHERE id = {address_id[0]['address_id']}",
street=form.street.data,
number=form.number.data,
zip_code=form.zip_code.data,
city=form.city.data,
region=form.region.data,
country=form.country.data)
return redirect(url_for("customer_account"))
else:
return render_template("address.html", form=form)
query = str(
db.execute(
f"SELECT a.* FROM customers AS c INNER JOIN addresses AS a ON c.address_id = a.id AND c.user_id = :user",
user=session["user_id"]))
data = ast.literal_eval(query[1:len(query) - 1])
return render_template("address.html",
form=AddressAccountForm(formdata=MultiDict(data)))
def scheduledTask():
conn = sql.connect('db.sqlite3')
db = conn.cursor()
db.execute(
'''DELETE FROM balance_info where user_id in (select id from user where plan_cycle in (0,5) or status NOT IN ('ACTIVE', 'Active'))'''
)
conn.commit()
info = db.execute('''SELECT
id,last_paid_date,plan_cycle, rate_plan,start_date,discount_amount
FROM user JOIN balance_info ON user.id = balance_info.user_id
WHERE status = "Active"''').fetchall()
for i in range(len(info)):
last_date = datetime.datetime.strptime(info[i][1],
"%Y-%m-%d %H:%M:%S").date()
if (last_date == datetime.date(1970, 1, 1)):
last_date = datetime.datetime.strptime(info[i][4],
"%Y-%m-%d").date()
periods = calculate_periods(info[i][2], last_date)
amount = info[i][5]
if (info[i][5] is None):
amount = 0
due_amount = periods * amount
due_date = next_due_date(info[i][2], last_date)
if (periods > 24):
due_amount = 0
db.execute(
'''UPDATE balance_info set due_amount = ?,due_date = ? WHERE user_id = ?''',
[due_amount, due_date, info[i][0]])
conn.commit()
def addRequest(data):
if 'authorised' not in session or not session['authorised']:
emit('denyRequest', room=request.sid)
return
# Add skate information to database
id = str(uuid.uuid4())
skateInfo = (id, data['time'], float(data['size']),
(1 if data['age'] == 'youth' else 0),
('figure', 'hockey', 'speed').index(data['type']), 0)
db.execute("INSERT INTO skates VALUES(?, ?, ?, ?, ?, ?)", skateInfo)
conn.commit()
# Add skate information to memory queue
queueItem = {
'time': data['time'],
'size': float(data['size']),
'age': data['age'],
'type': data['type'],
'status': 0
}
queue[id] = queueItem
print('+ {}: Size {}{} {}'.format(id, data['size'],
('Y' if data['age'] == 'youth' else ''),
data['type']))
# Acknowledge addition
emit('addSuccess', id, room=request.sid)
emit('pubRequest', dict({'id': id}, **queueItem), broadcast=True)
def register():
form = RegisterForm()
if form.validate_on_submit():
email = form.email.data
username = email[:email.find("@")]
table = None
if request.form.get("type_options") == BUSINESS:
table = USER_BUSINESS_TABLE
else:
table = USER_CUSTOMER_TABLE
check_user = db.execute(
f"SELECT * FROM {table} WHERE username = :username OR email = :email",
username=username,
email=email)
if len(check_user) >= 1:
return render_template(REGISTER_PAGE,
form=form,
message=USER_EXISTS_MESSAGE)
db.execute(
f"INSERT INTO {table} (username, email, hash_pass) VALUES (:username, :email, :hash_pass)",
username=username,
email=email,
hash_pass=generate_password_hash(form.password.data, "sha256"))
return redirect(url_for(LOGIN))
return render_template(REGISTER_PAGE, form=form)
def business_account():
# get business data
query = str(
db.execute(
f"SELECT ub.id, ub.username, ub.email, b.name, b.fiscal_number, b.description, b.phone, b.mobile, ba.designation FROM user_businesses AS ub INNER JOIN business AS b ON ub.id = b.user_id AND ub.id = :user INNER JOIN businessAreas AS ba ON b.activity_sector_id = ba.id",
user=session['user_id']))
business = ast.literal_eval(query[1:len(query) - 1])
if request.method == POST:
form = BusinessAccountForm()
if form.validate():
db.execute(
f"UPDATE business SET name = :name, description = :description, phone = :phone, mobile = :mobile WHERE user_id = :user",
user=session["user_id"],
phone=form.phone.data,
mobile=form.mobile.data,
name=form.name.data,
description=form.description.data)
message = UPDATE_SUCCESS_MESSAGE
return render_template(ACCOUNT_PAGE,
form=form,
message=message,
data=business)
return render_template(ACCOUNT_PAGE, form=form, data=business)
return render_template(
ACCOUNT_PAGE,
form=BusinessAccountForm(formdata=MultiDict(business)),
data=business)
def get_cleanup_id(domainname):
obj_userdata= list()
column = model.get_columns("v_all_content")
query = "SELECT * FROM v_all_content WHERE nm_zone = \'"+domainname+"""\'"""
try:
result = list()
db.execute(query)
rows = db.fetchall()
for row in rows:
result.append(dict(zip(column,row)))
except Exception as e:
respons = {
"status" : False,
"messages":str(e)
}
else:
for i in result:
data = {
"id_zone": str(i['id_zone']),
"id_content": str(i['id_content']),
"id_content_serial": str(i['id_content_serial']),
"id_ttldata": str(i['id_ttldata']),
"id_record": str(i['id_record'])
}
obj_userdata.append(data)
finally:
return obj_userdata
def admin_book_search():
l = {
"bookname": "",
"author": "",
"quantity": "",
"section": "",
"serial_no": "",
"issuedby": "",
}
if request.method == "POST":
search = request.form.get("search")
username_data = db.execute(
"SELECT * FROM book WHERE bookname=:search", {"search": search}
).fetchone()
issuedby_data = db.execute(
"SELECT username FROM issue WHERE bookname=:bookname", {"bookname": search}
).fetchall()
size = int(len(issuedby_data))
if username_data is None:
flash(
"no book of this name is available here we will try to add it", "danger"
)
# db.execute("INSERT INTO book_req(name) VALUES(:username_data)",{'username_data':username_data})
# db.commit()
return render_template("admin_book_search.html", l1=l)
else:
l["bookname"] = username_data[1]
l["author"] = username_data[2]
l["quantity"] = username_data[3]
l["section"] = username_data[4]
l["serial_no"] = username_data[5]
l["issuedby"] = issuedby_data
return render_template("admin_book_search.html", l1=l, size=size)
return render_template("admin_book_search.html", l1=l)
def admin_insert_book():
if request.method == "POST":
bookname = request.form.get("bookname")
author = request.form.get("author")
quantity = request.form.get("quantity")
section = request.form.get("section")
serial_no = request.form.get("serial_no")
# db.execute("INSERT INTO Ibook(bookname) VALUES(:bookname)",{'bookname':bookname})
db.execute(
"INSERT INTO bookcn(bookname) VALUES(:bookname)", {"bookname": bookname}
)
db.execute(
"INSERT INTO book(bookname,author,quantity,section,serial_no) VALUES(:bookname,:author,:quantity,:section,:serial_no)",
{
"bookname": bookname,
"author": author,
"quantity": quantity,
"section": section,
"serial_no": serial_no,
},
)
# connection.commit()
db.commit()
flash("book inserted successfully", "success")
return render_template("admin_insert_book.html")
def delete(table, field=None, value=None):
rows_deleted = 0
try:
db.execute("DELETE FROM " + table + " WHERE " + field + " =" + value)
rows_deleted = db.rowcount
except (Exception, psycopg2.DatabaseError) as error:
print(error)
return rows_deleted
def get_columns(table):
column = None
try:
db.execute("SHOW columns FROM " + table)
column = [row[0] for row in db.fetchall()]
except (Exception, psycopg2.DatabaseError) as e:
column = str(e)
return column
def create(id_, name, email, profile_pic):
db = get_db()
db.execute(
"INSERT INTO user (id, name, email, profile_pic)"
" VALUES (?, ?, ?, ?)",
(id_, name, email, profile_pic),
)
db.commit()
def get_all(table):
results = None
try:
db.execute("SELECT * FROM "+table)
results = db.fetchall()
except Exception as e:
return str(e)
else:
return results
def delete(table, field = None, value = None):
rows_deleted = 0
try:
db.execute("DELETE FROM "+table+" WHERE "+field+" ="+value)
rows_deleted = db.rowcount
except Exception as error:
raise error
else:
return rows_deleted
def get_columns(table):
column = None
try:
# db.execute("SHOW columns FROM "+table)
db.execute("SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name='"+table+"'")
column = [row[0] for row in db.fetchall()]
except (Exception, psycopg2.DatabaseError) as e:
column = str(e)
return column
def get_by_id(table, field= None, value= None):
results = list()
try:
db.execute("SELECT * FROM "+table+" WHERE "+field+"=%s",(value,))
results = db.fetchall()
except Exception as e:
raise e
else:
return results
def user():
if current_user.is_anonymous:
return abort(404)
with Database() as db:
params = (current_user.discord_id, current_user.discord_tag,
current_user.discord_avatar, current_user.ns2_id)
db.execute(ns2plus_queries.UPDATE_DISCORD_DATA, params)
return render_template('user.html')
def main():
f = open("books.csv")
reader = csv.reader(f)
next(reader, None)
for isbn, title, author, year in reader:
db.execute("INSERT INTO books (isbn, title, author, year) VALUES (:isbn, :title, :author, :year)",
{"isbn": isbn, "title": title, "author": author, "year": year})
# book = Book(isbn=isbn, title=title, author=author, year=year)
# db.session.add(book)
db.commit()
def contactos():
if request.method == "POST":
nombre = request.form.get("nombre")
numero = request.form.get("numero")
correo = request.form.get("correo")
db.execute(f"insert into contactos values(,,)")
contactos = db.execute("select * from contactos where id_usuario =:Id",
Id=session["id_user"])
return render_template("contactos/index.html", contactos=contactos)
def get_all(table):
column = get_columns(table)
results = list()
try:
db.execute("SELECT * FROM " + table)
rows = db.fetchall()
for row in rows:
results.append(dict(zip(column, row)))
return results
except (Exception, psycopg2.DatabaseError) as e:
return column + str(e)
def cancelRequest(id):
if 'authorised' not in session or not session['authorised']:
emit('denyRequest', room=request.sid)
return
if id in queue:
queue.pop(id, None)
db.execute("DELETE FROM skates WHERE id = ?", (id, ))
conn.commit()
print('- ' + id)
emit('deleteRequest', id, broadcast=True)
def _create(cls, model_class, *args, **kwargs):
sql = "INSERT INTO AnswerOptions (id, title, question_id, with_text) VALUES (%s, %s, %s, %s);"
db.execute(
sql,
[
kwargs["id"],
kwargs["title"],
kwargs["question"]["id"],
kwargs["with_text"],
],
)
return kwargs
def _create(cls, model_class, *args, **kwargs):
sql = "INSERT INTO Questions (id, title, survey_id, question_type) VALUES (%s, %s, %s, %s);"
db.execute(
sql,
[
kwargs["id"],
kwargs["title"],
kwargs["survey"]["id"],
kwargs["question_type"],
],
)
return kwargs
def get_by_id(table, field= None, value= None):
column = get_columns(table)
results = list()
try:
db.execute("SELECT * FROM "+table+" WHERE "+field+"=%s",(value,))
rows = db.fetchall()
for row in rows:
results.append(dict(zip(column, row)))
except (Exception, psycopg2.DatabaseError) as e:
raise e
else:
return results
def customer_account():
# get customer and user data
query = str(
db.execute(
f"SELECT uc.id, uc.username, uc.email, c.first_name, c.last_name, c.age, c.address_id FROM user_customers AS uc INNER JOIN customers AS c ON uc.id = c.user_id AND uc.id = :id",
id=session["user_id"]))
user_cust = ast.literal_eval(query[1:len(query) - 1])
# get customer address
check_address = db.execute(
f"SELECT a.id FROM addresses AS a INNER JOIN customers AS c ON a.id = c.address_id AND c.user_id = :user",
user=session['user_id'])
if check_address:
address_query = str(
db.execute(
f"SELECT a.* FROM addresses AS a INNER JOIN customers AS c ON a.id = c.address_id AND c.user_id = :user",
user=session['user_id']))
address = ast.literal_eval(address_query[1:len(address_query) - 1])
else:
address = []
if request.method == POST:
form = CustomerAccountForm()
message = None
if form.validate():
db.execute(
f"UPDATE user_customers SET username = :username, email = :email, hash_pass = :password WHERE id = {session['user_id']}",
username=form.username.data,
email=form.email.data,
password=generate_password_hash(form.password.data, "sha256"))
message = UPDATE_SUCCESS_MESSAGE
else:
return render_template(ACCOUNT_PAGE,
form=form,
user_type=session["type"],
data=user_cust,
address=address)
return render_template(ACCOUNT_PAGE,
form=form,
user_type=session["type"],
message=message,
data=user_cust,
address=address)
return render_template(
ACCOUNT_PAGE,
form=CustomerAccountForm(formdata=MultiDict(user_cust)),
user_type=session["type"],
data=user_cust,
address=address)
def perform_action(media):
# actions to be performed on media
print "Going to like media with id: ", str(media.id)
print media.link
print media.user.id
print media.user.full_name
if is_liked(media) == 0:
api.like_media(media.id)
db.execute(
(
""" INSERT into media_likes (media_id, media_link, media_user_id, media_location, liked_by,
access_token, created_on)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}' ,now())"""
).format(media.id, media.link, media.user.id, str(media.location), current_user, access_token)
)
def table():
# Renders table.html.
conn = pymysql.connect(user=user, passwd=passwd, host=host, port=port)
db = conn.cursor()
db.execute('USE news_graph')
# get list of all domains
sql_q = ''' SELECT * from nodes
ORDER BY alexa ASC'''
db.execute(sql_q)
out = db.fetchall()
table = []
for line in out:
table.append(
[line[1], line[3], line[2], "%.4f" %
line[10], line[7], line[6], line[9]])
return render_template('table.html', table=table)
def create_tournament():
form = CreateTournament(request.form)
db = get_db()
if request.method == 'POST':
nam = request.form['tourn_name']
loc = request.form['location']
cal = request.form['calendar']
sys = request.form['system']
tie = request.form['tie_break']
db.execute('''INSERT INTO tournament \
(id, name, location, calendar, system, tie_break, round_num) \
VALUES (NULL, ?, ?, ?, ?, ?, ?)''', (nam, loc, cal, sys, tie, 1))
db.commit()
tournamentID = str(
db.execute('''SELECT max(id) FROM tournament''').fetchone()[0])
db.close()
return redirect(url_for('add_players', tournamentID=tournamentID))
return render_template('create_tournament.html', title='Create new tournament', form=form)
def load_tournament():
db = get_db()
cur = db.execute('SELECT id, name, round_num FROM tournament')
TOURNS = cur.fetchall()
db.close()
if request.method == 'POST':
tourn_to_load = request.form['tourn']
return redirect(url_for("round", tournamentID=tourn_to_load, round_num=2))
return render_template('load_tournament.html', tourns=TOURNS)
def is_liked(media):
result = db.execute(
"""
SELECT COUNT(*) FROM media_likes where
liked_by = '%s' and media_id = '%s' ;
"""
% (current_user, media.id)
)
return result.first()[0]
def round(tournamentID, round_num):
"""
gets the round number, PLAYERS' names and round match schedule,
pass it into the rendered template """
db = get_db()
cur = db.execute('''select * from game, game_tournament WHERE \
game_tournament.tournament_id = ? AND \
game.round_num = ?''', (tournamentID, round_num))
games = cur.fetchall()
if request.method == 'POST':
return redirect(url_for("standings"))
return render_template('test.html', games=games)
def home():
db = get_db()
cur = db.execute('SELECT * FROM tournament')
TOURNS = cur.fetchall()
if request.method == 'POST':
if request.form['choice'] == 'Create new':
return redirect(url_for("create_tournament"))
elif request.form['choice'] == 'Load old':
# go to load pages and pull in all tournament entries from the
# database
return redirect(url_for("load_tournament"))
db.close()
return render_template('index.html', tourns=TOURNS, title="Main menu")
def search():
# Renders index.html.
# connect db
conn = pymysql.connect(user=user, passwd=passwd, host=host, port=port)
db = conn.cursor()
db.execute('USE news_graph')
# get list of all domains
sql_q = ''' SELECT label, alexa from nodes
ORDER BY alexa ASC'''
db.execute(sql_q)
tuples = db.fetchall()
all_domains = [{'name': v[0]} for v in tuples]
all_domains_list = [x['name'] for x in all_domains]
# generate forms
pos_form = PosForm(csrf_enabled=False)
neg_form = NegForm(csrf_enabled=False)
sliders_form = SlidersForm(csrf_enabled=False)
# dafault values for sliders
if "config" in session:
config = session["config"]
else:
config = {
'neg_domains': [],
'pos_domains': [],
'links_slider': '40',
'alexa_slider': '40',
'p_rank_slider': '40',
'in_slider': '0', 'out_slider': '0', 'self_slider': '0'}
rank_sel = [int(
config[
'links_slider']), int(config['alexa_slider']), int(config['p_rank_slider']),
int(config['in_slider']), int(config['out_slider']), int(config['self_slider'])]
# add pos domain
if pos_form.data["add_pos_domain"]:
domain = pos_form.data["add_pos_domain"]
if domain not in config["pos_domains"] + config["neg_domains"] and domain in all_domains_list:
config['pos_domains'].append(domain)
# add neg domain
if neg_form.data["add_neg_domain"]:
domain = neg_form.data["add_neg_domain"]
if domain not in config["pos_domains"] + config["neg_domains"] and domain in all_domains_list:
config['neg_domains'].append(domain)
# delete pos domain
if pos_form.data["del_pos_domain"]:
domain = pos_form.data["del_pos_domain"]
config['pos_domains'].remove(domain)
# delete neg domain
if neg_form.data["del_neg_domain"]:
domain = neg_form.data["del_neg_domain"]
config['neg_domains'].remove(domain)
# sliders change
if sliders_form.data['links_slider']:
rank_sel = [int(
sliders_form.data[
'links_slider']), int(sliders_form.data['alexa_slider']),
int(sliders_form.data['p_rank_slider']), int(
sliders_form.data['in_slider']),
int(sliders_form.data['out_slider']), int(sliders_form.data['self_slider'])]
config['links_slider'] = str(rank_sel[0])
config['alexa_slider'] = str(rank_sel[1])
config['p_rank_slider'] = str(rank_sel[2])
config['in_slider'] = str(rank_sel[3])
config['out_slider'] = str(rank_sel[4])
config['self_slider'] = str(rank_sel[5])
session["config"] = config
if len(config['neg_domains']) == 0 and len(config['pos_domains']) == 0:
return render_template('search.html',
pos_form=pos_form,
neg_form=neg_form,
sliders_form=sliders_form,
all_domains=all_domains,
config=config)
arcs, sel_pos_nodes, sel_neg_nodes, rec_nodes = query_db(
db, config["pos_domains"], config["neg_domains"])
data, graph = process(arcs, sel_pos_nodes, sel_neg_nodes, rec_nodes, config[
"pos_domains"], config["neg_domains"], np.array(rank_sel) / float(sum(rank_sel)))
return render_template('search.html',
pos_form=pos_form,
neg_form=neg_form,
sliders_form=sliders_form,
all_domains=all_domains,
config=config,
data=data,
graph=graph)
def query_db(db, pos_domains, neg_domains):
pos_domains_str = '"' + '", "'.join(pos_domains + ["1"]) + '"'
neg_domains_str = '"' + '", "'.join(neg_domains + ["1"]) + '"'
joined_domains_str = '"' + \
'", "'.join(pos_domains + neg_domains + ["1"]) + '"'
# get all the relevant arcs
sql_q = '''SELECT DISTINCT arc_id, source, target, weight_n1 from arcs join (SELECT node_id FROM nodes WHERE label IN (''' + \
joined_domains_str + \
''')) as A ON arcs.source = A.node_id OR arcs.target = A.node_id'''
db.execute(sql_q)
arcs = db.fetchall()
# get all the relevant pos nodes
sql_q = '''SELECT * from nodes WHERE label IN (''' + pos_domains_str + ''')
ORDER BY alexa ASC'''
db.execute(sql_q)
sel_pos_nodes = db.fetchall()
# get all the relevant neg nodes
sql_q = '''SELECT * from nodes WHERE label IN (''' + neg_domains_str + ''')
ORDER BY alexa ASC'''
db.execute(sql_q)
sel_neg_nodes = db.fetchall()
# get recommendation
sql_q = '''SELECT node_id,label, alexa,location,n_out,n_in,w_out,w_in,w_diff,w_self,p_rank, SUM(amount) as am FROM
((SELECT * from (
SELECT * from nodes
JOIN
(SELECT col, SUM(weight_n2)/2.0 as amount FROM (
(SELECT source as col, weight_n2 from arcs
JOIN
(SELECT node_id FROM nodes WHERE label IN (
''' + pos_domains_str + ''')) as A
ON arcs.target = A.node_id
) UNION
(SELECT target as col, weight_n2 from arcs
JOIN
(SELECT node_id FROM nodes WHERE label IN (
''' + pos_domains_str + ''')) as B
ON arcs.source = B.node_id
)) as T
GROUP BY col
) as E
on nodes.node_id = E.col
ORDER BY amount DESC
LIMIT 100) as G
WHERE label NOT IN (''' + joined_domains_str + '''))
UNION
(SELECT * from (
SELECT * from nodes
JOIN
(SELECT col, -SUM(weight_n2) as amount FROM (
(SELECT source as col, weight_n2 from arcs
JOIN
(SELECT node_id FROM nodes WHERE label IN (
''' + neg_domains_str + ''')) as A
ON arcs.target = A.node_id
) UNION
(SELECT target as col, weight_n2 from arcs
JOIN
(SELECT node_id FROM nodes WHERE label IN (
''' + neg_domains_str + ''')) as B
ON arcs.source = B.node_id
)) as T
GROUP BY col
) as E
on nodes.node_id = E.col
ORDER BY amount ASC
LIMIT 10000) as G
WHERE label NOT IN (''' + joined_domains_str + '''))
) as j
GROUP BY node_id,label,alexa,location,n_out,n_in,w_out,w_in,w_diff,w_self,p_rank
ORDER BY am DESC
'''
db.execute(sql_q)
out = db.fetchall()
rec_nodes = np.array(out)
return arcs, sel_pos_nodes, sel_neg_nodes, rec_nodes