COPY: date and datetime

[solovieff]

When using date column, datetime objects go there fine, unless another column is None.

with psycopg.get_connection("someurl") as scorer_conn:
        query = """
            drop table if exists _r;
            create temp table _r (
                datadate date,
                val double precision
            );
        """
        #df = pd.DataFrame([{"val": None, "datadate": datetime.datetime.today().date()}]) -- works ok
        #df = pd.DataFrame([{"val": 15, "datadate": datetime.datetime.today().date()}]) -- works ok
        df = pd.DataFrame([{"val": 15, "datadate": datetime.datetime.today()}]) # works ok
        df = pd.DataFrame([{"val": None, "datadate": datetime.datetime.today()}]) # does not work
        col_names = (df.columns)
        # TODO: looks like we can pass it a server parameter 
        col_names_str = ','.join(col_names)
        cursor = scorer_conn.execute(query)
        
        with cursor.copy(f"COPY _r ({col_names_str}) FROM STDIN") as copy:
            for index, row in df.iterrows():
                rec = row.values.tolist()
                copy.write_row(rec)

Traceback (most recent call last):
  File "/Users/tests/tester.py", line 35, in <module>
    copy.write_row(rec)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/contextlib.py", line 126, in __exit__
    next(self.gen)
  File "/Users/venv9/lib/python3.9/site-packages/psycopg/cursor.py", line 903, in copy
    raise ex.with_traceback(None)
psycopg.errors.DatetimeFieldOverflow: date/time field value out of range: "1666534675947488000"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY _r, line 1, column datadate: "1666534675947488000"

[solovieff]

For some reason having None in one columns breaks datetime field conversion

[dvarrazzo]

Can I see what a rec contains?

[solovieff]

df = pd.DataFrame([{"val": None, "datadate": datetime.datetime.today()}])
        col_names = (df.columns)
print(df)
print(df.dtypes)

leads to:

val                   datadate
0  None          2022-10-23 20:53:04.807565

val                 object
datadate    datetime64[ns]
dtype: object

Rec is:
[None, 1666558384807565000]

Taking into account it works if you add .date() to "datadate", the problem is in datetime64[ns] to date conversion. By default it tries to use timestamp, as far as I understand.

[dvarrazzo]

1666558384807565000 is not a date, as far as Python, Postgres, and me are concerned...

You are assuming that you can pass a dataframe to psycopg. That's not supported yet.

[solovieff]

How does it work then if no None is there in another field -- that is the question.

Also, it seems to mostly work, it's the only problem I faced for now (=
As far as I understand the problem is in rec = row.values.tolist() typings?

[dvarrazzo]

That seems strange. What's the value of rec when it works?

[solovieff]

For df = pd.DataFrame([{"val": 15, "datadate": datetime.datetime.today()}])
[15, Timestamp('2022-10-23 22:14:14.760529')]

For df = pd.DataFrame([{"val": None, "datadate": datetime.datetime.today()}])
[None, 1666563341422186000]

Interesting!

[dvarrazzo]

Maybe it is, but Pandas data objects are not supported yet: see #316

If you convert that object to a datetime.date() it will be dumped correctly.

[solovieff]

Thank you for your work!