0

I'm trying to insert NOW() as one of the values using cursor.execute() in python. Problem is, the value is treated as string, and I get an error.

What I'm doing:

cursor.execute(cursor,"UPDATE t SET d=%s",'NOW()')

The query I want:

UPDATE t SET d=NOW()

The query I get:

UPDATE t SET d="NOW()"

Is there any way to fix this, so that NOW() is inserted in the query exactly as intended? I'd rather not use workarounds like datatime.now() due to time zone issues.

Thank you!

Sasha
  • 1,032
  • 9
  • 14
  • 1
    why not just write `update ... set d= now()` directly? Yu don't need to bind/interpolate a mysql function call, because binding turns that `now()` into a string with the letters `n`, `o`, etc..., not a function call. – Marc B May 25 '16 at 17:07
  • @MarcB thanks for the comment. I'm not sure I understand your question. Why use execute? Because it prevents sql injection. The query in the question is just an example, in reality the queries are much complex and I have my own wrapper that updates an entire object. The question you pointed to as duplicate is not the same, as it simply talks about SQL syntax. – Sasha May 25 '16 at 17:20
  • what sql injection? you're not using any "outside" data. it is impossible for that query to be injected, unless you explicitly make it happen. – Marc B May 25 '16 at 17:37
  • @MarcB as I mentioned, this is a trivial example to illustrate my problem. The actual use case is much more complex. Imagine I want to write a method that takes 2 key-value objects, and translates one to the WHERE clause, and another to the column values. – Sasha May 25 '16 at 17:45
  • @MarcB So basically I want to have a method: `def update(table_name,key_values_object,key_value_filters)` and use it like this: `update("my_table",{"email":user_email,"date":"NOW()"},{"role":"admin"})` so that it would be translated to: `UPDATE my_table SET email="x@test.com",date=NOW() WHERE role="admin"` Does that make sense? – Sasha May 25 '16 at 17:53
  • can't do it with placeholders. function calls are NOT passed through the placeholder/binding process as anything other than a string, which means they're not functions anymore. – Marc B May 25 '16 at 18:32
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/112957/discussion-between-sasha-and-marc-b). – Sasha May 25 '16 at 19:33

0 Answers0