CODEWITHSUNDEEP
pythonpython-3.xpandasoracle-databasesqlalchemy
Jennings
Jennings

Reputation: 457

pandas df.to_sql to Oracle database datatype inconsistency

Problem

I have a pandas dataframe and I'm trying to use the pd.df.to_sql() function to an Oracle database. My Oracle database is 19.3c. Seems easy enough right? Why won't it work??

I saw in a few other another stackoverflow posts that I should be using sqlalchemy datatypes. Okay. Links:

from sqlalchemy.types import Integer, String
from sqlalchemy.dialects.oracle import NUMBER, VARCHAR2, DATE

oracle_dtypes = {
        'id' : NUMBER(38,0),
        'counts' : Integer,
        'name' : VARCHAR2(50),
        'swear_words' : String(9999)
        'date' : DATE()   
}

df_upload.to_sql(
    "oracle_table", 
    db.engine, 
    schema="SA_COVID", 
    if_exists="replace", 
    index=False
    dtype=oracle_dtypes
)

It never fails to convert random groups to CLOB or some other random datatypes. What should I do?

Things i've tried

I've tried and didn't work...

Maybe it's an Oracle specific issue?

Things I haven't tried:

Things I haven't tried:

Related Links:

Upvotes: 2

Views: 3735

Answers (2)

Jennings
Jennings

Reputation: 457

Turns out I needed to double check the incoming datatypes from the API into my pandas dataframe (made a dumb assumption the data was clean)... The API was yielding all strings, and using df.info really helped.

Needed to convert all the the integer, numeric, and dates to the appropriate datatypes in python (that was the main issue), and from there could re-map the the database datatypes. In short...

API (all strings) --> Python (set datatypes) --> Database (map datatypes using sqlalchemy)

I used the pd.Int64Dtype() for integer columns with null values, and 'datetime64[ns]' for datetimes.

Upvotes: 3

Talha Tayyab
Talha Tayyab

Reputation: 27385

I faced a similar issue when I was using df.to_sql

import sqlalchemy as sa
df_upload.to_sql(
    "oracle_table", 
    db.engine, 
    schema="SA_COVID", 
    if_exists="replace", 
    index=False
    dtype=oracle_dtypes
)

Change your dtypes like this:

oracle_dtypes = {
        'id' : sa.types.NUMBER(38,0),
        'counts' : sa.types.Integer,
        'name' : sa.types.VARCHAR2(50),
        'swear_words' : sa.types.String(9999)
        'date' : sa.types.DATE()   
}

Upvotes: 1

Related Questions