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.

To put the comment of @balderman as an answer: do this in SQL.

Using SQL:

• avoids the overheads of copying data from the database to Python, and then back to the database

• removes the issues with data types

Try this in your favorite SQL tool, such as SQL*Plus:

CREATE TABLE new_emp AS SELECT * FROM emp;

To do this in your application code, you could do:

with Session(engine) as session:
    connection = session.connection()
    cursor = connection.connection.cursor()
    sql = """create table new_emp as select * from emp"""
    cursor.execute(sql)

But really this is the kind of thing to do in SQL*Plus since it is likely to be an uncommon operation. Or perhaps you would directly with python-oracledb without the overhead of Pandas & SQLAlchemy:

import getpass
import oracledb

un = 'cj'
cs = 'localhost/orclpdb1'
pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        sql = """create table new_emp as select * from emp"""
        cursor.execute(sql)