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
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.

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