MaxS MaxS - 6 months ago 12
Python Question

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?

Answer

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)