How to convert python datetime to timestamp and insert in oracle database using to_sql

Question

I get DatabaseError: ORA-00904: "DAT_ULT_ALT": invalid identifier when I try to insert a datetime to a timestamp in oracle using to_sql from pandas with SQL Alchemy engine. My code:

import sqlalchemy as sa

import datetime

import itertools

...

oracle_db = sa.create_engine('oracle://username:password@host:port/database')

connection= oracle_db.connect()

...

dat_ult_alt = datetime.datetime.now()

df_plano['DAT_ULT_ALT'] = pd.Series(list(itertools.repeat(dat_ult_alt, max)))
df_plano.to_sql('table_name', connection, if_exists='append', index=False)

This code works to fields of type "Date" but does not work with fields of type "timestamp". Do you know what I need to do to convert dat_ult_alt to timestamp?

ChicDance · Accepted Answer · 2020-09-30 11:32:47Z

3

Check out the Oracle Data Types of the sqlalchemy documentation. In the module sqlalchemy.dialects.oracle you can find the datatype TIMESTAMP. Import it and set it as dtype for the relevant column like this.

from sqlalchemy.dialects.oracle import TIMESTAMP
df_plano.to_sql('table_name', connection, if_exists='append', index=False, dtype={'DAT_ULT_ALT':TIMESTAMP})

answered Sep 30, 2020 at 11:32

ChicDance

312 bronze badges

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

Comments

Kaushik Nayak · Accepted Answer · 2018-11-22 15:50:32Z

1

Not sure about sqlalchemy as I have never used it with Oracle. Here's a sample code using Cx_Oracle which works.

create table test ( tstamp TIMESTAMP);

import cx_Oracle
import datetime
conn = cx_Oracle.connect('usr/pwd@//host:1521/db')
cur = conn.cursor()
dtime=datetime.datetime.now()
cur.prepare( "INSERT INTO test(tstamp) VALUES(:ts)" )
cur.setinputsizes(ts=cx_Oracle.TIMESTAMP)
cur.execute(None, {'ts':dtime})
conn.commit()
conn.close()

select * from test;

TSTAMP                        
------------------------------
22-11-18 09:14:19.422278000 PM

answered Nov 22, 2018 at 15:50

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

1 Comment

Patricia Rocha Faria Over a year ago

I appreciate your help, but I need a solution with sqlalchemy instead of cx_oracle so that I can insert the role dataframe with 3 millions rows to the oracle table at once, with one command to_sql(), so I cannot treat

Collectives™ on Stack Overflow

How to convert python datetime to timestamp and insert in oracle database using to_sql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related