def __init__(self, _id):
self.user_id = _id
self.db = DBConnect()
# print("user ID: "+str(self.user_id))
self.conn = pymysql.connect('localhost', 'root', 'SqlAccount!23', 'DMP', charset='utf8')
self.c = self.conn.cursor()
self.menu()
def __init__(self, modelP, entityidP, commandstrP):
self.model = modelP
self.entityid = entityidP
self.commandStr = commandstrP
self.db = DBConnect()
if "_actor_" in self.commandStr:
self.relation = "actor"
elif "_genre_" in self.commandStr:
self.relation = "genre"
elif "_user_" in self.commandStr:
self.relation = "user"
class Login:
def __init__(self):
self.db = DBConnect()
# self.conn = pymysql.connect('localhost', 'root', 'SqlAccount!23', 'DMP', charset='utf8')
# self.c = self.conn.cursor()
print('połączenie ustanowione!')
self.menu()
def menu(self):
print("Witamy Cię w Programie zarządzania rysunkami DMP:\n")
loop = True
while loop:
option = input(
"Wybierz opcję: Z-zaloguj, N-nowy użytkownik,Q-wyjście:"
).upper()
while option == "":
option = input(
"Wybierz opcję działania: Z-zaloguj, N-nowy użytkownik,Q-wyjście: "
).upper()
if option == "Q":
loop = False
elif option == "N":
login = input("Podaj login dla nowego użutkowsnika: ")
password = input("Podaj hasło dla nowego użytkownika: ")
query = "INSERT INTO USERS (login_users, pass_users) values ('" + login + "', '" + password + "');"
self.db.execute(query)
self.db.conn.commit()
# self.c.execute(
# "INSERT INTO USERS (login_users, pass_users) values ('" + login + "', '" + password + "');")
self.logIn(login, password)
elif option == "Z":
login = input("Podaj login: "******"Podaj hasło: ")
self.logIn(login, password)
elif option == "Q":
loop = False
else:
print("Brak takiej opcji!")
def logIn(self, _login, _password):
query = "SELECT * FROM USERS WHERE login_users = '" + _login + "' AND pass_users = '" + _password + "';"
# self.db.fetchAll(query)
# self.c.execute()
# logRes = self.c.fetchall()
logRes = self.db.fetchAll(query)
if len(logRes) == 1:
print("Zalogowano!")
# print(logRes)
logRes_id = logRes[0]
currentUser = User(logRes[0][0])
else:
print("Niepoprawne login lub/i hasło!")
def confirm_user(token):
with DBConnect(mysql_details) as cursor:
_SQL = """select * from users where token = '{0}'""".format(token)
cursor.execute(_SQL)
result = cursor.fetchone()
flash(_SQL)
flash(result)
if result is None:
return 'an error'
else:
with DBConnect(mysql_details) as cursor:
UPDATE_TOKEN_SQL = """update users set confirmedemail = 1 where username = '******'""".format(result[0])
cursor.execute(UPDATE_TOKEN_SQL)
return redirect(url_for('login_route'))
def register_route():
if request.method == 'GET':
return render_template("register.html")
if request.method == 'POST':
username = request.form["username"]
email = request.form["email"]
userType = request.form["userType"]
encryptedpassword = generate_password_hash(request.form["password"])
sqlcheckusername = """select * from users where username = '******'""".format(username)
with DBConnect(mysql_details) as cursor:
cursor.execute(sqlcheckusername)
result = cursor.fetchall()
if len(result)==0:
with DBConnect(mysql_details) as cursor:
_SQL = "insert into users (username, password, email, userType) values (%s, %s, %s, %s)"
cursor.execute(_SQL, (username, encryptedpassword, email, userType))
token = generate_confirmation_token(request.form["email"])
with DBConnect(mysql_details) as cursor:
_INSERT_TOKEN_SQL = """update users set token = '{0}' where username = '******'""".format(token,username)
cursor.execute(_INSERT_TOKEN_SQL)
confirm_url = url_for('confirm_user', token=token, _external=True)
msg_content = render_template('confirmemail.html', confirm_url=confirm_url)
msg_subject = "User registration for The Mangled Badger"
send_email(email, msg_subject, msg_content)
flash('A confirmation email has been sent via email.')
return redirect(url_for('default_route'))
else:
flash('The username you picked already exists. Please choose another.')
return redirect(url_for('register_route'))
def run(self):
# Connect to db, receive db connection Object
db_connect = DBConnect()
db_connect.connect()
db = db_connect.get_connection()
"""
#Start reading the file, receive results list with data
fileReader = FileReader(self.url)
fileReader.open_and_read_file()
result = fileReader.getResults()
"""
# Setting up a class object and connecting.
unix_reader = UNIXReader("src/client/upload_stream.sock")
unix_reader.connect()
# Data parser class object to parse receieved data
data_parser = DataParser()
# Query class object getting ready to query database
query_db = QueryDB(db)
query_db.find_trip_id()
print 'Running main loop'
while True:
# Receive data from unix reader object
data = unix_reader.revc_socket()
json_data = json.loads(data)
print(json_data)
# Parse the result data to appropriate format
if json_data:
sorted_results = data_parser.parseToDict(json_data)
if db_connect.check_connection():
# Send data to database
query_db.query(sorted_results)
else:
db_connect.connect()
# Send data to database
query_db.query(sorted_results)
# Close connection to database
db_connect.disconnect()
def car_history_is_valid(self, car_id):
"""
car history is valid if
1. chain is valid
2. entries in data base match corresponding chain entries
3. the hashes that link the car history are valid
"""
if self.chain_is_valid():
client = DBConnect()
car_history = client.get_car_history(car_id)
for stage in car_history:
if stage != self.chain[stage["_id"]]:
return False
if len(car_history) <= 1:
return True
index = 1
while index < len(car_history):
if self.get_hash(
car_history[index -
1]) != car_history[index]["hash"]["car"]:
return False
index += 1
return True
else:
return False
def login_route():
if request.method == 'GET':
return render_template("login.html")
if request.method == 'POST':
username = request.form["username"]
userType = request.form["userType"]
with DBConnect(mysql_details) as cursor:
_SQL = """select password, confirmedemail
from users
where username=%s AND userType=%s"""
cursor.execute(_SQL, (username, userType))
fetched_data = cursor.fetchall()
if not cursor.rowcount:
flash('Sorry, we do not recognise that username or password.')
return redirect(url_for('login_route'))
else:
if check_password_hash(fetched_data[0][0],request.form["password"]) is True:
if fetched_data[0][1] is 1:
session['emailverified'] = True
session['logged_in'] = True
if userType == 'admin':
session['admin'] = True
else:
session['admin'] = False
return redirect(url_for('admin_route'))
else:
flash('Please verify your username.')
return redirect(url_for('login_route'))
else:
flash('Sorry, we do not recognise that password.')
return redirect(url_for('login_route'))
def findByQuery(self, query):
conn = DBConnect()
data = conn.executionQueryTargets(query, self.organism, self.smile)
return data
def __init__(self, model):
self.model = model
self.db = DBConnect()
self.tfIdf = TFIDF("", "", "_actor_")
class MovieTensor:
model = None
db = None
tfIdf = None
def __init__(self, model):
self.model = model
self.db = DBConnect()
self.tfIdf = TFIDF("", "", "_actor_")
def getListAsString(self, moviesList):
moviesListStr = str(moviesList)
moviesListStr = moviesListStr.replace('[', '(')
moviesListStr = moviesListStr.replace(']', ')')
return moviesListStr
def getTensor(self):
if self.model == 1:
yearsCountQuery = "select count(distinct year) from mlmovies"
#movieActorsCountQuery = "select count(distinct movieid) from mlmovies where movieid in (6058,9818,5914,6097,7232,9443,7062,8929,4354,10059) "
res = self.db.executeQuery(yearsCountQuery)
countStr = res[0]
countString = str(countStr)
countString = self.tfIdf.getCount(countString)
noOfDistinctYear = int(countString)
# get the no of actors
movieActorsCountQuery = "select count(*) from imdb_actor_info "
#movieActorsCountQuery = "select count(distinct actorid) from imdb_actor_info where actorid in (17838,45899,61523,68671,96585,99457,128645,133985) "
res = self.db.executeQuery(movieActorsCountQuery)
countStr = res[0]
countString = str(countStr)
countString = self.tfIdf.getCount(countString)
noOfActors = int(countString)
# get the no of movies
movieActorsCountQuery = "select count(*) from mlmovies "
#movieActorsCountQuery = "select count(distinct movieid) from mlmovies where movieid in (6058,9818,5914,6097,7232,9443,7062,8929,4354,10059) "
res = self.db.executeQuery(movieActorsCountQuery)
countStr = res[0]
countString = str(countStr)
countString = self.tfIdf.getCount(countString)
noOfMovies = int(countString)
#noOfMovies = 2
# actorMovieYearTensor = np.ndarray( shape=(noOfActors,noOfMovies,noOfDistinctYear))
# for i in range(0,noOfActors):
# for j in range(0,noOfMovies):
# for k in range(0,noOfDistinctYear):
# actorMovieYearTensor[i,j,k] = 0.0
# #print actorMovieYearTensor[i,j,k]
#build movie indices
movieIdVsIndex = {}
movieIndexVsName = {}
query = "select * from mlmovies order by movieid"
#query = "select * from mlmovies where movieid in (6058,9818,5914,6097,7232,9443,7062,8929,4354,10059) order by movieid"
movieIndex = 0
res = self.db.executeQuery(query)
for movie in res:
movieId = movie[0]
movieName = movie[1]
movieIdVsIndex[movieId] = movieIndex
movieIndexVsName[movieIndex] = movieName
movieIndex = movieIndex + 1
#build year indices
yearVsIndex = {}
yearIndexVsYear = {}
q = "select distinct year from mlmovies order by year"
res = self.db.executeQuery(q)
yearIndex = 0
for yearRow in res:
year = yearRow[0]
yearVsIndex[str(year)] = yearIndex
yearIndexVsYear[yearIndex] = year
yearIndex = yearIndex + 1
actorMovieYearMatrix = np.zeros(
(noOfActors, noOfMovies, noOfDistinctYear))
query = "select * from imdb_actor_info order by actorid "
actors = self.db.executeQuery(query)
actorIndex = 0
actorIdVsIndex = {}
actorIndexVsName = {}
for actor in actors:
actorid = actor[0]
actorName = actor[1]
actorrelatedMoviesQ = "select * from movie_actor where actorid = " + str(
actorid)
actorrelatedMovies = self.db.executeQuery(actorrelatedMoviesQ)
movieIds = []
for movie in actorrelatedMovies:
movieIds.append(movie[0])
# we got the movies
moviesQuery = "select * from mlmovies where movieid in " + self.getListAsString(
movieIds)
res = self.db.executeQuery(moviesQuery)
for movieYear in res:
movieid = movieYear[0]
year = movieYear[2]
#actorMovieYearTensor[actorIndex,movieIdVsIndex[movieid],yearVsIndex[str(year)]] = 1.0
actorMovieYearMatrix[actorIndex][movieIdVsIndex[movieid]][
yearVsIndex[str(year)]] = 1
actorIdVsIndex[actorid] = actorIndex
actorIndexVsName[actorIndex] = actorName
actorIndex = actorIndex + 1
actorMovieYearMatrix[0][0][0] = 1
actorMovieYearMatrix[1][1][1] = 1
actorMovieYearTensor = tl.tensor(actorMovieYearMatrix)
decomposed = dec.parafac(actorMovieYearTensor, rank=5)
semanticsActor = decomposed[0]
semanticsMovie = decomposed[1]
semanticsYear = decomposed[2]
for i in range(0, semanticsActor.shape[1]):
actorsRow = semanticsActor[:, i]
mean = np.mean(actorsRow)
print("ACTORS GROUPED UNDER LATENT SEMANTICS {0} ".format(i +
1))
for j in range(0, noOfActors):
if (actorsRow[j] >= mean):
print(actorIndexVsName[j])
for i in range(0, semanticsMovie.shape[1]):
moviesRow = semanticsMovie[:, i]
mean = np.mean(moviesRow)
print("MOVIES GROUPED UNDER LATENT SEMANTICS {0}".format(i +
1))
for j in range(0, noOfMovies):
if (moviesRow[j] >= mean):
print(movieIndexVsName[j])
for i in range(0, semanticsYear.shape[1]):
yearsRow = semanticsYear[:, i]
mean = np.mean(yearsRow)
print("YEARS GROUPED UNDER LATENT SEMANTICS {0}".format(i + 1))
for j in range(0, noOfDistinctYear):
if (yearsRow[j] >= mean):
print(yearIndexVsYear[j])
elif self.model == 2:
noOfTags = 0
query = "select count(*) from genome_tags"
count = self.db.executeQuery(query)
countStr = self.tfIdf.getCount(str(count[0]))
noOfTags = int(countStr)
# get the no of movies
movieActorsCountQuery = "select count(*) from mlmovies "
res = self.db.executeQuery(movieActorsCountQuery)
countStr = res[0]
countString = str(countStr)
countString = self.tfIdf.getCount(countString)
noOfMovies = int(countString)
q = "select count(distinct rating) from mlratings"
res = self.db.executeQuery(q)
countStr = res[0]
countString = str(countStr)
countString = self.tfIdf.getCount(countString)
noOfRatings = int(countString)
tagMovieRatingMatrix = np.zeros(
(noOfTags, noOfMovies, noOfRatings))
#print tagMovieRatingTensor
# build tag index
query = "select * from genome_tags order by tagid"
tags = self.db.executeQuery(query)
tagIndex = 0
tagIdVsIndex = {}
tagIndexVsName = {}
for tag in tags:
tagid = tag[0]
tagName = tag[1]
tagIdVsIndex[tagid] = tagIndex
tagIndexVsName[tagIndex] = tagName
tagIndex = tagIndex + 1
query = "select * from mlmovies order by movieid"
movieIndex = 0
movieIdVsIndex = {}
movieIndexVsName = {}
movies = self.db.executeQuery(query)
for movie in movies:
movieid = movie[0]
movieName = movie[1]
movieIdVsIndex[movieid] = movieIndex
movieIndexVsName[movieIndex] = movieName
movieTagsQ = "select * from mltags where movieid = " + str(
movieid)
movieTags = self.db.executeQuery(movieTagsQ)
movieTagsList = []
for movieTag in movieTags:
movieTagsList.append(movieTag[2])
totalNoOfRatingsQ = "select count(*) from mlratings where movieid = " + str(
movieid)
res = self.db.executeQuery(totalNoOfRatingsQ)
totalRatingsStr = self.tfIdf.getCount(str(res[0]))
totalRatings = int(totalRatingsStr)
sumQ = "select movieid, sum(rating) from mlratings where movieid = " + str(
movieid) + " group by movieid"
res = self.db.executeQuery(sumQ)
sumRating = 0
for r in res:
sumRating = sumRating + r[1]
avgRating = float(sumRating) / totalRatings
for tag in movieTagsList:
tagIndex = tagIdVsIndex[tag]
for i in range(1, noOfRatings + 1):
if avgRating <= float(i):
tagMovieRatingMatrix[tagIndex][movieIndex][i -
1] = 1
#print "setting one"
movieIndex = movieIndex + 1
tagMovieRatingMatrix[0][0][0] = 1
tagMovieRatingMatrix[1][1][1] = 1
tagMovieRatingTensor = tl.tensor(tagMovieRatingMatrix)
decomposed = dec.parafac(tagMovieRatingTensor, rank=5)
semanticsTag = decomposed[0]
semanticsMovie = decomposed[1]
semanticsRating = decomposed[2]
for i in range(0, semanticsTag.shape[1]):
tagRows = semanticsTag[:, i]
mean = np.mean(tagRows)
print(" TAGS GROUPED UNDER LATENT SEMANTICS {0} ".format(i +
1))
for j in range(0, noOfTags):
if (tagRows[j] >= mean):
print(tagIndexVsName[j])
for i in range(0, semanticsMovie.shape[1]):
movieRows = semanticsMovie[:, i]
mean = np.mean(movieRows)
print("MOVIES GROUPED UNDER LATENT SEMANTICS {0}".format(i +
1))
for j in range(0, noOfMovies):
if (movieRows[j] >= mean):
print(movieIndexVsName[j])
for i in range(0, semanticsRating.shape[1]):
ratingRows = semanticsRating[:, i]
mean = np.mean(ratingRows)
print("RATINGS GROUPED UNDER LATENT SEMANTICS {0}".format(i +
1))
for j in range(0, noOfRatings):
if (ratingRows[j] >= mean):
print(j + 1)
import sys import configparser import pymysql import os up_one_level = os.path.dirname(__file__) print(up_one_level) base_dir_path = os.path.dirname(up_one_level) sys.path.append(base_dir_path) from DBConnect import DBConnect keys = sys.argv[1] print(keys) conf = configparser.ConfigParser() conf_path = base_dir_path + r'/test/config/db_config.config' conf.read(conf_path, encoding='utf-8') host_value = conf.get(keys, 'host') port_value = conf.get(keys, 'port') user_value = conf.get(keys, 'user') password_value = conf.get(keys, 'password') db_value = conf.get(keys, 'db') conn = DBConnect() #类需要初始化 conn.get_connect(host_value, port_value, user_value, password_value, db_value)
def __init__(self, _project_id, _user_id):
self.project_id = _project_id
self.user_id = _user_id
self.db = DBConnect()
self.menu()
class Project:
def __init__(self, _project_id, _user_id):
self.project_id = _project_id
self.user_id = _user_id
self.db = DBConnect()
self.menu()
def menu(self):
loop = True
while loop:
self.availableOptions()
option = input("Wybierz opcję").upper()
while option == "":
option = input("Wybierz opcję").upper()
if option == "D":
self.addDrawing()
# self.deleteDrawing()
elif option == "P":
self.showDrawings()
elif option == "Z":
self.showPaidDrawings()
elif option == "N":
self.showUnpaidDrawings()
elif option == "M":
self.modifyDrawingData()
elif option == "F":
self.findDrawing()
# elif option == "W":
# self.selectDrawingsByDate()
elif option == "Q":
loop = False
else:
print("Nieznana opcja!")
def availableOptions(self):
print("Dostępne opcje:")
print("D - dodaj nowy rysunek")
# print("U - usuń rysunek")
print("P - pokaż wszystkie rysunki")
print("Z - pokaż listę rozliczonych rysunków")
print("N - pokaż listę nierozliczonych rysunków")
print("M - modyfikuj dane o rysunku")
print("F - szukaj rysunku")
# print("W - wybierz rysunki po dacie")
print("Q-wyjście")
def showDrawings(self):
print("Lista rysunków.")
drawingsQuery = "select d.id_drawings, drawing_name, drawing_num, width, height, individual_rate,project_name" \
" from drawings as d left join projects as p on (d.id_projects=p.id_projects) " \
"join projects_has_users as phu on (phu.projects_id_projects = p.id_projects) " \
"where Users_id_Users = " + str(self.user_id) + ";"
result = self.db.fetchAll(drawingsQuery)
print('|%3s|%40s|%20s|%20s|%20s|%20s|%20s' %
("ID", "Nazwa rysunku", "Numer rysunku", "Szerokość rys.",
"Wysokość rys.", "stawka za A0 [PLN]", "Nazwa projektu"))
for row in result:
print('|%3i|%40s|%20s|%20s|%20s|%20s|%20s' %
(row[0], row[1], row[2], row[3], row[4], row[5], row[6]))
def addDrawing(self):
drawing_name = self.nonOptionalInput("Podaj nazwę rysunku: ")
drawing_num = self.nonOptionalInput("Podaj numer rysunku: ")
width = self.nonOptionalInput("Podaj szerokość rysunku: ")
height = self.nonOptionalInput("Podaj wysokość rysunku: ")
individual_rate = self.nonOptionalInput("Podaj indywidualną stawkę: ")
paid = self.nonOptionalInput(
"Czy rysunek jest rozliczony? [Tak=>1|Nie=>0]: ")
insertQuery = "INSERT INTO drawings (drawing_name, drawing_num, width, height, individual_rate, id_projects," \
" paid, id_Users) values ('" + drawing_name + "', '" + drawing_num + "', " + width + "" \
", " + height + ", "+ individual_rate+", "+str(self.project_id)+", "+paid+", "+str(self.user_id)+")"
self.db.execute(insertQuery)
self.db.conn.commit()
# id_drawings, drawing_name, drawing_num, width, height, individual_rate, id_projects, paid, id_Users
# print("Dodawanie rysunku")
# def deleteDrawing(self):
# print("Usuwanie rysunku")
def showPaidDrawings(self):
print("Lista rozliczonych rysunków")
drawingsQuery = "select d.id_drawings, drawing_name, drawing_num, width, height, individual_rate,project_name" \
" from drawings as d left join projects as p on (d.id_projects=p.id_projects) " \
"join projects_has_users as phu on (phu.projects_id_projects = p.id_projects) " \
"where (Users_id_Users = " + str(self.user_id) + " AND " \
"paid =1 AND p.id_projects = " + str(self.project_id) + ");"
result = self.db.fetchAll(drawingsQuery)
print('|%3s|%40s|%20s|%20s|%20s|%20s|%20s' %
("ID", "Nazwa rysunku", "Numer rysunku", "Szerokość rys.",
"Wysokość rys.", "stawka za A0 [PLN]", "Nazwa projektu"))
for row in result:
print('|%3i|%40s|%20s|%20s|%20s|%20s|%20s' %
(row[0], row[1], row[2], row[3], row[4], row[5], row[6]))
def showUnpaidDrawings(self):
print("Lista nierozliczonych rysunków")
drawingsQuery = "select d.id_drawings, drawing_name, drawing_num, width, height, individual_rate,project_name" \
" from drawings as d left join projects as p on (d.id_projects=p.id_projects) " \
"join projects_has_users as phu on (phu.projects_id_projects = p.id_projects) " \
"where (Users_id_Users = " + str(self.user_id) + " AND " \
"paid =0 AND p.id_projects = " + str(self.project_id) + ");"
result = self.db.fetchAll(drawingsQuery)
print('|%3s|%40s|%20s|%20s|%20s|%20s|%20s' %
("ID", "Nazwa rysunku", "Numer rysunku", "Szerokość rys.",
"Wysokość rys.", "stawka za A0 [PLN]", "Nazwa projektu"))
for row in result:
print('|%3i|%40s|%20s|%20s|%20s|%20s|%20s' %
(row[0], row[1], row[2], row[3], row[4], row[5], row[6]))
def modifyDrawingData(self):
print("Modyfikacja rysunku")
self.showDrawings()
drawingId = self.nonOptionalInput(
"Wybierz ID rysunku do modyfikacji: ")
query = "Select id_drawings, drawing_name, drawing_num, width, height, individual_rate, id_projects," \
" paid, id_Users from projects where id_drawings ="+drawingId+" "
result = self.db.fetchAll(query)
drawing_name = result[1]
drawing_num = result[2]
width = result[3]
height = result[4]
rate = result[5]
paid = result[7]
drawing_name = self.optionalInput(
"Podaj nową nazwę rysunku lub naciśnij ENTER, aby nie zmieniać: ",
drawing_name)
drawing_num = self.optionalInput(
"Podaj nowy numer rysunku lub naciśnij ENTER, aby nie zmieniać: ",
drawing_num)
width = self.optionalInput(
"Podaj nową szerokość lub naciśnij ENTER, aby nie zmieniać: ",
height)
height = self.optionalInput(
"Podaj nową wysokość lub naciśnij ENTER, aby nie zmieniać: ",
width)
rate = self.optionalInput(
"Podaj nową stawkę lub naciśnij ENTER, aby nie zmieniać: ", rate)
paid = self.optionalInput(
"Podaj status [rozliczony=>1|nierozliczony=>0] lub naciśnij ENTER, aby nie zmieniać: ",
paid)
query = "UPDATE drawings SET drawing_name ='"+drawing_name+"', drawing_num = '"+drawing_num+"', width ="+\
width+", height= "+height+", individual_rate="+rate+", paid="+paid+" where id_drawings="+drawingId+";"
self.db.execute(query)
print("Zmodyfikowano!")
# id_drawings, drawing_name, drawing_num, width, height, individual_rate, id_projects, paid, id_Users
def findDrawing(self):
print("Wyszukiwanie rysunku")
phrase = self.nonOptionalInput("Podaj frazę do wyszukania: ")
findQuery = "Select * from drawings where drawing_name" \
" like '%" + phrase + "%' or drawing_num like '%" + phrase + "%' "
result = self.db.fetchAll(findQuery)
print('|%3s|%40s|%20s|%20s|%20s|%20s|%20s' %
("ID", "Nazwa rysunku", "Numer rysunku", "Szerokość rys.",
"Wysokość rys.", "stawka za A0 [PLN]", "Nazwa projektu"))
for row in result:
print('|%3i|%40s|%20s|%20s|%20s|%20s|%20s' %
(row[0], row[1], row[2], row[3], row[4], row[5], row[6]))
# def selectDrawingsByDate(self):
# print("wybierz rysunki po dacie")
@staticmethod
def nonOptionalInput(napis):
result = input(napis)
while result == "":
result = input(napis)
return result
@staticmethod
def optionalInput(napis, data):
userInput = input(napis)
if userInput != "":
result = userInput
else:
result = data
return result
def __init__(self, logged):
"""Inicializa la conexion con la base de datos
"""
self.logged = logged
self.db = DBConnect()
def findByQuery(self):
conn = DBConnect()
self.listAllSmiles = conn.executionQuerySimilarity(self.query)
return self.listAllSmiles
class User:
def __init__(self, _id):
self.user_id = _id
self.db = DBConnect()
# print("user ID: "+str(self.user_id))
self.conn = pymysql.connect('localhost', 'root', 'SqlAccount!23', 'DMP', charset='utf8')
self.c = self.conn.cursor()
self.menu()
def availableOptions(self):
print("Dostępne opcje:")
print("D-dodaj nowego klienta")
print("K-pokaż klientów")
print("P-pokaż projekty")
print("A-dodaj projekt")
# print("U-usuń projekt")
print("M-zarządzaj wybranym projektem")
print("R - Pokaż wszystkie rysunki")
print("Z - Pokaż listę rozliczonych rysunków")
print("N - Pokaż listę nierozliczonych rysunków")
print("Q-wyjście")
def menu(self):
loop = True
while loop:
self.availableOptions()
option = input("Wybierz opcję: ").upper()
while option == "":
option = input("Wybierz opcję: ").upper()
if option == "D":
self.addClient()
elif option == "K":
self.showClients()
elif option == "P":
self.showProjects()
elif option == "A":
self.addProject()
# elif option == "U":
# self.deleteProject()
elif option == "R":
self.showDrawings()
elif option == "M":
self.manageProject()
elif option == "Z":
self.showPaidDrawings()
elif option == "N":
self.showUnpaidDrawings()
elif option == "Q":
loop = False
else:
print("Nieznana opcja!")
def addClient(self):
print("Dodawanie klienta: ")
clientName = input("Podaj nazwę klienta [pole obowiązkowe]: ")
while clientName == "":
clientName = input("Podaj nazwę klienta [pole obowiązkowe]: ")
clientFirstName = input("Podaj imię klienta [pole opcjonalne]: ")
clientLastName = input("Podaj nazwisko klienta [pole opcjonalne]: ")
clientPhoneNumber = input("Podaj numer telefonu klienta [pole opcjonalne]: ")
clientEmail = input("Podaj adres email klienta [pole obowiązkowe]: ")
while clientEmail == "":
clientEmail = input("Podaj adres email klienta [pole obowiązkowe]: ")
query = "INSERT INTO CUSTOMERS (customers_name, first_name, last_name, phone_number, email, id_Users)" \
" values ('" + clientName + "', '" + clientFirstName + "', '" + clientLastName + "', " + \
clientPhoneNumber.replace(" ", "") + ", '" + clientEmail + "'," + str(self.user_id) + ")"
self.c.execute(query)
self.conn.commit()
def showProjects(self):
print("Lista projektów\n")
query = "select id_projects, project_name, customers_name, project_date, project_deadline,rate_per_drawing " \
"from projects" \
" as p join customers as c on (c.id_customers= p.id_customers) " \
"where id_users = " + str(self.user_id) + " order by project_name"
self.c.execute(query)
result = self.c.fetchall()
print('|%3s|%20s|%20s|%20s|%20s|%20s' % (
"ID", "Nazwa projektu", "Nazwa klienta", "start projektu", "termin projektu", "stawka za A0 [PLN]"))
for row in result:
print('|%3i|%20s|%20s|%20s|%20s|%20s' % (row[0], row[1], row[2], row[3], row[4], row[5]))
def showClients(self):
# pokazanie listy projektów
print("Lista klientów\n")
query = "select * from customers where id_users = " + str(self.user_id) + " order by customers_name"
self.c.execute(query)
result = self.c.fetchall()
print('|%3s|%20s|%20s|%20s|%12s|%20s' % (
"ID", "Nazwa klienta", "Imię klienta", "Nazwisko klienta", "nr telefonu", "adres e-mail"))
for row in result:
print('|%3i|%20s|%20s|%20s|%12s|%20s' % (row[0], row[1], row[2], row[3], row[4], row[5]))
def addProject(self):
# pokazanie listy projektów
self.showClients()
client = input("Wybierz ID klienta do dodania projektu: ")
while client == "":
print("Musisz wybrać ID klienta")
client = input("Wybierz ID klienta do dodania projektu: ")
projectName = input("Podaj nazwę projektu: ")
while projectName == "":
print("Musisz podać nazwę projektu!")
projectName = input("Podaj nazwę projektu: ")
projectDate = input("Podaj datę przyjęcia projektu [YYYY-MM-DD]: ")
projectDeadline = input("Podaj termin wykonania projektu [YYYY-MM-DD]: ")
projectRate = input("Podaj stawkę za A0: ")
self.c.execute("SET FOREIGN_KEY_CHECKS=0")
insertQueryToProjects = "INSERT INTO projects (project_name, rate_per_drawing, project_date, " \
"project_deadline, id_customers) values ('" + projectName + "', " + projectRate +\
", '"+projectDate+"', '"+projectDeadline+"', " + client+")"
self.c.execute(insertQueryToProjects)
self.conn.commit()
query = "INSERT INTO projects_has_users (Users_id_Users, customers_id_customers) " \
"values (" + str(self.user_id) + ", " + client + ")"
self.c.execute(query)
self.conn.commit()
print("Dodano projekt!")
print(" ")
# def deleteProject(self):
# print("1")
def manageProject(self):
self.showProjects()
selectedProject = input("Podaj ID projektu do zarządzania: ")
projectManage = Project(selectedProject, self.user_id)
projectManage.menu()
def showDrawings(self):
drawingsQuery = "select d.id_drawings, drawing_name, drawing_num, width, height, individual_rate,project_name" \
" from drawings as d left join projects as p on (d.id_projects=p.id_projects) " \
"join projects_has_users as phu on (phu.projects_id_projects = p.id_projects) " \
"where Users_id_Users = "+ str(self.user_id) + ";"
result = self.db.fetchAll(drawingsQuery)
print('|%3s|%40s|%20s|%20s|%20s|%20s|%20s' %
("ID", "Nazwa rysunku", "Numer rysunku", "Szerokość rys.", "Wysokość rys.", "stawka za A0 [PLN]", "Nazwa projektu"))
for row in result:
print('|%3i|%40s|%20s|%20s|%20s|%20s|%20s' % (row[0], row[1], row[2], row[3], row[4], row[5], row[6]))
def showUnpaidDrawings(self):
drawingsQuery = "select d.id_drawings, drawing_name, drawing_num, width, height, individual_rate,project_name" \
" from drawings as d left join projects as p on (d.id_projects=p.id_projects) " \
"join projects_has_users as phu on (phu.projects_id_projects = p.id_projects) " \
"where (Users_id_Users = " + str(self.user_id) + " AND paid =0);"
result = self.db.fetchAll(drawingsQuery)
print('|%3s|%40s|%20s|%20s|%20s|%20s|%20s' %
("ID", "Nazwa rysunku", "Numer rysunku", "Szerokość rys.", "Wysokość rys.", "stawka za A0 [PLN]",
"Nazwa projektu"))
for row in result:
print('|%3i|%40s|%20s|%20s|%20s|%20s|%20s' % (row[0], row[1], row[2], row[3], row[4], row[5], row[6]))
def showPaidDrawings(self):
drawingsQuery = "select d.id_drawings, drawing_name, drawing_num, width, height, individual_rate,project_name" \
" from drawings as d left join projects as p on (d.id_projects=p.id_projects) " \
"join projects_has_users as phu on (phu.projects_id_projects = p.id_projects) " \
"where (Users_id_Users = " + str(self.user_id) + " AND paid =1);"
result = self.db.fetchAll(drawingsQuery)
print('|%3s|%40s|%20s|%20s|%20s|%20s|%20s' %
("ID", "Nazwa rysunku", "Numer rysunku", "Szerokość rys.", "Wysokość rys.", "stawka za A0 [PLN]",
"Nazwa projektu"))
for row in result:
print('|%3i|%40s|%20s|%20s|%20s|%20s|%20s' % (row[0], row[1], row[2], row[3], row[4], row[5], row[6]))
def __init__(self):
self.db = DBConnect()
# self.conn = pymysql.connect('localhost', 'root', 'SqlAccount!23', 'DMP', charset='utf8')
# self.c = self.conn.cursor()
print('połączenie ustanowione!')
self.menu()
class TFIDF:
model = None
entityid = None
commandStr = None
relation = None
db = None
tableName = None
epoch = datetime.utcfromtimestamp(0)
def __init__(self, modelP, entityidP, commandstrP):
self.model = modelP
self.entityid = entityidP
self.commandStr = commandstrP
self.db = DBConnect()
if "_actor_" in self.commandStr:
self.relation = "actor"
elif "_genre_" in self.commandStr:
self.relation = "genre"
elif "_user_" in self.commandStr:
self.relation = "user"
def getEntityMovieTableName(self):
if self.relation == "actor":
return "movie_actor"
elif self.relation == "genre":
return "mlmovies"
elif self.relation == "user":
return "mlratings"
def getWeight(self, rank, totalCount):
per = rank / totalCount
inv = 1 - per
return inv
def getMillis(self, timestampDiff):
if timestampDiff == None:
return None
#print "getMillis : "+str(timestampDiff)
#timestampDiffStr = str(timestampDiff)
return (timestampDiff - self.epoch).total_seconds() * 1000.0
# d = datetime.strptime(timestampDiffStr, "%Y-%m-%d %H:%M:%S,%f").strftime('%s')
# d_in_ms = int(d)*1000
#return d_in_ms
def getCount(self, countString):
countString = countString.replace('(', '')
countString = countString.replace('[', '')
countString = countString.replace(']', '')
countString = countString.replace(')', '')
countString = countString.replace('L', '')
countString = countString.replace(',', '')
countString = countString.replace('\'', '')
countString = countString.replace('\'', '')
return countString
def calcTFIDFApproach2(self, movies):
isActor = None
#print "self relation = "+self.relation
if self.relation == "actor":
isActor = True
else:
isActor = False
#records will be list of tuples
#print "type of records object = "+str(type(records))
movieVsWeight = {}
moviesList = []
moviesListStr = None
moviesCount = 0
globalTagIdVsTF = {}
globalTagIdVsIDF = {}
globalTagNameVsTFIDF = {}
tagIdVsMovieList = {}
for tup in movies:
movieid = tup[0]
rank = 0
if isActor:
rank = tup[2]
moviesList.append(movieid)
#print "movie id = "+str(movieid)
#print "rank = "+str(rank)
weight = 0
if isActor:
#Weight for actor rank for this movie
movieActorsCountQuery = "select count(*) from " + self.tableName + " where movieid = " + str(
movieid)
res = self.db.executeQuery(movieActorsCountQuery)
countStr = res[0]
countString = str(countStr)
countString = self.getCount(countString)
weight = self.getWeight(rank,
int(countString) + 1) #movie weight
#print "weight = "+str(weight)
movieVsWeight[movieid] = weight
moviesCount = moviesCount + 1
# Weight for tags for this movie
tagsRelatedToThisMovie = "select * from mltags where movieid =" + str(
movieid) + " order by mtimestamp desc"
tagIdVsTimeStamp = {}
tags = self.db.executeQuery(tagsRelatedToThisMovie)
if tags == None or len(tags) == 0:
continue
i = 0
latest = None
oldest = None
for tag in tags:
movieid = tag[1]
tagid = tag[2]
timestamp = tag[3]
if i == 0:
latest = timestamp
oldest = timestamp
tagIdVsTimeStamp[tagid] = timestamp
i = i + 1
if tagid in tagIdVsMovieList:
tagMoviesList = tagIdVsMovieList[tagid]
tagMoviesList.append(movieid)
tagIdVsMovieList[tagid] = tagMoviesList
else:
tagIdVsMovieList[tagid] = [movieid]
latestMillis = self.getMillis(latest)
oldestMillis = self.getMillis(oldest)
timeStampDiff = latestMillis - oldestMillis
tagIdVsWeight = {}
totalTagWeights = 0.00
for tagId, timeStamp in tagIdVsTimeStamp.items():
tagWeight = 0.00
if len(tags) == 1:
tagWeight = 0.9
else:
tagWeight = (self.getMillis(timeStamp) -
oldestMillis) / timeStampDiff
#print "tag= "+str(tagId)+" tagWeight = "+str(tagWeight)
combinedWeight = weight + tagWeight # actor weight + tag weight
tagIdVsWeight[tagId] = combinedWeight
#print "tag= "+str(tagId)+" combinedWeight = "+str(combinedWeight)
totalTagWeights = totalTagWeights + combinedWeight
# TF calculation
for tagId, tagWeight in tagIdVsWeight.items():
tf = 0
if totalTagWeights != 0.0:
tf = tagIdVsWeight[
tagId] / totalTagWeights # weight / totalWeight for this movie tags
#print "tagId = "+str(tagId)+" tf = "+str(tf)
if tagId in globalTagIdVsTF:
currentWeight = globalTagIdVsTF[tagId]
currentWeight = currentWeight + tf
globalTagIdVsTF[tagId] = currentWeight
else:
globalTagIdVsTF[tagId] = tf
# end for
#print "total Movies = "+str(moviesCount)
# IDF calculation
tagsList = []
for tagId, movieSet in tagIdVsMovieList.items():
noOfMoviesAssociated = len(movieSet)
#print "tagid = "+str(tagId)
#print "no of movies associated = "+str(noOfMoviesAssociated)
idf = moviesCount / noOfMoviesAssociated
globalTagIdVsIDF[tagId] = idf
tagsList.append(tagId)
tagsListStr = str(tagsList)
tagsListStr = tagsListStr.replace('[', '(')
tagsListStr = tagsListStr.replace(']', ')')
tagsQuery = "select * from genome_tags where tagid in " + tagsListStr
tags = self.db.executeQuery(tagsQuery)
tagIdVsName = {}
for tag in tags:
tagIdVsName[tag[0]] = tag[1]
for tagId, Name in tagIdVsName.items():
globalTagNameVsTFIDF[
Name] = globalTagIdVsTF[tagId] * globalTagIdVsIDF[tagId]
print "TF IDF SORTED"
sortedTagVsIDF = sorted(globalTagNameVsTFIDF.items(),
key=operator.itemgetter(1),
reverse=True)
print "" + str(sortedTagVsIDF)
# for tagId,v in globalTagIdVsTF.items():
# print " "+tagIdVsName[tagId]+" = "+str(v)
def calcTFIDFApproach1(self, movies):
isActor = False
if self.relation == "actor":
isActor = True
movieVsWeight = {}
moviesList = []
for movie in movies:
movieid = movie[0]
rank = movie[2]
weight = 0
if isActor:
#Weight for actor rank for this movie
movieActorsCountQuery = "select count(*) from " + self.tableName + " where movieid = " + str(
movieid)
res = self.db.executeQuery(movieActorsCountQuery)
countStr = res[0]
countString = str(countStr)
countString = self.getCount(countString)
weight = self.getWeight(rank,
int(countString) + 1) #movie weight
#print "weight = "+str(weight)
movieVsWeight[movieid] = weight
moviesList.append(movieid)
moviesListStr = str(moviesList)
moviesListStr = moviesListStr.replace('[', '(')
moviesListStr = moviesListStr.replace(']', ')')
#print "movieslist = "+moviesListStr
#Get the tags related to the actor/genre/user
oldestTagQuery = "select * from mltags where movieid in " + moviesListStr + " order by mtimestamp limit 1"
#print "oldestTagQuery = "+oldestTagQuery
oldestTagQueryRes = self.db.executeQuery(oldestTagQuery)
oldestTimeStamp = None
newestTimeStamp = None
timeRange = None
for oldTag in oldestTagQueryRes:
oldestTimeStamp = oldTag[3]
oldesMillis = self.getMillis(oldestTimeStamp)
tagsQuery = "select * from mltags where movieid in " + moviesListStr + " order by mtimestamp desc"
tags = self.db.executeQuery(tagsQuery)
actorTagsCount = len(tags)
tagVsTotalWeight = {}
tagVsTF = {}
tagIdVsTF = {}
taglist = []
movieVsTags = {}
n = 1
for tag in tags:
movieid = tag[1]
tagid = tag[2]
timestamp = tag[3]
if n == 1:
newestTimeStamp = timestamp
timeRange = self.getMillis(newestTimeStamp) - oldesMillis
# if movieid in movieVsTags:
# l = movieVsTags[movieid]
# if tagid in l and l.count(tagid) > 3: # same movieid and tag id might be irrelevant after a certain count
# print" same movieid and tag id skipping..."
# continue
# else:
# l.append(tagid)
# movieVsTags[movieid]=l
# else:
# l=[tagid]
# movieVsTags[movieid]=l
taglist.append(tagid)
# print "tagid = "+str(tagid)
# print "movie id = "+str(movieid)
# print "timestamp = "+str(tag[3])
tagWeight = self.getWeight(n, actorTagsCount + 1)
tagWeight = (self.getMillis(timestamp) - oldesMillis) / timeRange
rankWeight = movieVsWeight[movieid]
#print "rankweight = "+str(rankWeight)
n = n + 1
#print "tagWeight = "+str(tagWeight)
#combinedWeight = (tagWeight + (3 *rankWeight))/4
combinedWeight = (tagWeight + rankWeight)
#print "combinedWeight = "+str(combinedWeight)
if tagid in tagVsTotalWeight:
tempWeight = tagVsTotalWeight[tagid]
tempWeight += combinedWeight
tagVsTotalWeight[tagid] = tempWeight
else:
tagVsTotalWeight[tagid] = combinedWeight
# dict ("tagid" ,"weight1,weight 2")
#print "tagVsTotalWeight = "+str(tagVsTotalWeight)
tagsListStr = str(taglist)
tagsListStr = tagsListStr.replace('[', '(')
tagsListStr = tagsListStr.replace(']', ')')
#Get the tags related to the actor for Tag Id Vs Name dictionary
tagsQuery = "select * from genome_tags where tagid in " + tagsListStr
tags = self.db.executeQuery(tagsQuery)
tagIdVsName = {}
for tag in tags:
tagIdVsName[tag[0]] = tag[1]
totalWeight = 0
for key, val in tagVsTotalWeight.items():
# print "key - "+str(key)
# print "weight = "+str(val)
totalWeight += val
# Calcualting TF for each tag
for key, val in tagVsTotalWeight.items():
tf = tagVsTotalWeight[key] / totalWeight
tagVsTF[tagIdVsName[key]] = tf
tagIdVsTF[key] = tf
sortedTagVsTF = sorted(tagVsTF.items(),
key=operator.itemgetter(1),
reverse=True)
if self.model == "TF":
print "TAG Vs TF " + str(sortedTagVsTF)
if self.model == "TF":
print "model = TF"
return
# Calculating IDF
totalDocsCount = 27279
if isActor:
totalDocsCount = 27279
elif self.relation == "genre":
totalDocsCount = 19
elif self.relation == "user":
totalDocsCount = 71567
#print "Total documenst = "+str(totalDocsCount)
tagIdVsIDF = {}
tagIdVsTFIDF = {}
#Total no of documents
for key, val in tagIdVsTF.items():
tagid = key
#print "tagid = "+str(tagid)
moviesRelatedToThisTag = "select movieid from mltags where tagid = " + str(
tagid)
movies = self.db.executeQuery(moviesRelatedToThisTag)
moviesList = []
for mov in movies:
moviesList.append(mov[0])
movListStr = str(moviesList)
movListStr = movListStr.replace('[', '(')
movListStr = movListStr.replace(']', ')')
genreSet = set()
totalRelatedWithThisTag = 0
if isActor:
actorIds = "select count(distinct actorid ) from movie_actor where movieid in " + movListStr
res = self.db.executeQuery(actorIds)
#print "actorids query = "+str(res)
actorSet = self.getCount(str(res))
#print "actorSet = "+str(actorSet)
totalRelatedWithThisTag = int(actorSet)
elif self.relation == "genre":
genres = "select * from mlmovies where movieid in " + movListStr
res = self.db.executeQuery(genres)
for genre in res:
genreStr = genre[2]
genreList = genreStr.split('|')
for g in genreList:
genreSet.add(g)
totalRelatedWithThisTag = len(genreSet)
#print "genres = "+str(genreSet)
elif self.relation == "user":
users = "select count(distinct userid) from mltags where tagid =" + str(
tagid)
countUsers = self.db.executeQuery(users)
countofUsers = self.getCount(str(countUsers))
totalRelatedWithThisTag = int(countofUsers)
#print "totalGenresWithThisTag = "+str(totalRelatedWithThisTag)
idf = totalDocsCount / totalRelatedWithThisTag
idf = math.log(idf)
tagIdVsIDF[tagid] = idf
tagIdVsTFIDF[tagIdVsName[key]] = tagIdVsTF[tagid] * idf
#print "tagId = "+str(tagid)
#print "IDF = "+str(idf)
print "Tag vs TF-IDF "
sortedTagVsIDF = sorted(tagIdVsTFIDF.items(),
key=operator.itemgetter(1),
reverse=True)
print "" + str(sortedTagVsIDF)
def calcSVD(self, movies):
isActor = False
movieVsWeight = {}
query = "select * from genome_tags order by tagid"
tags = self.db.executeQuery(query)
tagIndex = 0
tagIdVsIndex = {}
tagIndexVsName = {}
for tag in tags:
tagid = tag[0]
tagName = tag[1]
tagIdVsIndex[tagid] = tagIndex
tagIndexVsName[tagIndex] = tagName
tagIndex = tagIndex + 1
moviesList = []
noOfMovies = len(movies)
q = "select count(*) from genome_tags"
res = self.db.executeQuery(q)
countStr = self.getCount(str(res[0]))
noOfTags = int(countStr)
movieTFIDF = np.zeros((noOfMovies, noOfTags))
movieIndex = 0
movieIdVsIndex = {}
for movie in movies:
movieid = movie[0]
weight = 0
#Get the tags related to the actor/genre/user
oldestTagQuery = "select * from mltags where movieid = " + str(
movieid) + " order by time_stamp limit 1"
#print "oldestTagQuery = "+oldestTagQuery
oldestTagQueryRes = self.db.executeQuery(oldestTagQuery)
oldestTimeStamp = None
newestTimeStamp = None
timeRange = None
for oldTag in oldestTagQueryRes:
oldestTimeStamp = oldTag[3]
oldesMillis = self.getMillis(oldestTimeStamp)
tagsQuery = "select * from mltags where movieid = " + str(
movieid) + " order by time_stamp desc"
tags = self.db.executeQuery(tagsQuery)
actorTagsCount = len(tags)
tagVsTotalWeight = {}
tagVsTF = {}
tagIdVsTF = {}
taglist = []
movieVsTags = {}
n = 1
for tag in tags:
movieid = tag[1]
tagid = tag[2]
timestamp = tag[3]
if n == 1:
newestTimeStamp = timestamp
timeRange = self.getMillis(newestTimeStamp) - oldesMillis
taglist.append(tagid)
tagWeight = self.getWeight(n, actorTagsCount + 1)
if timeRange != 0:
tagWeight = (self.getMillis(timestamp) -
oldesMillis) / timeRange
#print "rankweight = "+str(rankWeight)
n = n + 1
#print "tagWeight = "+str(tagWeight)
#combinedWeight = (tagWeight + (3 *rankWeight))/4
combinedWeight = (tagWeight)
#print "combinedWeight = "+str(combinedWeight)
if tagid in tagVsTotalWeight:
tempWeight = tagVsTotalWeight[tagid]
tempWeight += combinedWeight
tagVsTotalWeight[tagid] = tempWeight
else:
tagVsTotalWeight[tagid] = combinedWeight
# dict ("tagid" ,"weight1,weight 2")
#print "tagVsTotalWeight = "+str(tagVsTotalWeight)
tagsListStr = str(taglist)
tagsListStr = tagsListStr.replace('[', '(')
tagsListStr = tagsListStr.replace(']', ')')
totalWeight = 0
for key, val in tagVsTotalWeight.items():
# print "key - "+str(key)
# print "weight = "+str(val)
totalWeight += val
# Calcualting TF for each tag
for key, val in tagVsTotalWeight.items():
tf = tagVsTotalWeight[key] / totalWeight
tagIdVsTF[key] = tf
# Calculating IDF
totalDocsCount = noOfMovies
#print "Total documenst = "+str(totalDocsCount)
tagIdVsIDF = {}
tagIdVsTFIDF = {}
totalRelatedWithThisTag = 0
for tagid in taglist:
users = "select count(distinct movieid) from mltags where tagid =" + str(
tagid)
countUsers = self.db.executeQuery(users)
countofUsers = self.getCount(str(countUsers))
totalRelatedWithThisTag = int(countofUsers)
#print "totalGenresWithThisTag = "+str(totalRelatedWithThisTag)
idf = totalDocsCount / totalRelatedWithThisTag
idf = math.log(idf)
tagIdVsIDF[tagid] = idf
tagIdVsTFIDF[tagid] = tagIdVsTF[tagid] * idf
for key, val in tagIdVsTFIDF.items():
tagid = key
tfIdf = val
movieTFIDF[movieIndex][tagIdVsIndex[tagid]] = tfIdf
movieIdVsIndex[movieid] = movieIndex
movieIndex = movieIndex + 1
print "Movie tag Matrix"
print movieTFIDF
print movieTFIDF[movieIdVsIndex[7247]][tagIdVsIndex[1128]]
u, s, v = np.linalg.svd(movieTFIDF, full_matrices=False)
print "S"
print s
print "V"
print v
def calcUserVector(self):
query = "select * from mlratings where userid = " + str(self.entityid)
movies = self.db.executeQuery(query)
self.calcTFIDFApproach1(movies)
def calcGenreVector(self):
self.tableName = self.getEntityMovieTableName()
query = "select * from mlmovies where genres like '%" + str(
self.entityid) + "%'"
#print "query = "+query
movies = self.db.executeQuery(query)
self.calcTFIDFApproach1(movies)
return None
def calcMoviesVector(self):
self.tableName = self.getEntityMovieTableName()
query = "select * from mlmovies "
movies = self.db.executeQuery(query)
self.calcSVD(movies)
return None
def calcActorVector(self):
tableName = self.getEntityMovieTableName()
query = "select * from " + tableName + " where actorid = " + str(
self.entityid)
movies = self.db.executeQuery(query)
#self.calcTFIDFApproach2(movies)
self.calcTFIDFApproach1(movies)
def getWeightedTagVector(self):
#print "Inside getWeightedTagVector"
self.tableName = self.getEntityMovieTableName()
if self.relation == "actor":
self.calcActorVector()
elif self.relation == "genre":
self.calcGenreVector()
else:
self.calcUserVector()
def pDiff1(self, model, genre1, genre2):
#print "Model = "+model
genre1MovieList = []
genre1TagsList = []
genre2MovieList = []
genre2TagsList = []
genre1TagVsWeight = {}
genre2TagVsWeight = {}
totalMoviesSet = set()
# Movies associated with Genre 1
query = "select distinct movieid from mlmovies where genres like '%" + str(
genre1) + "%'"
movies = self.db.executeQuery(query)
for movie in movies:
genre1MovieList.append(movie[0])
totalMoviesSet.add(movie[0])
noOfMoviesGenre1 = len(genre1MovieList)
#print "Genre1 movies = "+str(genre1MovieList)
#print "Count1 = "+str(noOfMoviesGenre1)
# Movies associated with Genre 2
query = "select distinct movieid from mlmovies where genres like '%" + str(
genre2) + "%'"
movies = self.db.executeQuery(query)
for movie in movies:
genre2MovieList.append(movie[0])
totalMoviesSet.add(movie[0])
noOfMoviesGenre2 = len(genre2MovieList)
#print "Count2 = "+str(noOfMoviesGenre2)
totalMovies = len(totalMoviesSet)
#print "moviesList "+str(genre1MovieList)
movListStr1 = str(genre1MovieList)
movListStr1 = movListStr1.replace('[', '(')
movListStr1 = movListStr1.replace(']', ')')
movListStr2 = str(genre2MovieList)
movListStr2 = movListStr2.replace('[', '(')
movListStr2 = movListStr2.replace(']', ')')
# tags associated to Genre 1
query = "select distinct tagid from mltags where movieid in " + movListStr1
tags = self.db.executeQuery(query)
tagsList = []
for tag in tags:
tagsList.append(tag[0])
genre1Genre2MoviesForTag = set()
genre2MoviesForTag = []
noOfMoviesAssociatedWithThisTagGenre1 = 0
r = 0
m = 0
R = 0
M = 0
if model == "P-DIFF1":
query = "select distinct movieid from mltags where movieid in " + movListStr1 + " and tagid = " + str(
tag[0])
res = self.db.executeQuery(query)
for movie in res:
genre1Genre2MoviesForTag.add(movie[0])
noOfMoviesAssociatedWithThisTagGenre1 = len(
genre1Genre2MoviesForTag)
query = "select distinct movieid from mltags where movieid in " + movListStr2 + " and tagid = " + str(
tag[0])
res = self.db.executeQuery(query)
for movie in res:
genre1Genre2MoviesForTag.add(movie[0])
genre2MoviesForTag.append(movie[0])
r = noOfMoviesAssociatedWithThisTagGenre1
m = len(genre1Genre2MoviesForTag)
R = noOfMoviesGenre1
M = totalMovies
elif model == "P-DIFF2":
query = "select count(distinct movieid) from mltags where movieid in " + movListStr2 + " and tagid != " + str(
tag[0])
res = self.db.executeQuery(query)
r = int(self.getCount(str(res)))
query = "select count(distinct movieid) from mltags where movieid in " + movListStr1 + " or movieid in " + movListStr2 + "and tagid != " + str(
tag[0])
res = self.db.executeQuery(query)
m = int(self.getCount(str(res)))
R = noOfMoviesGenre2
M = totalMovies
# print "tagid = "+str(tag[0])
# print "r = "+str(r)
# print "m = "+str(m)
# print "R = "+str(R)
# print "M = "+str(M)
# smallmMinusr = smallmMinusr + 0.5
# R =R + 1
# r = r+0.5
# CapMminusR= CapMminusR+1
# if tag[0] == 1013:
# print "m = "+str(m)
# print "r = "+str(r)
# print "M = "+str(M)
# print "R = "+str(R)
# print "m-r = "+str(smallmMinusr)
x = float(r + float(m) / float(M)) / float(R + 1)
y = float(m - r + float(m) / M) / float(M - R + 1)
w = float(((x * (1 - y)) / (y * (1 - x))) * math.fabs((x - y)))
w = math.log(float(w))
# if tag[0] == 1013:
# print "w = "+str(w)
# num = r / (R - r)
# denom1 = m-r
# denom2 = M - m;
# denom2 = denom2 - R + r
# denom = denom1 * denom2
# leftExpression = num / denom
# right1 = r / R
# right2 = (m - r) / (M - R)
# right = math.fabs(right1 - right2)
# w = leftExpression * right
genre1TagVsWeight[tag[0]] = w
tagsListStr = str(tagsList)
tagsListStr = tagsListStr.replace('[', '(')
tagsListStr = tagsListStr.replace(']', ')')
tagsQuery = "select * from genome_tags where tagid in " + tagsListStr
tags = self.db.executeQuery(tagsQuery)
tagIdVsName = {}
for tag in tags:
tagIdVsName[tag[0]] = tag[1]
genre1TagNamVsWeight = {}
for tagid, weight in genre1TagVsWeight.items():
genre1TagNamVsWeight[tagIdVsName[tagid]] = weight
sortedTagVsWeight = sorted(genre1TagNamVsWeight.items(),
key=operator.itemgetter(1),
reverse=True)
print "Tag vs Weight = " + str(sortedTagVsWeight)
#print "Genre2 = "+str(genre2TagVsWeight)
# tags associated to Genre 2
# query = "select distinct tagid from mltags where movieid in "+movListStr2
# tags = self.db.executeQuery(query)
# for tag in tags:
# genre2genre1MoviesForTag = set()
# genre1MoviesListForThisTag = []
# query = "select distinct movieid from mltags where movieid in "+movListStr2+" and tagid = "+str(tag[0])
# res = self.db.executeQuery(query)
# for movie in res:
# genre2genre1MoviesForTag.add(movie[0])
# noOfMoviesAssociatedWithThisTagGenre2 = len(genre2genre1MoviesForTag)
#
# query = "select distinct movieid from mltags where movieid in "+movListStr1+" and tagid = "+str(tag[0])
# res = self.db.executeQuery(query)
# for movie in res:
# genre2genre1MoviesForTag.add(movie[0])
# genre1MoviesListForThisTag.append(movie[0])
# noOfMoviesAssociatedWithThisTagGenre2 = len(genre1MoviesListForThisTag)
#
#
# r = noOfMoviesAssociatedWithThisTagGenre2
# m = len(genre2genre1MoviesForTag)
# R = noOfMoviesGenre2
# M = totalMovies
#
# x = (r + float(m)/float(M))/ float(R)
# y = ( m-r + float(m)/M) / float(M-R + 1)
# w = ((x*(1-y)) / (y * (1-x))) * (x -y)
# #w = math.log(w)
# genre2TagVsWeight[tag[0]] = w
# print "Genre 2 Tag weight = "+str(genre2TagVsWeight)
# num = r / (R - r)
# denom1 = m-r
# denom2 = M - m;
# denom2 = denom2 - R + r
# denom = denom1 * denom2
# leftExpression = num / denom
# right1 = r / R
# right2 = (m - r) / (M - R)
# right = math.fabs(right1 - right2)
# w = leftExpression * right
# genre1TagVsWeight[tag[0]] = w
# print "tagid = "+str(tag[0])
# print "r= = "+str(r)
# print "m = "+str(m)
# print "R = "+str(R)
# print "M = "+str(M)
#print "weight = "+str(w)
def tfIdfDiff(self, genre1, genre2):
query = "select distinct movieid from mlmovies where genres like '%" + str(
genre1) + "%'"
movies = self.db.executeQuery(query)
moviesList = []
for movie in movies:
moviesList.append(movie[0])
moviesListStr = str(moviesList)
moviesListStr = moviesListStr.replace('[', '(')
moviesListStr = moviesListStr.replace(']', ')')
query = "select tagid from mltags where movieid in " + moviesListStr
totaltags = 0
tagIdVsFreq = {}
tagIdVsTF = {}
tagIdVsIDF = {}
tagIdVsName = {}
tagVsTFIDFDIFF = {}
genre1MovieList = []
totalMoviesSet = set()
genre2MovieList = []
tagsList = []
tags = self.db.executeQuery(query)
for tag in tags:
tagsList.append(tag[0])
totaltags = totaltags + 1
if tag[0] in tagIdVsFreq.items():
freq = tagIdVsFreq[tag[0]]
freq = freq + 1
tagIdVsFreq = freq
else:
tagIdVsFreq[tag[0]] = 1
for tagid, freq in tagIdVsFreq.items():
tagIdVsTF[tagid] = tagIdVsFreq[tagid] / totaltags
query = "select distinct movieid from mlmovies where genres like '%" + str(
genre1) + "%'"
movies = self.db.executeQuery(query)
for movie in movies:
totalMoviesSet.add(movie[0])
#print "Genre1 movies = "+str(genre1MovieList)
#print "Count1 = "+str(noOfMoviesGenre1)
# Movies associated with Genre 2
query = "select distinct movieid from mlmovies where genres like '%" + str(
genre2) + "%'"
movies = self.db.executeQuery(query)
for movie in movies:
totalMoviesSet.add(movie[0])
totalMovies = len(totalMoviesSet)
# IDF Calcualtion
for tag in tags:
query = "select count(distinct movieid) from mltags where tagid = " + str(
tag[0])
count = self.db.executeQuery(query)
moviesWithThisTag = self.getCount(str(count))
tagIdVsIDF[tag[0]] = math.log(totalMovies / int(moviesWithThisTag))
tagsListStr = str(tagsList)
tagsListStr = tagsListStr.replace('[', '(')
tagsListStr = tagsListStr.replace(']', ')')
tagsQuery = "select * from genome_tags where tagid in " + tagsListStr
tags = self.db.executeQuery(tagsQuery)
tagIdVsName = {}
for tag in tags:
tagIdVsName[tag[0]] = tag[1]
for tagid, val in tagIdVsTF.items():
tagVsTFIDFDIFF[
tagIdVsName[tagid]] = tagIdVsTF[tagid] * tagIdVsIDF[tagid]
sortedTagVsWeight = sorted(tagVsTFIDFDIFF.items(),
key=operator.itemgetter(1),
reverse=True)
print "Tag vs TF-IDF-DIFF = " + str(sortedTagVsWeight)
class WorkWithUser:
db_url = os.environ.get('DATABASE_URL')
tab_users = DBConnect(db_url, "users")
tab_rating = DBConnect(db_url, "rating")
tab_users_rating = DBConnect(
db_url, "{}, {}".format(tab_users.table, tab_rating.table))
def create_rating_user(self, login, number_level):
where_request = "name_user = '******'".format(login)
id_user = self.tab_users.req_select_db("id", where_request)
record_existence = self.tab_rating.req_select_db(
"*", "id_user = '******' and number_level = {}".format(
id_user[0][0], number_level))
if not record_existence:
values = """nextval('rating_id_seq'), 0, now(),
{}, {}""".format(id_user[0][0], number_level)
self.tab_rating.req_insert_db(values)
return True
else:
return False
def check_text_char(self, text):
characters = """ABCDEFGHIKLMNOPQRSTVXYZ
abcdefghiklmnopqrstvxyz1234567890"""
for i in text:
find = False
for j in characters:
if i == j:
find = True
break
if not find:
return False
return True
def check_text_len(self, text, len_text):
if len(text) < len_text:
return False
else:
return True
def validation_of_login_password(self, login, password):
if (self.check_text_char(login) and self.check_text_char(password)
and self.check_text_len(login, 5)
and self.check_text_len(password, 6)):
return True
else:
return False
def signup_in_game(self, login, password):
record = self.tab_users.req_select_db("name_user",
"name_user = '******'".format(login))
if not record and self.validation_of_login_password(login, password):
values = "nextval('users_id_seq'),'{}','{}'".format(
login, password)
self.tab_users.req_insert_db(values)
self.create_rating_user(login, 1)
return True
else:
return False
def signin_in_game(self, login, password):
record = self.tab_users.req_select_db("name_user",
"name_user = '******'".format(login))
if record:
record = self.tab_users.req_select_db(
"password_user",
"name_user = '******' and password_user = '******'".format(
login, password))
try:
if record[0][0] == password:
return True
except IndexError:
return False
else:
return False
def delete_user(self, login, password):
record = self.tab_users.req_select_db("password_user",
"name_user = '******'".format(login))
try:
if record[0][0] == password:
self.tab_users.req_delete_db("name_user = '******'".format(login))
return True
else:
return False
except IndexError:
return False
def update_score(self, login, score_user, number_level):
text_where_max_rating = """id_user = (select id from users
where name_user = '******') and number_level = {}""".format(
login, number_level)
socre_from_db = self.tab_rating.req_select_db("score",
text_where_max_rating)
if socre_from_db[0][0] >= int(score_user):
return
set_value = "score = {}, date_add = now()".format(score_user)
where_request = """
id_user = (select id from users where name_user = '******')
and number_level = {};
""".format(login, number_level)
self.tab_rating.req_update_db(set_value, where_request)
def select_top_10(self, number_level):
columns = "name_user, score, date_add"
where_request = """users.id = rating.id_user and
rating.number_level = {} order by score desc
limit 10""".format(number_level)
select_val = self.tab_users_rating.req_select_db(
columns, where_request)
for i in range(len(select_val)):
select_val[i] = list(select_val[i])
select_val[i][2] = str(select_val[i][2])
return select_val
def prevate_score(self, login, number_level):
columns = "score"
id_user = self.tab_users.req_select_db(
"id", "name_user = '******'".format(login))
try:
where_request = """id_user = {} and
number_level = {}""".format(id_user[0][0], number_level)
record = self.tab_rating.req_select_db(columns, where_request)
return record[0][0]
except IndexError:
return False
def max_number_level(self, login):
id_user = self.tab_users.req_select_db(
"id", "name_user = '******'".format(login))
record = self.tab_rating.req_select_db(
"max(number_level)", "id_user = {}".format(id_user[0][0]))
return record[0][0]
def mine_block(self, block_type, block_data):
client = DBConnect()
last_block = self.csv_operator.get_last_block()
_id = self.csv_operator.get_chain_length()
car_id = block_data[0]
nonce = self.proof_of_work(last_block["nonce"])
last_hash_block = self.get_hash(last_block)
last_hash_car = "None"
if block_type == "Production":
block = {
"_id": _id,
"car_id": car_id,
"nonce": nonce,
"hash": {
"block": last_hash_block,
"car": last_hash_car
},
"details": {
"block_type": "Production"
}
}
elif block_type == "NewRegister":
block = {
"_id": _id,
"car_id": car_id,
"nonce": nonce,
"hash": {
"block": last_hash_block,
"car": last_hash_car
},
"details": {
"block_type": "NewRegister"
}
}
elif block_type == "Repair":
last_car_entry = client.get_car_history(car_id)[-1]
last_hash_car = self.get_hash(last_car_entry)
block = {
"_id": _id,
"car_id": car_id,
"nonce": nonce,
"hash": {
"block": last_hash_block,
"car": last_hash_car
},
"details": {
"block_type": "Repair",
}
}
elif block_type == "Sale":
last_car_entry = client.get_car_history(car_id)[-1]
last_hash_car = self.get_hash(last_car_entry)
block = {
"_id": _id,
"car_id": car_id,
"nonce": nonce,
"hash": {
"block": last_hash_block,
"car": last_hash_car
},
"details": {
"block_type": "Sale"
}
}
else:
return "wrong input"
client.ingest_block(block)
self.csv_operator.add_block(block)
def __init__(self):
conn = DBConnect()
conn.autocommit = False
self.dbConn = conn.mydb.cursor(dictionary=True)
self.mydb = conn.mydb
class WBChecker:
interface.implements(ICredentialsChecker)
credentialInterfaces = (credentials.IUsernamePassword,
credentials.IUsernameHashedPassword)
def __init__(self, logged):
"""Inicializa la conexion con la base de datos
"""
self.logged = logged
self.db = DBConnect()
def stop(self):
"""Termina la conexion con la base de datos
"""
self.db.close()
def _checkUser(self, credentials):
"""Consulta en la base de datos por el usuario de credentials
"""
query = "select id, username, password, kind from person where username = '******'" % credentials.username
d = self.db.db.runQuery(query)
d.addCallback(self.checkValidUser, credentials)
return d
def _passMatches(self, matches, avatarId):
"""Chequea matches (que lo devuelve _checkPassword),
para devolver el avatarId (que corresponde con el id del usuario)
o levantar un error
"""
if matches:
if str(avatarId) in self.logged.keys():
return failure.Failure(error.UnauthorizedLogin(_('El usuario ya esta en el sistema')))
else:
return avatarId
else:
return failure.Failure(error.UnauthorizedLogin(_('Nombre de usuario o clave incorrecta')))
def checkValidUser(self, result, credentials):
"""Toma el resultado de una consulta y una credencial y
chequea si el password de la credencial coincide con el
de la consulta (a traves de checkPassword)
"""
if len(result) > 0:
d = self._checkKind(result[0][0], result[0][3])
d.addCallback(self._checkPassword, credentials, result)
return d
else:
#el usuario no existe.
if DEMO_MODE:
if credentials.checkPassword ('demoCreate:TUTOR'):
kind = TUTOR
elif credentials.checkPassword ('demoCreate:PUPIL'):
kind = PUPIL
else: #se ofrece crear un usuario temporal.
return failure.Failure(error.UnauthorizedLogin('no_existe'))
demo = DemoMaker(credentials.username, kind, self.db)
d = demo.createDemo()
return d
else:
return failure.Failure(error.UnauthorizedLogin(_('Nombre de usuario o clave incorrecta')))
def requestAvatarId(self, credentials):
"""Chequea con una credential por un usuario
"""
print "intentando chequear:", credentials.username
d = self._checkUser(credentials)
return d
def _checkKind(self, avatarId, kind):
if kind == TUTOR:
d = defer.maybeDeferred(lambda: True)
elif kind == PUPIL:
query = "select expires from pupil where id = %d" % avatarId
d = self.db.db.runQuery(query)
d.addCallback(self._checkExpired)
else:
d = defer.maybeDeferred(lambda: False)
return d
def _checkExpired (self, result):
if len(result) > 0 and result[0][0] >= now():
return True
else:
return failure.Failure(error.UnauthorizedLogin(_('Sus horas ya expiraron')))
def _checkPassword(self, kindPass, credent, result):
#up = credentials.IUsernamePassword(credent, None)
#print "pass? ", up.password
if kindPass:
pas = result[0][2]
#print "pas db = "+pas
d = defer.maybeDeferred(credent.checkPassword, pas)
d.addCallback(self._passMatches, result[0][0])
return d
else:
return failure.Failure(error.UnauthorizedLogin(_('Nombre de usuario o clave incorrecta')))
