CODEWITHSUNDEEP
pythonmysqlpython-2.7tkintermysql-python
Gungor Budak
Gungor Budak

Reputation: 150

Retrieving New Rows of MySQL Database without Restarting App in Python

I created a chat app in PHP using jQuery/AJAX. And now I'm trying to create a Python (2.7.3) app with GUI (Tkinter) that will be used to view and administer chats.

I have a MySQL database that stores user and chat information.

On the browser, by using setInterval and AJAX calls, I can get new messages without page refresh.

In Python app, I used "after" to call a function to retrieve the information like setInterval. I think it's working. However, I cannot get new entries that I submit on the browser.

How can I get newly submitted entries (after the Python app is started) without restarting the app?

I'm using MySQLdb module to access and change the database and I'm working on localhost.

As an example how I try to do it, I'm posting my get_messages function:

def get_messages(sohbet_id, messages_name):
    messages_name.delete(1.0, END) # Remove the content of Text widget
    sql = "SELECT *
        FROM ileti
        WHERE sohbet_id='" + str(sohbet_id) + "'
        ORDER BY created ASC"
    cursor.execute(sql)
    result = cursor.fetchall() # Fetch all the chat info
    chat = ""
    for i in result:
        name = chatter_name(i[1])
        time = datetime.fromtimestamp(i[4]).strftime("%H:%M:%S")
        chat += time + " " + str(name) + ": " + str(i[3]) + "\n" # Create a string of chat messages
    messages_name.insert(END, chat) # Insert all the chat messages to the Text widget
    messages_name.yview(END) # Scroll it down so that the latest message can be seen
    messages_name.after(1000, lambda: get_messages(sohbet_id, messages_name)) # Run this again after 1 second
    return

Edit: A little more information: I create widget in functions actually. When the app starts, only the main frame is there and I call a function which creates the first tab (summary of all chats) and then that function calls another function that opens new tabs (individual) chats. In those individual chat tabs I have this get_messages function to query for the chat messages that belong to that chat (or tab). In the above function messages_name is actually a Text widget. I pass it to the function because I'm modifying it in that function (I couldn't find any other way). When the get_messages is called no new rows appear. The only way to get them is to restart the app. It's like when the app starts it gets all the data from database in that instance and that's all. The only thing I know, I will need something like PHP & jQuery/AJAX way of reaching database and getting rows without refresing page.

Upvotes: 1

Views: 1921

Answers (1)

Gungor Budak
Gungor Budak

Reputation: 150

Connecting to database in a class with methods to get and insert data solved my problem.

That's the class:

class DBConnection(object):

    def __init__(self, DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME):
        self.host = DB_HOST
        self.port = DB_PORT
        self.name = DB_NAME
        self.user = DB_USER
        self.password = DB_PASSWORD
        self.con = None

    def connect_db(self):
        if self.con is None:
            self.con = MySQLdb.connect(host = self.host,
                                        port = self.port,
                                        db = self.name,
                                        user = self.user,
                                        passwd = self.password)
            self.con.set_character_set("utf8")
        return self.con

    def fetch_db(self, query):
        self.query = query
        self.cursor = self.con.cursor()
        self.cursor.execute("SET NAMES utf8;")
        self.cursor.execute("SET CHARACTER SET utf8;")
        self.cursor.execute("SET character_set_connection=utf8;")
        self.cursor.execute(self.query)
        self.result = self.cursor.fetchall()

        return self.result

    def insert_db(self, query):
        self.query = query
        self.cursor = self.con.cursor()
        self.cursor.execute("SET NAMES utf8;")
        self.cursor.execute("SET CHARACTER SET utf8;")
        self.cursor.execute("SET character_set_connection=utf8;")
        self.cursor.execute(self.query)
        self.con.commit()

        return

That's how I connect (before mainloop):

DBC = DBConnection('localhost',3306,'root','','sohbet')
con = DBC.connect_db()

And that's how I do queries:

result = DBC.fetch_db("SELECT * FROM ileti WHERE sohbet_id='" + str(sohbet_id) + "' ORDER BY created ASC")

However, I need to check the way of doing the query as I told by mmgp.

Upvotes: 1

Related Questions