[Python] Clone data with new table in oracle using pandas

I am trying to clone SCOTT.EMP table with TEST table. However datatypes are not matching.

Please advise how to create a clone of EMP with new Table "TEST" with Same datatype in python pandas.

DESC EMP:
Name Null? Type
-------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

import pandas as pd
import oracledb
from sqlalchemy import create_engine
# create engine
engine = create_engine("oracle+oracledb://scott:tiger@LAPTOP-QMH68LT9:1521?service_name=ORCLPDB")
# query Oracle and put the result into a pandas Dataframe
df= pd.read_sql('SELECT * FROM EMP', engine)
>>> df_test = df.copy()
>>> df_test.to_sql('test', con=engine,if_exists='replace',index=False,dtype={'sal':sa.types.Float,'mgr':sa.types.Float,'comm':sa.types.Float})
-1

DESC TEST
Name Null? Type
-------- ----- ----------
EMPNO NUMBER(19)
ENAME CLOB
JOB CLOB
MGR FLOAT(126)
HIREDATE DATE
SAL FLOAT(126)
COMM FLOAT(126)
DEPTNO NUMBER(19)


Observation: EMPNO : NUMBER(4) is changed to NUMBER(19) - Is this default in Pandas df Precesion ? where we can get list of default values.

ENAME VARCHAR2(10) : -> Changed to CLOB

SAL NUMBER(7,2) : -> Changed to FLOAT(126) . How we can load with Same Precesion/Scale value.

Continue reading...