One of the easiest things that you can do in Python is read a CSV file and insert that data into a Postgres database. In this blog I will show you how to open up and read a CSV file and then loop thru that CSV creating an insert statement to move that data into the database.
Table structure in Postgres database
CREATE TABLE user
(
business CHARACTER VARYING(155),
firstname CHARACTER VARYING(75),
lastname CHARACTER VARYING(100),
title CHARACTER VARYING(50)
);
First thing we want to do is create a connection and create a cursor
conn = psycopg2.connect(host=”HOSTNAME“, database=”DATABASE NAME“,user=”USER“,password=”PASSWORD“)
cur = conn.cursor()
Second lets create an insert statement
insert_stmt = ” INSERT INTO TEST (business, firstname, lastname, title) VALUES (%s,%s,%s,%s)”
Third we are now going to open the CSV file, read it, build insert statements and insert. Sample file lines
“BUSINESS”,”FIRSTNAME”,”LASTNAME”,”TITLE”
“Jakes Landscape”,”Jake”,”Switch”,”President”
“Lamps etc”,”Robert”,”Jump”,”Sales”
“PMP Light Bulbs”,”David”,”Downes”,”Warehouse”
file_name =”PATH TO CSV FILE”
reader = csv.DictReader(open(file_name))
# Count how many records have been processed
recordcount = 0
for row in reader:
try:
cur.execute(insert_stmt, (row[‘BUSINESS’],
row[‘FIRSTNAME’],
row[‘LASTNAME’],
row[‘TITLE’]))
conn.commit()
recordcount = recordcount+1
except (Exception, psycopg2.DatabaseError) as error:
print(sys.stderr)
conn.close()
cur.close
print (‘Process ‘+str(recordcount)+’ rows from ‘+file_name)
It is as simple as that to read a CSV file and insert its data into a database.
