Dataframe with datetime64 dtype insert into to postgressql timestamp column

Question

I am taking a dataframe and inserting it into a Postgresql table.

One column in the dataframe is a datetime64 dtype. The column type in PostgreSQL is 'timestamp without time zone.' To prepare the dataframe to insert, I am using to_records:

listdf = df.to_records(index=False).tolist()

When I run the to_records, it gives an error at the psycopg2's cur.executemany() that I am trying to insert Biginit into a Timestamp without timezone.

So I tried to add a dict of column_dtypes to the to_records. But that doesn't work. The below gives the error: "ValueError: Cannot convert from specific units to generic units in NumPy datetimes or timedeltas"

DictofDTypes = dict.fromkeys(SQLdfColHAedings,'float')
DictofDTypes['Date_Time'] = 'datetime64'
listdf = df.to_records(index=False,column_dtypes=DictofDTypes).tolist()

I have also tried type of str, int, and float. None worked in the above three lines.

How do I convert the column properly to be able to insert the column into a timestamp sql column?

Jonathan Stearns · Accepted Answer · 2021-02-14 16:52:45Z

2

I removed defining the dtypes from to_records. And before to_recordes, I converted the datetime to str with: df['Date_Time'] = df['Date_Time'].apply(lambda x: x.strftime('%Y-%m-%d %H:%M:%S')) The sql insert command then worked.

answered Feb 14, 2021 at 16:52

Jonathan Stearns

3142 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

Dataframe with datetime64 dtype insert into to postgressql timestamp column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related