psycopg2 / psycopg2.DataError: invalid input syntax for type

A

ASh

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"
 
P

Philip Semanchuk

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 Anton,
It sounds to me like the problem is with your SQL rather than with
psycopg2 or Python. Try the query directly in Postgres -- does it work
there? If so, then your next step should be to ask on the psycopg2
mailing list that Google can find for you.

Good luck
Philip
 
D

D'Arcy J.M. Cain

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"))

Put single quotes around the first %s in the query.
 
A

ASh

Hi Anton,
It sounds to me like the problem is with your SQL rather than with  
psycopg2 or Python. Try the query directly in Postgres -- does it work  
there? If so, then your next step should be to ask on the psycopg2  
mailing list that Google can find for you.

Good luck
Philip

sql itself is correct
 
A

ASh

Put single quotes around the first %s in the query.

Tried like you said, got this error:

psycopg2.ProgrammingError: syntax error at or near "NOW"
LINE 1: update orderdb.orders set creation_date = 'E'NOW() - ''29
da...
^
 
S

Steve Holden

D'Arcy J.M. Cain said:
Put single quotes around the first %s in the query.
And in future please tell us exactly what error you are trying to
explain by quoting the traceback exactly.

regards
Steve
 
D

D'Arcy J.M. Cain

Tried like you said, got this error:

psycopg2.ProgrammingError: syntax error at or near "NOW"
LINE 1: update orderdb.orders set creation_date = 'E'NOW() - ''29
da...

Right. I misread it. Please show us the exact error that you get with
the original code.
 
D

Dennis Lee Bieber

Put single quotes around the first %s in the query.

If a purportedly db-api query requires quoting of placeholder
fields, I'd consider it to be flawed.
<html>
<body>
-- <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Wulfraed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dennis Lee Bieber&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AF6VN<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (e-mail address removed)&nbsp;&nbsp;&nbsp; <a href="HTTP://wlfraed.home.netcom.com/" eudora="autourl">HTTP://wlfraed.home.netcom.com/<br>
</a></body>
</html>
 
M

Michael Ricordeau

Hi

You cannot add 'NOW() - '29 days'::INTERVAL' as a query because cursor.execute() will try to mogrify it.

You can do :
import datetime
idays = psycopg2.extensions.adapt(datetime.timedelta(days=29))
self.dyndb.orderdb.query('update xxxx set creation_date=(NOW() - %s) where id_order=%s', idays, "123"))

Or:
import datetime
interval = datetime.datetime.now() - datetime.timedelta(days=29)
self.dyndb.orderdb.query('update xxxx set creation_date=%s where id_order=%s', (interval, "123"))
# But in this case current date/time is not evaluated from postgresql server but only from python env ... this may cause some bugs

You may also try to add an interval type with psycopg2.extensions.INTERVAL (I never played with it)



Le Tue, 30 Mar 2010 17:26:51 -0400,
 
A

Anton Shishkov

Right.  I misread it.  Please show us the exact error that you get with
the original code.

Error:
cursor.execute(sql, params)
File "/opt/local/lib/python2.5/site-packages/psycopg2/extras.py",
line 118, in execute
return _cursor.execute(self, query, vars, async)
psycopg2.ProgrammingError: syntax error at or near "NOW"
LINE 1: update orderdb.orders set creation_date = 'E'NOW() - ''29
da...
^
 
A

Anton Shishkov

Hi

You cannot add 'NOW() - '29 days'::INTERVAL' as a query because cursor.execute() will try to mogrify it.

You can do :
  import datetime
  idays = psycopg2.extensions.adapt(datetime.timedelta(days=29))
  self.dyndb.orderdb.query('update xxxx set creation_date=(NOW() - %s) where id_order=%s', idays, "123"))

Or:
  import datetime
  interval = datetime.datetime.now() - datetime.timedelta(days=29)
  self.dyndb.orderdb.query('update xxxx set creation_date=%s where id_order=%s', (interval, "123"))
  # But in this case current date/time is not evaluated from postgresql server but only from python env ... this may cause some bugs    

You may also try to add an interval type with psycopg2.extensions.INTERVAL (I never played with it)

Le Tue, 30 Mar 2010 17:26:51 -0400,
Philip Semanchuk <[email protected]> a écrit :

thank you for good examples (bow)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top