'Insert null values to postgresql timestamp data type using python

I am tying to insert null value to a postgres timestamp datatype variable using python psycopg2.

The problem is the other data types such as char or int takes None, whereas the timestamp variable does not recognize None.

I tried to insert Null , null as a string because I am using a dictionary to get append the values for insert statement.

Below is the code.

queryDictOrdered[column] = queryDictOrdered[column] if isNull(queryDictOrdered[column]) is False else NULL

and the function is 

def isNull(key):
    if str(key).lower() in ('null','n.a','none'):
        return True
    else:
        False

I get the below error messages:

DataError: invalid input syntax for type timestamp: "NULL"
DataError: invalid input syntax for type timestamp: "None"

postgresqlpython-2.7psycopg2


Solution 1:[1]

Empty timestamps in Pandas dataframes come through as NaT (not a time), which is NOT pg compatible with NULL. A quick work around is to send it as a varchar and then run these 2 queries:

 update <<schema.table_name>> set <<column_name>> = Null where
 <<column_name>> = 'NULL';

or (depending on what you hard coded empty values as)

update <<schema.table_name>> set <<column_name>> = Null where <<column_name>> = 'NaT';

Finally run:

alter table <<schema.table_name>> 
alter COLUMN <<column_name>>  TYPE timestamp USING <<column_name>>::timestamp without time zone;

Solution 2:[2]

Surely you are adding quotes around the placeholder. Read psycopg documentation about passing parameters to queries.

Solution 3:[3]

Dropping this here incase it's helpful for anyone.

Using psycopg2 and the cursor object's copy_from method, you can copy missing or NaT datetime values from a pandas DataFrame to a Postgres timestamp field.

The copy_from method has a null parameter that is a "textual representation of NULL in the file. The default is the two characters string \N". See this link for more information.

Using pandas' fillna method, you can replace any missing datetime values with \N via data["my_datetime_field"].fillna("\\N"). Notice the double backslash here, where the first backslash is necessary to escape the second backslash.

Using the select_columns method from the pyjanitor module (or .loc[] and some subsetting with the column names of your DataFrame), you can coerce multiple columns at once via something akin to this, where all of your datetime fields end with an _at suffix.

data_datetime_fields = \
    (data
     .select_columns("*_at")
     .apply(lambda x: x.fillna("\\N")))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jennifer P
Solution 2 piro
Solution 3 Ethan Wicker

Related Questions