How to properly insert pandas NaT datetime values to my postgresql table

You're re-inventing the wheel. Just use pandas' to_sql method and it will

• match up the column names, and
• take care of the NaT values.

Use method="multi" to give you the same effect as psycopg2's execute_values.

from pprint import pprintimport pandas as pdimport sqlalchemy as satable_name = "so64435497"engine = sa.create_engine("mssql+pyodbc://@mssqlLocal64", echo=False)with engine.begin() as conn:    # set up test environment    conn.execute(sa.text(f"DROP TABLE IF EXISTS {table_name}"))    conn.execute(        sa.text(            f"CREATE TABLE {table_name} ("            "id int identity primary key, "            "txt nvarchar(50), "            "txt2 nvarchar(50), dt datetime2)"        )    )    df = pd.read_csv(r"C:\Users\Gord\Desktop\so64435497.csv")    df["dt"] = pd.to_datetime(df["dt"])    print(df)    """console output:                       dt  txt2  txt    0 2020-01-01 00:00:00  foo2  foo    1                 NaT  bar2  bar    2 2020-01-02 03:04:05  baz2  baz    """    # run test    df.to_sql(        table_name, conn, index=False, if_exists="append", method="multi"    )    pprint(        conn.execute(            sa.text(f"SELECT id, txt, txt2, dt FROM {table_name}")        ).fetchall()    )    """console output:    [(1, 'foo', 'foo2', datetime.datetime(2020, 1, 1, 0, 0)),     (2, 'baz', 'baz2', None),     (3, 'bar', 'bar2', datetime.datetime(2020, 1, 2, 3, 4, 5))]    """

Regarding your original update statement:
df = df.where(pd.notnull(df), 'None')

What's happening here is you are replacing the values with the STRING 'None' and not the special Python object None. Then in the following insert statement, it tries to insert the string 'None' into a timestamp field and throws an error.

What's funny is that the version of this you'd expect to work:
df = df.where(pd.notnull(df), None)
does not actually seem to work as expected for NaT values for reasons I don't fully understand. (See example below)

But what DOES seem to work is this statement (assuming you have numpy imported as np):
df = df.replace({np.NaN: None})
So if you do THAT, then the NaN and NaT values all convert to Python None and then psycopg2 (or probably any other db connector) will correctly treat those values as SQL Nulls on inserts.

Here's some example code to illustrate:

import datetime as dtimport pandas as pdimport numpy as npdata = [    ['one', 1.0, pd.NaT],    ['two', np.NaN, dt.datetime(2019, 2, 2)],    [None, 3.0, dt.datetime(2019, 3, 3)]    ]df = pd.DataFrame(data, columns=["Name", "Value", "Event_date"])

Got our basic dataframe:

>>> df   Name  Value Event_date0   one    1.0        NaT1   two    NaN 2019-02-022  None    3.0 2019-03-03

As mentioned above, this update leaves the NaT in it for some reason:

>>> df.where(pd.notnull(df), None)   Name Value Event_date0   one   1.0        NaT1   two  None 2019-02-022  None   3.0 2019-03-03

But this version gets both the NaNs and NaTs and leaves the expected Nones:

>>> df.replace({np.NaN: None})   Name Value           Event_date0   one   1.0                 None1   two  None  2019-02-02 00:00:002  None   3.0  2019-03-03 00:00:00

The accepted answer is probably the "better" way if you can use sqlalchemy for what you want to do, but if you gotta do it the hard way, this worked for me.

H/T to the discussion in this pandas issue for much of the details of this answer.