def test_check_engine(get_actual_ship):
actual_ship = get_actual_ship
select_req = f'SELECT ship, engine FROM ships where ship="{actual_ship.name}"'
db_helper = DbHelper()
expect = db_helper.select_prod_data(select_req)
assert expect[0][0] == actual_ship.name
assert expect[0][1] == actual_ship.engine_name
def ships_test_data():
select_request = 'SELECT ship, weapon, hull, engine FROM ships'
db_helper = DbHelper()
ships = db_helper.select_tests_data(select_request)
actual_ships = []
for a_ship in ships:
actual_ships.append(Ship(a_ship[0], a_ship[1], a_ship[2], a_ship[3]))
return actual_ships
class MySpider():
def __init__(self, url, enable_headless, number_of_scrolls):
self.url = url
self.enable_headless = enable_headless
self.number_of_scrolls = number_of_scrolls
self.all_items = []
self.db = DbHelper()
def start_driver(self):
print("starting driver...")
options = Options()
if (self.enable_headless):
options.headless = True
self.driver = webdriver.Chrome(chrome_options=options)
def close_driver(self):
print("closing driver...")
self.driver.quit()
print("driver closed.")
def parse_page(self):
self.start_driver()
self.driver.get(self.url)
start = 0
end = 1000
for i in range(1, self.number_of_scrolls):
self.driver.execute_script("window.scrollTo(" + str(start) + ", " +
str(end) + ");")
start += 1000
end += 1000
sleep(5)
self.all_items = self.driver.find_elements_by_xpath(
".//*[@class='ripple movie-card vertical']")
self.store_items()
self.close_driver()
def store_items(self):
db_movies = self.db.get_movies('hotstar')
for parent_element in self.all_items:
name = parent_element.find_element_by_xpath(
".//*[@class='content-title ellipsise']").get_attribute(
"innerHTML")
image_url = parent_element.find_element_by_xpath(
".//*[@class='card card-img-container']"
).find_element_by_tag_name("img").get_attribute("src")
try:
flag_element = parent_element.find_element_by_xpath(
".//*[@class='flag_element']")
except NoSuchElementException:
flag_element = False
if name.strip() not in db_movies:
self.db.addMovies(movie_name.strip(), "hotstar", play_url,
image_url, is_paid)
def create_and_fill_db():
db_preparer = DbPreparer(prod_db)
db_preparer.create_all_table()
db_preparer.fill_all_table(get_ships(), get_weapons(), get_hulls(),
get_engines())
db_preparer.connection_close()
db_helper = DbHelper()
db_helper.copy_prod_db()
def __init__(self):
self.db = DbHelper("janicka", "janicka",
"Tajne heslo, ktere neni na githubu")
self.denni_prijem_kj = self.db.select_single_value(
"SELECT u.denni_prijem_kj FROM uzivatel u WHERE id = 1")
self.kj_potrebne_snidane = int(self.denni_prijem_kj / 100 *
20) # snidane 20%
self.kj_potrebne_svacina = int(self.denni_prijem_kj / 100 *
10) # svacina 10-15%
self.kj_potrebne_obed = int(self.denni_prijem_kj / 100 *
35) # obed 30-35%
self.kj_potrebne_vecere = int(self.denni_prijem_kj / 100 *
25) # vecere 15-25%
self.prebytek_ze_snidane = 0
self.prebytek_z_obeda = 0
class BaseVypocet():
def __init__(self):
self.db = DbHelper("janicka", "janicka",
"Tajne heslo, ktere neni na githubu")
self.denni_prijem_kj = self.db.select_single_value(
"SELECT u.denni_prijem_kj FROM uzivatel u WHERE id = 1")
self.kj_potrebne_snidane = int(self.denni_prijem_kj / 100 *
20) # snidane 20%
self.kj_potrebne_svacina = int(self.denni_prijem_kj / 100 *
10) # svacina 10-15%
self.kj_potrebne_obed = int(self.denni_prijem_kj / 100 *
35) # obed 30-35%
self.kj_potrebne_vecere = int(self.denni_prijem_kj / 100 *
25) # vecere 15-25%
self.prebytek_ze_snidane = 0
self.prebytek_z_obeda = 0
def nacti_zasoby(self):
funkce.nacti_zasoby(
) # toto je zde, aby se tabulka nenacitala hned po spusteni serveru
def _formatuj_vystup(self, jidla):
vypis = []
for jidlo in jidla:
nazev = jidlo[0]
mnozstvi = "{} {}".format(jidlo[1], jidlo[2])
vypis.append(Polozka_jidelnicku(nazev, mnozstvi))
return vypis
def get_snidane(self):
snidane = funkce.vytvor_snidani(self.kj_potrebne_snidane)
self.prebytek_ze_snidane = snidane[0]
return self._formatuj_vystup(snidane[1])
def get_svacina_dopo(self):
svacina = funkce.vytvor_svacinu(self.kj_potrebne_svacina)
return self._formatuj_vystup(svacina[1])
def get_obed(self):
obed = funkce.vytvor_obed(self.kj_potrebne_obed +
self.prebytek_ze_snidane)
self.prebytek_z_obeda = obed[0]
return self._formatuj_vystup(obed[1])
def get_svacina_odpo(self):
svacina = funkce.vytvor_svacinu(self.kj_potrebne_svacina)
return self._formatuj_vystup(svacina[1])
def get_vecere(self):
vecere = funkce.vytvor_veceri(self.kj_potrebne_vecere +
self.prebytek_z_obeda)
return self._formatuj_vystup(vecere[1])
def test_check_weapon(get_actual_ship):
actual_ship = get_actual_ship
select_req = f'SELECT ship, weapon FROM ships where ship="{actual_ship.name}"'
db_helper = DbHelper()
expect = db_helper.select_prod_data(select_req)
assert expect[0][0] == actual_ship.name
assert expect[0][1] == actual_ship.weapon_name
db_helper.random_update_weapon(actual_ship.weapon_name)
sql_request = 'SELECT weapon, reloadSpeed, rotationalSpeed, ' \
f'diameter, powerVolley, count FROM weapons where weapon="{actual_ship.weapon_name}"'
actual_weapon = db_helper.select_tests_data(sql_request)
expect_weapon = db_helper.select_prod_data(sql_request)
assert expect_weapon[0][0] == actual_weapon[0][0]
assert expect_weapon[0][1] == actual_weapon[0][1]
assert expect_weapon[0][2] == actual_weapon[0][2]
assert expect_weapon[0][3] == actual_weapon[0][3]
assert expect_weapon[0][4] == actual_weapon[0][4]
assert expect_weapon[0][5] == actual_weapon[0][5]
from dbHelper import DbHelper
import configparser
if __name__ == "__main__":
cf = configparser.ConfigParser()
cf.read("../webrest.conf")
mysql_host = cf.get("mysql", "host")
mysql_uid = cf.get("mysql", "uid")
mysql_pwd = cf.get("mysql", "pwd")
mysql_db = cf.get("mysql", "db")
mysql_port = cf.getint("mysql", "port")
web_port = cf.getint("web", "port")
db = DbHelper(mysql_host, mysql_uid, mysql_pwd, mysql_port, mysql_db)
sql = '''
DROP TABLE IF EXISTS t_jieba;
CREATE TABLE t_jieba (
f_time datetime NOT NULL,
f_content text NOT NULL
)
'''
print(sql)
d = db.execute_sql(sql)
print(d)
def __init__(self, url, enable_headless, number_of_scrolls):
self.url = url
self.enable_headless = enable_headless
self.number_of_scrolls = number_of_scrolls
self.all_items = []
self.db = DbHelper()
def __init__(self):
self.db = DbHelper("ada", "ada", "To heslo ti nereknu")
class VypocetDbBased(BaseVypocet):
SNIDANE_ID = 1
SVACINA_DOPO_ID = 2
OBED_ID = 3
SVACINA_ODPO_ID = 4
VECERE_ID = 5
def __init__(self):
self.db = DbHelper("ada", "ada", "To heslo ti nereknu")
def get_snidane(self):
return self.get_dennichod(self.SNIDANE_ID)
def get_svacina_dopo(self):
return get_dennichod(self.SVACINA_DOPO_ID)
def get_obed(self):
return get_dennichod(self.OBED_ID)
def get_svacina_odpo(self):
return get_dennichod(self.SVACINA_ODPO_ID)
def get_vecere(self):
return get_dennichod(self.VECERE_ID)
def get_dennichod(self, dennichod_id):
denni_prijem_kj = self.db.select_single_value(
"SELECT u.denni_prijem_kj FROM uzivatel u WHERE id = 1")
mozne_patterny = self.db.select_all(
"SELECT p.id FROM Pattern p JOIN denni_chody dch ON p.id_denni_chody = dch.id WHERE 0 < all(SELECT sum(CASE WHEN (z.mnozstvi-z.blokovano)*pom.kj >= dch.zastoupeni_procenta * pl.ratio * %s then 1 else 0 END) FROM pattern_line pl JOIN pattern_line_set pls ON pl.id_pattern = pls.id_pattern and pl.line = pls.pattern_line JOIN potraviny_kat_skup pom ON pls.id_potraviny = pom.id_potravina or pls.id_kategorie = pom.id_kategorie or pls.id_skupina = pom.id_skupina LEFT JOIN zasoby z ON pom.id_potravina = z.id_potraviny WHERE pl.id_pattern = p.id GROUP BY pl.id_pattern, pl.line) and exists(select 1 from pattern_line pl where pl.id_pattern = p.id) and p.id_denni_chody = %s",
(
denni_prijem_kj,
dennichod_id,
))
if not mozne_patterny:
raise Exception("nenalezen ani jeden vhodny pattern")
vybrany_pattern = random.choice(mozne_patterny)[0]
print(vybrany_pattern)
mozne_potraviny = self.db.select_all(
"SELECT pl.line, pom.id_potravina, pot.nazev, round(dch.zastoupeni_procenta * pl.ratio * %s/pot.kj) as mnozstvi, mj.zkratka FROM pattern_line pl JOIN Pattern p on pl.id_pattern = p.id JOIN denni_chody dch ON p.id_denni_chody = dch.id JOIN pattern_line_set pls ON pl.id_pattern = pls.id_pattern and pl.line = pls.pattern_line JOIN potraviny_kat_skup pom ON pls.id_potraviny = pom.id_potravina or pls.id_kategorie = pom.id_kategorie or pls.id_skupina = pom.id_skupina JOIN zasoby z ON pom.id_potravina = z.id_potraviny JOIN potraviny pot on pot.id = pom.id_potravina JOIN merna_jednotka mj on pot.id_merna_jednotka = mj.id WHERE (z.mnozstvi-z.blokovano)*pom.kj >= dch.zastoupeni_procenta * pl.ratio * "
+ str(denni_prijem_kj) + " and p.id = %s", (
denni_prijem_kj,
vybrany_pattern,
))
pocet_lines = self.db.select_single_value(
"select COUNT (*) from pattern_line pl where pl.id_pattern = %s",
(vybrany_pattern, ))
polozky_jidelnicku = []
for i in range(1, pocet_lines + 1):
vybrana_polozka = random.choice(
list(filter(lambda x: x[0] == i, mozne_potraviny)))
polozky_jidelnicku.append(
Polozka_jidelnicku(
vybrana_polozka[2],
str(vybrana_polozka[3]) + " " + vybrana_polozka[4]))
print(polozky_jidelnicku)
return polozky_jidelnicku
class Cleanup:
def __init__(self, portNum, uname, pw, dbname):
self.dbHelper = DbHelper(portNum, uname, pw, dbname)
def numOfRecords(self):
''' map datetimes to number of records collected over start and end dates'''
''' results: missing the following records
2011-09-19 09:30:00 125
2011-09-15 20:50:00 100
2011-09-15 16:50:00 50
'''
start = datetime.datetime(2011, 9, 7, 20, 30) #2011, 9, 7, 15, 28 #
end = datetime.datetime(2011, 9, 20, 9, 50) #2011, 9, 7, 20, 18 #
change = datetime.timedelta(minutes=10)
tempChange = datetime.timedelta(minutes=5)
timeDict = {}
records = 0
while start <= end:
tempEnd = start + tempChange
sql = "Select count(*) from Reddit.reddit WHERE scraped BETWEEN '"+str(start)+"' AND '"+str(tempEnd)+"';"
res = self.dbHelper.customQuery(sql)
timeDict[start] = res[0][0]
records += timeDict[start]
print start, timeDict[start], records
start += change
# check which ones didnt make 200 records
print "res:"
total = 0
totalMissing = 0
for key in timeDict:
total += 200
if timeDict[key] != 200:
totalMissing += 200 - timeDict[key]
print key, timeDict[key]
print total, totalMissing, total-totalMissing
def removeEdgeData(self, time):
''' moves rows from main table to aux table
rows should satisfy the following conditions:
1. already on the front page when bot started
2. was still on the front page when bot ended
'''
tempChange = datetime.timedelta(minutes=5)
# find rows that were on the front page during this time
res = self.dbHelper.getTimeInclusive(time, time + tempChange)
print "Number of pids to be affected by cleanup: ", len(res)
for item in res:
pid = item[0]
#print self.dbHelper.getField('*', 'pid', pid)
# move row to aux table
sql = ("INSERT INTO Reddit.moved (scraped, rank, site, subreddit, post_text, pid, title, score, over_18, thumbnail, subreddit_id, downs,permalink, created, url, author, num_comments, ups)"
" SELECT scraped, rank, site, subreddit, post_text, pid, title, score, over_18, thumbnail, subreddit_id, downs,permalink, created, url, author, num_comments, ups FROM Reddit.reddit"
" WHERE pid = '"+pid+"';")
resMove = self.dbHelper.customQuery(sql)
# delete row from regular table
sql = ("DELETE from Reddit.reddit WHERE pid='"+pid+"';")
resDelete = self.dbHelper.customQuery(sql)
print "Moved", resMove[1], 'row(s) and deleted ', resDelete[1],"row(s) with pid", pid
def __init__(self, portNum, uname, pw, dbname): self.dbHelper = DbHelper(portNum, uname, pw, dbname)
class Grab:
def __init__(self, portNum, uname, pw, dbname):
self.email = ''
self.dbHelper = DbHelper(portNum, uname, pw, dbname)
def writeRes(self, fileName, res):
f = open(fileName, 'a')
f.write(str(res))
f.close()
def lengthOfStay(self):
''' length that posts stay on front page (r1 to r25)'''
sql = """SELECT pid, MAX(scraped), MIN(scraped) FROM Reddit.reddit WHERE rank<25 GROUP BY pid;"""
posts = self.dbHelper.customQuery(sql)
lengthDict = {}
for post in posts:
# get datetimes that first and last appeared on frontpage
minDate = post[2]
maxDate = post[1]
#print minDate, maxDate
diff = (maxDate - minDate).seconds/(60)
# insert into dict
lengthDict[diff] = 1 if diff not in lengthDict else lengthDict[diff] + 1
self.writeRes('lengthOfStay.res', lengthDict)
return lengthDict
def timeToReachFront(self):
''' time that it takes posts reach the front page'''
sql = """ SELECT pid, created, MIN(scraped) FROM Reddit.reddit WHERE rank>=1 AND rank<=25 GROUP BY pid"""
posts = self.dbHelper.customQuery(sql)
lengthDict = {}
for post in posts:
minDateAppeared = post[2]
#convert date created to datetime
createdDate = datetime.datetime.utcfromtimestamp(float(post[1]))
# add 4 hours because I messed up on writing to the db with gm time
minDateAppeared = minDateAppeared + datetime.timedelta(hours=4)
diff = (minDateAppeared - createdDate).seconds/(60)
# insert into dict
lengthDict[diff] = 1 if diff not in lengthDict else lengthDict[diff] + 1
self.writeRes('timeToReachFront.res', lengthDict)
return lengthDict
def postTime(self, posts, f):
''' time posted vs number of posts eventually reaching cutoff rank'''
postDict = {}
for post in posts:
# get time post was created
created = int(round(float(post[1])))
createdHour = time.gmtime(created).tm_hour
createdDate = time.gmtime(created).tm_yday
# increment count
if createdDate in postDict:
postDict[createdDate][createdHour] = 1 if createdHour not in postDict[createdDate] else postDict[createdDate][createdHour]+1
else:
postDict[createdDate] = {createdHour:1}
self.writeRes(f, postDict)
return postDict
def peak(self, posts, f, peakType="Rank"):
''' number of posts that have reached max rank vs posting time'''
peakDict = {}
for post in posts:
# figure out max rank
fields = (post[0], peakType)
sql = """ SELECT created, scraped FROM Reddit.reddit WHERE pid='%s' ORDER BY %s, scraped ASC LIMIT 1"""%fields
res = self.dbHelper.customQuery(sql)
# figure out time diff between created and scraped
scraped = res[0][1]
createdDate = datetime.datetime.utcfromtimestamp(float(res[0][0]))
# add 4 hours because I messed up on writing to the db with gm time
scraped = scraped + datetime.timedelta(hours=4)
diff = (scraped-createdDate).days*1440 + (scraped - createdDate).seconds/(60)
peakDict[diff] = 1 if diff not in peakDict else peakDict[diff] + 1
self.writeRes(f, peakDict)
return peakDict
def repost(self):
''' number of posts that are repeats, ie different pids share same url'''
sql = """SELECT url from Reddit.reddit GROUP BY url HAVING COUNT(DISTINCT pid) > 1;"""
res = self.dbHelper.customQuery(sql)
firstPosts = []
repeatPosts = []
for item in res:
fields = (item[0])
sql = """SELECT pid FROM Reddit.reddit WHERE url='%s' GROUP BY pid ORDER BY created ASC;"""%fields
posts = self.dbHelper.customQuery(sql)
for i, post in enumerate(posts):
fields = (post[0])
sql = """SELECT rank FROM Reddit.reddit WHERE pid='%s' ORDER BY scraped ASC;"""%fields
ranks = self.dbHelper.customQuery(sql)
resRanks = [rank[0] for rank in ranks]
if i == 0: # get all the ranking data of the url that was posted first
firstPosts.append(resRanks)
else: # get all ranking data of urls that were posted later
repeatPosts.append(resRanks)
# write to file
self.writeRes('firstPosts.res', firstPosts)
self.writeRes('repeatPosts.res', repeatPosts)
return firstPosts, repeatPosts
def score(self, posts, f, byRank=False, overallScore=True, upsOnly=False, downsOnly=False):
''' score since time posting'''
scoreDict = {}
for post in posts:
fields = (post[0])
sql = """SELECT scraped, created, score, ups, downs, rank FROM Reddit.reddit WHERE pid='%s' ORDER BY scraped ASC;"""%fields
res = self.dbHelper.customQuery(sql)
createdDate = datetime.datetime.utcfromtimestamp(float(post[1]))
print post[0], post[1], createdDate
# map it to time since creation
oldScore = 0
oldUps = 0
oldDowns = 0
for r in res:
scraped = r[0]
# add 4 hours because I messed up on writing to the db with gm time
scraped = scraped + datetime.timedelta(hours=4)
diff = (scraped-createdDate).days*1440 + (scraped - createdDate).seconds/(60)
if byRank:
rank = r[5]
ups = r[3] - oldUps
oldUps = r[3]
downs = r[4] - oldDowns
oldDowns = r[4]
if rank in scoreDict:
upsList = scoreDict[rank][0]
upsList.append(ups)
downsList = scoreDict[rank][1]
downsList.append(downs)
diffList = scoreDict[rank][2]
diffList.append(diff)
scoreDict[rank] = (upsList, downsList, diffList)
else:
scoreDict[rank] = ([ups], [downs], [diff])
else:
if not overallScore:
score = r[2] - oldScore
oldScore = r[2]
else:
score = r[2]
if upsOnly:
score = r[3]
if downsOnly:
score = r[4]
if diff in scoreDict:
prev = scoreDict[diff]
scoreDict[diff] = (prev[0]+score, prev[1]+1)
else:
scoreDict[diff] = (score, 1)
self.writeRes(f, scoreDict)
return scoreDict
def subreddit(self, posts, f):
subredditDict = {}
for post in posts:
subredditDict[post[2]] = 1 if post[2] not in subredditDict else subredditDict[post[2]] + 1
self.writeRes(f, subredditDict)
return subredditDict
def bayesianSubreddit(self, subreddit):
fields = (25, subreddit)
sql = """SELECT pid, created FROM Reddit.reddit WHERE rank <=%s AND subreddit='%s' GROUP BY pid ORDER BY created;"""%fields
res = self.dbHelper.customQuery(sql)
subredditDict = {}
for hour in range(0, 24):
number = 0.0
for item in res:
createdDate = datetime.datetime.utcfromtimestamp(float(item[1]))
if createdDate.hour == hour:
number += 1
subredditDict[hour] = number
fields = (200, subreddit)
sql = """SELECT pid, created FROM Reddit.reddit WHERE rank <=%s AND subreddit='%s' GROUP BY pid ORDER BY created;"""%fields
res = self.dbHelper.customQuery(sql)
for hour in range(0, 24):
number = 0.0
for item in res:
createdDate = datetime.datetime.utcfromtimestamp(float(item[1]))
if createdDate.hour == hour:
number += 1
if subredditDict[hour] != 0:
subredditDict[hour] = subredditDict[hour]/number
self.writeRes(subreddit+'_bayesian.res', subredditDict)
return subredditDict
def rankVsScore(self, f):
rankDict = {}
for rank in range(1, 201):
fields = (rank)
sql = """SELECT score FROM Reddit.reddit WHERE rank=%s"""%fields
res = self.dbHelper.customQuery(sql)
scoreList = []
for r in res:
scoreList.append(r[0])
rankDict[rank] = scoreList
self.writeRes(f, rankDict)
return rankDict
def scoreVsTime(self, f, rank):
resDict = {}
fields = (rank)
sql = """SELECT score, scraped FROM Reddit.reddit WHERE rank=%s ORDER BY scraped ASC"""%fields
res = self.dbHelper.customQuery(sql)
scores = []
scrapes = []
for item in res:
scores.append(item[0])
scrapes.append(item[1])
resDict['scores'] = scores
resDict['scrapes'] = scrapes
self.writeRes(f, resDict)
return resDict
def compareComments(self, posts, f):
'''get lowest rank of each post, check num of comments'''
commentsDict = {}
for post in posts:
fields = (post[0])
sql = """SELECT num_comments, rank FROM Reddit.reddit WHERE pid='%s' ORDER BY rank DESC LIMIT 1;"""%fields
res = self.dbHelper.customQuery(sql)
print "grabbing post", post[0], res[0][0], res[0][1]
if res[0][1] not in commentsDict:
commentsDict[res[0][1]] = [res[0][0]]
else:
temp = commentsDict[res[0][1]]
temp.append(res[0][0])
commentsDict[res[0][1]] = temp
self.writeRes(f, commentsDict)
return commentsDict
from flask import Flask, render_template, request, redirect, url_for
from flask_table import Table, Col
import psycopg2
from polozka_jidelnicku import Polozka_jidelnicku
from dbHelper import DbHelper
from baseVypocet import BaseVypocet #baseVypocet obsahuje testovaci hodnoty pro jidelnicek
from vypocetDbBased import VypocetDbBased
'''vypocetDbBased: generovani jidelnicku zalozeno na selectech z DB
(DB pracuje navic s tabulkami pattern, pattern_line, pattern_line_set) narozdil od vypoctu zalozenem na pythonu'''
from forms import SignupForm, VytvorZasoby
from config import Config
import json
#---------------------------------------------------------PRIPOJENI DO DB & SELECTY ------------------------------------------------------------#
DB = DbHelper("janicka", "janicka", "Tajne heslo, ktere neni na githubu")
#v DBHelperu je obsazeno napojeni do DB a zakladni selecty (select_all,single row, single value)
VYPOCET = BaseVypocet()
#VYPOCET = VypocetDbBased ()
app = Flask(__name__)
app.config.from_object(Config) #pro wtf forms
#-------------------------------------------------------------------HOMEPAGE--------------------------------------------------------------------#
@app.route("/")
def index():
return render_template("index.html")
def session_scope():
db_helper = DbHelper()
db_helper.copy_prod_db()
yield
db_helper.clear_test_db()