Re: psycopg2 / psycopg2.DataError: invalid input syntax for type timestamp with time zone: Options

On 31/03/10 00:53, Anton Shishkov wrote:
> Hi, please help me understand why am I getting error with this query
>
>              new_start_date = "NOW() - '29 days'::INTERVAL"
>              self.dyndb.orderdb.query('''update xxxx set creation_date
> = %s
>              where id_order = %s''', (new_start_date, "123"))
>
> ...
> psycopg2.DataError: invalid input syntax for type timestamp with time
> zone: "NOW() - '29 days'::INTERVAL"

Hi,

what is happening is that your are passing a string to psycopg2 
(new_start_date is a string) and it tries to use it as a string in your 
query. So then you get a a query that looks like this:

update xxxx set creation_date = E'NOW() - ''29 days''::INTERVAL' where 
id_order = '123'

Notice how the creation date got transformed into a string (with the 
necessary quoting).

In your case, because you know how the string will look like exactly I 
would simply go for

self.dyndb.orderdb.query('''update xxxx set creation_date = NOW() - '29 
days'::INTERVAL where id_order = %s''', ("123", ))

Cheers,
Jan