Re: Insert with a lot of columns

On 12/31/2013 10:35 AM, David Kerr wrote:
> Howdy all
>
> I’m a bit of a python noob so bear with me if this is obvious but i’m
> trying to insert into a table with a lot of columns (486 columns)
>
> so far i’ve got
>
> query_string = “””
> INSERT INTO TABLE ( <stuff> )
> VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s, ...

>
> Now, the good news is that i have a RealDictCursor with all the data in it that i want to insert (in the right order)

First to use a dictionary to supply parameters you need to use the named
format( %(name)s ) for the placeholders. So the above is not going to
work. See here for more detail:

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

This would seem to be a case where using INSERT INTO SELECT * FROM might
work better:

http://www.postgresql.org/docs/9.3/interactive/sql-insert.html

>
> so i’d love to be able to do
>
> cur.execute(query_string, row)
>
> but that generates an error:
> Traceback (most recent call last):
> File "./mover.py", line 1540, in <module>
> do_insert(conn, row2)
> File "./mover.py", line 498, in do_insert
> cur.execute(insert_sql, row)
> File "/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/psycopg2/extras.py", line 223, in execute
> return super(RealDictCursor, self).execute(query, vars)
> KeyError: 0
>
> I actually went through and did
> cur.execute(query_string, row[‘first_column’],row[‘second_column’]…)
>
> and that generated an error about “More than 255 arguments”
>
> Any suggestions on how to make this work?
>
> Thanks
>
>
>
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com