Обсуждение: Insert with a lot of columns
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,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%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)
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",
line223, 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
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", line223, 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.klaver@gmail.com
On Dec 31, 2013, at 10:50 AM, Adrian Klaver <adrian.klaver@gmail.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 theabove 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.
On 12/31/2013 10:56 AM, David Kerr wrote: > > On Dec 31, 2013, at 10:50 AM, Adrian Klaver <adrian.klaver@gmail.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 theabove 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.klaver@gmail.com
On Dec 31, 2013, at 1:30 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 12/31/2013 10:56 AM, David Kerr wrote: >> >> On Dec 31, 2013, at 10:50 AM, Adrian Klaver <adrian.klaver@gmail.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. Sothe 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.
On 12/31/2013 03:47 PM, David Kerr wrote: > > On Dec 31, 2013, at 1:30 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > >> On 12/31/2013 10:56 AM, David Kerr wrote: >>> >>> On Dec 31, 2013, at 10:50 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: >> >> 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. One way I tackled this was to use cursor.description to get the fields names and then build the parameter variables on the fly i.e '%(' + field_name + ')s' and use that to build a string to put into VALUES. > > > -- Adrian Klaver adrian.klaver@gmail.com