Reading CSV and inserting data with Python

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
business CHARACTER VARYING(155),
firstname CHARACTER VARYING(75),
lastname CHARACTER VARYING(100),

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

“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:

cur.execute(insert_stmt, (row[‘BUSINESS’],
recordcount = recordcount+1
except (Exception, psycopg2.DatabaseError) as error:

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.

Python – Unable to load psycopg2

Well this was very frustrating to fix “Unable to load psycopg2” but I wanted to share my findings with everyone so they will not go through the ordeal that I did.

I installed a new version of python to my work station, updated my python code to support that new version of python. After executing the python script I received the following error message “Library not loaded: @loader_path/../lib/libssl.1.0.0.dylib”. After much searching I found several sites that helped me figure out the issue. So what I figured out is that if I linked the files from /usr/local/lib you don’t get that error message.

You might have to edit the below command line items to your paths but this should give you a pretty good idea on what to do

cd /usr/local/lib
sudo ln -s /Library/PostgreSQL/9.6/lib/libssl.1.0.0.dylib
sudo ln -s /Library/PostgreSQL/9.6/lib/libcrypto.1.0.0.dylib
sudo ln -s /Library/PostgreSQL/9.6/lib/libpq.5.dylib