Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > Psycopg2 date problems: "Can't adapt"

Reply
Thread Tools

Psycopg2 date problems: "Can't adapt"

 
 
Steve Holden
Guest
Posts: n/a
 
      10-21-2005
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, 8)

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, 8)
>>>


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/

 
Reply With Quote
 
 
 
 
bonono@gmail.com
Guest
Posts: n/a
 
      10-21-2005
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, 8)

> 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, 8)
> >>>

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


 
Reply With Quote
 
 
 
 
Steve Holden
Guest
Posts: n/a
 
      10-21-2005
http://www.velocityreviews.com/forums/(E-Mail Removed) 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, 8)

>>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, 8)
>> >>>

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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
psycopg2 / psycopg2.ProgrammingError: syntax error at or near"E'mytable'" mrdrew Python 5 04-05-2010 05:27 PM
psycopg2 / psycopg2.DataError: invalid input syntax for type ASh Python 10 03-31-2010 10:50 AM
Psycopg2 date problems: "Can't adapt" mvanier@cs.caltech.edu Python 0 01-12-2006 01:57 AM
Date, date date date.... Peter Grison Java 10 05-30-2004 01:20 PM
Given a date, how to find the beginning date and ending date of that week Matt ASP .Net 1 11-08-2003 09:14 PM



Advertisments