Mysql prepared statements not getting replaced with values in python

Question

Have tried with both (?) and (%s) but doesn't seem to be working. Where am I gong wrong?

def update(phone,name):
    conn = sqlite3.connect(db)
    print ("\nOpened database for updates successfully")

    sql = "UPDATE VARUN set PHONE = %s where NAME= %s "
    print (sql)
    conn.execute(sql,(phone,name))
    '''
    conn.execute("UPDATE VARUN set PHONE = (?) where NAME= (?) ",(phone,name));
    '''
    conn.commit()

----- calling function ----

contactlist[selection()]=[nameVar.get(), phoneVar.get()]
updt = (contactlist[selection()])
name = (updt[0])
phone = (updt[1])
print (name,phone)

try:
    update(name,phone)
except:
    tkinter.messagebox.showwarning("cannot be blank")
else:

    setList ()
    saveContact()

`conn.execute('UPDATE VARUN set PHONE = ? where NAME = ?', (phone, name))` should work fine. — poke, Jul 31 '17 at 23:47
@poke Thanks for pointing that out. But it still doesn't update in the database. Can let know how can i debug this? All other operations like insert are working fine, so there is problem with the database. — Varun, Aug 01 '17 at 00:51

score 2 · Accepted Answer · answered Jul 31 '17 at 23:47

2

First of all, you are using a bare except clause which prevents you from seeing what errors are thrown from the function. Remove it and see how it fails.

And, you need to have ? placeholders without the surrounding parenthesis:

def update(phone, name):
    conn = sqlite3.connect(db)
    cursor = conn.cursor()
    print ("\nOpened database for updates successfully")

    sql = "UPDATE VARUN set PHONE = ? where NAME= ?"
    cursor.execute(sql, (phone, name))
    conn.commit()

answered Jul 31 '17 at 23:47

alecxe

462,703
120
1,088
1,195

Thanks for pointing that out. I'm not getting any errors after changing it but it still doesn't update in the database. Can let know how can i debug this? All other operations like insert are working fine, so there is problem with the database. – Varun Aug 01 '17 at 00:50
@Varun well, make sure you are actually updating the same table in the same database as you are checking the results in. I've also noticed that the title says about mysql but there is sqlite3 connection code posted..thanks. – alecxe Aug 01 '17 at 00:54
I cross checked and everything seems to be fine. But somehow it doesn't insert. Is there a way to print the query being executed? I tried converting it to string and print but it still prints as object. – Varun Aug 01 '17 at 01:58

Mysql prepared statements not getting replaced with values in python

1 Answers1