Save pandas (string/object) column as VARCHAR in Oracle DB instead of CLOB (default behaviour)
<p>i am trying to transfer a dataframe to oracle database, but the transfer is taking too long, because the datatype of the variable is showing as <strong>clob</strong> in oracle. However i believe if i convert the datatype from <strong>clob</strong> to string of <strong>9 digits</strong> with <strong>padded 0's</strong> , it will not take that much time. data is </p>

<pre><code>product
000012320
000234234
</code></pre>

<p>is there a way to change the datatype of this variable to string of 9 digits. so that oracle does not think of it as CLOB object. i have tried the following.</p>

<pre><code>df['product']=df['product'].astype(str)
</code></pre>

<p>or is there something else that might slow the transfer from python to oracle ?</p>

<p>Here is a demo:</p>

<pre><code>import cx_Oracle
from sqlalchemy import types, create_engine
engine = create_engine('oracle://user:password@host_or_scan_address:1521/ORACLE_SID')

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')
</code></pre>

<p>In Oracle DB:</p>

<pre><code>SQL&gt; desc test
 Name                Null?    Type
 ------------------- -------- -------------
 ID                           NUMBER(19)
 C_STR                        CLOB
 C_INT                        NUMBER(38)
 C_FLOAT                      FLOAT(126)
</code></pre>

<p>now let's specify an SQLAlchemy dtype: 'VARCHAR(max_length_of_C_STR_column)':</p>

<pre><code>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())})
</code></pre>

<p>In Oracle DB:</p>

<pre><code>SQL&gt; desc test
 Name            Null?    Type
 --------------- -------- -------------------
 ID                       NUMBER(19)
 C_STR                    VARCHAR2(13 CHAR)
 C_INT                    NUMBER(38)
 C_FLOAT                  FLOAT(126)
</code></pre>

<p>PS for padding your string with 0's please check <a href="http://stackoverflow.com/a/39504563/5741205">@piRSquared's answer</a></p>

python python-3.x pandas dataframe