From: | Octavi Fors <octavi(at)live(dot)unc(dot)edu> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | psycopg(at)postgresql(dot)org |
Subject: | Re: Fwd: error in cur.mogrify line |
Date: | 2015-03-27 18:53:33 |
Message-ID: | CAJEYUR-h7iixqcoHkzUjzWdMS=J8SsMn4Rfwh1Ref3oz4UrUTg@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
Hi Adrian,
I've made some progress.
First I realized that tbl2['OBJECT'] should be the 4th parameters in the
list targets1 (does this matter when using dicts)?).
Now I can insert into the database by either:
- transforming the list targets1 (see L39, attached
'insert_list_tuple.py') into the tuple targets,
- or transforming the numpy.array targets1 (see L39, attached
'insert_nparray_tuple.py') into the tuple targets.
Executions timings when inserting the 17586-row '
img_20130109_001427_2_10.0s_c1_calib.cat' file I passed in my previous
message, are a bit better for numpy.array script:
Script Real time execution
insert_nparray_tuple.py 4.3-4.9
insert_list_tuple.py 5.1-5.5
Fluctuations in timings might be due to I'm reading the '
img_20130109_001427_2_10.0s_c1_calib.cat' file from a NAS.
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.
>
good question.
Yes, I wouldn't bother at all to give up putting the parameters into a
numpy.array and doing it in a dictionary as you suggest, as long as the
insert timing does not increase.
The problem is that I don't have experience converting a list targets1
(L36) into a dict.
Also I'm not able to find an example of cur.execute("""INSERT with dicts.
I would be very grateful if you and/or someone else could give me a hand
one these those last issues.
Thanks in advance,
Octavi.
On Fri, Mar 27, 2015 at 12:52 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
> 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(dot)klaver(at)aklaver(dot)com
>
Attachment | Content-Type | Size |
---|---|---|
insert_list_tuple.py | text/x-python | 2.3 KB |
insert_nparray_tuple.py | text/x-python | 2.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-03-27 19:06:40 | Re: Fwd: error in cur.mogrify line |
Previous Message | Adrian Klaver | 2015-03-27 16:52:42 | Re: Fwd: error in cur.mogrify line |