Convert python datetime to timestamp and insert in oracle database using to_sql

4f18d79e-4bfd-461b-b351-f6ccf5b5fd13

import sqlalchemy as sa

import datetime

import itertools

...

oracle_db = sa.create_engine('oracle://username:[email protected]: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)

I get DatabaseError: ORA-00904: "DAT_ULT_ALT": invalid identifier

How can I insert a datetime to a timestamp in oracle using to_sql from pandas with SQL Alchemy engine?

Gaz in Oz

By not showing all your code, it is difficult to say what you have done wrong. Post a VERY SIMPLE and SHORT complete example that shows what you are trying to do and how you are trying to do it...

The actual Oracle error ORA-00904 suggests that the query sqlalchemy is throwing to the Oracle database contains a non-existent column name, not an issue with a date or timestamp.

Add debug your code and output to screen the actual query sqlalchemy is trying to send to the database.

As a general tip, use sqlplus to test out "stuff". If you don't already have it on your machine, you can download Oracle instantclient and tools from here:

https://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html

With basic or basic-light and sqlplus installed you can run the above sqlalchemy  debug output query in an Oracle client, connected to the database with the same credentials, and see exactly what line and where the error is occurring. Yo can also "describe" the table you are trying to insert into, or select from, or what ever.

Here's a very simple example sqlplus session with a simple SELECT query failing, to give you an idea of how you would start to debug your above error in sqlplus:

$ sqlplus gaz/[email protected]:port/service_name...SQL> select col1,  2         dummy  3  from   dual;select col1,       *ERROR at line 1:ORA-00904: "COL1": invalid identifierSQL> describe dual Name                          Null?    Type ----------------------------- -------- -------------------- DUMMY                                  VARCHAR2(1)SQL> l  1  select col1,  2         dummy  3* from   dualSQL> 1  1* select col1,SQL> c/col1,//  1* selectSQL> l  1  select  2         dummy  3* from   dualSQL> /D-X1 row selected.SQL>