Retrieving New Rows of MySQL Database without Restarting App in Python

Question

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.

Never perform SQL queries like that, in Python terms you after parametrized queries. Besides that, it is not clear how this is related to tkinter at all. After you execute that query, if you print result, do you get more rows than the ones shown in the messages_name widget ? If not, you have to first fix the way the database is being updated. — mmgp
– mmgp, Commented Feb 10, 2013 at 2:50
@mmgp 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). — Gungor Budak
– Gungor Budak, Commented Feb 10, 2013 at 3:59
@mmgp (continuing...) 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. And for the SQL query, I will look at it, thanks for pointing that out; I'm just very new to Python. — Gungor Budak
– Gungor Budak, Commented Feb 10, 2013 at 4:00
When the app start it certainly doesn't "get all the data from database", it doesn't work like that (that or you have some other very important piece of code not being shown). Since you never show how the database is updated, there is nothing else to be done here. — mmgp
– mmgp, Commented Feb 10, 2013 at 4:05
@mmgp Data is inserted to database in two ways, on the browser using PHP and in Python. When I send something in PHP, it appears on the browser, but not in Python app. However, when I send a message in Python, it appears in both. Somehow, I cannot get the data inserted on the browser. All are in the same table in database. I might post all the codes, if it's necessary. It's about 150 lines long, I didn't want to create a mess. — Gungor Budak
– Gungor Budak, Commented Feb 10, 2013 at 4:32

Gungor Budak · Accepted Answer · 2013-02-10 04:53:25Z

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.

Collectives™ on Stack Overflow

Retrieving New Rows of MySQL Database without Restarting App in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related