Fwd: error in cur.mogrify line

From: Octavi Fors <octavi(at)live(dot)unc(dot)edu>
To: psycopg(at)postgresql(dot)org
Subject: Fwd: error in cur.mogrify line
Date: 2015-03-27 14:30:12
Message-ID: CAJEYUR9XWY8LosTuhpJc=J1ZcAWdJbu73JA2nmrnkRcGi27Acg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: psycopg

Dear psycopg2 users/developers,

I'm trying to use psycopg2.2.6 to insert some columns from the attached
FITS binary table file 'img_20130109_001427_2_10.0s_c1_calib.cat' into a
postgresql 9.2 server table called 'referencecat'.

To do so I'm using the attached 'insert.py' script.
As you see I'm reading the FITS table with ATpy 0.9.7
<https://atpy.readthedocs.org/en/latest/> module.
From there I convert the columns of tbl2 I want to insert into a
numpy.array (L36), I connect to the database (password is fake but doesn't
matter for the purpose of my question :), and then I get the error in
around cur.mogrify in L54:

Traceback (most recent call last):
File "insert.py", line 54, in <module>
args_str = ','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s)", x) for x
in targets)
File "insert.py", line 54, in <genexpr>
args_str = ','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s)", x) for x
in targets)
TypeError: not all arguments converted during string formatting

The 'referencecat' table definition is the following:

CREATE TABLE referencecat
(
ref_id bigserial NOT NULL, -- SEXtractor running reference object number.
ref_alphawin_j2000 double precision, -- Windowed right ascension (J2000).
ref_deltawin_j2000 double precision, -- Windowed declination (J2000).
ref_object character varying(18), -- Source identificator from (RA,DEC):
JHHMMSSSS+DDMMSSSS
ref_snr real, -- SNR (flux_auto / fluxerr_auto)
ref_elongation real, -- A_IMAGE/B_IMAGE
ref_fwhm_image real, -- FWHM assuming a gaussian core.
ref_flags character varying(3), -- Extraction flags.
CONSTRAINT pk_ref_id PRIMARY KEY (ref_id)
)
WITH (
OIDS=FALSE
);

I've been struggling and googling searching for a solution but
unsuccessfully :(

Could anybody please provide some light into this?
Any advise/help would be more than welcome.

Thanks a lot in advance,

Octavi.

PS: I read here
<http://stackoverflow.com/questions/8134602/psycopg2-insert-multiple-rows-with-one-query>
from ant32 that with the kind of arg_str and cur.execute (with a for x loop
inside) the insert speed can be boosted up to 100x. This is why I'm trying
to replicate the same approach.

--
Octavi Fors
Postdoctoral Research Associate
Department of Physics and Astronomy
The University of North Carolina at Chapel Hill
CB #3255, #157 Phillips Hall
Chapel Hill, NC 27599
Office: (919) 962-3606
Fax: (919) 962-0480
http://octavi.web.unc.edu/

Attachment Content-Type Size
img_20130109_001427_2_10.0s_c1_calib.cat application/vnd.ms-pki.seccat 1.1 MB
insert.py text/x-python 2.2 KB

Responses

Browse psycopg by date

  From Date Subject
Next Message Adrian Klaver 2015-03-27 15:23:49 Re: Fwd: error in cur.mogrify line
Previous Message Adrian Klaver 2015-03-12 00:03:49 Re: segfault on psycopg2 on CentOS