def index():
query="SELECT * FROM statistics WHERE TYPE='{}';".format(request.method)
methods = sql.query(query)
if methods is None:
query = "INSERT INTO statistics (`TYPE`, `VALUE`) VALUES ('{}', 1);".format(request.method)
sql.query(query)
else:
print(methods)
value = methods[0]['VALUE'] + 1
query = "UPDATE statistics SET `VALUE`={} WHERE TYPE='{}' ;".format(value, methods[0]['TYPE'])
sql.query(query)
#query="INSERT INTO statistics (`TYPE`, `VALUE`) VALUES ("
return render_template('index.html', methods = methods[0])
def syncdb1todb2():
check(sql.conndb(*db2), "Connect to db2")
desp, rs = sql.query("select [id] from %s order by [id] desc;" % ntb)
maxid = 0
if rs == False:
print "[-]", "Error:", geterror()
else:
if len(rs) > 0:
maxid = rs[0][0]
print "[+]", "GetMaxID:", maxid
check(sql.conndb(*db1), "Connect To db1")
desp, rs = sql.query(
"select id, loginname, password, lastlogintime, lastloginip, Email, nickname, city, sex, age, sendemail, status, birth, rightid, type from %s where id > %d"
% (otb, maxid))
keys = formatdesp(desp)
check(sql.conndb(*db2), "Connect To db2")
r = check(sql.execsql("set IDENTITY_INSERT %s on" % ntb),
"set IDENTITY_INSERT on")
if not r: return
for e in rs:
pswd = e[2]
userid = e[0]
v = l2s(e)
s = u"insert into %s %s values %s\n" % (ntb, keys, v)
s += u"update %s set password=N'%s' where id=%s" % (ntb, sql.md5(pswd),
e[0])
check(
sql.uexecsql(s),
u"Insert and Update Password for Account(%d) %s" % (userid, e[1]))
check(1, "Update %d record" % len(rs))
def add_quiz(cls, title):
query = "SELECT max(ID) FROM QUIZ"
new_id = sql.query(query)[0][0] + 1
print(new_id)
new_quiz = cls(title, new_id)
query_2 = """INSERT INTO QUIZ (title) VALUES (?)"""
params = [title]
sql.query(query_2, params)
return new_quiz
def check_answers(lista_punktow):
query_2 = 'DROP TABLE `answers`;'
new_id = sql.query(query_2)
query = "CREATE TABLE `answers` (`ID` INTEGER, `username` TEXT NOT NULL, `socket`, `time` TEXT NOT NULL, PRIMARY KEY(`ID`));"
new_id = sql.query(query)
for socket in lista_punktow:
query_3 = """INSERT INTO `answers` (`username`, `socket`, `time`)
VALUES (?, ?, ?)"""
params = socket
new_id = sql.query(query_3, params)
def buy(self, buy_id):
# お金が足りるか確認用
check = "select buy_price from buy where buy_id = '" + buy_id + "'"
price = sql.query(self, check)[0]
if self.money - price < 0:
return "お金が足りません"
else:
text = "select * from buy where buy_id = '" + buy_id + "'"
result = sql.query(self, text)
self.money -= price
self.neet_motivation += result[3]
return result[1] + "をあげた"
def update_story_cache(url,symbols,change_date=False):
if symbols:
symbol_count=len(symbols.split(' '))
else:
symbol_count=0
sql.query("insert into story\
(url,url_md5,symbols,symbol_count,fetch_date,hit_count)\
values (%s,md5(%s),%s,%s,now(),1)\
on duplicate key update hit_count=hit_count+1",(url,url,symbols,symbol_count))
if change_date:
sql.query("update story set\
fetch_date=now(), symbols=%s, symbol_count=%s where\
url_md5=md5(%s)" , (symbols,symbol_count,url))
def feed(self, food_id):
# お金が足りるか確認用
check = "select food_price from food1 where food_id = '" + food_id + "'"
price = sql.query(self, check)[0]
if self.money - price < 0:
return "お金が足りません"
else:
text = "select * from food1 where food_id = '" + food_id + "'"
result = sql.query(self, text)
self.money -= price
self.neet_fulness += result[3]
self.neet_motivation += result[4]
return result[1] + "をあげた"
def talk(self, talk_id):
# selfのステータスを更新
# DBのtalkテーブルからニートの返事を選択
text = "select * from talk where talk_id = '" + talk_id + "'"
result = sql.query(self, text)
self.neet_motivation += result[3]
return result[2][random.randint(0, 1)]
def remuser(params):
""" login: remove user from database"""
login=params[0]
if sql.query("delete from kolmognus_user where login=%s" , login):
return "user %s removed from database" % login
else:
return "can't remove user %s: not in database..." % login
def get_usernames():
query = """SELECT user_name FROM accounts"""
parameter = ""
fetch = "col"
usernames = sql.query(query, parameter, fetch)
if usernames is None:
usernames = []
return usernames
def addfeeds(params):
""" feed [feed.xml]... : add a feed to fetcher"""
added_feeds=0
for feed in params:
if sql.query("insert into feed (url,url_md5,hit_count) values (%s,MD5(%s),0)" , (feed,feed)):
added_feeds+=1
else:
return "can't add feed %s..." % feed
return "added %d feeds" % added_feeds
def buy(self, buy_id):
# お金が足りるか確認用
check = "select buy_price from buy where buy_id = '" + str(
self.buys_id[int(buy_id)]) + "'" # 変更箇所
price = sql.query(self, check)[0]
self.mother_fatigue -= 30
# お金が足りないときエラーメッセージ
if self.money - price < 0:
return "(お金が足りない)"
else:
text = "select * from buy where buy_id = '" + str(
self.buys_id[int(buy_id)]) + "'" # 変更箇所
result = sql.query(self, text)
self.money -= price
self.neet_motivation += result[3]
self.buys.pop(int(buy_id))
self.buys_id.pop(int(buy_id))
return "(" + result[1] + "をあげた)"
def bracelet_id(bracelet):
Bracelet_id = {
"cmd": 1,
'data': {
'bracelet_id': sql.query(bracelet), # 手环ID int32
'bracelet_type': 1, #手环类型 1普通手环 2 cpu卡手环 int8
},
}
return Bracelet_id
def adduser(params):
""" login password likedsymbol1 [liked_symbol...]: add user to database"""
login=params[0]
passwd=params[1]
symbols=' '.join(params[2:])
if sql.query("insert into kolmognus_user (login,pass,liked_symbols) values (%s,PASSWORD(%s),%s)" , (login,passwd,symbols)):
return "user %s added to database" % login
else:
return "can't add user %s..." % login
def query():
"""Execute SQL queries.
"""
read_query_list()
fn_id = 1
for qs in sql_queries:
results = sql.query(qs)
fn = 'query_results/q' + str(fn_id) + '_results'
fileio.write_query_results(fn, qs, results)
fn_id += 1
def addtags(params):
""" tag [tag]... : add delicious tag feeds to fetcher"""
added_feeds=0
for tag in params:
feed='http://delicious.com/rss/tag/%s' % tag
if sql.query("insert into feed (url,url_md5,hit_count) values (%s,MD5(%s),0)" , (feed,feed)):
added_feeds+=1
else:
return "can't add feed %s..." % feed
return "added %d feeds" % added_feeds
def get_quiz_list(cls):
query = "SELECT * FROM QUIZ"
q_list = sql.query(query)
list_to_send = []
for item in q_list:
quiz_id = item['id']
title = item['title']
list_to_send.append([quiz_id, title])
cls(title, quiz_id)
return list_to_send
def feed(self, food_id):
# お金が足りるか確認用
check = "select food_price from food1 where food_id = '" + str(
self.foods_id[int(food_id)]) + "'" # 変更箇所
price = sql.query(self, check)[0]
self.mother_fatigue -= 50
# お金が足りないときエラーメッセージ
if self.money - price < 0:
return "(お金が足りない)"
else:
text = "select * from food1 where food_id = '" + str(
self.foods_id[int(food_id)]) + "'" # 変更箇所
result = sql.query(self, text)
self.money -= price
self.neet_fulness += 100
self.neet_motivation += result[3]
self.foods.pop(int(food_id))
self.foods_id.pop(int(food_id))
return "(" + result[1] + "をあげた)"
def get_questions_by_id(quiz_id):
query = "SELECT * FROM QUESTIONS WHERE quiz_id=(?)"
ans_list = sql.query(query, [quiz_id])
list_to_push = []
for ans in ans_list:
list_to_push.append([
ans['id'], ans['number'], ans['quiz_id'], ans['img'],
ans['question'], ans['a_one'], ans['a_two'], ans['a_three'],
ans['a_good']
])
return list_to_push
def update_feed_cache(feed,urls,change_date=False):
sql.query("insert into feed (url,url_md5,fetch_date,hit_count) values (%s,md5(%s),now(),1)\
on duplicate key update hit_count=hit_count+1" , (feed,feed))
if change_date:
sql.query("update feed set fetch_date=now() where url_md5=md5(%s)",feed)
for url in urls:
sql.query("insert into story (url,url_md5,hit_count) values (%s,md5(%s),0)\
on duplicate key update id=id" , (url,url)) #nice hack
sql.query("insert into feed_story (story_id,feed_id)\
select story.id,feed.id\
from story,feed\
where story.url_md5=md5(%s)\
and feed.url_md5=md5(%s)\
on duplicate key update story_id=story_id" ,(url,feed)) #nice hack II
def fetch():
#is incoming full??
if sql.request("select count(id) from story where rated_date is null")[0][0]<250:
#build feed list from recent and requested tags
#print "INFO: building feed list"
feeds=sql.request("select url,added_by from feed where isnull(fetch_date) and not url= '' or addtime(fetch_date,'01:00:00') < now()")
#feeds=["rss.xml"]
stories=[]
#print "INFO: found %d updatable feeds" % len(feeds)
for k,(feed,added_by) in enumerate(feeds):
#print"INFO: updating %d/%d feed %s" % (k+1,len(feeds),feed)
for url,title,symbols in get_stories(feed,added_by):
sql.query("insert into story (url,url_md5,hit_count,symbols,symbol_count,fetch_date,title) values (%s,md5(%s),0,%s,%s,now(),%s)\
on duplicate key update title=%s" , (url,url,symbols,len(symbols.split()),title,title))
sql.query("insert into feed_story (story_id,feed_id)\
select story.id,feed.id\
from story,feed\
where story.url_md5=md5(%s)\
and feed.url_md5=md5(%s)\
on duplicate key update story_id=story_id" ,(url,feed)) #nice hack II
sql.query("update feed set fetch_date=now() where url_md5=md5(%s)",feed)
print "INFO: updated %d feeds (%s)" % (len(feeds),time.ctime())
else:
print "INFO: no more urls needed"
def update_data(self):
# random.seed(2 * self.count + self.time+int(self.player_id))
self.foods_id = random.sample(range(1, 40), 5)
self.buys_id = random.sample(range(1, 40), 5)
self.talks_id = random.sample(range(1, 40), 5)
self.foods.clear()
self.buys.clear()
self.talks.clear()
for item in self.foods_id: # データの読み込み
check = "select food_name from food1 where food_id = '" + str(
item) + "'"
check2 = "select food_price from food1 where food_id = '" + str(
item) + "'"
name = sql.query(self, check)
price = sql.query(self, check2)
self.foods.append({
'name': name[0],
'price': price[0]
}) # foods(配列)にデータを追加
for item in self.buys_id: # データの読み込み
check = "select buy_name from buy where buy_id = '" + str(
item) + "'"
check2 = "select buy_price from buy where buy_id = '" + str(
item) + "'"
name = sql.query(self, check)
price = sql.query(self, check2)
self.buys.append({
'name': name[0],
'price': price[0]
}) # buys(配列)にデータを追加
for item in self.talks_id: # データの読み込み
check = "select talk_sentence from talk where talk_id = '" + str(
item) + "'"
name = sql.query(self, check)
self.talks.append(name[0])
def create(self, id, password):
text = "select exists(select * from users where player_id = " + id + ");"
result = sql.query(self, text)
# ユーザIDが重複するとき
if result[0]:
return False
else: # ユーザIDが重複しないとき
text = "insert into users values(" + id + ",'" + password + "',0,1000,True,0,0,1);"
sql.add(self, text)
self.player_id = id
return True
def login(self, id, password):
# DBと入力を照合(true or false)
text = "select exists(select * from users where player_id = '" + id + "');"
result = sql.query(self, text)
# ユーザIDが重複するとき
if (result[0]):
text = "select * from users where player_id=" + id
result = sql.query(self, text)
if result[1] == password:
self.player_id = id
self.mother_fatigue = result[2]
self.money = result[3]
self.time = result[4]
self.neet_fulness = result[5]
self.neet_motivation = result[6]
return True
else:
return False
# ユーザIDが重複しないとき
else:
return False
def talk(self, talk_id):
self.mother_fatigue += 10
if self.neet_fulness < -100: # お腹がすいている場合の返事(追加)
self.neet_motivation -= 10
return "お腹がすいた"
else: # DBのtalkテーブルからニートの返事を選択
text = "select * from talk where talk_id = '" + str(
self.talks_id[int(talk_id)]) + "'"
result = sql.query(self, text)
rand_int = random.randint(0, 1)
self.neet_motivation += result[3][rand_int]
self.talks.pop(int(talk_id))
self.talks_id.pop(int(talk_id))
return result[2][rand_int]
def if_answer_correct(cls, user_choice, quizId, answerId):
query = "SELECT a_good FROM QUESTIONS WHERE quiz_id=(?) AND `number`=(?)"
params = [int(quizId), int(answerId) - 1]
good_answer = sql.query(query, params)[0][0]
print(good_answer)
user_choice = str(user_choice)
if user_choice == cls.a:
user_choice = 'a'
elif user_choice == cls.b:
user_choice = 'b'
elif user_choice == cls.c:
user_choice = 'c'
if user_choice == good_answer:
return True
return False
def get_state_pop_est_65_and_over(s, y):
"""Get state population estimate for age>=65 for a specific sex and year.
Arguments:
s (string) -- sex ('0' = Total, '1' = Male, '2' = Female)
y (string) -- year ('2010', '2011', '2012', '2013', '2014')
Returns:
(list) -- population estimate for age>=65 for specific sex for each state
"""
spe_list = []
for i in range(len(state_abbrev)):
q = sql.query('SELECT SUM(popEst' + y + 'Civ) FROM statePopEst ' +
'WHERE sex=' + s + ' AND (age BETWEEN 65 AND 85) ' +
"AND name='" + state_abbrev[i] + "'")
spe_list.append(int(q[0][0]))
return spe_list
def get_total_discharges_per_state(s, y):
"""Get total discharges per state for a specific drgDefinition and year.
Arguments:
s (string) -- drgDefinition
y (string) -- year ('2011', '2012', or '2013')
Returns:
(list) -- list of total discharges for each state
"""
td_list = []
for i in range(len(state_abbrev)):
q = sql.query('SELECT SUM(totalDischarges) FROM ipps' + y + ' ' +
"WHERE drgDefinition LIKE '%" + s + "%' " +
"AND providerState='" + state_abbrev[i] + "'")
if q[0][0] is None:
td_list.append(0)
else:
td_list.append(int(q[0][0]))
return td_list
def main(connection):
print("""Setting up Udacity Log Analysis Program\n
RE: FSND - Project 1 - Cory A. Ramirez""")
###########################################################################
# What articles have been accessed the most of all time?
try:
results = query(
"""SELECT articles.title, COUNT(*)
AS results from articles, log
WHERE log.status = '200 OK'
AND articles.slug = substr(log.path, 10)
GROUP BY articles.title
ORDER BY results desc
limit 3;""", connection)
except Exception as e:
print("Query error:", e)
print("Top three most accessed articles: ")
for i, record in enumerate(results):
print("Rank {rank}: \"{name}\" with {views} views.".format(
rank=i + 1, name=record[0], views=record[1]))
###########################################################################
# Which authors got the most page views?
try:
results = query(
"""SELECT authors.name, count (*)
AS results from log, authors, articles
WHERE log.status = '200 OK'
AND articles.slug = substr(log.path, 10)
AND authors.id = articles.author
GROUP BY authors.name
ORDER BY results desc""", connection)
except Exception as e:
print("Query error:", e)
print("Authors with the most page views: ")
for i, record in enumerate(results):
print("Rank {rank}: \"{name}\" with {views} views.".format(
rank=i + 1, name=record[0], views=record[1]))
###########################################################################
# Which days had greater than 1% of HTTP errors?
try:
results = query(
"""SELECT errors.date, errors.err/errors.total * 100
AS percentage
FROM (SELECT cast(time AS date) as date,
COUNT(*) AS total,
CAST(sum(cast(status != '200 OK' as int)) AS float) AS err
FROM log
GROUP BY date) AS errors
WHERE errors.err/errors.total > 0.01;""", connection)
except Exception as e:
print("Query error:", e)
print("Days with HTTP errors larger than 1% of all errors: ")
print("Date: " + str(results[0][0]) + " with number of errors totaling " +
"{0:0.1f}".format(results[0][1]) + "%.")
# Close db connection
db_conn.close()
def main():
# -------DROP-------
query("""DROP TABLE `QUESTION`;""")
query("""DROP TABLE `BEACON`;""")
query("""DROP TABLE `USERS`;""")
query("""DROP TABLE `ADMIN`;""")
# -------CREATE-------
query(
"""CREATE TABLE `QUESTION` (`ID` INTEGER, `QUESTION` TEXT, `ANSWER1` TEXT, `ANSWER2` TEXT, `ANSWER3` TEXT, `ANSWER4` TEXT, `CORRECT` TEXT, `USED` INTEGER, PRIMARY KEY(`ID`));"""
)
query(
"""CREATE TABLE `BEACON` (`ID` INTEGER,`ID_BEACON` TEXT, `LOCATION1` TEXT,`LOCATION2` TEXT,`LOCATION3` TEXT,`LOCATION4` TEXT,`CORRECT` TEXT,`HINT` TEXT, `USED` INTEGER ,PRIMARY KEY(`ID`));"""
)
query(
"""CREATE TABLE `USERS` (`ID` INTEGER,`MAIL` TEXT,`PASSWORD` TEXT, `IS_BEACON` INTEGER, `POINTS_AMOUNT` INTEGER, ID_QUESTION INTEGER , PRIMARY KEY(`ID`));"""
)
query(
"""CREATE TABLE `ADMIN` (`ID` INTEGER,`MAIL` TEXT,`PASSWORD` TEXT, PRIMARY KEY(`ID`));"""
)
#elif int(year) == 999:
# year = "多季"
elif 1991 <= int(year[:4]) and int(year[:4]) <= 2000:
year = "1991-2000"
elif 1981 <= int(year[:4]) and int(year[:4]) <= 1990:
year = "1981-1990"
elif int(year[:4]) <= 1980:
year = "1980以前"
if subtype == "movie":
table_name = "Movie"
Path = ("Movie\\%s\\%s" % (year, name))
elif subtype == "tv":
table_name = "TV"
Path = ("TVSeries\\%s\\%s\\%s" % (reg1, year, name))
query = sql.query(db_name, table_name,
save.split("\t")[0]) #查詢舊有資料
print
if query != None and (not config.DataUpdate
or query[-1] == Path): #若存在舊有資料且不須更新現有資料
LogNPrint("Ignore : " + query[-1])
Path = query[-1]
name = Path[Path.rfind("\\") + 1:]
elif query != None and config.DataUpdate and query[
-1] != Path: #若存在舊有資料且與新的資料不相符(數據更新)且更新資料參數為True
sql.input(db_name,
table_name,
save.split("\t") + [Path],
replace=True)
command2 = (
"rclone move -v \"%s\" \"%s\" --log-file=%s" %
(mypath + "\\" + query[-1], mypath + "\\" + Path, Logfile))
def delete_vote(parameters):
query = """DELETE FROM planet_votes WHERE planet_id=%s AND planet_name=%s AND account_id=%s;"""
fetch = ""
sql.query(query, parameters, fetch)
def create_new_account(username, password_hash, time):
query = """INSERT INTO accounts (user_name, password, reg_date) VALUES (%s, %s, %s) """
parameter = (username, password_hash, time)
sql.query(query, parameter, "")
def votes(user_id):
query = """ SELECT planet_name FROM planet_votes WHERE account_id=%s """
parameters = (user_id)
fetch = "col"
return sql.query(query, parameters, fetch)
def get_password(username):
query = """SELECT password FROM accounts WHERE user_name=%s;"""
parameters = (username, )
fetch = 'cell'
return sql.query(query, parameters, fetch)
def get_user_id(user):
query = """ SELECT id FROM accounts WHERE user_name=%s"""
parameters = (user, )
fetch = 'one'
return sql.query(query, parameters, fetch)
def save_vote(parameters):
query = """ INSERT INTO planet_votes (planet_id, planet_name, account_id,sub_time) VALUES (%s, %s, %s, %s)"""
fetch = ""
sql.query(query, parameters, fetch)
def rate_story(param,session):
rating_translation={'good':'G', 'bad':'B'}
if session.has_key('login') and param.has_key('rating') and param.has_key('story_id'):
sql.query("update kolmognus_user, recommended_story\
set recommended_story.user_rating=%s, recommended_story.userrating_date=now()\
where kolmognus_user.id=recommended_story.user_id and kolmognus_user.login=%s and recommended_story.story_id=%s", (rating_translation[param["rating"]],session['login'],param['story_id']))
def statistics():
query = 'SELECT * FROM statistics;'
return render_template('statistics.html', table=sql.query(query))
def solve(src, dest, mode, w1, qzzz):
# mode=0 means all trains, mode=1 means high-speed bullet trains (G/D/C), mode=2 means legacy trains (Z/T/K/numbers)
# cost = w1 * distance + (1-w1) * time
# 'qzzz' is the abbreviation of Chinese word '强制中转'(Qiang2 Zhi4 Zhong1 Zhuan3), which means forced transit city,
# user may choose the transit city according to his or her own wish.
selected_city = ''
selected_train1 = []
selected_train2 = []
all_res_list = []
selected_cost = 40000 # min([each[1] for each in selected_train1]) if selected_train1 else 40000
selected_dist = 40000
selected_time = 40000
# The number '40000' is from Mao Zedong's poet “坐地日行八万里，巡天遥看一千河” (By sitting on the ground
# we travel 80,000 Chinese miles a day, touring the sky and seeing a thousand galaxies from afar)
# Because the perimeter of Earth equator is about 40,000 km, I use 40000 here to represent infinity.
for city, stations in major_station_list.items(): # transit city
if qzzz != '':
if city != qzzz:
continue
if src in stations or dest in stations:
continue
res_list1 = []
res_list2 = []
for station in stations: # transit station
tmp = sql.query(src, station, mode, w1)
if tmp:
res_list1.extend(tmp)
if not res_list1:
continue
for station in stations:
tmp = sql.query(station, dest, mode, w1)
if tmp:
res_list2.extend(tmp)
if not res_list2:
continue
# in fact not all res_list need to be sorted; only the one in the final return values needs
# however, according to my test, the according optimization makes little use,
# and thus sorting isn't the performance bottleneck
res_list1.sort(key=lambda x: (x[1], x[3]))
cost1 = res_list1[0][1]
res_list2.sort(key=lambda x: (x[1], x[3]))
cost2 = res_list2[0][1]
# each record in the res_list is like:
# (
# train_no, cost, dist, time_delta,
# (src station, arrived_time, start_time),
# (dest station, arrived_time, start_time)
# )
cost = cost1 + cost2
all_res_list.append((city, cost, res_list1[0], res_list2[0]))
# each record in all_res_list is like:
# (
# city, cost, dist, time
# (
# train1_no, cost1, dist1, time_delta1,
# (src station, arrived_time, start_time),
# (transfer station, arrived_time, start_time)
# ),
# (
# train2_no, cost2, dist2, time_delta2,
# (transfer station, arrived_time, start_time),
# (dest station, arrived_time, start_time)
# ),
# )
# train1/train2 above is the one with lowest cost between src sta and trans sta / trans sta and dest sta
if cost >= selected_cost:
continue
selected_city = city
selected_cost = cost
selected_dist = min(train1[2] for train1 in res_list1) + min(
train2[2] for train2 in res_list2)
selected_time = min(train1[3] for train1 in res_list1) + min(
train2[3] for train2 in res_list2)
selected_train1 = res_list1
selected_train2 = res_list2
all_res_list.sort(key=lambda x: (x[1], x[3]))
return selected_city, selected_cost, selected_dist, selected_time, selected_train1, selected_train2, all_res_list
def clearfeeds(params):
""": clear fetcher feeds"""
if sql.query("truncate table feed"):
return "feeds cleared"
def get(self):
data = sql.query(ConnInfo)
self.write(kit.get_success(data))
#!/usr/bin/env python
import sql
import classifier
if __name__ == '__main__':
rerate_delay="0 00:00:01"
stories=sql.request("select id,url_md5,url,symbols from story where addtime(fetch_date,'12:00:00') > now() and (not isnull(fetch_date)) and (isnull(rated_date) or addtime(rated_date,%s) < now())" , rerate_delay) #do not rerate old news
users=sql.request("select id,login from kolmognus_user")
classifiers={}
alluser_info=classifier.get_alluser_info()
for user_id,login in users: #rate stories for each user
if not user_id in classifiers:
classifiers[user_id]=classifier.BayesianClassifier(user_id,alluser_info)
#classifiers[user_id]=classifier.DumbClassifier()
classif=classifiers[user_id]
for url_id,umd5,url,symbols in stories:
rating=classif.rate(symbols.split())
sql.query("insert into recommended_story (user_id,story_id,computed_rating)\
values(%s,%s,%s)\
on duplicate key update computed_rating=%s",(user_id,url_id,rating,rating))
for url_id,umd5,url,symbols in stories: #mark stories as rated
sql.query("update story set rated_date=now() where id=%s;",url_id)
sql.db.close()
import time
print "INFO: (%s) rated %d stories for %d users" % (time.asctime(),len(stories),len(users))
all_users=set()
all_stories=set()
learned=[]
header_printed=False
for user_id,story_id,user_rating,symbols in sql.request("select user_id,story_id,user_rating,symbols from recommended_story,story \
where id=story_id and user_rating != '?' and isnull(learned)"):
if not header_printed:
print "INFO: (%s) :"%time.asctime(),
header_printed=True
print ".",
all_users.add(user_id)
learned.append((user_id,story_id))
for s in symbols.split():
all_symbols.add(s)
if user_rating=='G':
increment(goods,user_id,s)
else:
increment(bads,user_id,s)
for user in all_users:
for s in all_symbols:
g_inc,b_inc=(goods.get(user,{}).get(s,0),bads.get(user,{}).get(s,0))
if b_inc!=0 or g_inc!=0:
sql.request("insert into bayes_data values (%s,%s,%s,%s)\
on duplicate key update\
good_count=good_count+%s, bad_count=bad_count+%s",(user,s,g_inc,b_inc,g_inc,b_inc))
for recom in learned:
sql.query("update recommended_story set learned=1 where user_id=%s and story_id=%s",recom)
sql.db.close()
