Save the pandas column (row / object) as VARCHAR in Oracle DB instead of CLOB (default behavior)

Question

I am trying to transfer the database to the oracle database, but the transfer takes too long because the data type of the variable is displayed as clob in oracle. However, I believe that if I convert the data type from clob to a string of 9 digits with the padded 0 , it will not take much time. data

product
000012320
000234234

Is there a way to change the data type of this variable to a string of 9 digits. so the oracle does not consider it a CLOB object. I have tried the following.

df['product']=df['product'].astype(str)

or is there something else that can slow down the transfer from python to oracle?

Answer 1

Here is a demo:

import cx_Oracle
from sqlalchemy import types, create_engine
engine = create_engine('oracle://user:password@host_or_scan_address:1521:ORACLE_SID')
#engine = create_engine('oracle://user:password@host_or_scan_address:1521/ORACLE_SERVICE_NAME')

In [32]: df
Out[32]:
           c_str  c_int   c_float
0        aaaaaaa      4  0.046531
1            bbb      6  0.987804
2  ccccccccccccc      7  0.931600

In [33]: df.to_sql('test', engine, index_label='id', if_exists='replace')

In Oracle DB:

SQL> desc test
 Name                Null?    Type
 ------------------- -------- -------------
 ID                           NUMBER(19)
 C_STR                        CLOB
 C_INT                        NUMBER(38)
 C_FLOAT                      FLOAT(126)

you can now specify the dtype SQLAlchemy: 'VARCHAR (max_length_of_C_STR_column)':

In [41]: df.c_str.str.len().max()
Out[41]: 13

In [42]: df.to_sql('test', engine, index_label='id', if_exists='replace',
   ....:           dtype={'c_str': types.VARCHAR(df.c_str.str.len().max())})

In Oracle DB:

SQL> desc test
 Name            Null?    Type
 --------------- -------- -------------------
 ID                       NUMBER(19)
 C_STR                    VARCHAR2(13 CHAR)
 C_INT                    NUMBER(38)
 C_FLOAT                  FLOAT(126)

PS to fill your line 0, please check @piRSquared answer

Answer 2

use str.zfill

df['product'].astype(str).str.zfill(9)

0    000012320
1    000234234
Name: product, dtype: object