MaxS MaxS - 2 years ago 53
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

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"
data_id = str(tf_data_id)

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

DOI= '10.1371/journal.pone.0151666'

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 Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download