On 03/27/2015 11:53 AM, Octavi Fors wrote:
> 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
> <http://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
> <http://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.
A little digging found atpy is now part of astropy and astropy.Table has:
http://docs.astropy.org/en/stable/io/unified.html#built-in-table-readers-writers
In particular ascii.csv.
Seems this opens up reading the Table data into a io object:
https://docs.python.org/2/library/io.html#module-io
and then using the psycopg2 COPY methods to move the data in bulk:
http://initd.org/psycopg/docs/cursor.html#cursor.copy_from
The methods start here. I generally end up using copy_expert() as it
allows for more customization.
The exact usage of the above is dependent on the size of the files you
are working with.
>
> Thanks in advance,
>
> Octavi.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
