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?

Answer 1

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