Re: Fwd: error in cur.mogrify line - Mailing list psycopg
From | Adrian Klaver |
---|---|
Subject | Re: Fwd: error in cur.mogrify line |
Date | |
Msg-id | 55157605.9070602@aklaver.com Whole thread Raw |
In response to | Fwd: error in cur.mogrify line (Octavi Fors <octavi@live.unc.edu>) |
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 Typically that means there is a mismatch between the %s placeholders and the values passed in. Best guess is you are not unpacking the array the way you think you are. > > > 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. Look at here: http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries > > 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/ > > > -- Adrian Klaver adrian.klaver@aklaver.com