Re: Fwd: error in cur.mogrify line - Mailing list psycopg

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)?).

No, that is why I like the named parameters, order is not important. The
placeholder and the values in the parameter list are matched up on name,
this is where the self documenting feature comes in. Also if you change
your table and add fields and are doing a SELECT * somewhere you do not
have to contend with new fields hitting your SQL string.

>

> 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.

See dict comprehension:

http://stackoverflow.com/questions/14507591/python-dictionary-comprehension

> Also I'm not able to find an example of cur.execute("""INSERT with dicts.

http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

cur.execute(
...     """INSERT INTO some_table (an_int, a_date, another_date, a_string)
...         VALUES (%(int)s, %(date)s, %(date)s, %(str)s);""",
...     {'int': 10, 'str': "O'Reilly", 'date': datetime.date(2005, 11, 18)})


>
> 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.
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com