CODEWITHSUNDEEP
pythonmysqlsql-updatepymysql
MaxS
MaxS

Reputation: 1028

Pymysql UPDATE with %s rises unknown error

I try to update a line in my sql database with pymysql. To make the whole thing more pythonic I define the variables and position to update with %s. This has worked for one variable, however the other is rising an error i don't understand. This is my code:

with connection.cursor() as cursor:
    #sql = "update tf_data set doi=\'"+str(DOI)+"\' where tf_data_id="+str(tf_data_id)+";"
    sql = "update tf_data set doi=%(DOI)s where tf_data_id=%(data_id)s"
    DOI=DOI
    data_id = str(tf_data_id)
    cursor.execute(sql)

Connection was defined at the beginning of the script and has already worked. The variables are:

DOI= '10.1371/journal.pone.0151666'

and tf_data_id = 1
if I execute, what I think the command should do in mysql directly it workes:

update tf_data set doi='10.1371/journal.pone.0151666' where tf_data_id='1';

Can someone please tell me, what the problem in this is?

Upvotes: 0

Views: 681

Answers (1)

kvorobiev
kvorobiev

Reputation: 5070

You need just

sql = "update tf_data set doi='%s' where tf_data_id='%s'" % (str(DOI), str(tf_data_id))

or you could use format() method

sql = "update tf_data set doi='{DOI}' where tf_data_id='{data_id}'".format(DOI=DOI, data_id= tf_data_id)

Upvotes: 1

Related Questions