1

I need to insert a pandas dataframe into an Oracle table. One of the columns is a date, but it looks like this:

09/07/2021 13:07

How could I convert it into a date type column so as Oracle takes it when inserting the value?

CREATE TABLE test (
    call_date          TIMESTAMP NOT NULL,
    cacont_acc         VARCHAR2(12) NOT NULL,
    status             VARCHAR2(5),
    exhausted          VARCHAR2(20),
    attempts           NUMBER,
    exhausted_reason   VARCHAR2(2000),
    agency             VARCHAR2(10)
);

EDIT: This is how the python process to insert the dataframe looks like:

# Insert the exhausted df into Oracle
truncate_exhausted = """
truncate table test
"""

insert_exhausted = """
INSERT INTO test (
    call_date,
    cacont_acc,
    status,
    exhausted,
    attempts,
    exhausted_reason,
    agency
) VALUES (
    :1,
    :2,
    :3,
    :4,
    :5,
    :6,
    :7
)
"""

rows = [tuple(x) for x in df_exhausted.values]

cur.execute(truncate_exhausted)
cur.executemany(insert_exhausted, rows)
conn.commit()
banana_99
  • 591
  • 5
  • 15

2 Answers2

1

That (09/07/2021 13:07) looks like a date (along with time) to me. What would you want to insert (if not that)? Is it, maybe, that you need to use to_date('09/07/2021 13:07', 'dd/mm/yyyy hh24:mi')?

Besides, if you modified the table so that date column has its default value, you wouldn't have to pass anything, e.g.

SQL> create table test
  2    (call_date   date           default sysdate,       --> this
  3     status      varchar2(5)
  4    );

Table created.

SQL> insert into test (status) values ('A');     --> no CALL_DATE column here

1 row created.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select * from test;

CALL_DATE           STATU
------------------- -----
13.07.2021 14:13:22 A

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Thanks for your answer. I can't have the default date added, I need the one included in the pandas dataframe. I've edited my question to show the full process. – banana_99 Jul 13 '21 at 12:15
  • 1
    Aha. I don't know anything about the *pandas dataframe*, sorry. – Littlefoot Jul 13 '21 at 12:16
1

Ensure the date value in python is a datetime value, not a string.

In case it cannot be changed, this workaround should work:

insert_exhausted = """
INSERT INTO test (
    call_date,
    cacont_acc,
    status,
    exhausted,
    attempts,
    exhausted_reason,
    agency
) VALUES (
    TO_TIMESTAMP(:1, 'dd/mm/yyyy hh24:mi'),
    :2,
    :3,
    :4,
    :5,
    :6,
    :7
)
"""

Update

Make a clear distinction of your data.

  • The input value: This is python value. So far, it is not clear to me whether this is a datetime data type value or a string representing a date, e.g. 09/07/2021 13:07 of format dd/mm/yyyy hh24:mi
  • The stored value of DATE or TIMESTAMP data type in your database. This value does not have any format, it is stored as https://stackoverflow.com/a/13568348/3027266
  • The (default) displayed value. This is controlled by NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT session parameters. Or you can explicitly define your own format with TO_CHAR function.
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Actually, by converting the python value to datetime, Oracle was able to take it. Thanks! – banana_99 Jul 13 '21 at 14:15
  • Sorry, I just realised that the date formats python vs Oracle do differ. For example, when passed 08/07/2021, Oracle takes 07/08/2021. How can I make Oracle take the exact date provided in the dataframe without changing it? – banana_99 Jul 14 '21 at 10:42
  • When the python datatype is a `datetime` value, then it should not matter. Elaborate when you say "Oracle takes 07/08/2021" - What is 07/08/2021? Please see my update, I have no idea which of these three values you are talking about. And please do not expect us to guess if this date means July, 8th or August 7th. – Wernfried Domscheit Jul 14 '21 at 11:45