In python, How to update variable dynamically?

Question

I have some data that I want to change(unixtime -> human date time). After then, How can I update result on mysql dynamically.

>>> print data
((1424794931452.0,), (1424794931645.0,), (1424794931821.0,), (1424794932014.0,), (1424794932189.0,)



for i in data:
    dt = datetime.fromtimestamp(i[0] // 1000)
    s = dt.strftime('%Y-%m-%d %H:%M:%S')
    sql2 = "UPDATE accelerometer SET test = "+ s +"WHERE _id="+i
    cursor.execute(sql2)
    data = cursor.fetchall()
    print (s)

this is error message.

TypeError: cannot concatenate 'str' and 'tuple' objects

I want to update those below result data on Mysql dynamically. But have some problem. What is the problem?

2015-02-24 11:22:11
2015-02-24 11:22:11
2015-02-24 11:22:11
2015-02-24 11:22:12
2015-02-24 11:22:12

"UPDATE accelerometer SET test = {} WHERE _id= {}".format(s,i)" It is working for me — Nirmal Patel, May 08 '20 at 11:40

Martijn Pieters · Accepted Answer · 2015-03-29T04:05:29.197

You are looping over tuples with an id, not a list of ids:

((1424794931452.0,), (1424794931645.0,), (1424794931821.0,), (1424794932014.0,), (1424794932189.0,)

So each i is set to one of those tuples. Extract the id, by using indexing or by adding a comma to the for loop assignment:

for i in data:
    dt = datetime.fromtimestamp(i[0] // 1000)
    s = dt.strftime('%Y-%m-%d %H:%M:%S')
    sql2 = "UPDATE accelerometer SET test = " + s + "WHERE _id=" + i[0]
    cursor.execute(sql2)

or

for i, in data:
    dt = datetime.fromtimestamp(i // 1000)
    s = dt.strftime('%Y-%m-%d %H:%M:%S')
    sql2 = "UPDATE accelerometer SET test = " + s + "WHERE _id=" + i
    cursor.execute(sql2)

You should really use SQL parameters instead of string concatenation here; you can then reuse the SQL statement:

sql2 = "UPDATE accelerometer SET test = ? WHERE _id=?"

for i, in data:
    dt = datetime.fromtimestamp(i // 1000)
    cursor.execute(sql2, (i, dt))

where I made two assumptions: that your database driver uses ? as the placeholder syntax (it could be %s instead) and that it natively supports datetime objects (most can these days), so you don't need to use datetime.strftime() to produce a string first.

The statement reuse can go further, in that the database only has to parse the query once, and only has to produce one query plan; this speeds up repeated executions.

Using SQL parameters has another very important advantage: it prevents SQL injection attacks (where an attacker adds quoting and additional SQL statements). For your specific inputs that is not so much of a concern, but should always be kept in mind.

score 0 · Answer 2 · answered Mar 29 '15 at 03:56

Your core problem is that you're trying to put together a self-sufficient string (with string concatenation in your case, problems with string interpolation are more common, but it's pretty much the same issue).

Rather, use placeholders:

sql2 = "UPDATE accelerometer SET test = %s WHERE _id= %s"

then pass the data to execute:

cursor.execute(sql2, (s, i))

which will do the right thing for you.

It is, of course, possible, though tricky, to build the right stand-alone string -- but it's still very wrong to do so, see https://xkcd.com/327/ and don't expose yourself, ever!, to SQL-injection risks: use placeholders and data in execute, instead!

The risk of SQL injection is absent *for these statements* as those input ids are almost certainly a result of a `fetchall()` call on a previous SQL statement; the biggest advantage here is the reuse. Still, SQL security can never be emphasised enough. — Martijn Pieters, Mar 29 '15 at 04:02

score 0 · Answer 3 · answered Mar 29 '15 at 04:03

Your data is stored in tuples. And the error message says that strings and tuples can't be combined as a string, as a tuple isn't a string.

Instead of:

sql2 = "UPDATE accelerometer SET test = "+ s +"WHERE _id="+i

Try:

sql2 = "UPDATE accelerometer SET test = "+ s +"WHERE _id=%f" % i

I wouldn't recommend to use %s to build the string, as that could change the format of your floating point values.

In python, How to update variable dynamically?

3 Answers3