ETL из Excel File в базу данных Oracle с той же определенной таблицей ⇐ Python

[Anonymous]

Я работаю над проектом, где мне нужно автоматически извлекать данные из файла Excel и загрузить их в базу данных Oracle. Я использую Python, Toad, Oracle Client и VS -код. Цель состоит в том, чтобы запустить загрузку, как только новый файл Excel добавляется в определенную папку. Пожалуйста, помогите с кодом для этого. < /P>
import os
import pandas as pd
import cx_Oracle
from time import sleep

oracle_connection_string = 'c##chbz/excelpass@localhost:1521/XE'

#create a connection to the folder where the daily excel report will be populated.
folder_path = r'C:\Users\USERR\Desktop\FolderDailyExcels'

#connection between python script and oracle database
def connect_to_oracle():
try:
connection = cx_Oracle.connect(oracle_connection_string)
print("Successfully connected:")
return connection
except cx_Oracle.DatabaseError as e:
print("There was an error connecting to the database:", e)
return None
connect_to_oracle()

#create a connection to the excel sheet
def read_excel(file_path):
try:
data = pd.read_excel(file_path)
return data
except Exception as e:
print(f"Error reading Excel file: {e}")
return None
# Now call the function like this:
file_path = r'C:\Users\USERR\Desktop\FolderDailyExcels\Excel1.xlsx'
read_excel(file_path)

#Upload Data to Oracle Database
def upload_to_oracle(data, Project_table):
connection = connect_to_oracle()
if connection is None:
return

cursor = connection.cursor()

for index, row in data.iterrows():
try:
sql = f"INSERT INTO {Project_table} (FULLNAME, NOOFATTACK, DATEOFATTACK) VALUES (:1, :2, :3)"
cursor.execute(sql, (row['column1'], row['column2'], row['column3']))
connection.commit()
except Exception as e:
print(f"Error uploading row {index}: {e}")

cursor.close()
connection.close()

#Monitor folder and automate ETL
def monitor_folder():
while True:
files = os.listdir(folder_path)
for file in files:
if file.endswith(".xlsx"):
file_path = os.path.join(folder_path, file)
print(f"Found new file: {file_path}")
data = read_excel(file_path)
if data is not None:
upload_to_oracle(data, 'Project_table') # Change 'Project_table' to your table name
sleep(60) # Check the folder every minute


Подробнее здесь: https://stackoverflow.com/questions/795 ... ined-table