psycopg2 / psycopg2.DataError: invalid input syntax for type

Discussion in 'Python' started by ASh, Mar 30, 2010.

  1. ASh

    ASh Guest

    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"
     
    ASh, Mar 30, 2010
    #1
    1. Advertising

  2. On Mar 30, 2010, at 4:47 PM, ASh 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 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
     
    Philip Semanchuk, Mar 30, 2010
    #2
    1. Advertising

  3. On Tue, 30 Mar 2010 13:47:42 -0700 (PDT)
    ASh <> 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"))


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

    --
    D'Arcy J.M. Cain <> | Democracy is three wolves
    http://www.druid.net/darcy/ | and a sheep voting on
    +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
     
    D'Arcy J.M. Cain, Mar 30, 2010
    #3
  4. ASh

    ASh Guest

    On Mar 31, 12:26 am, Philip Semanchuk <> wrote:
    > On Mar 30, 2010, at 4:47 PM, ASh 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 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
     
    ASh, Mar 30, 2010
    #4
  5. ASh

    ASh Guest

    On Mar 31, 12:50 am, "D'Arcy J.M. Cain" <> wrote:
    > On Tue, 30 Mar 2010 13:47:42 -0700 (PDT)
    >
    > ASh <> 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"))

    >
    > Put single quotes around the first %s in the query.
    >
    > --
    > D'Arcy J.M. Cain <>         |  Democracy is three wolveshttp://www.druid.net/darcy/               |  and a sheep voting on
    > +1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


    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...
    ^
     
    ASh, Mar 30, 2010
    #5
  6. ASh

    Steve Holden Guest

    D'Arcy J.M. Cain wrote:
    > On Tue, 30 Mar 2010 13:47:42 -0700 (PDT)
    > ASh <> 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"))

    >
    > 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
    --
    Steve Holden +1 571 484 6266 +1 800 494 3119
    See PyCon Talks from Atlanta 2010 http://pycon.blip.tv/
    Holden Web LLC http://www.holdenweb.com/
    UPCOMING EVENTS: http://holdenweb.eventbrite.com/
     
    Steve Holden, Mar 31, 2010
    #6
  7. On Tue, 30 Mar 2010 15:46:12 -0700 (PDT)
    ASh <> wrote:
    > > >             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.

    >
    > 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'Arcy J.M. Cain <> | Democracy is three wolves
    http://www.druid.net/darcy/ | and a sheep voting on
    +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
     
    D'Arcy J.M. Cain, Mar 31, 2010
    #7
  8. On Tue, 30 Mar 2010 17:50:56 -0400, "D'Arcy J.M. Cain" <>
    declaimed the following in gmane.comp.python.general:

    >
    > 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; &nbsp;&nbsp;&nbsp; <a href="HTTP://wlfraed.home.netcom.com/" eudora="autourl">HTTP://wlfraed.home.netcom.com/<br>
    </a></body>
    </html>
     
    Dennis Lee Bieber, Mar 31, 2010
    #8
  9. 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 <> a écrit :

    >
    > On Mar 30, 2010, at 4:47 PM, ASh 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 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
    >
     
    Michael Ricordeau, Mar 31, 2010
    #9
  10. On Mar 31, 3:10 am, "D'Arcy J.M. Cain" <> wrote:
    > On Tue, 30 Mar 2010 15:46:12 -0700 (PDT)
    >
    > ASh <> wrote:
    > > > >             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.

    >
    > > 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'Arcy J.M. Cain <>         |  Democracy is three wolveshttp://www.druid.net/darcy/               |  and a sheep voting on
    > +1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


    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...
    ^
     
    Anton Shishkov, Mar 31, 2010
    #10
  11. On Mar 31, 11:29 am, Michael Ricordeau <>
    wrote:
    > 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 <> a écrit :
    >
    >
    >
    >
    >
    > > On Mar 30, 2010, at 4:47 PM, ASh 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 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


    thank you for good examples (bow)
     
    Anton Shishkov, Mar 31, 2010
    #11
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dietrich Bollmann
    Replies:
    0
    Views:
    356
    Dietrich Bollmann
    Apr 22, 2008
  2. mrdrew
    Replies:
    5
    Views:
    2,747
    Dennis Lee Bieber
    Apr 5, 2010
  3. Mateusz Loskot
    Replies:
    0
    Views:
    214
    Mateusz Loskot
    Jan 11, 2012
  4. Replies:
    9
    Views:
    340
    Karim
    Jan 10, 2013
  5. Replies:
    1
    Views:
    52
    Mark Lawrence
    May 28, 2014
Loading...

Share This Page