What is the alternative of string format in python

Question

I have below python code as sql query to update a record in table:

query = "UPDATE mytable SET data='{}' where id='{}'".format(value, id)

This updated the record in table. Now I have another query which include datetime:

send_dt = datetime.utcnow()
query = "UPDATE mytable SET data='{}', send_datetime='{}' where id='{}'".format(value, send_dt, id)

Now because I have include send_dt inside format, it is making the type datetime to string and thus I am getting below error:

Conversion failed when converting date and/or time from character string.

How can I update my query so that datetime remains datetime and not string. Thanks

Please do not use string formatting to prepare queries! This is vulnerable to malicious and erroneous input. — MisterMiyagi, Jan 23 '21 at 15:00

score -1 · Accepted Answer · answered Jan 23 '21 at 14:56

-1

use to_date function to convert into date format. Somthing like this.

send_dt = datetime.utcnow()
query = "UPDATE mytable SET data='{}', send_datetime=to_date('{}','YYYY-MM-DD hh24:mi:ss.FF9') where id='{}'".format(value, str(send_dt), id)

or instead of calculating the date use the predefined function in the sql.

query = "UPDATE mytable SET data='{}', send_datetime=sysdate where id='{}'".format(value, id)

answered Jan 23 '21 at 14:56

Anirban Banerjee

145
1
9

`'to_date' is not a recognized built-in function name` – S Andrew Jan 23 '21 at 15:02
to_date is valid function for oracle db. Which db you are using. – Anirban Banerjee Jan 23 '21 at 15:05
I am using azure sql – S Andrew Jan 23 '21 at 15:06
Use convert instead of timestamp https://stackoverflow.com/questions/45074493/use-to-date-in-sql-server-2012#:~:text=3%20Answers&text=CAST()%20or%20CONVERT(),when%20they%20return%20null%20instead. – Anirban Banerjee Jan 23 '21 at 15:15

What is the alternative of string format in python

1 Answers1