def getComments(self, username): try: self.lastCommentId = 0 self.comments = [] commentTableConn = getConnection() commentTableCursor = commentTableConn.cursor() commentTableCursor.execute( """SELECT * FROM COMMENTTABLE WHERE friendUsername=%s;""", (username, )) commentTableConn.commit() dbData = commentTableCursor.fetchall() if dbData != None: for comment in dbData: myComment = Comment() myComment.commentId = comment[0] myComment.userId = comment[1] myComment.userName = comment[2] myComment.friendUsername = comment[3] myComment.content = comment[4] self.comments.append(myComment) self.lastCommentId += 1 commentTableCursor.close() commentTableConn.close() except commentTableConn.Error as Error: print(Error) return self
def getLocations(self,username): try: self.lastLocationId = 0 self.myLocations = [] userMapConnection = getConnection(); userMapCursor = userMapConnection.cursor() userMapCursor.execute("""SELECT * FROM USERMAPTABLE WHERE user_id=%s;""",(username,)) userMapConnection.commit() dbData = userMapCursor.fetchall() if dbData != None: for locations in dbData: userLocations = UserLocation() userLocations.userName = locations[1] userLocations.mapInfo = locations[2] userLocations.locationLabel = locations[3] userLocations.lat = locations[4] userLocations.lng = locations[5] userLocations.locationId=locations[0] self.myLocations.append(userLocations) location = self.myLocations[0] self.lastLocationId += 1 userMapCursor.close() userMapConnection.close() except userMapConnection.Error as userMapError: print(userMapError) return self
def search(username, password): conn = getConnection() cursor = conn.cursor() try: cursor.execute(""" SELECT * FROM USERTABLE WHERE username= %s;""", (username, )) conn.commit() dbData = cursor.fetchone() if dbData is None: status = 'There is no user with this username ' else: if password == dbData[2]: status = 'Success' else: status = 'Password is invalid' except conn.Error as error: print(error) status = 'Password or Username is invalid' cursor.close() conn.close() return status
def GetLocation(self, location_id): connection = getConnection() cursor = connection.cursor() query = """ SELECT location_id,user_id,lat,long,photo,video,document FROM EVENTTABLE WHERE location_id= %s;""" try: cursor.execute(query, (location_id)) fetched_data = cursor.fetchone() connection.commit() if fetched_data is None: status = 'Invalid or no location data' connection.close() return None else: location_id = fetched_data[0] lat = fetched_data[1] long = fetched_data[2] photo = fetched_data[3] video = fetched_data[4] document = fetched_data[5] location = Location(location_id, lat, long, photo, video, document) except connection.Error as error: print(error) connection.close() return location
def GetLocations(self): connection = getConnection() cursor = connection.cursor() query = """ SELECT * FROM EVENTTABLE ORDER BY event_id;""" try: cursor.execute(query) fetched_data = cursor.fetchone() if fetched_data is None: status = 'No location data ' connection.close() return None lat = fetched_data[0] long = fetched_data[1] photo = fetched_data[2] video = fetched_data[3] document = fetched_data[4] locations = [(Location(lat, long, photo, video, document))] for row in cursor: location_id, lat, long, photo, video, document = row locations_row = [(Location(location_id, lat, long, photo, video, document))] locations += locations_row connection.commit() except connection.Error as error: print(error) connection.close() return locations
def getNotifications(self, username): try: self.lastNotificationId = 0 self.myNotifications = [] notificationTableConn = getConnection() notificationCursor = notificationTableConn.cursor() notificationCursor.execute( """SELECT * FROM NOTIFICATIONTABLE WHERE friendUsername = %s;""", (username, )) notificationTableConn.commit() dataFromDb = notificationCursor.fetchall() if dataFromDb != None: for notifications in dataFromDb: notification = Notification() notification.notificationId = notifications[0] notification.requester = notifications[1] notification.requested = notifications[2] messageId = notifications[3] if messageId: notification.type = 'message' notification.typeId = messageId commentId = notifications[4] if commentId: notification.type = 'comment' notification.typeId = commentId self.myNotifications.append(notification) self.lastNotificationId += 1 notificationCursor.close() notificationTableConn.close() except notificationTableConn.Error as error: print(error) return self
def getUsers(self): conn = getConnection() cursor = conn.cursor() self.userTable = [] self.lastUserCounter = 0 try: cursor.execute(""" SELECT * FROM USERTABLE;""") conn.commit() dbData = cursor.fetchall() if dbData != None: for users in dbData: user = User() user.userId = users[0] user.username = users[1] user.password = users[2] user.email = users[3] user.name = users[4] user.surname = users[5] self.userTable.append(user) self.lastUserCounter += 1 cursor.close() conn.close() return User except conn.Error as error: print(error) return 'Error' return self
def deleteLocation(self, locationId, newAddress): try: userMapConnection = getConnection(); userMapCursor = userMapConnection.cursor() userMapcursor.execute("""DELETE FROM USERMAPTABLE WHERE userMap_id=%d;""",(locationId,)) userMapConnection.commit() except userMapConnection.Error as userMapError: print(userMapError) userMapConnection.close()
def updateLocationAddress(self, locationId, newAddress): try: userMapConnection = getConnection(); userMapCursor = userMapConnection.cursor() userMapcursor.execute("""UPDATE USERMAPTABLE SET address=%s WHERE userMap_id=%d;""",(newaddress,locationId)) userMapConnection.commit() userMapCursor.close() userMapConnection.close() except userMapConnection.Error as userMapError: print(userMapError)
def updateLocationInformation(self, locationId, newInfo): try: userMapConnection = getConnection(); userMapCursor = userMapConnection.cursor() userMapcursor.execute("""UPDATE USERMAPTABLE SET mapInformation=%s WHERE userMap_id=%d;""",(newInfo,locationId)) userMapConnection.commit() except userMapConnection.Error as userMapError: print(userMapError) userMapConnection.close()
def updateFriends(self,friendId,newStatus): try: friendTableConnection = getConnection(); friendCursor = friendTableConnection.cursor() friendCursor.execute("""UPDATE FRIENDSTABLE SET status=%s WHERE friendRecordId=%s;""",(newStatus,friendId)) friendTableConnection.commit() except friendTableConnection.Error as Error: print(Error) friendTableConnection.close()
def DeleteLocation(self, location_id): connection = getConnection() cursor = connection.cursor() query = """DELETE FROM MMAPTABLE WHERE location_id = %s""" try: cursor.execute(query, (location_id)) connection.commit() except connection.Error as error: print(error) connection.close()
def UpdateLocation(self, event, event_id): connection = getConnection() cursor = connection.cursor() query = """UPDATE MMAPTABLE SET lat = %s, long = %s, photo = %s, video = %s, document = %s WHERE location_id = %s""" try: cursor.execute(query, (location.lat, location.long, location.photo, location.video, location.document)) connection.commit() except connection.Error as error: print(error) connection.close()
def deleteUser(username): connection = getConnection() cursor = connection.cursor() try: cursor.execute("""DELETE FROM USERTABLE WHERE username=%s;""", (username, )) connection.commit() connection.close() except connection.Error as error: print(error)
def deleteRelation(self, friendId ): try: friendTableConnection = getConnection(); friendCursor = friendTableConnection.cursor() friendCursor.execute("""DELETE FROM FRIENDSTABLE WHERE friendRecordId=%s;""",(friendId,)) friendTableConnection.commit() self.lastFriendId = 0 self.myFriends = [] except friendTableConnection.Error as Error: print(Error) friendTableConnection.close()
def addFriend(self, Friend): self.lastFriendId+= 1 Friend.friendStatus = 'casualFriend' self.myFriends.append(Friend) try: friendTableConnection = getConnection(); friendCursor = friendTableConnection.cursor() friendCursor.execute("""INSERT INTO FRIENDSTABLE (user_id,firends_id,status) VALUES(%s,%s,%s);""", (Friend.userName,Friend.friendUsername,Friend.friendStatus)) friendTableConnection.commit() friendCursor.close() friendTableConnection.close() except friendTableConnection.Error as Error: print(Error)
def deleteNotification(self, notificationId): try: notificationTableConn = getConnection() notificationCursor = notificationTableConn.cursor() notificationCursor.execute( """DELETE FROM NOTIFICATIONTABLE WHERE notificationId =%s;""", (notificationId, )) notificationTableConn.commit() notificationCursor.close() notificationTableConn.close() except notificationTableConn.Error as error: print(error)
def sendMessageNotification(self, notification): try: notificationTableConn = getConnection() notificationCursor = notificationTableConn.cursor() notificationCursor.execute( """INSERT INTO NOTIFICATIONTABLE(user_name, friendUsername,messageId) VALUES(%s,%s,%s);""", (notification.requester, notification.requested, notification.typeId)) notificationTableConn.commit() notificationCursor.close() notificationTableConn.close() except notificationTableConn.Error as error: print(error)
def deleteComment(self, commentId): try: commentTableConn = getConnection() commentTableCursor = commentTableConn.cursor() commentTableCursor.execute( """DELETE FROM COMMENTTABLE WHERE commentId=%s;""", (commentId, )) commentTableConn.commit() self.lastFriendId = 0 self.myFriends = [] except commentTableConn.Error as Error: print(Error) commentTableConn.close()
def sendMessage(self, message): newMessage = Message() newMessage.sender = message.sender newMessage.receiver = message.receiver newMessage.content = message.content newMessage.status = 'normal' try: messageTableConnection = getConnection(); messageCursor = messageTableConnection.cursor() messageCursor.execute("""INSERT INTO MESSAGETABLE (user_id,firends_id,content,status) VALUES(%s,%s,%s,%s);""", (message.sender,message.receiver,message.content,message.status)) messageTableConnection.commit() messageCursor.close() messageTableConnection.close() except messageTableConnection.Error as Error: print(Error)
def addComment(self, Comment): self.lastCommentId += 1 self.comments.append(Comment) try: commentTableConn = getConnection() commentTableCursor = commentTableConn.cursor() commentTableCursor.execute( """INSERT INTO COMMENTTABLE (userId,user_name,friendUsername,content) VALUES(%s,%s,%s,%s);""", (Comment.userId, Comment.userName, Comment.friendUsername, Comment.content)) commentTableConn.commit() commentTableCursor.close() commentTableConn.close() except commentTableConn.Error as Error: print(Error)
def blockFriend(self,friendId,username): try: friendTableConnection = getConnection(); friendCursor = friendTableConnection.cursor() friendCursor.execute("""SELECT * FROM FRIENDSTABLE WHERE friendRecordId=%s;""",(friendId,)) dbData = friendCursor.fetchone() if dbData[1] == username: friendCursor.execute("""UPDATE FRIENDSTABLE SET status=%s WHERE friendRecordId=%s;""",('blocked1',friendId)) if dbData[2] == username: friendCursor.execute("""UPDATE FRIENDSTABLE SET status=%s WHERE friendRecordId=%s;""",('blocked2',friendId)) friendTableConnection.commit() except friendTableConnection.Error as Error: print(Error) friendTableConnection.close()
def AddLocation(self, location): self.lastid += 1 self.locations[self.lastid] = location location._id = self.lastid connection = getConnection() cursor = connection.cursor() query = """INSERT INTO MMAPTABLE (lat, long, photo, video, document) VALUES (%s, %s, %s, %s, %s)""" try: cursor.execute(query, (location.lat, location.long, location.photo, location.video, location.document)) self.last_key = cursor.lastrowid connection.commit() except connection.Error as error: print(error) connection.close()
def setUserToDb(User): connection = getConnection() cursor = connection.cursor() username = User.username password = User.password email = User.email name = User.name surname = User.surname try: cursor.execute( """INSERT INTO USERTABLE (username, password, email, name, surname) VALUES(%s,%s,%s,%s,%s);""", (username, password, email, name, surname)) connection.commit() connection.close() except connection.Error as error: print(error)
def updateUser(User): connection = getConnection() cursor = connection.cursor() username = User.username password = User.password email = User.email name = User.name surname = User.surname try: cursor.execute( """UPDATE USERTABLE SET username=%s, password=%s,email=%s, name=%s, surname=%s WHERE username=%s;""", (username, password, email, name, surname, username)) connection.commit() connection.close() except connection.Error as error: print(error)
def getMessages(self,username): try: self.lastConversationId = 0 self.conversations = [] messageTableConnection = getConnection(); messageCursor = messageTableConnection.cursor() messageCursor.execute("""SELECT * FROM MESSAGETABLE WHERE firends_id=%s OR user_id=%s;""",(username,username)) messageTableConnection.commit() dbData = messageCursor.fetchall() if dbData != None: for messages in dbData: myMessage = Message() myMessage.messageId = messages[0] myMessage.sender = messages[1] myMessage.receiver = messages[2] myMessage.content = messages[3] myMessage.status = messages[4] found = 'false' if myMessage.status == 'deleted': if username == myMessage.receiver: continue if self.conversations != None: i = 0 while i < self.lastConversationId: if self.conversations[i].sender == messages[1] or self.conversations[i].sender == messages[2]: self.conversations[i].addMessages(myMessage) found = 'true' self.conversations[i].lastMessageId += 1 i += 1 if found == 'false': newConversation = Conversation() newConversation.addMessages(myMessage) if myMessage.sender == username: newConversation.sender = myMessage.receiver else: newConversation.sender = myMessage.sender self.conversations.append(newConversation) self.lastConversationId += 1 messageCursor.close() messageTableConnection.close() except messageTableConnection.Error as Error: print(Error) return self
def updateAndDeleteMessages(self,messageId,username): try: messageTableConnection = getConnection(); messageCursor = messageTableConnection.cursor() messageCursor.execute("""SELECT * FROM MESSAGETABLE WHERE messageId=%s;""",(messageId,)) dbData = messageCursor.fetchone() if dbData[1] == username: messageCursor.execute("""DELETE FROM MESSAGETABLE WHERE messageId=%s;""",(messageId,)) else: newContent = 'deleted' messageCursor.execute("""UPDATE MESSAGETABLE SET status=%s WHERE messageId=%s;""",(newContent,messageId)) messageTableConnection.commit() except messageTableConnection.Error as Error: print(Error) messageTableConnection.close()
def getFriends(self,username): try: self.lastFriendId = 0 self.myFriends = [] friendTableConnection = getConnection(); friendCursor = friendTableConnection.cursor() friendCursor.execute("""SELECT * FROM FRIENDSTABLE WHERE user_id=%s;""",(username,)) friendTableConnection.commit() dbData = friendCursor.fetchall() if dbData != None: for friends in dbData: myFriend = Friend() myFriend.friendId = friends[0] myFriend.userName = friends[1] myFriend.friendUsername = friends[2] if friends[3] != 'blocked2': if friends[3] == 'blocked1': myFriend.friendStatus = 'blockedByMe' else: myFriend.friendStatus = friends[3] self.myFriends.append(myFriend) self.lastFriendId += 1 friendCursor.execute("""SELECT * FROM FRIENDSTABLE WHERE firends_id=%s;""",(username,)) friendTableConnection.commit() dbData = friendCursor.fetchall() if dbData != None: for friends in dbData: myFriend = Friend() myFriend.friendId = friends[0] myFriend.userName = friends[2] myFriend.friendUsername = friends[1] if friends[3] != 'blocked1': if friends[3] == 'blocked2': myFriend.friendStatus = 'blockedByMe' else: myFriend.friendStatus = friends[3] self.myFriends.append(myFriend) self.lastFriendId += 1 friendCursor.close() friendTableConnection.close() except friendTableConnection.Error as Error: print(Error) return self
def getUserFromDb(username): conn = getConnection() cursor = conn.cursor() try: cursor.execute(""" SELECT * FROM USERTABLE WHERE username= %s;""", (username, )) conn.commit() dbData = cursor.fetchone() User.userId = dbData[0] User.username = dbData[1] User.password = dbData[2] User.email = dbData[3] User.name = dbData[4] User.surname = dbData[5] cursor.close() conn.close() return User except conn.Error as error: print(error) return 'Error'
def addLocation(self, userLocation): newUserLocation = UserLocation() self.lastLocationId+= 1 newUserLocation.locationId = self.lastLocationId userLocation.locationId=self.lastLocationId newUserLocation.userName = userLocation.userName newUserLocation.mapInfo = userLocation.mapInfo newUserLocation.locationLabel = userLocation.locationLabel newUserLocation.lat = userLocation.lat newUserLocation.lng = userLocation.lng self.myLocations.append(newUserLocation) try: userMapConnection = getConnection(); userMapCursor = userMapConnection.cursor() userMapCursor.execute("""INSERT INTO USERMAPTABLE (userMap_id,user_id,mapInformation,locationLabel,lat,lng) VALUES(%s,%s,%s,%s,%s,%s);""", (userLocation.locationId, userLocation.userName, userLocation.mapInfo,userLocation.locationLabel,userLocation.lat, userLocation.lng )) userMapConnection.commit() userMapCursor.close() userMapConnection.close() except userMapConnection.Error as userMapError: print(userMapError)