Is it possible to download data to a csv file by the cx_Oracle module, so that the floating point numbers have a comma instead of a dot?
I need this functionality to properly load the downloaded csv file into another table in the Oracle database.
When I try to load such a csv file with floating point numbers, I get an error: cx_Oracle.DatabaseError: ORA-01722: invalid number

I have already solved the problem using the pandas library.
My question:
Is there a solution without the use of data frame pandas.

def load_csv():
       
    conn = cx_Oracle.connect(user=db_user, password=db_userpwd, dsn=dsn, encoding="UTF-8")
    cursor = conn.cursor()

    cursor.execute(str("select * from tablename")) 

    result_set = cursor.fetchall()

    with open(table_name['schemat'] "__" table_name['tabela'] ".csv", "w") as csv_file:
        csv_writer = csv.writer(csv_file, delimiter='|', lineterminator="\n", quoting=csv.QUOTE_NONNUMERIC)
        for row in result_set:
            csv_writer.writerow(row)

    #df = pandas.read_sql("select * from tablename", conn)
    #df.to_csv(table_name['schemat'] "__" table_name['tabela'] ".csv", index = False, encoding='utf-8', decimal=',', sep='|', header=False)

    cursor.close()
    conn.close()


def export_csv():
        
    # Open connection to Oracle DB
    conn = cx_Oracle.connect(user=db_user, password=db_userpwd, dsn=dsn, encoding="UTF-8")

    # Open cursor to Oracle DB
    cursor = conn.cursor()

    batch_size = 1

    with open(table_name['schemat'] "__" table_name['tabela'] ".csv", 'r') as csv_file:
        csv_reader = csv.reader(csv_file, delimiter='|' )

        sql = sql_insert
        data = []
        for line in csv_reader:
            data.append([i for i in line])
            if len(data) % batch_size == 0:
                cursor.executemany(sql, data)
                data = []
            if data:
                cursor.executemany(sql, data)
            conn.commit()

    cursor.close()
    conn.close()

I tried to set it up by changing the session, but unfortunately it doesn't work for me.

# -*- coding: utf-8 -*- 
import csv
import os
import sys
import time
import decimal
import pandas as pd

import cx_Oracle


dsn = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)" \
      "(PORT=xxx))(CONNECT_DATA=(SERVICE_NAME = xxx)))"
db_user = "xxx"
db_userpwd = "xxx"


def init_session(conn, requested_tag):
    cursor = conn.cursor()
    cursor.execute("alter session set nls_numeric_characters = ', '")
    cursor.execute("select to_number(5/2) from dual")
    dual, = cursor.fetchone()
    print("dual=", repr(dual))

pool = cx_Oracle.SessionPool(user=db_user, password=db_userpwd,
                             dsn=dsn, session_callback=init_session, encoding="UTF-8")

with pool.acquire() as conn:

    # Open cursor to Oracle DB
    cursor = conn.cursor()
    cursor.execute("select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'")        
    nls_session_parameters, = cursor.fetchone()
    print("nls_session_parameters=", repr(nls_session_parameters))
    
    #qryString = "select * from tablename"
    #df = pd.read_sql(qryString,conn)
    #df.to_csv(table_name['schemat'] "__" table_name['tabela'] ".csv", index = False, encoding='utf-8', decimal=',') 
    
    cursor.execute(str("select * from tablename")) 

    result_set = cursor.fetchall()
    #result, = cursor.fetchone()
    #print("result is", repr(result))

    with open(table_name['schemat'] "__" table_name['tabela'] ".csv", "w") as csv_file:
        csv_writer = csv.writer(csv_file, delimiter='|', lineterminator="\n")
        for row in result_set:
            csv_writer.writerow(row)

I would be grateful for any suggestions on how I can get data to csv file without pandas library.

example:
problematic result: 123.45
correct result: 123,45

CodePudding user response：

Another, possibly simpler option:

Create an output type handler that tells Oracle to fetch the value as a string. Then replace the period with a comma:

import cx_Oracle as oracledb

def output_type_handler(cursor, name, default_type, size, precision, scale):
    if default_type == oracledb.DB_TYPE_NUMBER:
        return cursor.var(str, arraysize=cursor.arraysize,
                          outconverter=lambda s: s.replace(".", ","))

conn = oracledb.connect("user/password@host:port/service_name")
conn.outputtypehandler = output_type_handler
with conn.cursor() as cursor:
    cursor.execute("select * from TestNumbers")
    for row in cursor:
        print(row)

Put the output type handler on the cursor if you only want to do this for one query instead of all queries.

CodePudding user response：

You can do by TO_CHAR(<numeric_value>,'999999999D99999999999','NLS_NUMERIC_CHARACTERS=''.,''') conversion such as

cursor.execute("""
                  SELECT TRIM(TO_CHAR(5/2,'999999999D99999999999',
                         'NLS_NUMERIC_CHARACTERS=''.,'''))
                    FROM dual
               """)

result_set = cursor.fetchall()

with open(table_name['schemat'] "__" table_name['tabela'] ".csv", "w") as csv_file:
    csv_writer = csv.writer(csv_file, delimiter='|', lineterminator="\n")
    for row in result_set:
        csv_writer.writerow(row)

btw, switching ''.,'' to '',.'' will yield 2,50000000000 again

CodePudding user response：

Since you're writing to a text file and presumably also want to avoid any Oracle decimal format to Python binary format precision issues, fetching as a string like Anthony showed has advantages. If you want to move the decimal separator conversion cost to the DB you could combine his solution and yours by adding this to your original code:

def output_type_handler(cursor, name, default_type, size, precision, scale):
    if default_type == cx_Oracle.NUMBER:
        return cursor.var(str, arraysize=cursor.arraysize)

and then after you open the cursor (and before executing), add the handler:

cursor.outputtypehandler = output_type_handler

Since the DB does the conversion to string, the value of NLS_NUMERIC_CHARACTERS is respected and you get commas as the decimal separator.