How to pass Null as value to a stored proc to query postgresdb using python?

Question

I have a stored proc where I need to pass 'null' as value to the query

Here is the dict:

dict1 = {'name':'apple', 'age':23, 'place':None, 'year':None, 'country': ''}

Note: The values will always change for respective keys.

I tried this but it does not work for my scenario as he/she are having constant values and are having no empty strings as I do. How to insert 'NULL' values into PostgreSQL database using Python?

I tried to pass the values as None in place null but python is taking it as None not null. Its throwing me an error saying psycopg2.ProgrammingError: column "none" does not exist

How do I pass dict1 None value as null value to the stored proc query like below using python?

select * from sampletable."getvalues"('apple', 23, null, null, '')

klin · Accepted Answer · 2018-10-15 15:53:01Z

0

None is correctly converted to NULL by psycopg2, regardless of whether there are functions in the query or not. This is a proper way to pass a dictionary to cursor.execute():

dict1 = {'name':'apple', 'age':23, 'place':None, 'year':None, 'country': ''}
query = cur.mogrify('select * from func(%(name)s, %(age)s, %(place)s, %(year)s, %(country)s)', dict1)
print(query)

Output:

b"select * from func('apple', 23, NULL, NULL, '')"

answered Oct 15, 2018 at 15:53

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

cayman Over a year ago

Your answer does not work as 'select * from sampletable."getvalues"' would change the query to "select * from sampletable."getvalues"(\'apple',\23\,NULL,NULL,\'\');" using mogrify which totally changes the query.

klin Over a year ago

mogrify() is only to see the query, just replace it with execute() to run the query. It's hard to debug the code I cannot see. Please, edit the question and show your python code to have a chance someone could help you.

Collectives™ on Stack Overflow

How to pass Null as value to a stored proc to query postgresdb using python?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related