current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 16 down vote favorite
10

I have a block of data, currently as a list of n-tuples but the format is pretty flexible, that I'd like to append to a Postgres table - in this case, each n-tuple corresponds to a row in the DB.

What I had been doing up to this point is writing these all to a CSV file and then using postgres' COPY to bulk load all of this into the database. This works, but is suboptimal, I'd prefer to be able to do this all directly from python. Is there a method from within python to replicate the COPY type bulk load in Postgres?

share|improve this question
up vote 41 down vote accepted

If you're using the psycopg2 driver, the cursors provide a copy_to and copy_from function that can read from any file-like object (including a StringIO buffer).

There are examples in the files examples/copy_from.py and examples/copy_to.py that come with the psycopg2 source distribution.

This excerpt is from the copy_from.py example:

conn = psycopg2.connect(DSN)
curs = conn.cursor()
curs.execute("CREATE TABLE test_copy (fld1 text, fld2 text, fld3 int4)")

# anything can be used as a file if it has .read() and .readline() methods
data = StringIO.StringIO()
data.write('\n'.join(['Tom\tJenkins\t37',
                  'Madonna\t\N\t45',
                  'Federico\tDi Gregorio\t\N']))
data.seek(0)

curs.copy_from(data, 'test_copy')
share|improve this answer
    
Awesome, that's exactly what I was looking for. – geoffjentry Dec 8 '09 at 22:03
2  
My pleasure... if it works for you, don't forget to hit the accept button for this answer. I've noticed that you haven't accepted any of the answers to your other questions, but the "accept" checkmark helps others who have the same problem know at a glance which answer solved the problem for you. – Jarret Hardie Dec 8 '09 at 22:10
    
Sorry, I hadn't even noticed the check marks before :) – geoffjentry Dec 9 '09 at 5:15
    
ALL THE UPVOTES – K Raphael Nov 19 '15 at 0:33
    
Please modify this to include booleans and arrays as these seems to not work for me! – david_adler Jul 20 '16 at 9:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.