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 or execute_batch if speed is important for you. — shmee
– shmee, Commented Sep 18, 2018 at 17:09

Nick · Accepted Answer · 2018-09-18 17:42:53Z

3

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

answered Sep 18, 2018 at 17:42

Nick

7,5012 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Walter White Over a year ago

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

Collectives™ on Stack Overflow

How to insert NULL values into PostgreSQL database using Python?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related