Re: Fwd: error in cur.mogrify line - Mailing list psycopg

On 03/27/2015 07:30 AM, Octavi Fors wrote:
> 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
> <http://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.

To follow up on my previous post, not sure why you put the parameters
into a numpy.array? Why not create a dictionary and use the name style
placeholder. One added benefit is it documents the arguments used in the
SQL string.

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

I would start simple, test and then move up to more sophisticated.

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


--
Adrian Klaver
adrian.klaver@aklaver.com