How to refresh Mysql connection?

Question

I have a program using Python + python mysql connector + Mysql which is installed in a network and uses the same database. How do I refresh a connection without restarting the program on other Machines?

The Program is installed in more than one Machine and connects to the same Mysql database which is located on a Server. The program is working properly, but, when a new data is entered or modified "SQL INSERT, UPDATE.... Statments" from one machine is not reflected in all other Machines until the program is restarted, which means that a new connection is necessary to show the new database data.

So, I would like to know how to refresh the connection without restarting the program on other machines.

Here is the sample Connection code:

import mysql.connector as mc

conn = mc.connect(host='host', user='user', passwd='passw', db='db_name', port=port_number)

cur = conn.cursor()

How to refresh This Connection while the program is running?

Martin Bozek · Accepted Answer · 2021-09-27 19:24:50Z

1

closing and reopening connection may also solve your problem, see following example in loop

import mysql.connector as mc

conn = mc.connect(host='host', user='user', passwd='passw', db='db_name', port=port_number)

while True:
    # check if youre connected, if not, connect again
    if (conn.is_connected() == False):
        conn = mc.connect(host='host', user='user', passwd='passw', db='db_name', port=port_number)

    cur = conn.cursor()
    sql = "INSERT INTO db_name.myTable (name) VALUES (%(val)s);"
    val = {'val':"Slim Shady"}
    cur.execute(sql,val)
    conn.commit()
    conn.close()

answered Sep 27, 2021 at 19:24

Martin Bozek

112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VILAZ Over a year ago

mysql-connector-python has cmd_refresh() method which refreshes the connection and fetchs the most recent data, so to refresh it I used conn.cmd_refresh(1). Thanks for your answer and sorry for later comment.

L Tyrone · Accepted Answer · 2024-08-14 00:14:23Z

1

You can reconnect the database connection. It will fix the problem.

self.mycursor.close()
self.db.reconnect()
self.mycursor = self.db.cursor(dictionary=True)

edited Aug 14, 2024 at 0:14

L Tyrone

8,36123 gold badges34 silver badges47 bronze badges

answered Aug 8, 2024 at 7:31

HaMza MuGhal

111 bronze badge

Comments

nacho · Accepted Answer · 2019-01-31 07:40:52Z

0

After inserting or updating, you should do a commit to make sure all data it's writed to the DB. Then it will be visible for everyone:

conn.commit()

answered Jan 31, 2019 at 7:40

nacho

5,3772 gold badges28 silver badges35 bronze badges

Collectives™ on Stack Overflow

How to refresh Mysql connection?

Here is the sample Connection code:

How to refresh This Connection while the program is running?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Here is the sample Connection code:

How to refresh This Connection while the program is running?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related