0

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?

Patricia Rocha Faria
  • 1
  • 1
  • 3

2 Answers2

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})
ChicDance
  • 31
  • 2
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
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • 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 – Patricia Rocha Faria Nov 22 '18 at 19:09