Re: Fwd: error in cur.mogrify line

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