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;
