CSV data into postgreSQL using Python

Question

I am trying to import CSV data into postgreSQL using Python. I tried to search on Stack Overflow for this issue but couldn't find anything fruition for my situation. I have empty columns in my CSV file, when I run the code it throws out an error for the blank column for which there's no information. I want to be able to tell Python to ignore the blank column and continue to the next column. Not all of the columns have data in them so I want your kind assistance to implement a solution which I can have edited in the script for the columns which have no data in them. I am new to Programming so please pardon my stupidity.

import psycopg2
import csv
csv_data = csv.reader(file('SampleData1.csv'))
database = psycopg2.connect (database = "**", user="**", password="***", host="**", port="**")
cursor = database.cursor()
delete = """Drop table if exists "Real".SampleDataOne"""
print (delete)
mydata = cursor.execute(delete)
cursor.execute("""Create Table "Real".SampleDataOne
 (Username varchar(55),
 TimeStamp timestamp,
 Week date,
 Actual_Sale_Date date,
 Estimated_Closing_Date date, 
 Last_Name_First_Name varchar(55),
 Ages varchar(55)
 );""")
print "Table created successfully" 
next(csv_data)
for row in csv_data:
cursor.execute("""Insert into "Real".SampleDataOne(Username,TimeStamp, Week, Actual_Sale_Date, Estimated_Closing_Date, \
 Last_Name_First_Name, Ages)"""\
 """VALUES (%s,%s,%s,%s,%s,%s,%s)""",
 row)
cursor.close()
database.commit()
database.close()
print "CSV imported"

The error is as follows: It has a point upwards (^) next to the column after the 'False'.

Drop table if exists "Real".SampleDataOne
Table created successfully
Traceback (most recent call last):
File "C:/Users/Desktop/Programming/SampleData1Python.py", line 61, in <module>
row)
DataError: invalid input syntax for type date: ""
LINE 1: ...****@jcp.com','****@comcast.net','No','FALSE','','','')

• Nick: Any reason you aren't using copy_to in the Psycopg2 library - or even the COPY command in PostgreSQL itself? It may resolve a few of your issues here.
• Jake Wagner: I have not heard of the copy_to function. Will it solve this problem?
• Hannu: The error message is rather informative... '' is not a correctly formatted date. If your database allows null entries in your date fields, you would still need to send None instead of ''.
• Jake Wagner: Yes I want to be able to send blank(null) as None in the database but don't know how to.
• Hannu: You need to convert '' to None. I don't think it is possible with your current syntax. There may be a clever preprocessor option to your database but I would rather just extract fields from row to variables and with date fields use datevar = row[5] if row[5] != '' else None format. Then use these variables in your database insert statement.

Answer 1

http://initd.org/psycopg/docs/cursor.html#cursor.copy_from

f = open('SampleData1.csv')
cursor.copy_from(f, '"Real".sampledataone', sep=',', null='')

Should work provided you have simple csv data without quoting or commas in the data.

• Jake Wagner: Thanks for the reply, where can I have this code edited in the original script?
• BmoreAGG: In lieu of the for loop with insert sql.
• bimsapi: I use copy_* frequently, but a notable gotcha is that the data has to be perfect, including types and constraints. There is no error recovery - the first error stops the process. As a workaround, I will do the loads into an untyped temp table (e.g., table of all varchar, with no constraints), and do post processing on it (still via SQL, with explicit type conversions and casts).