How to insert NULL values into PostgreSQL database using Python?

Question

I have list of tuples with data something like this:

list1 = [(1100, 'abc', '{"1209": "Y", "1210": "Y"}'), (1100, 'abc', None)]

def insert_sample_data(col_val):
    cur = self.con.cursor()
    sql = """insert into sampletable values {}""".format(col_val)
    cur.execute(sql)
    self.con.commit()
    cur.close()

values1 = ', '.join(map(str, list1))  #bulk insert
insert_sample_data(values1)

Table Structure: ssid int, name varchar, rules jsonb

When I am trying to insert the data but it throws me an error saying "insert column "none" does not exist". How can we load the data into table with 'Null' or 'None'?

I looked at this solution but it does not help in this case How to insert 'NULL' values into PostgreSQL database using Python?

Why does the solution from the linked question not work in this case? Because of the bulk insert? For that you should use [`executemany`](http://initd.org/psycopg/docs/cursor.html#cursor.executemany) or [`execute_batch`](http://initd.org/psycopg/docs/extras.html#psycopg2.extras.execute_batch) if speed is important for you. — shmee, Sep 18 '18 at 17:09

score 3 · Accepted Answer · answered Sep 18 '18 at 17:42

As @shmee states, you need to use something like executemany and parameterize your values instead of using format, which is vulnerable to SQL injection.

I would modify your code as follows:

def insert_sample_data(self, values): # added self since you are referencing it below
    with self.con.cursor() as cur:
        sql = "insert into sampletable values (%s, %s, %s)" # Use %s for parameters
        cur.executemany(sql, values) # Pass the list of tuples directly
        self.con.commit()

list1 = [(1100, 'abc', '{"1209": "Y", "1210": "Y"}'), (1100, 'abc', None)]
self.insert_sample_data(list1) # pass the list directly

As @shmee said, speed is something really important for my process as there are atleast 100k to 1mil records to be inserted and using `executemany` takes a lot of time. I made few modifications to your code and it worked like a charm. Thanks @Nicarus — Walter White, Sep 19 '18 at 14:09

How to insert NULL values into PostgreSQL database using Python?

1 Answers1