1 Reply Latest reply on Nov 23, 2018 5:51 AM by Gaz in Oz

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

       

      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?

        • 1. Re: Convert python datetime to timestamp and insert in oracle database using to_sql
          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/gaz@host:port/service_name
          ...
          SQL> select col1,
            2         dummy
            3  from   dual;
          select col1,
                 *
          ERROR at line 1:
          ORA-00904: "COL1": invalid identifier
          
          
          SQL> describe dual
           Name                          Null?    Type
           ----------------------------- -------- --------------------
           DUMMY                                  VARCHAR2(1)
          
          SQL> l
            1  select col1,
            2         dummy
            3* from   dual
          SQL> 1
            1* select col1,
          SQL> c/col1,//
            1* select
          SQL> l
            1  select
            2         dummy
            3* from   dual
          SQL> /
          
          D
          -
          X
          
          1 row selected.
          
          SQL>