Psycopg2 date problems: "Can't adapt"

[bonono at gmail.com]

Is "None" a valid value for SQL ? Or should it be NULL ? May be it is
because your input is NULL which is being converted to None in python
but haven't been converted back to NULL on its way out.

Steve Holden wrote:
> I'm trying to copy data from an Access database to PostgreSQL, as the
> latter now appears to work well in the Windows environment. However I'm
> having trouble with date columns.
>
> The PostgreSQL table receiving the data has the following definition:
>
> CREATE TABLE Lines (
>    LinID SERIAL PRIMARY KEY,
>    LinDate   TIMESTAMP(0),
>    LinQty    INTEGER,
>    LinPrdID  INTEGER ,
>    LinPrice  NUMERIC(8,2),
>    LinInvoice INTEGER)
>
> Here's the problem in a nutshell:
>
>  >>> d
> <DateTime object for '2003-10-02 00:00:00.00' at af9be0>
>  >>> ocurs.execute("DELETE FROMI Lines")
>  >>> osql
> 'INSERT INTO Lines(LinID, LinDate, LinQty, LinPrdID, LinPrice,
> LinInvoice) VALUES(%s, %s, %s, %s, %s, %s)'
>  >>> ocurs.execute(osql, (1, d, 1, 1, 12500.0, 88))
> Traceback (most recent call last):
>    File "<string>", line 1, in <string>
> psycopg.ProgrammingError: can't adapt
>  >>> ocurs.execute(osql, (1, None, 1, 1, 12500.0, 88))
>  >>>
>
> Since the date value's the only difference between the two, I deduce
> it's causing the problem.
>
> I'd rather not have to manipulate the data (in other words, I'd rather
> just change the definition of the receiving table to avoid the error if
> possible), as the copying operation attempts to be table-independent. It
> currently reads:
>
> for tbl, cols in d.items():
>      print "Copying", tbl
>      dsql = "DELETE FROM %s" % tbl
>      ocurs.execute(dsql)
>      isql = "SELECT %s FROM %s" % (", ".join(cols), tbl)
>      osql = "INSERT INTO %s(%s) VALUES(%s)" % (
>          tbl, ", ".join(cols), ", ".join("%s" for c in cols))
>      print isql, '\n', osql
>      icurs.execute(isql)
>      for row in icurs.fetchall():
>          ocurs.execute(osql, row)
>
> Though until I started stepping through the data row by row the last two
> lines were replaced by
>
>      ocurs.executemany(osql, icurs.fetchall())
>
> Who can help me past this little sticking point?
>
> regards
>   Steve
> --
> Steve Holden       +44 150 684 7255  +1 800 494 3119
> Holden Web LLC                     www.holdenweb.com
> PyCon TX 2006                  www.python.org/pycon/