CODEWITHSUNDEEP
pythonpostgresql
Walid Mashal
Walid Mashal

Reputation: 342

how to set date value or null in sql formatted query string for a date column

I have a task to copy data from one db table to another db table using psycopg2 library in python language.

Now after fetching a row from first db I have to insert the row into the second db table, but the problem I face is that I need to format the query and insert the value for date column from a variable which may or may not have a date value, so my query is like the following:

cur.execute("""update table_name set column1 = '%s', column2 = '%s',column_date = '%s'""" % (value1, value2, value_date))

will now the value_date may be a date or a None value, so how to I convert this None value to sql null or something so that it can be stored in the date column.

Note: considering the value1, value2 and value_date are variables containing values.

Upvotes: 0

Views: 2559

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

Psycopg adapts a Python None to a Postgresql null. It is not necessary to do anything. If there is no processing at the Python side skip that step and update directly between the tables:

cur.execute("""
    update t1
    set column1 = t2.c1, column2 = t2.c2, column_date = t2.c3
    from t2
    where t1.pk = t2.pk
"""

This is how to pass date and None to Psycopg:

from datetime import date

query = '''
    update t
    set (date_1, date_2) = (%s, %s)
'''
# mogrify returns the query string
print (cursor.mogrify(query, (date.today(), None)).decode('utf8')) 
cursor.execute(query, (date.today(), None))

query = 'select * from t'
cursor.execute(query)
print (cursor.fetchone())

Output:

update t
set (date_1, date_2) = ('2017-03-16'::date, NULL)

(datetime.date(2017, 3, 16), None)

Upvotes: 3

Related Questions