如何在python模块cx_Oracle中将浮点数中的点转换为逗号？

Question

是否可以通过cx_Oracle模块将数据下载到csv文件，以便浮点数有逗号而不是点？
我需要此功能才能将下载的 csv 文件正确加载到 Oracle 数据库中的另一个表中。
当我尝试加载带有浮点数的此类 csv 文件时，出现错误：cx_Oracle.DatabaseError: ORA-01722: invalid

我已经使用 pandas 库解决了这个问题。 我的问题：
有没有不使用数据框 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()

我尝试通过更改会话来设置它，但不幸的是它对我不起作用。

# -*- 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)

如果有任何有关如何在没有 pandas 库的情况下将数据获取到 csv 文件的建议，我将不胜感激。

示例：
有问题的结果：123.45
正确结果：123,45

Answer 1

另一个可能更简单的选项：

创建一个输出类型处理程序，告诉 Oracle 以字符串形式获取值。然后用逗号替换句点：

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)

如果您只想对一个查询而不是所有查询执行此操作，请将输出类型处理程序放在光标上。

Answer 2

您可以通过 TO_CHAR(,'999999999D99999999999','NLS_NUMERIC_CHARACTERS=''.,''')进行转换，例如

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)

顺便说一句，切换 <代码>''.,'' 到'',.'' 将再次产生 2,50000000000

Answer 3

由于您正在写入文本文件，并且可能还希望避免任何 Oracle 十进制格式到 Python 二进制格式的精度问题，因此像 Anthony 所示的那样作为字符串获取具有优势。如果您想将小数分隔符转换成本移至数据库，您可以通过将其添加到原始代码中来结合他的解决方案和您的解决方案：

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

然后在打开光标后（在执行之前），添加处理程序：

cursor.outputtypehandler = output_type_handler

因为数据库执行转换为字符串时，会考虑 NLS_NUMERIC_CHARACTERS 的值，并且您会得到逗号作为小数点分隔符。