Execute the COPY command using python's Psycopg

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

Required library

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.

Recommended Posts

Execute the COPY command using python's Psycopg
Using cgo with the go command
Automatically write admin.py using the django-extensions admin_generator command
[Python] Let's execute the module regularly using schedule
Operate Route53 on the command line using AWS-CLI.
[Linux] Execute git pull regularly with the crontab command
Execute the command on the web server and display the result
I want to automate ssh using the expect command!
How to execute a command using subprocess in Python
About the service command
Install the pip command
Try using Python's feedparser.
Try using Python's Tkinter
Execute command from python
I want to automate ssh using the expect command! part2
Recursively copy files from the directory directly under the directory using Python
Let's execute the command on time with the bot of discord
Check in advance what happens when you execute the command
Paste the image into an excel file using Python's openpyxl
Execute a command in Go like Python's subprocess.call (~, shell = True)