CODEWITHSUNDEEP
pythonmysqlbluetooth
Christopher
Christopher

Reputation: 788

Python Update in MySQL

I've been struggling to get this really simple query to actually work in Python. I am a complete python newb, and strings seem to be handled a lot differently than what I am accustomed to.

The query I am trying...

cur = db.cursor()
cur.execute("SELECT bluetooth_Id FROM student_Data")
data = cur.fetchall()


for row in data :
    bluetoothId=row[0]
    result=bluetooth.lookup_name(bluetoothId,timeout=5)
    print(result)
    if(result != None):
        cur.execute("UPDATE student_Data SET attendance = 1 WHERE bluetooth_Id= %s",(bluetoothId))
        print("UPDATE student_Data SET attendance = 1 WHERE bluetooth_Id= %s",(bluetoothId))

What seems to be the problem is that my actual SQL query is not correctly formatted.. I know this because that last print statement returns this

('UPDATE student_Data SET attendance = 1 WHERE bluetooth_Id= %s', 'th:is:is:my:bt:id')

and of course that id is not actually the id it returns... I didn't want to give that to you :)

I am following examples to the dot, and not getting anywhere.. my bluetooth is on, the program sees my bluetooth id, it processes through the list of ids already in my mysql table, but it isn't updating any records.

and I did check to make sure I entered my id in the mysql table correctly, so that is not the problem either!

Update

I was able to get the correct MySQL query created using this:

cur.execute("UPDATE student_Data SET attendance = 1 WHERE bluetooth_Id = '%s"%(bluetoothId)+"'")

which creates

UPDATE student_Data SET attendance = 1 WHERE bluetooth_Id = '11:11:11:11:11:11'

but the MySQL table still isn't updating correctly.. I'll have to look into seeing why that is...

Upvotes: 1

Views: 121

Answers (2)

Christopher
Christopher

Reputation: 788

The solution was this: 

cur.execute("UPDATE student_Data SET attendance = 1 WHERE bluetooth_Id = '%s"%(bluetoothId)+"'")

and then I had to add 

db.commit()

After the execute, in order to actually commit the changes to the MySQL table.

Thanks all for the help:)

Python mySQL Update, Working but not updating table

Upvotes: 1

Ricardo Cid
Ricardo Cid

Reputation: 239

You need to do this:

cur.execute("UPDATE student_Data SET attendance = 1 WHERE bluetooth_Id= %s"%(bluetoothId))

If you are doing substitution, you need a % instead of a comma

Upvotes: 0

Related Questions