def addPhotoFavoriteToDB(userid, photoid): cursor=conn.cursor() from datetime import datetime try: if cursor is not None: sql = ("select * from favorite where Userid=%(uid)s and Photoid=%(pic)s") data={'uid':userid, 'pic':photoid} cursor.execute(sql,data) result=cursor.fetchall() if (len(result)>0): return true else: sql=("Insert into favorite " "Values(%(uid)s, %(pic)s, %(time)s)") data={'uid':userid, 'pic':photoid,'time':datetime.now().strftime('%Y-%m-%d %H:%M:%S')} cursor.execute(sql,data) result=cursor.rowcount conn.commit() cursor.close() if (result>0): return true else: return false; except: print (exc_info()) conn.rollback() cursor.close() return false
def GetPassword(uid): try: cursor = conn.cursor() args = [uid, 0] result_args = cursor.callproc('uspGetPassword', args) #print(result_args[1]) return result_args[1] except Error as e: print(e) finally: cursor.close()
def addphoto(UID, PName, PDesc, PPath, FiName): newCursor = conn.cursor(); try: args = [UID, PName, PDesc, PPath, FiName, 0] result_args = newCursor.callproc('uspAddPhoto', args) conn.commit() newCursor.close() return (result_args[5]) except Error as e: conn.rollback() print(e) newCursor.close() return 0;
def addNewTag(tagText): newCursor=conn.cursor(); try: args = [tagText, 0] result_args = newCursor.callproc('uspAddTag', args) conn.commit() if result_args[1]>0: return result_args[1] except Error as e: conn.rollback() print (e) newCursor.close() return 0;
def getUserToken(userid): cursor=conn.cursor() try: sql=("select accessToken from userdb.userinfo " "where ID =%(uid)s)") data={'uid':userid} cursor.execute(sql,data) result=cursor.fetchall() cursor.close() return result[0][0] except: print (exc_info()) cursor.close() return None
def removeComment(userid, pic_id, comment_id): if (userid<1 or pic_id<1): return False; try: cursor=conn.cursor() sql="Delete FROM userdb.comments where ID=%(c_id)s" data={'c_id':comment_id} cursor.execute(sql,data) #result=cursor.fetchall() cursor.close() return True except: print (exc_info()) return False return False
def AddNewUser(Username, Pword, salt, email): from datetime import datetime try: cursor=conn.cursor() timeFormat='%Y-%m-%d %H:%M:%S' args = [Username,Pword, 0, salt, email, datetime.utcnow().strftime(timeFormat),0] result_args = cursor.callproc('uspAddUser', args) conn.commit() cursor.close() #print(result_args[6]) return result_args[6] except Error as e: conn.rollback() cursor.close() print(e) return 0
def UnlikePic(UID,PID): if UID==0 or PID==0: return False try: cursor = conn.cursor() args = [UID, PID, 0] result_args = cursor.callproc('uspCancelLike', args) conn.commit() cursor.close() return True # print(result_args[3]) except Error as e: conn.rollback() print(e) cursor.close() return False
def AddLike(UID, PID): if UID==0 or PID==0: return try: cursor = conn.cursor() args = [UID, PID, 0] result_args = cursor.callproc('uspAddLike', args) conn.commit() return True # print(result_args[3]) except Error as e: conn.rollback() print(e) return False finally: cursor.close()
def AddUserRelation(u1id, u2id, Rtype): if u1id ==0 or u2id==0: return success=False try: cursor = conn.cursor() args = [u1id, u2id, Rtype, 0] result_args = cursor.callproc('uspAddUserRelation', args) conn.commit() # print(result_args[3]) success=True except Error as e: conn.rollback() print(e) finally: cursor.close() return success
def followingCount(uid): if uid == 0: return None cursor=conn.cursor() try: sql=("select count(user2ID) from userrelation" " where User1ID=%(uid)s") data={"uid":uid} cursor.execute(sql,data) result=cursor.fetchall() if result is not None and len(result)>0: return (result[0][0]-1) except: print (exc_info()) return 0; finally: cursor.close()
def get_user_follower_ids_fromDB(uid): if uid == 0: return None cursor=conn.cursor() try: sql=("select user1ID from userrelation" " where User2ID=%(uid)s") data={"uid":uid} cursor.execute(sql,data) result=cursor.fetchall() if result is not None and len(result)>0: return result except: print (exc_info()) return None finally: cursor.close()
def postCount(uid): if uid == 0: return None cursor=conn.cursor() try: sql=("select count(*) from userdb.Photos" " where uid=%(uid)s") data={"uid":uid} cursor.execute(sql,data) result=cursor.fetchall() if result is not None and len(result)>0: return (result[0][0]) except: print (exc_info()) return None finally: cursor.close()
def addcomment(CText, UID, PID): if UID==0 or PID==0 or CText is None: return if len(CText) <1: return cursor = conn.cursor() try: args = [CText, UID, PID, 0] result_args = cursor.callproc('uspAddComment', args) conn.commit() #print(result_args[4]) return result_args[3] except Error as e: conn.rollback() print(e) finally: cursor.close()
def getUserFavioritePic(uid): result=list() cursor=conn.cursor() try: sql=("select photoid from Favorite " " where userid=%(uid)s") data={"uid":uid} cursor.execute(sql,data) rows=cursor.fetchall() if rows is not None and len(rows)>0: for e in rows: result.append(e[0]) except: print (exc_info()) finally: cursor.close() return result
def getUserProfile(uid): if uid==0: return None cursor=conn.cursor() try: sql=("select username,location,brithday,Gender,Occupation,Height,Weight from userdb.userdetails" " where userdetails.id=%(uid)s") data={"uid":uid} cursor.execute(sql,data) row=cursor.fetchall() result=dict(username=row[0][0],location=row[0][1],brithday=row[0][2],Gender=row[0][3],Occupation=row[0][4], Height=row[0][5],Weight=row[0][6]) except: print (exc_info()) return None finally: cursor.close() return result
def getUserProfilePhoto(uid): if uid==0: return None try: cursor = conn.cursor() sql = ("select Path, Filename,UID from userdb.photos " "where ID = (select photoid from userdb.profilePhoto where userid=%(uid)s)") data = {'uid':uid} cursor.execute(sql,data) Pics=cursor.fetchall() cursor.close() except: print (exc_info()) if len(Pics)>0: for pic in Pics: import os url=os.path.join(pic[0],pic[1]) return url
def getFeedsFromDb(uid): result=list() if uid ==0: return None try: cursor = conn.cursor() sql=("select photos.id as PhotoId from photos where photos.uid in (select userrelation.user2ID from userrelation" " where User1ID=%(uid)s) order by PAddDate desc") data={"uid":uid} cursor.execute(sql,data) rows=cursor.fetchall() cursor.close() for row in rows: if row[0] is not None: result.append(row[0]) return result except: print (exc_info())
def getUserPasswordByName(username): if username<1: return None cursor=conn.cursor() try: sql="SELECT Password FROM userdb.userinfo WHERE Username =%(uname)s " data={"uname":username} cursor.execute(sql,data) result=cursor.fetchall() if result is not None: cursor.close() return result[0][0] else: cursor.close() return None; except: print(exc_info()) cursor.close() return None;
def getUserPrivatekey(userid): if userid<1: return None cursor=conn.cursor() try: sql="SELECT Private_key FROM userdb.userinfo WHERE ID =%(uid)s " data={"uid":userid} cursor.execute(sql,data) result=cursor.fetchall() if result is not None: cursor.close() return result[0][0] else: cursor.close() return None; except: print(exc_info()) cursor.close() return None;
def checkAvailableUsername(username): if (username is None): return False else: try: cursor=conn.cursor() sql="select username in userdb.userinfo where username =%(user)s " data={"user":username} cursor.execute(sql,data) if (cursor.rowcount>0): cursor.close() return False else: cursor.close() return true; except: print(exc_info()) cursor.close() return False;
def getUserPosts(uid): result=list() if uid == 0: return None cursor=conn.cursor() try: sql=("select id from userdb.Photos" " where uid=%(uid)s") data={"uid":uid} cursor.execute(sql,data) rows=cursor.fetchall() if rows is not None and len(rows)>0: for e in rows: result.append(e[0]) except: print (exc_info()) finally: cursor.close() return result
def addphotoTags(Pid,Tags): newCursor=conn.cursor(); try: for tag in Tags: tagid=tag['tagid'] if (tagid == 0): tagid=addNewTag(tag['text']) sql=("Insert into PhotoTags (PhotoID,TagID,LeftX,TopY)" "Values(%(pic)s, %(tag)s, %(x)s,%(y)s)") data={'pic':Pid, 'tag':tagid,'x':tag['left'],'y':tag['top']} newCursor.execute(sql,data) result=newCursor.rowcount conn.commit() newCursor.close() return true except Error as e: conn.rollback() print (e) newCursor.close() return false;
def addUserProfile(uid,Uname,Location,brithday,Gender,Occupation,Height,Weight): if uid==0: return None cursor=conn.cursor() try: sql=("insert into userdb.userDetails (UserId,username,location,brithday,Gender,Occupation,Height,Weight)" " Values(%(uid)s, %(uname)s, %(loca)s, %(brith)s, %(Gender)s, %(Occup)s, %(Height)s, %(Weight)s)") data={"uid":uid, "uname":Uname,"loca":Location,"brith":datetime.strptime(brithday,'%Y-%m-%d'),"Gender":Gender,"Occup":Occupation,"Height":Height,"Weight":Weight} cursor.execute(sql,data) row=cursor.rowcount conn.commit(); cursor.close() if row >0: return true; else: return false; except: print (exc_info()) conn.rollback() cursor.close() return false;
def saveUserKeys(userid,priv_pem,pub_pem): if userid<1: return False cursor=conn.cursor() try: sql=("Update userdb.userinfo " " SET Private_key=%(prv)s, Public_key=%(pub)s " " WHERE ID=%(uid)s") data={'uid':userid, 'prv':priv_pem,'pub':pub_pem} cursor.execute(sql,data) result=cursor.rowcount conn.commit() cursor.close() if (result>0): return true else: return false except: print (exc_info()) conn.rollback() cursor.close() return False;
def updateUserProfile(uid,Uname,Location,brithday,Gender,Occupation,Height,Weight): if uid==0: return None cursor=conn.cursor() try: sql=("Update userdetails " "set Username=%(uname)s,Location=%(loca)s,Brithday=%(brith)s,Gender=%(Gender)s,Occupation=%(Occup)s," "Height=%(Height)s,Weight=%(Weight)s " "Where UserID=%(uid)s") data={"uid":uid, "uname":Uname,"loca":Location,"brith":datetime.strptime(brithday,'%Y-%m-%d'),"Gender":Gender,"Occup":Occupation,"Height":Height,"Weight":Weight} cursor.execute(sql,data) row=cursor.rowcount conn.commit(); cursor.close() if row >0: return true; else: return false; except: print (exc_info()) conn.rollback() cursor.close() return false;
def updateUserToken(userid, token): if userid<0: return False else: cursor=conn.cursor() try: sql=("Update userdb.userinfo " " SET accessToken=%(token)s " " WHERE ID=%(uid)s") data={'uid':userid, 'token':token} cursor.execute(sql,data) result=cursor.rowcount conn.commit() cursor.close() if (result>0): return True else: return False except: print (exc_info()) conn.rollback() cursor.close() return False;