While loading the data from csv file into oracle db using python code True value is displaying as 1-CodePudding

While loading the data from csv file into oracle db using python code. After loading data into oracle db True value is displaying as 1 and false value is displaying as 0 Iam using following python code:

import cx_Oracle as orcCon
from cx_Oracle import DatabaseError
import pandas as pd
import csv

csv_input=pd.read_csv(r"E:\Python_projects\test\python1.csv",index_col=False,na_values=" ").fillna('')
#csv_input.head()
#print(csv_input)

try:
    conn = orcCon.connect('scott/tiger@localhost:1522/orcl',encoding="UTF-8")
    if conn:
        print("cx_Oracle version:", orcCon.version)
        print("Database version:", conn.version)
        print("Client version:", orcCon.clientversion())
        
        # Now execute the sqlquery 
        cursor = conn.cursor()
        print("You're connected.................")
        print("TRUNCATING THE TARGET TABLE")
        cursor.execute("TRUNCATE TABLE PYTHON_TESTING")
        print("Inserting data into table")
        for i,row in csv_input.iterrows():
            sql = "INSERT INTO PYTHON_TESTING(C1,C2,C3)VALUES(:1,:2,:3)"
            cursor.execute(sql, tuple(row))
            # the connection is not autocommitted by default, so we must commit to save our changes
            conn.commit()
            #print("Record inserted successfullly")
except DatabaseError as e:
    err, = e.args
    print("Oracle-Error-Code:", err.code)
    print("Oracle-Error-Message:", err.message)

finally:
    cursor.close()
    conn.close()

I have created testing csv file as:

When data loaded into Oracle Table Data is displaying as:

Iam using python 3.9.2

CodePudding user response：

You are converting a Python bool data type to an (Oracle dialect) SQL VARCHAR2 data type. In SQL, Oracle does not have a concept of a boolean so there must be some mapping from one data type to another and the typical mapping use is True => 1 and False => 0 (if, in Python you try True == 1 then the output is True but if you try True == 'True' then the output is False).

So your Python code is mapping bool to an integer and passing it to Oracle and then Oracle is making an implicit cast from the number to a string.

You need to break the chain of implicit casts and make an explicit cast to the string values you are expecting.

One way would be to map the numeric truthy values to string literals and can use:

INSERT INTO PYTHON_TESTING(C1,C2,C3)
VALUES(
  CASE :1 WHEN 1 THEN 'TRUE' ELSE 'FALSE' END,
  CASE :2 WHEN 1 THEN 'TRUE' ELSE 'FALSE' END,
  CASE :3 WHEN 1 THEN 'TRUE' ELSE 'FALSE' END
);

Another way would be, in Python, to covert your tuple values from bool to strings; something like:

cursor.execute(sql, tuple('TRUE' if col else 'FALSE' for col in row))