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.
Installation is also possible with the PIP command. For more information http://initd.org/psycopg/docs/install.html
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 )
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.
Recommended Posts