读取EXCEL数据导入到oracle

import pandas as pd
import cx_Oracle
from datetime import datetime

# 数据库连接信息
username = '****'  # 替换为您的用户名
password = '****'    # 替换为您的密码
dsn = '192.168.10.216:1521/ORCL'              # 替换为您的数据源名称

# 读取Excel文件
excel_file = '20240701-20240731消费交易明细201261010066-20240801143548.xlsx'
df = pd.read_excel(excel_file) 

# 连接到Oracle数据库
connection = cx_Oracle.connect(username, password, dsn)
cursor = connection.cursor()

# 插入数据到WEIXIN_BILL表
for index, row in df.iterrows():
    order_no = row['工行订单号']  # 替换为Excel中实际的列名
    amount = row['订单金额']   # 替换为Excel中实际的列名
    received_amount = row['商户实收金额']  # 替换为Excel中实际的列名
    pay_time_str = row['支付时间']  # 替换为Excel中实际的列名

    # 转换支付时间为datetime对象
    pay_time = datetime.strptime(pay_time_str.strip(), '%Y-%m-%d %H:%M:%S')

    # 插入SQL语句
    insert_sql = """
    INSERT INTO WEIXIN_BILL (order_no, amount, received_amount, pay_time)
    VALUES (:order_no, :amount, :received_amount, :pay_time)
    """
    
    # 执行插入操作
    cursor.execute(insert_sql, {
        'order_no': order_no,
        'amount': amount,
        'received_amount': received_amount,
        'pay_time': pay_time
    })

# 提交事务
connection.commit()

# 关闭游标和连接
cursor.close()
connection.close()

print("数据导入成功！")