Clone data with new table in oracle using pandas

Question

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.

Did you try pure sql solution? Example:CREATE TABLE new_table AS SELECT * FROM old_table; — balderman, Commented Apr 20 at 5:41
And don't use pandas, pandas is notoriously slow. Also for sql you only need the built-in sqlite3 libary. — Ξένη Γήινος, Commented Apr 20 at 5:48
Columns can be reduced later, I know that I specified float for sal to avoid error. I am looking how to load exact datatype and why number(4) converted into number(19) and how to deal for Sal column — Veera V, Commented Apr 20 at 6:37
At a high level the why is because Python floats don't work like Oracle number. If you really wanted to reduce the discrepancy you might try explicitly setting the dtype to numpy.float16 though the best bet for compatibility is to follow @balderman's advice. — JonSG, Commented Apr 21 at 14:23

Christopher Jones · Accepted Answer · 2025-04-22 07:15:17Z

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)

Collectives™ on Stack Overflow

Clone data with new table in oracle using pandas

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related