Purpose

Run the COPY command on PostgreSQL in python. There is a copy_from function in psycopg, which makes it very easy and easy to understand.

Environment at the time of writing this article

python:2.7.10
postgresql 9.4
PC: MacBook Pro (Retina, 13-inch, Mid 2014)

Required library

Psycopg http://initd.org/psycopg/

Installation is also possible with the PIP command. For more information http://initd.org/psycopg/docs/install.html

Sample source code

Submit the file located on the machine running TSV format python to PostgreSQL.

`copy_from.py`


import psycopg2

try:
    #Connect to PostgreSQL
    conn = psycopg2.connect("dbname='Database name' user='Login username' host='hostname' password='****'")
    cur = conn.cursor()
    conn.set_isolation_level(0)

    #Read the file to put in the database
    f = open('/File Path/file name' mode='r' encoding='utf-8') #If you specify the character code, you can suppress the error at the time of COPY.

    #COPY execution
    cur.copy_from( f , 'table name', sep='\t', null='\\N',columns=('Column name 1','Column name 2'))
      # sep='Delimiter character type: TAB symbol in the example'
      # null='Null character type: NULL in the example'
      #　　columns=('')List the column names to be input in order, and can be omitted if all columns can be input.

    conn.commit() #Don't forget! !!
    f.close()
    print( "OK" )

except psycopg2.Error as e:
   print( "NG Copy error! ")
   print(  e.pgerror )

point

How to use Psycopg's copy_from function

The point of the copy_from function is the first argument.

file – file-like object to read data from. It must have both read() and readline() methods.

It seems that a file or an object like a file must have both read () and readline () present. In the sample code, the return value of the TSV file read by the open () function is given.

In colums, you can specify the column name to be input, so it is also effective for tables with serial type columns.

Execute the COPY command using python's Psycopg