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/
--
http://mail.python.org/mailman/listinfo/python-list
