Preferred method of adding data to MySQL database

Question

My set-up:

MySQL server.
host running a python script.

(1) and (2) are different machines on the network. The python script generates data which must be stored in a MySQL-database.

I use this (example-)code to achieve that:

def function sqldata(date,result):
    con = mdb.connect('sql.lan', 'demouser', 'demo', 'demo')
    with con:
        cur = con.cursor()
        cur.execute('INSERT INTO tabel(titel, nummer) VALUES( %s, %s)',(date, result))

The scipt generates one data-point approx. every minute. So this means that a new connection is opened and closed every minute. I'm wondering if it would be a better idea to open the connection at the start of the script and only close it when the script terminates. Effectively leaving the connection open indefinately.

This then obviously begs the question how to handle/recover when the SQL-server "leaves" the network (e.g. due to a reboot) for a while.

While typing my question this question appeared in the "Similar Questions" section. It is, however, from 2008 and possibly outdated and the 4 answers it received seem to contradict with each other.

What are the current insights in this matter?

try frameworks like sqlalchey or peewee

taesu
– taesu

2015-09-12 13:58:29 +00:00
Commented Sep 12, 2015 at 13:58 — taesu
– taesu, Commented Sep 12, 2015 at 13:58
check sqlobject.org/SQLObject.html

Barun Sharma
– Barun Sharma

2015-09-12 14:05:39 +00:00
Commented Sep 12, 2015 at 14:05 — Barun Sharma
– Barun Sharma, Commented Sep 12, 2015 at 14:05

hexerei software · Accepted Answer · 2015-09-12 14:46:48Z

Well the referred answer is right in it's point, but maybe not answering all your questions. I can not provide a full running python script for you here, but let me explain how i would go along with it:

Rule 1: Generally most mysql functions return values, that you should always check so that you can react on unwanted behavior.

Rule 2: Open a connection at the beginning of your script and use this one and only connection throughout your script.

Obviously you could check if there is an existing connection in your sqldata function, and if not then you could open a new one to the global con object.

if not con:
    con = mdb.connect('sql.lan', 'demouser', 'demo', 'demo')

And if there is a connection already, you could check it's "up status" by performing a simple query with fixed expected result that you can check to see if the sql server is running.

if con:
    cur = con.cursor()
    returned = cur.execute('SELECT COUNT(*) FROM tabel')   
    if returned.with_rows:
        ....

Basically you could avoid this, because if you don't get a cursor back, and you check that first before using it, then you already know if the server is alive or not.

So CHECK, CHECK and CHECK. You should check everything you get back from a function to have a good error handling. Just using a connection or using a cursor without checking it first, can lead you talking to a NIL object and crashing your script.

And the last BIG HINT i can give you is to use multiple row inserts. You can actually insert hundreds of rows, if you just add the values comma seperated to your insert string:

# consider result would be filled like this
result = '("First Song",1),("Second Song",2),("Third Song",3)'

# then this will insert 3 rows with one call
returned = cur.execute('INSERT INTO tabel (titel, nummer) VALUES %s',(date, result), multi=True)

# since literally it will execute
returned = cur.execute('INSERT INTO tabel (titel, nummer) VALUES ("First Song",1),("Second Song",2),("Third Song",3)', multi=True)

# and now you can check returned for any error
if returned:
    ....

Thanks for this elaborate answer. Lot's of good tips. Again, thank you.

Collectives™ on Stack Overflow

Preferred method of adding data to MySQL database

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related