Search code examples
pythonpostgresqlpsycopg2cstringio

How do you create an in-memory TSV file with null values, using cStringIO?

I know that I can make a simple in-memory CSV file like this:

from cStringIO import StringIO
tsv_string = '1\t2\t3\n'
f = StringIO(tsv_string)

But how do I adapt this to include null / None values? I am asking because I am generating my tsv_string dynamically using real data. Some of the integer fields are null and I cannot replace them with an empty string since Postgres cannot interpret that correctly.

To give even more background, I am doing this so that I can use psycopg2's copy_from function which currently gives me this error due to the reason above: 

psycopg2.DataError: invalid input syntax for integer: ""

So the question is, how do I place null values in my in-memory TSV file. Is there a more suitable file extension to use?

Solution

  • I found my answer from this question.

    I had to specify the null argument as an empty string:

    cursor.copy_from(f, 'my_table', columns=columns, null='')