Re: Insert with a lot of columns

On Dec 31, 2013, at 1:30 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:

> 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

Yeah, i’m doing more than just bulk moving data around so the select / insert pattern
is more appealing despite the lack of performance / simplicity / elegance of it.

Thanks for the help. I got over the hump and got it working with %(name)s for everything.