HomeLanguagesPython – Import CSV into PostgreSQL

Python – Import CSV into PostgreSQL

In this article, we will see how to import CSV files into PostgreSQL using the Python package psycopg2.

First, we import the psycopg2 package and establish a connection to a PostgreSQL database using the pyscopg2.connect() method. before importing a CSV file we need to create a table. In the example below, we created a table by executing the “create table” SQL command using the cursor.execute() method.

'''CREATE TABLE DETAILS(employee_id int NOT NULL,
                        employee_name char(20), 
                        employee_email varchar(30),
                        employee_salary float);'''

View of the empty table:

Table Description

After creating the table we need to execute the “copy” command in the SQL form. in the copy command, we need to specify the path of the CSV file. 

CSV File Used:

CSV File Used

'''COPY table_name
FROM 'C:\folder\file.csv' 
DELIMITER ',' 
CSV HEADER;'''

Below is the implementation:

Python3




import psycopg2
  
conn = psycopg2.connect(database="EMPLOYEE_DATABASE",
                        user='postgres', password='pass', 
                        host='127.0.0.1', port='5432'
)
  
conn.autocommit = True
cursor = conn.cursor()
  
  
sql = '''CREATE TABLE DETAILS(employee_id int NOT NULL,\
employee_name char(20),\
employee_email varchar(30), employee_salary float);'''
  
  
cursor.execute(sql)
  
sql2 = '''COPY details(employee_id,employee_name,\
employee_email,employee_salary)
FROM '/private/tmp/details.csv'
DELIMITER ','
CSV HEADER;'''
  
cursor.execute(sql2)
  
sql3 = '''select * from details;'''
cursor.execute(sql3)
for i in cursor.fetchall():
    print(i)
  
conn.commit()
conn.close()


Output:

(1, 'rajesh              ', '[email protected]', 60000.0)
(2, 'pratyusha           ', '[email protected]', 75000.0)
(3, 'pratibha            ', '[email protected]', 65000.0)
RELATED ARTICLES

2 COMMENTS

Most Popular

Dominic
32688 POSTS0 COMMENTS
Milvus
131 POSTS0 COMMENTS
Nango Kala
7057 POSTS0 COMMENTS
Nicole Veronica
12198 POSTS0 COMMENTS
Nokonwaba Nkukhwana
12293 POSTS0 COMMENTS
Shaida Kate Naidoo
7182 POSTS0 COMMENTS
Ted Musemwa
7447 POSTS0 COMMENTS
Thapelo Manthata
7160 POSTS0 COMMENTS
Umr Jansen
7150 POSTS0 COMMENTS