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

Question

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.

Clodoaldo Neto · Accepted Answer · 2017-03-16 17:14:10Z

3

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)

edited Mar 16, 2017 at 17:14

answered Mar 14, 2017 at 10:05

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

7 Comments

Walid Mashal Over a year ago

You mean if my query results in this: cur.execute("""update table_name set column1 = '%s', column2 = '%s',column_date = '%s'""" % ('name', 'lasname', None)) willl it work????

Walid Mashal Over a year ago

tnx for the help, I will let you know when I try it

Walid Mashal Over a year ago

One thing I want to point out is that both the tables on two different dbs, and my query will be converted to the following forms depending on the value: update table_name set column1 = 'somevalue', column2 = 'someothervalue',column_date = '2017-02-02' or update table_name set column1 = 'somevalue', column2 = 'someothervalue',column_date = 'None' which the case with None is not accepted.

Clodoaldo Neto Over a year ago

@WalidMashal Do not use the string 'None'. Use the real None value: column_date = None

Walid Mashal Over a year ago

If the following is my query:

cur.execute("""update duty_duty set                                                              duty_status_id = %s,to_date = %s,                                                             from_date = '%s',                                                             active = '%s' where id = %d""")

The follwing error is shown when I don't use single quotes: psycopg2.ProgrammingError: column "none" does not exist LINE 2: ... duty_status_id = 33,to_date = None,

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related