Re: Fwd: error in cur.mogrify line - Mailing list psycopg
| From | Octavi Fors |
|---|---|
| Subject | Re: Fwd: error in cur.mogrify line |
| Date | |
| Msg-id | CAJEYUR-h7iixqcoHkzUjzWdMS=J8SsMn4Rfwh1Ref3oz4UrUTg@mail.gmail.com Whole thread Raw |
| In response to | Re: Fwd: error in cur.mogrify line (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: Fwd: error in cur.mogrify line
Re: Fwd: error in cur.mogrify line |
| List | 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.
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
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.
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.
On Fri, Mar 27, 2015 at 12:52 PM, Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com