Python/MySQL: Select data in a for loop

Question

I apologize if it seems like I am having some trouble putting my problem into words. Basically, I am programming a messaging system where the user can send messages to other users and view their messages in an inbox. Here are some screenshots of what my database and GUI look like: https://i.stack.imgur.com/Jdm0v.jpg

This is my function for displaying the messages in the inbox:

#DISPLAY MESSAGE IN INBOX FUNCTION
def displayMessageInbox(mf, c, vm):
    recipient = config.nameInput

    cursor.execute("""SELECT CONCAT('Title: ', m.title, '\n',
                    'From: ', s.sender, '\n',
                    'Sent: ', s.timeSent)
                    FROM Message m
                    INNER JOIN SentMessage s ON m.id = s.messageID
                    WHERE s.recipient = %s""", (recipient))
    rows = cursor.fetchall()

    i = 3
    for message in rows:
        messageInfo = tk.Label(mf, text=message, bg="white", anchor=tk.W,
                               justify=tk.LEFT, width=30)
        messageInfo.grid(row=i, column=0, sticky=tk.W)
        viewReply = tk.Button(mf, text="View/Reply", anchor=tk.W,
                      command=lambda:c.show_frame(vm))
        viewReply.grid(row=i, column=1,padx=10, sticky=tk.W)
        i+=1

This is my function for displaying the message and its content after the user has clicked on "View/Reply." Note that it is selecting a specific message ID and not the one I actually want it to display. In other words, it's just placeholder.

#READ MESSAGE CONTENT FUNCTION
def readMessage(mf):
    mID = 'a6bb9c97-0f59-44ea-9f2b-759ffbac5031'

    cursor.execute("""SELECT CONCAT('From: ', sm.sender, '\n',
                'Title: ', m.title, '\n',
                'Message: ', m.content)
                FROM Message m
                INNER JOIN SentMessage sm ON m.id = sm.messageID
                WHERE m.id = %s""", (mID))
    rows = cursor.fetchone()

    message = rows[0]
    readMessageLabel = tk.Label(mf, text=message, bg="white", anchor=tk.W,
                                justify=tk.LEFT, width=100)
    readMessageLabel.grid(row=3, column=0, padx=10, sticky=tk.W)

So my problem is this... I want the user to be able to click on "view/reply" and view the corresponding message, and not just a placeholder. I don't even know how to begin solving this problem. I think that what I want to do is get the message id from a specific row in displayMessageInfo's for loop, but I'm not entirely sure. Any ideas?

Why have your formatting done in your SQL? You should (IMO) be querying your DB for all relevant info, in the first function's case the message ID, title, sender, timeSent. Your python/whatever should handle the formatting and display. Then you'll have your message ID that you can pass to your readMessage function -- which I'd argue should follow the same formatting rules. — Zippers, Apr 10 '15 at 01:11

Parfait · Accepted Answer · 2015-04-10T03:44:22.317

Obviously, mID is a static value. You need to make this variable dynamic by passing its value into the readMessage function conditional to the corresponding message after the user presses the 'View/Reply'.

While I don't know the setup of your app's interface controls but consider: 1) querying the message id, 2) save it to a variable, 3) pass it into the function. Maybe this could get you started:

def displayMessageInbox(mf, c, vm):
    recipient = config.nameInput

    cursor.execute("""SELECT m.id, CONCAT('Title: ', m.title, '\n',
                    'From: ', s.sender, '\n',
                    'Sent: ', s.timeSent) As messagetxt
                    FROM Message m
                    INNER JOIN SentMessage s ON m.id = s.messageID
                    WHERE s.recipient = %s""", (recipient))

    i = 3
    for message in cursor.fetchall():
          mID = message[0]
          messagetxt = message[1]
          ...

          if (viewReply == somevalue): # ENTER LOGIC TO CAPTURE WHICH MESSAGE USER SELECTED     
                 readMessage(mf, mID)

def readMessage(mf, mID):

    cursor.execute("""SELECT CONCAT('From: ', sm.sender, '\n',
            'Title: ', m.title, '\n',
            'Message: ', m.content)
            FROM Message m
            INNER JOIN SentMessage sm ON m.id = sm.messageID
            WHERE m.id = %s""", (mID))
    rows = cursor.fetchone()

    message = rows[0]
    ...

mID = message[0] doesn't return just the id, it returns the title, sender and timeSent as well. Is there a different way to retrieve just the id? — user2615805, Apr 10 '15 at 03:05
See above change. This will iterate across all mIDs and messages of the select query. However, you need to add some logic to only call the readMessage function corresponding to user's viewReply pick. Can you bind the message id to the viewReply button's value?. — Parfait, Apr 10 '15 at 03:47
Thanks. Sorry for the late reply. So far the only thing the viewReply button does is lead the user to a different page (which is a "ViewMessage" class on its own in the GUI module). readMessage is a function that is called in the ViewMessage class. I'll try and figure things out and let you know how things go. — user2615805, Apr 11 '15 at 01:18
Hi, just wanted to update on my situation. I used your solution in combination with this solution and it worked! http://stackoverflow.com/questions/17677649/tkinter-assign-button-command-in-loop-with-lambda — user2615805, Apr 15 '15 at 16:46

Python/MySQL: Select data in a for loop

1 Answers1