copy data from csv to postgresql using python

Question

I am on windows 7 64 bit. I have a csv file 'data.csv'. I want to import data to a postgresql table 'temp_unicommerce_status' via a python script.

My Script is:

import psycopg2
conn = psycopg2.connect("host='localhost' port='5432' dbname='Ekodev' user='bn_openerp' password='fa05844d'")
cur = conn.cursor()
cur.execute("""truncate table "meta".temp_unicommerce_status;""")
cur.execute("""Copy temp_unicommerce_status from 'C:\Users\n\Desktop\data.csv';""")
conn.commit()
conn.close()

I am getting this error

Traceback (most recent call last):
  File "C:\Users\n\Documents\NetBeansProjects\Unicommerce_Status_Update\src\unicommerce_status_update.py", line 5, in <module>
cur.execute("""Copy temp_unicommerce_status from     'C:\\Users\\n\\Desktop\\data.csv';""")
psycopg2.ProgrammingError: must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

Answer 1

I am going to post some of the errors I ran into trying to copy a csv file to a database on a linux based system....

here is an example csv file:

Name Age Height
bob  23   59
tom  56   67

1. You must install the library psycopg2 (i.e. pip install psycopg2 or sudo apt install python3-psycopg2 )

2. You must have postgres installed on your system before you can use psycopg2 (sudo apt install postgresql-server postgresql-contrib )

3. Now you must create a database to store the csv unless you already have postgres setup with a pre-existing database

COPY CSV USING POSTGRES COMMANDS

• After installing postgres it creates a default user account which gives you access to postgres commands

• To switch to the postgres account issue: sudo -u postgres psql

• Acess the prompt by issuing: psql

  command to create a database

  create database mytestdb;

  connect to the database to create a table

  \connect mytestdb;

  create a table with same csv column names

  create table test(name char(50), age char(50), height char(50));

  copy csv file to table

  copy mytestdb 'path/to/csv' with csv header;

COPY CSV USING PYTHON The main issue I ran into with copying the csv file to a database was I didn't have the database created yet, however this can be done with python still.

import psycop2 #import the postgres library

#connect to the database
conn = psycopg2.connect(host='localhost',
                       dbname='mytestdb',
                       user='postgres',
                       password='')  
#create a cursor object 
#cursor object is used to interact with the database
cur = conn.cursor()

#create table with same headers as csv file
cur.execute('''create table test(name char(50), age char(50), height char(50));''')

#open the csv file using python standard file I/O
#copy file into the table just created 
f = open('file.csv','r')
cursor.copy_from(f, 'test', sep=',')
f.close()

Answer 2

Try to do the same as the root user - postgres. If it were linux system, you could change file's permissions or move the file to /tmp. The problem results from missing credentials to read from the filesystem.

Answer 3

Use the copy_from cursor method

f = open(r'C:\Users\n\Desktop\data.csv', 'r')
cur.copy_from(f, temp_unicommerce_status, sep=',')
f.close()

The file must be passed as an object.

Since you are coping from a csv file it is necessary to specify the separator as the default is a tab character

Answer 4

You can use d6tstack which makes this simple

import d6tstack
import glob

c = d6tstack.combine_csv.CombinerCSV([r'C:\Users\n\Desktop\data.csv']) # single-file
c = d6tstack.combine_csv.CombinerCSV(glob.glob('*.csv')) # multi-file
c.to_psql_combine('postgresql+psycopg2://psqlusr:psqlpwdpsqlpwd@localhost/psqltest', 'tablename')

It also deals with data schema changes, create/append/replace table and allows you to preprocess data with pandas.

Answer 5

I know this question has been answered, but here are my two cent. I am adding little more description:

You can use cursor.copy_from method :

First you have to create a table with same no of columns as your csv file.

Example:

My csv looks like this:

Name,       age , college , id_no , country , state   , phone_no

demo_name   22  , bdsu    , 1456  , demo_co , demo_da , 9894321_

First create a table:

import psycopg2
from psycopg2 import Error

connection = psycopg2.connect(user = "demo_user",
                                  password = "demo_pass",
                                  host = "127.0.0.1",
                                  port = "5432",
                                  database = "postgres")
cursor = connection.cursor()


create_table_query = '''CREATE TABLE data_set
(Name  TEXT NOT NULL ,
age  TEXT NOT NULL ,
college  TEXT NOT NULL ,
id_no TEXT NOT NULL ,
country TEXT NOT NULL ,
state TEXT NOT NULL ,
phone_no TEXT NOT NULL);'''

cursor.execute(create_table_query)
connection.commit()

Now you can simply use cursor.copy_from where you need three parameters :

first file object , second table_name , third sep type

you can copy now :

f = open(r'final_data.csv', 'r')
cursor.copy_from(f, 'data_set', sep=',')
f.close()

done

Answer 6

The way I solved this problem particular to use psychopg2 cursor class function copy_expert (Docs: http://initd.org/psycopg/docs/cursor.html). copy_expert allows you to use STDIN therefore bypassing the need to issue a superuser privilege for the postgres user. Your access to the file then depends on the client (linux/windows/mac) user's access to the file

From Postgres COPY Docs (https://www.postgresql.org/docs/current/static/sql-copy.html):

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

You can also leave the permissions set strictly for access to the development_user home folder and the App folder.

csv_file_name = '/home/user/some_file.csv'
sql = "COPY table_name FROM STDIN DELIMITER '|' CSV HEADER"
cursor.copy_expert(sql, open(csv_file_name, "r"))