How can I communicate with other client connections on a MySQL server using python?
Multi tool use
How can I communicate with other client connections on a MySQL server using python?
I have multiple computers running python applications, each using the same MySQL server. Each of the applications contains a tkinter GUI that allows editing of a set of data (corresponding to data in a table in the MySQL server). Whenever the data is updated one machine (and in turn updated on the MySQL server), I would like the other machines to be prompted to update there displayed data by pulling from the server. I know I could simply have the applications self-update after a given interval, but I would prefer to only update when there is new data to pull.
How should I go about this?
2 Answers
2
This isn't something you can do with MySQL.
There is no provision in the client/server protocol for the server to spontaneously emit messages to a client, so there is no mechanism in MySQL that allows connected clients to be notified of events via a push notification.
Interesting. So just to clarify, there is no place for MySQL triggers as @ktzd commented on the original question?
– SamBG
Jul 1 at 15:41
Not really. Triggers allow an insert/update/delete to a row in a table to perform an action such as executing another query, but the problem is that the query inside the trigger doesn't get you any closer to notifying other threads. Amazon Aurora (MySQL compatible) allows a trigger to invoke an external Lambda function, which could send notifications via an external system (SNS, SQS, IoT) but then your clients would need to receive such notifications via some other channel (such as subscribing to SNS or listening on an IoT socket), which is certainly possible but significantly more complex.
– Michael - sqlbot
Jul 1 at 17:34
That's very helpful, thanks. I'll probably opt to simply put in a refresh button in place of any automatic polling.
– SamBG
Jul 1 at 19:34
I would suggest that your other client to do a long polling to your database and return a response if there are any feedback.
Thanks, this seems like the solution. Do you have any resources I could use to learn how to do this (perhaps specifically with Python connected to MySQL)? I haven't been able to find any.
– SamBG
Jul 1 at 16:03
There's really no such thing as "long polling" MySQL. If you have a solution, please explain how it's implemented... otherwise, I'd suggest you are proposing something that isn't properly called "long polling" ...perhaps intermittent or periodic polling... which eventually does not scale.
– Michael - sqlbot
Jul 1 at 17:41
Hi SamBG and Michael, sorry if I caused misunderstand here. I means the client (Python) to execute polling periodically (web service or http request) to check if there any record in the database.
– obl0702
Jul 2 at 13:01
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
sounds like you need to dig into MySql Triggers, maybe this answer would help stackoverflow.com/questions/17336804/…
– kztd
Jul 1 at 2:37