7

I am trying to load data from a StringIO object of python into a Postgres database table using psycopg2's copy_from() method.

My copy_from fails on the first record itself specifically for a particular (nullable) integer column which has null value ('' without quotes). I have also tried using Python's None keyword instead of '' for NULL values. It throws me the following error: DataError: invalid input syntax for integer: "" CONTEXT: COPY , line 1, column : ""

The code looks something like this:

table_data = StringIO.StringIO()
# Populate the table_data variable with rows delimited by \n and columns delimited by \t
cursor = db_connection.cursor()
cursor.copy_from(table_data, <table_name>)

This column is a smallint column.

user3422637
  • 3,967
  • 17
  • 49
  • 72

1 Answers1

16

By default, COPY FROM (and copy_from) encode a NULL value as \N. If you want to use the empty string to mean NULL, you need to say so explicitly:

cursor.copy_from(table_data, table_name, null="")
Eevee
  • 47,412
  • 11
  • 95
  • 127
  • Thanks. The issue has now been resolved. However, I get a new error: DataError: invalid byte sequence for encoding "UTF8": 0x92 Should I raise a new question for this? – user3422637 Oct 28 '14 at 21:10
  • that's probably already been answered a zillion times over :) it's an encoding problem. most likely your data is encoded as windows-1252, and you need to decode it. – Eevee Oct 28 '14 at 22:05
  • I did search the answer for this on stackoverflow and I found questions/answers on similar lines but those didn't solve my problem. I have put up a new descriptive question for that here. http://stackoverflow.com/questions/26619801/unicodedecodeerror-ascii-codec-cant-decode-byte-0x92-in-position-47-ordinal Please have a look. Thanks for your help – user3422637 Oct 28 '14 at 23:52
  • copy_from() method does not allow quoted strings like double quotes.Example row ["python", 598.00]; It sends to db like ""python"" – shrinathM May 11 '18 at 14:47