How to update MySQL cell with variable in MySQLDB

Question

import serial
import MySQLdb

dbConn = MySQLdb.connect("localhost","root”,”test”,”ISEF_DB") or die ("Could not connect to database")
cursor = dbConn.cursor()

device = '/dev/ttyACM0'

try:
    print "Trying...",device
    arduino = serial.Serial(device, 250000)
except:
    print "Failed to connect on",device

try:
    data = arduino.readline() #read data
#   pieces = data.split("\t")
    print "The data is:",data

    try:
        cursor.execute("UPDATE `ISEF_DB`.`attendance` SET `present`='1' WHERE `id` = 'data'")
        dbConn.commit()
        cursor.close()
        print "data inserted"
    except MySQLdb.IntegrityError:
        print "Failed to insert data"
    finally:
        cursor.close()
        print "done"
except:
    print "Failed to get data"

When I execute this code, it seems like it is working, though it does not update the present cell to 1. If I change 'data' to any known cell id, present is updated to 1. So, how do I update a cell from a variable?

score 0 · Answer 1 · answered Nov 24 '15 at 04:39

0

Something like this should work:

cursor.execute("UPDATE `ISEF_DB`.`attendance` SET `present`='1' WHERE `id` = '{}'".format(data))

This "inserts" the value of data into your SQL string.

answered Nov 24 '15 at 04:39

101

8,514
6
43
69

score 0 · Answer 2 · edited May 23 '17 at 12:15

0

You must use string formatting to substitute variable for its value

cursor.execute("UPDATE `ISEF_DB`.`attendance` SET `present`='1' WHERE `id` = '%s'" % data)

Although be warned that this leaves you susceptible to SQL Injection. To prevent that you can use exceute like so (from the docs)

cursor.execute("UPDATE `ISEF_DB`.`attendance` SET `present`='1' WHERE `id` = '%s'", (data,))

i.e. pass the values as tuples to execute. More information about avoiding SQL injection when using MySQLDB can be found in this question

edited May 23 '17 at 12:15

Community

1
1

answered Nov 24 '15 at 04:39

RedBaron

4,717
5
41
65

Those are both executing without error, however they still aren't updating the data in the database. Do the datatypes need to be something else? They currently are `CHAR(8)` for the id column and `INT(11)` for the present column. Could that be the problem? – Turner Eison Nov 24 '15 at 16:27
Are you sure ``data`` variable holds correct ID value? Maybe it has extra characters like ``\n`` or spaces? – RedBaron Nov 26 '15 at 04:41

How to update MySQL cell with variable in MySQLDB

2 Answers2