CODEWITHSUNDEEP
pythonpostgresql-9.2
Bill West
Bill West

Reputation: 121

Python copy_from not working and not throwing errors

I have the following procedure that doesn't load data into a table as expected:

def upload_data(parsed_buffer):  

    parsed_buffer.seek(0)

    try:

        con = psycopg2.connect(database=database, user=user, password=pw, host=host) 
        cur = con.cursor()
        try:
            cur.copy_from(parsed_buffer, 'staging.vcf_ht')
        except StandardError, err:
            conn.rollback()
            print("   Caught error (as expected):\n", err)

    except psycopg2.NotSupportedError as e:
        now = time.strftime("%H:%M:%S +0000", time.localtime())
        print("Copy failed at: " + now + " - " + e.pgerror)
        sys.exit(1)

    finally:

            if con:
                con.close()
                now = time.strftime("%H:%M:%S +0000", time.localtime())
                print('Finished loading data at:' + now)

In other posts they discuss adding the seek function after writes. That is in line 3 of my code. This did not work. A couple of other things I checked. 1. The string buffer is populated with tab delimited data. 2. If I redirect the output to a file and use the \copy command in psql it works as advertised. 3. If I write an insert statement instead of a string buffer this also works (but this is bad for performance). This procedure terminates with throwing any errors.

Upvotes: 3

Views: 1226

Answers (1)

Bill West
Bill West

Reputation: 121

The problem was no commit statement. Added: con.commit()

Upvotes: 7

Related Questions