Savva Sergey
Savva Sergey

Reputation: 403

psycopg2 "NULL" value from csv for sql insert

I'm trying to upload some data from csv file by using psycopg2. I know about COPY and copy_from commands, but in my case I need to use insert statement, whatever. My problem is that column in csv with integer values can contain "NULL". And when cursor try to execute insert postgres return error: invalid input syntax for integer: "NULL" psycopg2 documentation says that you should use None for passing Null to database, but in my case it looks like i must check all fields values is it equal to "NULL" and if so pass None. It doesn't look like correct way. Is there any way to read "NULL" as None or pass "NULL" to psycopg2 without any checking.

with open('/my.csv', 'rb') as csvfile:
    reader = csv.reader(csvfile)
    for row in reader:
        pg_cursor.execute(
            """ insert into "MyTable"("IntField")
                values(%s)""", row
        )
    conn.commit()

Upvotes: 1

Views: 3287

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1121584

You can use a list comprehension to replace 'NULL' with None:

row = [None if cell == 'NULL' else cell for cell in row]
pg_cursor.execute(
    """ insert into "MyTable"("IntField")
        values(%s)""", row
)

Upvotes: 2

Related Questions