def fetch_clust_name_arr_from_db():
name_arr = []
con = dbconf.connect_db()
try:
cur = con.cursor()
cur.execute(
"""SELECT movie_name
FROM cluster_mov_id_arr_cache1"""
)
con.commit()
except db.Error:
print "select error."
print db.Error
try:
con.rollback()
except db.Error:
print db.Error
row_num = int(cur.rowcount)
for i in range(row_num):
row = cur.fetchone()
name = row[0]
name_arr.append(name)
cur.close()
con.close()
print "fetch cluster name array success."
return name_arr
def fetch_item_matrix_from_db():
con = dbconf.connect_db()
result = {}
try:
cur = con.cursor()
cur.execute("""SELECT movie_name, sim_movie, similarity
FROM item_matrix_cache1""")
con.commit()
except db.Error:
print 'insert error'
print db.Error
try:
con.rollback()
except db.Error:
pass
num_mov = int(cur.rowcount)
for i in range( num_mov ):
row = cur.fetchone()
movie_name = str(row[0])
sim_movie = str(row[1])
similarity = float(row[2])
result.setdefault(movie_name, [])
result[movie_name].append( (similarity, sim_movie) )
cur.close()
con.close()
print 'fetch item matrix success.'
return result
def fetch_cluster_result_from_db():
con = dbconf.connect_db()
# create the variable to store the result
result = []
# select the cluster id, no need to confirm the sequence
try:
cur = con.cursor()
cur.execute(
"""SELECT DISTINCT(cluster_id)
FROM cluster_result_cache1"""
)
con.commit()
except db.Error:
print "select error."
print db.Error
try:
con.rollback()
except db.Error:
print db.Error
row_num = int(cur.rowcount)
cur.close()
cluster_id_arr = range(row_num)
# from the cluster id to find its element to complete the cluster which is a list
for cluster_id in cluster_id_arr:
one_cluster = []
try:
cur = con.cursor()
cur.execute(
"""SELECT clus_movie_id
FROM cluster_result_cache1
WHERE cluster_id=%d"""
% (cluster_id)
)
con.commit()
except db.Error:
print "select error"
print db.Error
try:
con.rollback()
except db.Error:
print db.Error
# append the element into the cluster list
rownum = int(cur.rowcount)
for i in range(rownum):
row = cur.fetchone()
clus_movie_id = int(row[0])
one_cluster.append(clus_movie_id)
cur.close()
# append the cluster into the result list
result.append(one_cluster)
con.close()
print "fetch cluster result success."
return result
def store_cluster_result(cluster_result):
con = dbconf.connect_db()
try:
cur = con.cursor()
cur.execute("""CREATE TABLE IF NOT EXISTS `cluster_result_cache1` (
`cluster_id` int(10) unsigned NOT NULL,
`clus_movie_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`cluster_id`,`clus_movie_id`),
KEY `clus_movie_id` (`clus_movie_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8""")
con.commit()
cur.close()
except db.Error:
pass
# clear the database table for a new cluster procedure
#try:
# cur = con.cursor()
# cur.execute("""DELETE FROM cluster_result_cache""")
# con.commit()
# cur.close()
#except db.Error:
# print 'delete failed.'
# print db.Error
# try:
# con.rollback()
# except db.Error:
# print db.Error
# store the cluster result in the database
for i in range( len(cluster_result) ):
one_cluster = cluster_result[i]
cluster_id = i
for clus_movie_id in one_cluster:
try:
cur = con.cursor()
cur.execute("""REPLACE cluster_result_cache1( cluster_id, clus_movie_id )
VALUES( %d, %d )""" % (cluster_id, clus_movie_id))
con.commit()
cur.close()
except db.Error:
print 'error'
print db.Error
try:
con.rollback()
#cur = con.cursor()
#cur.execute("""INSERT INTO cluster_result_cache( cluster_id, clus_movie_id )
# VALUES( %d, %d )""" % (cluster_id, clus_movie_id))
#con.commit()
#cur.close()
except db.Error:
print db.Error
con.close()
print 'store cluster result finished.'
def insert_type_one(self, data):
try:
dumped = json.dumps(data)
prem = json.loads(dumped)
process = connect_db().comments.insert_one(prem)
print(process)
print('Data Insertion successfull')
return True
except Exception as e:
print('error {}'.format(e))
def gen_rand_usr( num_of_usr ):
# fetch the database to get the numbers of the users
con = dbconf.connect_db()
cur = con.cursor()
cur.execute( """SELECT COUNT( DISTINCT( user_id ) )
FROM test1 """)
usr_sum = cur.fetchone()[0]
rand_usr_list = []
for i in range( num_of_usr ):
usr = random.randint( 1, usr_sum )
rand_usr_list.append(str(usr))
return rand_usr_list
def store_item_matrix(itemsim):
con = dbconf.connect_db()
try:
cur = con.cursor()
cur.execute(
"""CREATE TABLE IF NOT EXISTS `item_matrix_cache1` (
`row_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`movie_name` varchar(170) NOT NULL,
`sim_movie` varchar(170) NOT NULL,
`similarity` float unsigned NOT NULL,
PRIMARY KEY (`row_id`),
KEY `movie_name` (`movie_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8"""
)
con.commit()
cur.close()
except db.Error:
pass
# fetch the data to list
for key, val in itemsim.items():
movie_name = db.escape_string(key)
for i in range(len(val)):
sim_movie = db.escape_string(val[i][1])
similarity = float(val[i][0])
try:
cur = con.cursor()
cur.execute(
"""REPLACE item_matrix_cache1( similarity, movie_name, sim_movie )
VALUES( %f, '%s', '%s' )"""
% (similarity, movie_name, sim_movie)
)
con.commit()
cur.close()
except db.Error:
print "update error"
try:
con.rollback()
# cur = con.cursor()
# cur.execute("""INSERT INTO item_matrix_cache( movie_name, sim_movie, similarity)
# VALUES( '%s', '%s', %f )""" % (movie_name, sim_movie, similarity))
# con.commit()
# cur.close()
except db.Error:
print db.Error
pass
con.close()
print "store item matrix finished."
def store_name_arr_for_cluster(movie_name_arr):
con = dbconf.connect_db()
try:
cur = con.cursor()
cur.execute(
"""CREATE TABLE IF NOT EXISTS `cluster_mov_id_arr_cache1` (
`clus_movie_id` int(10) unsigned NOT NULL,
`movie_name` varchar(170) NOT NULL,
PRIMARY KEY (`clus_movie_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 """
)
con.commit()
cur.close()
except db.Error:
pass
for i in range(len(movie_name_arr)):
movie_name = movie_name_arr[i]
movie_name = db.escape_string(movie_name)
clus_movie_id = i
try:
cur = con.cursor()
cur.execute(
"""REPLACE cluster_mov_id_arr_cache1( movie_name, clus_movie_id )
VALUES( '%s', %d )"""
% (movie_name, clus_movie_id)
)
con.commit()
cur.close()
except db.Error:
print "error"
print db.Error
try:
con.rollback()
# cur = con.cursor()
# cur.execute("""INSERT INTO cluster_mov_id_arr_cache( clus_movie_id, movie_name )
# VALUES( %d, '%s' )""" % (clus_movie_id, movie_name))
# con.commit()
# cur.close()
except db.Error:
print db.Error
con.close()
print "store cluster name array finished."
def build_scan(target, tokens):
if tokens[0] == 'target':
if tokens[1] == '->':
if len(tokens) > 3:
print "other parameter"
else:
module = tokens[2].split('.')
mod_name = module[0]
start_module(mod_name, target, target)
else:
print '[!] You might miss the \'->\' in config flow'
else:
module_input = tokens[0].split('.')
try:
module_table = connect_db(target, module_input[0])
if tokens[1] == '->':
if len(tokens) > 3:
print "other parameter"
else:
module_output = tokens[2].split('.')
try:
if module_input[2] == 'each':
each = True
else:
print '[!]Error: Unknown option ' + module_input[2]
each = False
except:
each = False
if each:
for each in module_table:
start_module(module_output[0],
each[module_input[1]], target)
else:
for each in module_table.find(id=1):
start_module(module_output[0],
each[module_input[1]], target)
else:
print '[!] You might miss the \'->\' in config flow'
except Exception as e:
print '[!] Unable to find the ' + modulep[
0] + ' module results in database'
print e
def get_test_prefs():
# get data from database
con = dbconf.connect_db()
test_prefs = {}
movies = {}
try:
cursor = con.cursor()
cursor.execute("""SELECT movies.movie_id, movies.movie_name, test1.user_id, test1.rating
FROM movies INNER JOIN test1
ON movies.movie_id = test1.movie_id""")
con.commit()
except db.Error:
print 'ERROR: start to roll back'
print db.Error
try:
con.rollback()
except:
pass
num_mov = int(cursor.rowcount)
for i in range(num_mov):
row = cursor.fetchone()
movie_id = str(row[0])
movie_name = str(row[1])
user_id = str(row[2])
rating = float(row[3])
movies.setdefault(movie_id, "")
movies[movie_id] = movie_name
test_prefs.setdefault(user_id, {})
test_prefs[user_id][movies[movie_id]] = rating
cursor.close()
con.close()
return test_prefs
def get_data_from_db():
# get data from database
con = dbconf.connect_db()
prefs = {}
pref = {}
movies = {}
try:
cursor = con.cursor()
cursor.execute("""SELECT movies.movie_id, movies.movie_name, base1.user_id, base1.rating
FROM movies INNER JOIN base1
ON movies.movie_id = base1.movie_id""")
con.commit()
except db.Error, e:
print 'ERROR: start to roll back'
print db.Error
try:
con.rollback()
except:
pass
def gen_payoff_list( name_list ):
winning_list = []
# from the name list generate the payoff list use database
con = dbconf.connect_db()
# find the number of all users
try:
cursor = con.cursor()
cursor.execute("""SELECT COUNT( DISTINCT user_id )
FROM base1""")
con.commit()
except db.Error:
print 'ERROR: start to roll back'
print db.Error
try:
con.rollback()
except:
pass
row = cursor.fetchone()
all_usr_num = row[0]
cursor.close()
# find the expected rating and something evaluation of each item
for j in range( len(name_list) ):
name = name_list[j]
name = db.escape_string(name)
try:
cursor = con.cursor()
cursor.execute("""SELECT base1.rating
FROM movies INNER JOIN base1
ON movies.movie_id = base1.movie_id
WHERE movies.movie_name = '%s' """ % (name) )
con.commit()
except db.Error:
print 'ERROR: start to roll back'
print db.Error
try:
con.rollback()
except:
pass
num_mov = int(cursor.rowcount)
winning_score = 0.0
score_list = {}
for i in range( num_mov ):
row = cursor.fetchone()
row = str(row[0])
score_list.setdefault(row, 0)
score_list[row] += 1
sum = 0.0
div = 0.0
for key,val in score_list.items():
key = float(key)
sum += key * val
div += val
# the expected rating
expected_rating = float(sum) / float(div)
# the popularity
popularity = (float(div) / all_usr_num)*5.0
# form the winning score
winning_score = expected_rating + popularity
winning_list.append(winning_score)
return winning_list
