def createNewUser(username, passwrd):
db = DB.getDBConnection()
#create entry in users table
status = db.query_formatted(
'insert into users(username,password,isactive) values(%s,%s,%s)',
[username.lower(), passwrd, '0'])
status = bool(status)
#now update the user_data table
if status:
#get userid
result = db.query_formatted(
'select id from users where username=%s AND password=%s AND isactive=%s',
[username.lower(), passwrd, '0'])
if len(result.getresult()) > 0:
userID = result.getresult()[0][0]
#create entry in userdata table with default values
status = db.query_formatted(
'insert into user_data(id,history,historyupdated,userprofile) values(%s,%s,%s,%s)',
[userID, '', '0', ''])
db.close()
return bool(status)
else:
db.close()
return False
db.close()
return status
def isUserHistoryUpdated(userID):
db = DB.getDBConnection()
result = db.query_formatted(
'select historyupdated from user_data where id=%s', [userID])
db.close()
if len(result.getresult()) > 0:
return result.getresult()[0][0]
#if could not find the result just send that history is updated so that the data can be recreated
return 1
def isUserActive(userID):
db = DB.getDBConnection()
result = db.query_formatted(
'select id from users where id=%s AND isactive=%s', [userID, '1'])
isActive = 0
db.close()
if len(result.getresult()) > 0:
isActive = result.getresult()[0][0]
return isActive
def updateUserProfile(userID, profile):
db = DB.getDBConnection()
profile = pickle.dumps(profile)
# quer = "update user_data set userprofile=%s where id=%s" % (profile,userID)
# status = db.query(quer)
db.update('user_data', row={'id': userID}, userprofile=profile)
status = db.query_formatted(
'update user_data set historyupdated=0 where id=%s', [userID])
db.close()
return status
def getMoviesFromIndex(movieIDs):
db = DB.getDBConnection()
quer = ''
if len(movieIDs) == 1:
quer = 'select * from idmovietable where index = %s' % movieIDs[0]
else:
movieIDs = tuple(movieIDs)
quer = 'select * from idmovietable where index in %s' % str(movieIDs)
result = db.query(quer)
db.close()
return result.dictresult()
def loadUserProfile(userID):
db = DB.getDBConnection()
result = db.query_formatted(
'select userprofile from user_data where id=%s', [userID])
db.close()
if len(result.getresult()) > 0:
dataString = result.getresult()[0][0]
#if userprofile exists
if len(dataString) > 0:
return True, pickle.loads(dataString)
else: #create new userprofile
return False, dataString
# if could not find the userprofile handle exception
return False, None
def authenticateUser(userName, password):
db = DB.getDBConnection()
result = db.query_formatted(
'select id from users where username=%s AND password=%s',
(userName, password))
db.close()
if len(result.getresult()) > 0:
userID = result.getresult()[0][0]
status = _updateIsActive(userID)
if status:
return userID
else:
#throw error here in future and show message in UI that user does not exits
return None
else:
# throw error here in future and show message in UI that user does not exits
return None
def getUserHistory(userId, histFormat='dataframe'):
#get database connection
db = DB.getDBConnection()
quer = 'select history from user_data where id=%s' % str(userId)
hist = db.query(quer)
db.close()
if len(hist.getresult()) > 0:
hist_json = hist.dictresult()[0]['history']
#no history was created yet
if len(hist_json) == 0:
return hist_json
if histFormat == 'json':
return hist_json
else:
return pd.read_json(hist_json, orient='records')
else: #if no history is present
return ''
def getSearchResults(searchText, batchno):
db = DB.getDBConnection()
searchText = "'%%%s%%'" % searchText.lower()
querstr = "select * from idmovietable where lower(name) like %s" % searchText
result = db.query(querstr)
final_data = result.dictresult()
db.close()
#if no search result found
if len(final_data) == 0:
return False, final_data, 0
batches = len(final_data) / 10 if (len(final_data) %
10 == 0) else len(final_data) / 10 + 1
batchno = int(batchno)
if batches >= int(batchno):
return True, final_data[
(batchno - 1) * 10:batchno *
10], batches # return status, that batch of search result and number of batches
def updateUserHistory(userID, hist=None):
db = DB.getDBConnection()
status = db.query_formatted(
'update user_data set historyupdated=1 where id=%s', [userID])
if status: #update was successful
#update history if exists
if hist != None:
status = db.query_formatted(
'update user_data set history=%s where id=%s', [hist, userID])
db.close()
if status: #update was successful
return True
else:
return False
else:
db.close()
return True
else:
db.close()
return False
def _updateIsActive(userID):
db = DB.getDBConnection()
status = db.query_formatted('update users set isactive=1 where id=%s',
[userID])
db.close()
return status
def disableActive(userID):
db = DB.getDBConnection()
status = db.query_formatted('update users set isactive=%s where id=%s',
['0', userID])
db.close()
return status