Inserting date column in dataframe to Oracle table

Question

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

What do you mean by "but it looks like this"? Is this the output when you select the data or is this the input value you like to insert? — Wernfried Domscheit
– Wernfried Domscheit, Commented Jul 13, 2021 at 13:06

Wernfried Domscheit · Accepted Answer · 2021-07-14 11:44:32Z

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.

edited Jul 14, 2021 at 11:44

answered Jul 13, 2021 at 13:13

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

banana_99 Over a year ago

Actually, by converting the python value to datetime, Oracle was able to take it. Thanks!

banana_99 Over a year ago

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?

Wernfried Domscheit Over a year ago

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.

Littlefoot · Accepted Answer · 2021-07-13 12:14:11Z

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>

answered Jul 13, 2021 at 12:14

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

2 Comments

banana_99 Over a year ago

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.

Littlefoot Over a year ago

Aha. I don't know anything about the pandas dataframe, sorry.

Collectives™ on Stack Overflow

Inserting date column in dataframe to Oracle table

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related