psycopg2 copy_from Problems in Python 3

Question

I'm new to Python (and coding) and bit off more than I can chew trying to use copy_from.

I am reading rows from a CSV, manipulating them a bit, then writing them into SQL. Using the normal INSERT commands takes a very long time with hundreds of thousands of rows, so I want to use copy_from. It does work with INSERT though.

https://www.psycopg.org/docs/cursor.html#cursor.copy_from this example uses tabs as column separators and newline at the end of each row, so I made each IO line accordingly:

43620929    2018-04-11 11:38:14 30263506    30263503    30262500    0   0   0   0   0   1000    1000    0

That's what the below outputs with the first print statement:

def copyFromIO(thisOutput):
    print(thisOutput.getvalue())
    cursor.copy_from(thisOutput, 'hands_new')

    thisCommand = 'SELECT * FROM hands_new'
    cursor.execute(thisCommand)
    print(cursor.fetchall())

hands_new is an existing, empty SQL table. The second print statement is just [], so it isn't writing to the db. What am I getting wrong?

Obviously if it worked, I could make thisOutput much longer, with lots of rows instead of just the one.

Andrew Andrew · Accepted Answer · 2020-06-06T06:51:36

I think I figured it out, so if anyone comes across this in the future for some reason:

'thisOutput' format was wrong, I built it from smaller pieces including adding '\t' etc. It works if instead I do:

copyFromIO(io.StringIO('43620929\t2018-04-11 11:38:14\t30263506\t30263503\t30262500\t0\t0\t0\t0\t0\t1000\t1000\t0\n'))

& I needed the right columns in the copy_from command:

def copyFromIO(thisOutput):
    print(thisOutput.getvalue())
    thisCol = ('pkey', 'created', 'gameid', 'tableid', 'playerid', 'bet', 'pot',
               'isout', 'outround', 'rake', 'endstack', 'startstack', 'stppaid')

    cursor.copy_from(thisOutput, 'hands_new', columns=(thisCol))
    thisCommand = 'SELECT * FROM hands_new'
    cursor.execute(thisCommand)
    print(cursor.fetchall())

psycopg2 copy_from Problems in Python 3

1 Answers