Psycopg2 date problems: "Can't adapt"

Discussion in 'Python' started by Steve Holden, Oct 21, 2005.

  1. Steve Holden

    Steve Holden Guest

    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 FROM 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/
    Steve Holden, Oct 21, 2005
    #1
    1. Advertising

  2. Steve Holden

    Guest

    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/
    , Oct 21, 2005
    #2
    1. Advertising

  3. Steve Holden

    Steve Holden Guest

    wrote:
    > 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.
    >>

    [...]
    >>

    > 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.
    >

    Python's None is the way you communicate null values through the query
    parameterisation mechanism. You will observe that the statement with the
    None value for the date field runs fine, and the error occurs when I
    provide an actual date object.

    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/
    Steve Holden, Oct 21, 2005
    #3
    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. Matt
    Replies:
    1
    Views:
    588
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,229
    Michael Borgwardt
    May 30, 2004
  3. Replies:
    0
    Views:
    522
  4. ASh
    Replies:
    10
    Views:
    2,384
    Anton Shishkov
    Mar 31, 2010
  5. mrdrew
    Replies:
    5
    Views:
    2,690
    Dennis Lee Bieber
    Apr 5, 2010
Loading...

Share This Page