CODEWITHSUNDEEP
pythonoracle-databasesqlalchemytimestamppandas-to-sql
Patricia Rocha Faria
Patricia Rocha Faria

Reputation: 1

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

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?

Upvotes: 0

Views: 8127

Answers (2)

ChicDance
ChicDance

Reputation: 31

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})

Upvotes: 3

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

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

Upvotes: 1

Related Questions