Qfyilyi

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?

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