Python sending data to a MySQL DB

Question

I have a script running, updating certain values in a DB once a second. At the start of the script I first connect to the DB:

conn = pymysql.connect(host= "server",
               user="user",
               passwd="pw",
               db="db",
               charset='utf8')
x = conn.cursor()

I leave the connection open for the running time of the script (around 30min)

With this code I update certain values once every second:

query = "UPDATE missionFilesDelDro SET landLat = '%s', landLon='%s',landHea='%s' WHERE displayName='%s'" % (lat, lon, heading, mission)
x.execute(query)
conn.ping(True)

However now when my Internet connection breaks the script also crashes since It can't update the variables. My connection normally reestablishes within one minute. (the script runs on a vehicle which is moving. Internet connection is established via a GSM Modem)

Is it better to re-open every time the connection to the server prior an update of the variable so I can see if the connection has been established or is there a better way?

Jeremy Jones · Accepted Answer · 2017-12-08 12:22:44Z

2

You could just ping the connection first, instead of after the query, as that should reconnect if necessary.

Setup:

conn = pymysql.connect(host= "server",
                       user="user",
                       passwd="pw",
                       db="db",
                       charset='utf8')

and every second:

query = "UPDATE missionFilesDelDro SET landLat = '%s', landLon='%s',landHea='%s' WHERE displayName='%s'" % (lat, lon, heading, mission)
conn.ping()
x = conn.cursor()
x.execute(query)

Ref https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/connections.py#L872

It's still possible that the connection could drop after the ping() but before the execute(), which would then fail. For handling that you would need to trap the error, something similar to

from time import sleep

MAX_ATTEMPTS = 10

# every second:
query = "UPDATE missionFilesDelDro SET landLat = '%s', landLon='%s',landHea='%s' WHERE displayName='%s'" % (lat, lon, heading, mission)
inserted = False
attempts = 0

while (not inserted) and attempts < MAX_ATTEMPTS:
    attempts += 1
    try:
        conn.ping()
        x = conn.cursor()
        x.execute(query)
        inserted = True
    except StandardError: # it would be better to use the specific error, not StandardError
        sleep(10) # however long is appropriate between tries
        # you could also do a whole re-connection here if you wanted

if not inserted:
     # do something
     #raise RuntimeError("Couldn't insert the record after {} attempts.".format(MAX_ATTEMPTS))
     pass

edited Dec 8, 2017 at 12:22

answered Dec 7, 2017 at 22:39

Jeremy Jones

5,7414 gold badges23 silver badges28 bronze badges

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

2 Comments

user3439871 Over a year ago

thx for the instructions what happens if at the time of the x.execute(query) the internet crashes. Do i need to use try: except: so I can handle the error so that my script does not crash?

Jeremy Jones Over a year ago

Yes. I've my edited my answer with an example of how to do that. @user3439871

Jarvis Cochrane · Accepted Answer · 2017-12-07 22:31:19Z

I'm guessing the script fails with an exception at the line x.execute(query) when the connection drops.

You could trap the exception and retry opening the connection. The following 'pseudo-python' demonstrates the general technique, but will obviously need to be adapted to use real function, method, and exception names:

def open_connection(retries, delay):                                            
    for (x in range(0, retries)):                                               
        conn = pymysql.connection()                                             
        if (conn.isOpen()):                                                     
            return conn                                                         
        sleep(delay)                                                            
    return None                                                                 

conn = open_connection(30, 3)                                                   
x = conn.cursor()                                                               

while(conn is not None and more_data)                                                                

    # read data here                                                            

    query = ...                                                                 

    while(conn is not None):   # Loop until data is definitely saved                                                                  
        try:                                                                        
            x.execute(query)  
            break              # data saved, exit inner loop                                                      
        except SomeException:                                                       
            conn = open_connection(30,3)                      
            x = conn.cursor()

The general idea is that you need to loop and retry until either the data is definitely saved, or until you encounter an unrecoverable error.

Hm. If you're sampling or receiving data at a constant rate, but are only able to send it irregularly because of network failures, you've created a classic producer-consumer problem. You'll need one thread to read or receive the data, a queue to hold any backlog, and another thread to store the data. Fun! ;-)

Collectives™ on Stack Overflow

Python sending data to a MySQL DB

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related