How to insert 'NULL' values into PostgreSQL database using Python?

Question

Is there a good practice for entering NULL key values to a PostgreSQL database when a variable is None in Python?

Running this query:

mycursor.execute('INSERT INTO products (user_id, city_id, product_id, quantity, price) VALUES (%i, %i, %i, %i, %f)' %(user_id, city_id, product_id, quantity, price))

results in a a TypeError exception when user_id is None.

How can a NULL be inserted into the database when a value is None, using the psycopg2 driver?

by DB-API adapter I suppose you mean the db interface psycopg2. If not, can you be more specific? — xpanta, Nov 20 '10 at 06:34

score 72 · Accepted Answer · answered Nov 20 '10 at 06:59

72

To insert null values to the database you have two options:

omit that field from your INSERT statement, or
use None

Also: To guard against SQL-injection you should not use normal string interpolation for your queries.

You should pass two (2) arguments to execute(), e.g.:

mycursor.execute("""INSERT INTO products 
                    (city_id, product_id, quantity, price) 
                    VALUES (%s, %s, %s, %s)""", 
                 (city_id, product_id, quantity, price))

Alternative #2:

user_id = None
mycursor.execute("""INSERT INTO products 
                    (user_id, city_id, product_id, quantity, price) 
                    VALUES (%s, %s, %s, %s, %s)""", 
                 (user_id, city_id, product_id, quantity, price))

answered Nov 20 '10 at 06:59

mechanical_meat

163,903
24
228
223

Thank you. Can you point me somewhere so I can read why string interpolation in python may lead to sql-injection. This is very interesting and I haven't thought of that. – xpanta Nov 20 '10 at 07:17
3

You're welcome. This describes well what SQL injection attacks are: http://en.wikipedia.org/wiki/SQL_injection How does this relate to string interpolation?: Say you receive untrusted input to your program. That input could contain, for example, a `DROP TABLE` command. – mechanical_meat Nov 20 '10 at 07:29
4

I'm using None for the column value but get an error: ProgrammingError: column "none" does not exist LINE 1: ...01.00-ng20', report_date='2016-03-30', ec_enabled=None, ec_s... Here's the original query that's failing: UPDATE kw_daily_data_mart SET hostname='ctrlkey.com', users_licensed=7, licensed_users=10, sw_version='kw2016.01.00-ng20', report_date='2016-03-30', ec_enabled=None, ec_server_count=1, config_max_file_version=10, av_enabled=True, location_count=1, sso_enabled=False WHERE customer_id=127892 AND installation_id=3585 AND hostname='ctrlkey.com' RETURNING id – Varun Verma Mar 30 '16 at 18:29
3

I am using None, too and I get the same error as @Varun. Same python package psycopg2, v2.6.2. Posgresql v9.6 – Romulus Nov 14 '16 at 19:49
1

Solution also works with an update query. Thanks for sharing! – Varun Verma May 10 '17 at 17:08

score 9 · Answer 2 · edited Sep 09 '19 at 13:46

9

With the current psycopg, instead of None, use a variable set to 'NULL'.

variable = 'NULL'
insert_query = """insert into my_table values(date'{}',{},{})"""
format_query = insert_query.format('9999-12-31', variable, variable)
curr.execute(format_query)
conn.commit()

>> insert into my_table values(date'9999-12-31',NULL,NULL)

edited Sep 09 '19 at 13:46

user229044

232,980
40
330
338

answered Apr 15 '19 at 19:04

wolf2600

565
2
8
17

2

this works for psycopg2 2.8.4, solution with inserting None ends up with the value 'None' in the db, as least for postgres – bucky Jan 22 '20 at 14:49
2

If the field being inserted is a text field, using 'NULL', simply inserts those four characters. The answer above (use `None`) is the correct one. – Paul Hoffman Apr 05 '20 at 19:33

score 1 · Answer 3 · edited Feb 11 '21 at 07:10

1

Here is my solution:

text = 'INSERT INTO products (user_id, city_id, product_id, quantity, price) VALUES (%i, %i, %i, %i, %f)' %(user_id, city_id, product_id, quantity, price))

text = text.replace("nan", "null")

mycursor.execute(text)

edited Feb 11 '21 at 07:10

Sid Kwakkel

749
3
11
31

answered Feb 10 '21 at 23:57

Randy

63
8

score 0 · Answer 4 · answered Sep 21 '19 at 02:14

0

A simpler approach which also is practical with high number of columns:

Let row be a list of values to be inserted that may contain None. To insert it into PostgreSQL we do as follows

values = ','.join(["'" + str(i) + "'" if i else 'NULL' for i in row])
cursor.execute('insert into myTable VALUES ({});'.format(values))
conn.commit()

answered Sep 21 '19 at 02:14

LoMaPh

1,476
2
20
33

1

Maybe off topic but, I could suggest not to insert to a table without specifying the column names. – quents Jan 18 '21 at 14:15

How to insert 'NULL' values into PostgreSQL database using Python?

4 Answers4

Linked