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/ 2 5216
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/ bo****@gmail.com 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/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Askari |
last post by:
Yesterday, ALL code in python work and nothing when I close(finish) a code.
Today, when I close, some raise this windows error :
Instruction at "0x00FC3D70" use memory address "0x00000000". Can't...
|
by: Robin Tucker |
last post by:
I have some code that dynamically creates a database (name is @FullName) and
then creates a table within that database. Is it possible to wrap these
things into a transaction such that if any one...
|
by: rhat |
last post by:
I heard that beta 2 now makes ASP.NET xhtml compliant.
Can anyone shed some light on what this will change and it will break stuff
as converting HTML to XHTML pages DO break things. see,...
|
by: wangzhihuii |
last post by:
Hi all,
I'm really confused, can
cout<<"";
contribute anything to the routine ?!!
my programm won't work properly without this trivial sentence.
Sincerely
vivian
|
by: baumann.Pan |
last post by:
when define
char *p = " can not modify";
p ='b' ;is not allowed,
but if you declare p as
char p = "can modify";
p = 'b'; is ok?
why?
|
by: mvanier |
last post by:
There was a thread a while back dealing with an error message the
psycopg2 Postgres interface gives when trying to convert some
mxDateTime values: "can't adapt". The answer given was that psycopg2...
|
by: vl106 |
last post by:
A static code analysis tool gave me a warning on
if (ptr && ptr->data) { ... }
I assumed the tool doesn't get the "short circuit behaviour" in
the if statement.
But a collegue said it may...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |