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')))