Re: Insert with a lot of columns

On 12/31/2013 10:56 AM, David Kerr wrote:
>
> On Dec 31, 2013, at 10:50 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
>
>> 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
>
> ah, i had saw that but wasn’t sure if i’d run into the same 255 limit. I’ll give it a shot. thanks.
>
>>
>> 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
>>
>
> Yeah, that was how i started, and I may go back to it.

Another alternative would be to use the psycopg COPY methods:

http://initd.org/psycopg/docs/usage.html#copy

>

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