def return_all_goals_season(team):
cursor.execute("SELECT hts FROM match_results WHERE home_team='%s';" %
team)
home_goals = cursor.fetchall()
total_goals_scored = 0
for hts in home_goals:
total_goals_scored += hts[0]
cursor.execute("SELECT ats FROM match_results WHERE away_team='%s';" %
team)
away_goals = cursor.fetchall()
for ats in away_goals:
total_goals_scored += ats[0]
return total_goals_scored
def add_customer():
try:
if request.method == 'POST':
form = request.form
print(form)
cur.execute(f"INSERT INTO customer_master VALUES ('"+form['gstin_no']+"','"+form['name']+"', '"+form['address']+"', '"+form['place_of_supply']+"', '"+form['state_code']+"', '"+form['contact_no']+"', '"+form['contact_email']+"')")
con.commit()
res = jsonify(success=True)
return res
else:
return render_template('add-customer.html')
except Exception as e:
print(e)
print("Oooh!")
res = jsonify(success=False)
return res
def add_material():
try:
if request.method == 'POST':
form = request.form
print(form)
query =f"INSERT INTO material_master VALUES ('"+form['hsn']+"','"+form['mat-detail']+"', '"+form['uom']+"', "+form['rate']+","+form['tax-detail']+")"
print(query)
cur.execute(query)
con.commit()
res = jsonify(success=True)
return res
else:
return render_template('add-material.html')
except Exception as e:
print(e)
print("Oooh!")
res = jsonify(success=False)
return res
def invoice():
try:
if request.method == 'POST':
form = request.form
print(form)
query =f"INSERT INTO stock_register VALUES ('"+form['dc_no']+"','"+form['hsncode']+"', '"+form['date_of_process']+"', '"+form['process']+"',"+form['qty']+")"
print(query)
cur.execute(query)
con.commit()
res = jsonify(success=True)
return res
else:
cur.execute('select gstin_no,name from customer_master;')
rows = list(cur.fetchall())
return "blah"
except Exception as e:
print(e)
res = jsonify(success=False)
return res
def add_stock():
try:
if request.method == 'POST':
form = request.form
print(form)
query =f"INSERT INTO stock_register VALUES ('"+form['dc_no']+"','"+form['hsncode']+"', '"+form['date_of_process']+"', '"+form['process']+"',"+form['qty']+")"
print(query)
cur.execute(query)
con.commit()
res = jsonify(success=True)
return res
else:
return render_template('add-stock.html')
except Exception as e:
if(str(e)[1:5] == '1452'):
return "Product for given HSN code doesn't exist"
print("Oooh!")
res = jsonify(success=False)
return res
def login():
if request.method == "POST":
username_email = request.form["login_string"]
password = request.form["Password"]
try:
cursor.execute("SELECT * from users WHERE username = %s", (username_email,))
user_check_1 = cursor.fetchall()
except Exception as identifier:
user_check_1 = cursor.fetchall()
#print(user_check_1)
try:
cursor.execute("SELECT * from users WHERE email = %s", (username_email,))
user_check_2 = cursor.fetchall()
except Exception as identifier:
user_check_2 = cursor.fetchall()
#print(user_check_2)
if user_check_1 == []:
user_check = user_check_2
else:
user_check = user_check_1
#print (user_check)
#print (user_check[0][4])
if user_check:
if argon2.verify(password, user_check[0][4]) == True:
token = jwt.encode({'user': user_check[0][2], 'exp': datetime.datetime.utcnow() + datetime.timedelta(minutes=5)}, app.config['SECRET_KEY'])
#return jsonify({'token': token.decode('UTF-8')})
response = redirect(url_for('home'))
response.set_cookie('JWT', token)
return response
else:
flash("Incorrect password. Please try again.")
return redirect(request.url)
else:
flash("User with specified username or email does not exist. Check your spelling or please Sign Up.")
return redirect(request.url)
return render_template("login.html")
def home():
token = request.cookies['JWT']
data = jwt.decode(token, app.config['SECRET_KEY'])
current_user = data['user']
if request.method == "POST":
post_text = request.form["post_text"]
post_privacy = request.form["post_privacy"]
time = datetime.datetime.utcnow()
print(time)
#print (post_privacy, current_user, post_text)
cursor.execute("""INSERT INTO posts (user_username, post, post_privacy, datetime) VALUES (%s, %s, %s, %s);""", (current_user, post_text, post_privacy, time))
conn.commit()
try:
cursor.execute("""SELECT * FROM posts
WHERE user_username IN (SELECT follows FROM followers WHERE username = %s)
union
SELECT * FROM posts WHERE post_privacy = 'public'
ORDER BY id_post DESC
""", (current_user,))
data = cursor.fetchall()
return render_template("home.html", data=data)
except Exception as identifier:
print ('No data!')
return render_template("home.html")
def post(id):
if request.method == "POST":
comment_text = request.form["comment_text"]
#print(comment_text)
token = request.cookies['JWT']
token_decoded = jwt.decode(token, app.config['SECRET_KEY'])
current_user = token_decoded['user']
time = datetime.datetime.utcnow()
#print(current_user)
cursor.execute("""INSERT INTO comments (username, post_id, comment, datetime) VALUES (%s,%s,%s,%s);""", (current_user, id, comment_text, time))
conn.commit()
cursor.execute("""SELECT * FROM posts WHERE id_post = %s;""", (id,))
data_1 = cursor.fetchall()
cursor.execute("""SELECT * FROM comments WHERE post_id = %s;""", (id,))
data_2 = cursor.fetchall()
data=[data_1,data_2]
return render_template("post.html", data=data)
def signup():
if request.method == "POST":
name = request.form["Name"]
surname = request.form["Surname"]
username = request.form["Username"]
email = request.form["email"]
password = request.form["Password"]
confirm_password = request.form["ConfirmPassword"]
if password != confirm_password:
flash("Passwords doesn't match. Please check.")
return redirect(request.url)
else:
try:
cursor.execute("SELECT * from users WHERE username = %s", (username,))
username_check = cursor.fetchall()
except Exception as identifier:
username_check = cursor.fetchall()
try:
cursor.execute("SELECT * from users WHERE email = %s", (email,))
email_check = cursor.fetchall()
except Exception as identifier:
email_check = cursor.fetchall()
if not username_check:
if not email_check:
hash_password = argon2.hash(password)
cursor.execute("""INSERT INTO users (name, surname, username, email, password) VALUES (%s, %s, %s, %s, %s)""", (name, surname, username, email, hash_password))
conn.commit()
#flash("Account has been created. Proceed with signing in.")
return render_template("successful_reg.html")
else:
flash("Email already exists. Please use another one.")
return redirect(request.url)
else:
flash("Username already exists. Please use another one.")
return redirect(request.url)
return render_template("registration.html")
def profile(username):
token = request.cookies['JWT']
token_decoded = jwt.decode(token, app.config['SECRET_KEY'])
current_user = token_decoded['user']
try:
cursor.execute("""SELECT follows FROM followers WHERE username=%s AND follows=%s;""", (current_user, username))
following = cursor.fetchall()
except Exception as identifier:
following = cursor.fetchall()
if following:
try:
cursor.execute("""SELECT * FROM posts WHERE user_username = %s ORDER BY id_post DESC;""", (username,))
data = cursor.fetchall()
except Exception as identifier:
pass
if data == []:
data = [(username, 'No posts to show!'), False]
if request.method == "POST":
cursor.execute("""DELETE FROM followers WHERE username = %s AND follows = %s;""", (current_user, username))
conn.commit()
try:
cursor.execute("""SELECT * FROM posts WHERE user_username = %s AND post_privacy = %s ORDER BY id_post DESC;""", (username, 'public'))
data = cursor.fetchall()
except Exception as identifier:
pass
if data == []:
data = [(username, 'No posts to show!'), False]
return render_template('nfprofile.html', data=data)
return render_template("fprofile.html", data=data)
else:
try:
cursor.execute("""SELECT * FROM posts WHERE user_username = %s AND post_privacy = %s ORDER BY id_post DESC;""", (username, 'public'))
data = cursor.fetchall()
except Exception as identifier:
pass
if data == []:
data = [(username, 'No posts to show!'), False]
if request.method == "POST":
cursor.execute("""INSERT INTO followers (username, follows) VALUES (%s,%s);""", (current_user, username))
conn.commit()
try:
cursor.execute("""SELECT * FROM posts WHERE user_username = %s ORDER BY id_post DESC;""", (username,))
data = cursor.fetchall()
except Exception as identifier:
pass
if data == []:
data = [(username, 'No posts to show!'), False]
return render_template('fprofile.html', data=data)
return render_template("nfprofile.html", data=data)
from db_config import cursor, connection
file = 'index.txt'
sql = """
with per_word_count as (select word, count(word) as word_count from word group by word),
all_words_count as (select count(*) as all_words from word),
all_sites_count as (select count(*) as count from (select site_id from word group by site_id) as foo),
tf as (select word, (word_count::double precision / all_words::double precision) as tf
from per_word_count,
all_words_count),
idf as (select site_id, word, log(all_sites_count.count::double precision / count(word.word)::double precision) as idf
from word,
all_sites_count
group by site_id, word, all_sites_count.count),
tf_idf as (select tf.word, tf, idf, (tf * idf) as "tf-idf" from idf join tf on idf.word = tf.word)
select * from tf_idf;
"""
cursor.execute(sql)
result = cursor.fetchall()
index = open(file, 'a', encoding='utf-8')
index.write('word tf idf tf-idf\n')
for row in result:
index.write(row['word'] + ' ' + str(row['tf']) + ' ' + str(row['idf']) + ' ' + str(row['tf-idf']) + '\n')
cursor.close()
connection.close()
def insert_match_result(self):
insert_match_result_query = f"INSERT INTO match_results(home_team, away_team, fans, hts, ats)" \
f"VALUES('%s', '%s', %s, %s, %s);" % \
(self.home_team, self.away_team, self.fans, self.hts, self.ats)
cursor.execute(insert_match_result_query)
connection.commit()
from normalizer import preprocess_text
from db_config import cursor, connection
print('Введите запрос:')
query_tokens = preprocess_text(input())
cursor.execute('SELECT url FROM word join site s on s.id = word.site_id where word.word in %s group by s.url', (tuple(query_tokens),))
result = cursor.fetchall()
print('Результаты:')
for row in result:
print('\t' + row['url'])
cursor.close()
connection.close()
from normalizer import preprocess_text
from db_config import cursor, connection
print('Введите запрос:')
query_tokens = preprocess_text(input())
cursor.execute('SELECT url, "tf-idf" FROM tf_idf join site s on s.id = tf_idf.site_id where tf_idf.word in %s group by s.url, "tf-idf" order by "tf-idf"', (tuple(query_tokens),))
result = cursor.fetchall()
print('Результаты:')
all_results = {}
for row in result:
if row['url'] in all_results.items():
all_results[row['url']] = all_results.get(row['url']) * row['id']
else:
all_results[row['url']] = row['tf-idf']
all_results = dict(sorted(all_results.items(), key=lambda item: item[1]))
for key, value in all_results.items():
print('\t' + key + ' ' + str(value))
cursor.close()
connection.close()
from os import listdir
from os.path import isfile, join
from db_config import cursor, connection
sites_path = 'sites'
sites = [f for f in listdir(sites_path) if isfile(join(sites_path, f))]
insert_query = 'INSERT INTO site (url, filename) VALUES (%s, %s)'
for site in sites:
print('Processing ' + site)
url = site.split('.html', 1)[0]
cursor.execute(insert_query, (url, sites_path + "/" + site))
connection.commit()
cursor.close()
connection.close()
from db_config import cursor, connection
from normalizer import preprocess_text
try:
from BeautifulSoup import BeautifulSoup
except ImportError:
from bs4 import BeautifulSoup
cursor.execute('SELECT * FROM site')
sites = cursor.fetchall()
for row in sites:
print('Processing ' + row['url'])
html_file = open(row['filename'], "r", encoding="utf-8")
html = html_file.read().replace("<br>", "\n")
html_file.close()
parsed_html = BeautifulSoup(html, features="html.parser")
tokens = preprocess_text(parsed_html.text)
for word in tokens:
cursor.execute('INSERT INTO word (site_id, word) VALUES (%s, %s)',
(row['id'], word))
connection.commit()
cursor.close()
connection.close()
