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

Question

是否可以通过cx_Oracle模块将数据下载到csv文件，从而使浮点数字具有逗号而不是句点
我需要此功能才能将下载的csv文件正确加载到Oracle数据库中的另一个表中
当我尝试加载这样一个带有浮点数的csv文件时，我会得到一个错误：cx_Oracle。数据库错误：ORA-01722：无效的数字

我已经用熊猫图书馆解决了这个问题
我的问题：
有没有一个不使用数据帧panda的解决方案。

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(<numeric_value>,'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)

顺便说一句，将''.,''切换到'',.''将再次产生2500000000

Answer 3

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

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

由于DB将转换为字符串，因此会尊重NLS_NUMERIC_CHARACTERS的值，并使用逗号作为十进制分隔符。