Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

As the topic, this is the code and there is no error message but data did not get insert. This is my code, and can anyone tell me what's wrong with it?

import psycopg2
import sys
import os
import glob 
import csv

#open the csv folder
dictfile='******'
os.chdir(dictfile)
total=[]
for file in glob.glob("*.csv"):
    total.append(file)
con = None
try: 
    con = psycopg2.connect(host='localhost',database='*****',user='postgres', password='*****') 
    cur = con.cursor()
    for i in range(0,1):   
        filename='/Users/Shared'+'/'+total[0]
        print filename
        #better move all files into shared folder
        x="copy public.crossref_sample from "+ "'"+filename+"'"+" DELIMITERS ',' CSV"
        print x
        cur.execute(x)  
except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1) 
finally:
    if con:
        con.close()
share|improve this question
1  
I don't see a commit in your code – a_horse_with_no_name Dec 13 '12 at 22:12
Thanks, it works now – user1652287 Dec 14 '12 at 5:03

1 Answer

up vote 2 down vote

As alluded to by @a_horse_with_no_name, you're closing the PostgreSQL database connection, but you're not committing the transaction first.

psycopg2 opens a transaction for you if there isn't one already open. It expects you to commit this transaction when you've finished your work.

Unless you explicitly commit a transaction, closing the connection will rollback any work that's been done.

Try con.commit() after the last copy command.

share|improve this answer

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.