Sqlalchemy datetime insert into Oracle

Question

I'm using sqlalchemy and to_sql to insert my dateframe into my Oracle table. Everything works, but I added a DATE column to the end of the table, and cannot get sqlalchemy to insert that value.

Here is the short code snippet:

x = datetime.datetime.now()
y = x.strftime("%Y-%m-%d %H:%M:%S")
df['date_appended'] = y

df_ora.to_sql('table_name', con, if_exists='append', index=False, dtype={'date_appended': sqlalchemy.DateTime()})

cx_Oracle.DatabaseError) ORA-01861: literal does not match format string
'date_appended': '2020-12-01 08:18:36'

Any suggestions?

user208567 · Accepted Answer · 2020-12-01 14:37:04Z

1

According to the documentation the sqlalchemy.DateTime() type produces datetime.datetime objects and this would suggest to me that you should provide those object when saving:

x = datetime.datetime.now()
df['date_appended'] = x

df_ora.to_sql('table_name', con, if_exists='append', index=False, dtype={'date_appended': sqlalchemy.DateTime()})

Note that this is an educated guess

answered Dec 1, 2020 at 14:37

user208567

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

4 Comments

Landon Statis Over a year ago

That gives me this error: ORA-01830: date format picture ends before converting entire input string. Seems Oracle wants the column type to be TIMESTAMP, not just DATE. I find this curious........

user208567 Over a year ago

@LandonStatis if you want to use DATE then you probably want the python type datetime.date and sqlalchemy.Date()

Landon Statis Over a year ago

So, are you saying x = datetime.date.now() ??

user208567 Over a year ago

If you want the current date you should use datetime.date.today()

Collectives™ on Stack Overflow

Sqlalchemy datetime insert into Oracle

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related