convert clob to varchar python-CodePudding

I have this code to create table and import the data to the Oracle DB

# Connect to DB
conn = connect_to_oracle(user_db, pw_db)
connection = conn.raw_connection()
cursor = connection.cursor()

# Setting Param
test = pd.read_csv(r"template.csv",
                   dtype={'updated_date':'str'})

cursor.execute('''
CREATE TABLE customers_test
( updated_date VARCHAR2(100) NOT NULL,
  id_1 VARCHAR2(100) NOT NULL,
  id_2 VARCHAR2(100) NOT NULL,
  type VARCHAR2(10) NOT NULL,
  score float(10) NOT NULL,
  lvl VARCHAR2(10) NOT NULL,
  sgmt VARCHAR2(10))
''')

After that I used the to_sql function to import_data

test.to_sql('CUSTOMER_TEST', engine, schema, if_exists='append', index=False)

However when I check on DB I saw that almost all the columns were turned into CLOB

UPDATED_DATE, CLOB
ID_1, CLOB
ID_2, CLOB
TYPE, CLOB
SCORE, FLOAT
LVL, CLOB
SGMT, CLOB

So I want to know how to convert all columns into varchar2? How to prevent them becoming CLOB? Thank you

CodePudding user response：

Under normal conditions, the code should work without any issue

Python sample code

import cx_Oracle

host="ODCGRC1R.SCGER.DEV.CORP"
port=60995
sid='odcgrc1r'
user='CPL_REP'
password='Cpl3_r3p'
sid = cx_Oracle.makedsn(host, port, service_name=sid)

connection = cx_Oracle.connect(user, password, sid, encoding="UTF-8")
cursor = connection.cursor()
cursor.execute('''
CREATE TABLE customers_test
( updated_date VARCHAR2(100) NOT NULL,
  id_1 VARCHAR2(100) NOT NULL,
  id_2 VARCHAR2(100) NOT NULL,
  type VARCHAR2(10) NOT NULL,
  score float(10) NOT NULL,
  lvl VARCHAR2(10) NOT NULL,
  sgmt VARCHAR2(10))
''')

Now I execute a tnsping to my database, run the python code and connect by sqlplus to verify the table structure

C:\python>tnsping ODCGRC1R.SCGER.DEV.CORP:60995/odcgrc1r

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 29-SEP-2021 10:51:34

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Parameterdateien benutzt:
C:\Programme\Oracle\11.2.0\client\network\admin\sqlnet.ora

Adapter HOSTNAME zur Aufl÷sung des Alias benutzt
Verbindungsversuch mit (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=odcgrc1r))(ADDRESS=(PROTOCOL=TCP)(HOST=180.22.128.47)(PORT=60995)))
OK (140 ms)

C:\python> C:\python>tnsping ODCGRC1R.SCGER.DEV.CORP:60995/odcgrc1r

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 29-SEP-2021 10:51:34

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Parameterdateien benutzt:
C:\Programme\Oracle\11.2.0\client\network\admin\sqlnet.ora

Adapter HOSTNAME zur Aufl÷sung des Alias benutzt
Verbindungsversuch mit (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=odcgrc1r))(ADDRESS=(PROTOCOL=TCP)(HOST=180.22.128.47)(PORT=60995)))
OK (140 ms)

C:\python> python testconn.py

C:\python>sqlplus cpl_rep/Cpl3_r3p@//ODCGRC1R.SCGER.DEV.CORP:60995/odcgrc1r

SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 29 10:52:48 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Wed Sep 29 2021 10:47:26  02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> desc cpl_rep.customers_test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 UPDATED_DATE                              NOT NULL VARCHAR2(100)
 ID_1                                      NOT NULL VARCHAR2(100)
 ID_2                                      NOT NULL VARCHAR2(100)
 TYPE                                      NOT NULL VARCHAR2(10)
 SCORE                                     NOT NULL FLOAT(10)
 LVL                                       NOT NULL VARCHAR2(10)
 SGMT                                               VARCHAR2(10)

But if you don't specify the right dtype in the to_sql function, you can get this behaviour, as it has been stated by the question's poster.