Код Python, который записывает на сервер SQL, не возвращает правильные числа ⇐ Python

[Anonymous]

У меня есть код Python, который извлекает данные из таблицы Excel и записывает их в базу данных SQL. Типы данных базы данных SQL следующие. Имя скважины varchar, дата, число поплавкового масла. В столбцах выстраиваются названия лунок, например, B44 содержит название лунки. Где B78 содержит значение масла. Некоторые значения масла пусты или равны 0. Вот код. И что оно возвращает. импортировать ОС
импортировать панды как PDF
# Define the directory containing the Excel files
directory = r'C:\Users\Justin.Massaro\PycharmProjects\Email\Flash.Tchono@VAALCO.com'

# Initialize an empty DataFrame to store the combined data
combined_data = pd.DataFrame()

# Loop through all files in the directory
for filename in os.listdir(directory):
if filename.endswith('.xlsx') or filename.endswith('.xls'):
file_path = os.path.join(directory, filename)

# Load the Excel file
excel_data = pd.ExcelFile(file_path)

try:
# Extract the required data
date = excel_data.parse(sheet_name='Feuil1', usecols='B', skiprows=3, nrows=1).iloc[0, 0]
well_names = excel_data.parse(sheet_name='Feuil1', usecols='B:V', skiprows=43, nrows=1).columns
oil_data = excel_data.parse(sheet_name='Feuil1', usecols='B:V', skiprows=77, nrows=1)

# Print the extracted oil data to debug
print(f"Processing file: {filename}")
print("Extracted oil data:")
print(oil_data)

# Check if the extracted data is valid
if len(well_names) == 0 or oil_data.empty:
print(f"Skipping file {filename} due to missing data.")
continue

# Transform the columns into rows with the appropriate headers
data = pd.DataFrame({
'Date': [date] * len(well_names),
'Well_Name': well_names,
'Oil': oil_data.iloc[0, :].values
})

# Replace 0 or missing values with "Null"
data['Oil'] = data['Oil'].replace(0, 'Null').fillna('Null')

# Print the data to debug
print(f"Data from file {filename}:")
print(data)

# Append the data to the combined DataFrame
combined_data = pd.concat([combined_data, data], ignore_index=True)

except IndexError as e:
print(f"Error processing file {filename}: {e}")
continue

# Print the combined data to debug
print("Combined data before date conversion:")
print(combined_data)

# Convert the 'Date' column to the desired format
if 'Date' in combined_data.columns:
combined_data['Date'] = pd.to_datetime(combined_data['Date'], errors='coerce').dt.strftime('%B %d, %Y')
else:
print("The 'Date' column is missing in the combined data.")

# Display the combined data
print("Combined data after date conversion:")
print(combined_data) Here is what it is outputing Processing file: DR 24-11-10.xlsx
Extracted oil data:
Unnamed: 1 Unnamed: 2 Unnamed: 3 519.95491 179.331387 2468.194124 \
0 NaN NaN NaN NaN NaN NaN

1600.187764 363.968437 1508.930372 1090.950292 ... 741.731596 \
0 NaN NaN NaN NaN ... NaN

603.784375 105.052114 0 0.1 0.2 1328.113399 2084.837444 0.3 0.4
0 NaN NaN NaN NaN NaN NaN NaN NaN NaN

[1 rows x 21 columns]
Data from file DR 24-11-10.xlsx:
Date Well_Name Oil
0 2024-11-11 ET-1VA Null
1 2024-11-11 ET-4H Null
2 2024-11-11 ET-5H Null
3 2024-11-11 ET-6H Null
4 2024-11-11 ET-7H Null
5 2024-11-11 ET-8H ST Null
6 2024-11-11 ET-9H Null
7 2024-11-11 ET-10H Null
8 2024-11-11 ET-11H Null
9 2024-11-11 ET-12H Null
10 2024-11-11 ETSEM-2H Null
11 2024-11-11 ETSEM-4H Null
12 2024-11-11 ETBNM-1H Null
13 2024-11-11 ETBNM-2H Null
14 2024-11-11 ETBSM-1HB Null
15 2024-11-11 ETBSM-2H Null
16 2024-11-11 EAVOM-2H Null
17 2024-11-11 EAVOM-3HST2 Null
18 2024-11-11 EEBOM-2H Null
19 2024-11-11 EEBOM-3H Null
20 2024-11-11 EEBOM-4H Null
Processing file: DR 24-11-11.xlsx
Extracted oil data:
Unnamed: 1 Unnamed: 2 Unnamed: 3 519.95491 179.331387 2468.194124 \
0 NaN NaN NaN NaN NaN NaN

1600.187764 363.968437 1508.930372 1090.950292 ... 741.731596 \
0 NaN NaN NaN NaN ... NaN

603.784375 105.052114 0 0.1 0.2 1328.113399 2084.837444 0.3 0.4
0 NaN NaN NaN NaN NaN NaN NaN NaN NaN

[1 rows x 21 columns]
Data from file DR 24-11-11.xlsx:
Date Well_Name Oil
0 2024-11-12 ET-1VA Null
1 2024-11-12 ET-4H Null
2 2024-11-12 ET-5H Null
3 2024-11-12 ET-6H Null
4 2024-11-12 ET-7H Null
5 2024-11-12 ET-8H ST Null
6 2024-11-12 ET-9H Null
7 2024-11-12 ET-10H Null
8 2024-11-12 ET-11H Null
9 2024-11-12 ET-12H Null
10 2024-11-12 ETSEM-2H Null
11 2024-11-12 ETSEM-4H Null
12 2024-11-12 ETBNM-1H Null
13 2024-11-12 ETBNM-2H Null
14 2024-11-12 ETBSM-1HB Null
15 2024-11-12 ETBSM-2H Null
16 2024-11-12 EAVOM-2H Null
17 2024-11-12 EAVOM-3HST2 Null
18 2024-11-12 EEBOM-2H Null
19 2024-11-12 EEBOM-3H Null
20 2024-11-12 EEBOM-4H Null
Combined data before date conversion:
Date Well_Name Oil
0 2024-09-05 ET-1VA Null
1 2024-09-05 ET-4H Null
2 2024-09-05 ET-5H Null
3 2024-09-05 ET-6H Null
4 2024-09-05 ET-7H Null
.. ... ... ...
268 2024-11-12 EAVOM-2H Null
269 2024-11-12 EAVOM-3HST2 Null
270 2024-11-12 EEBOM-2H Null
271 2024-11-12 EEBOM-3H Null
272 2024-11-12 EEBOM-4H Null

[273 rows x 3 columns]
Combined data after date conversion:
Date Well_Name Oil
0 September 05, 2024 ET-1VA Null
1 September 05, 2024 ET-4H Null
2 September 05, 2024 ET-5H Null
3 September 05, 2024 ET-6H Null
4 September 05, 2024 ET-7H Null
.. ... ... ...
268 November 12, 2024 EAVOM-2H Null
269 November 12, 2024 EAVOM-3HST2 Null
270 November 12, 2024 EEBOM-2H Null
271 November 12, 2024 EEBOM-3H Null
272 November 12, 2024 EEBOM-4H Null

[273 rows x 3 columns] The data should look like this Date Well Name Oil
11/10/2024 ET-6H 520
11/10/2024 ET-7H 179
11/10/2024 ET-8H ST 2,468
11/10/2024 ET-9H 1,600
11/10/2024 ET-10H 364
11/10/2024 ET-11H 1,509
11/10/2024 ET-12H 1,091
11/10/2024 ETSEM-2H 2,065
11/10/2024 ETSEM-4H 742
11/10/2024 ETBNM-1H 604
11/10/2024 ETBNM-2H 105
11/10/2024 EAVOM-3HST2 1,328
11/10/2024 EEBOM-2H 2,085
11/11/2024 ET-6H 520
11/11/2024 ET-7H 179
11/11/2024 ET-8H ST 2,468
11/11/2024 ET-9H 1,600
11/11/2024 ET-10H 364
11/11/2024 ET-11H 1,509
11/11/2024 ET-12H 1,091
11/11/2024 ETSEM-2H 2,065
11/11/2024 ETSEM-4H 742
11/11/2024 ETBNM-1H 604
11/11/2024 ETBNM-2H 105
11/11/2024 EAVOM-3HST2 1,328
11/11/2024 EEBOM-2H 2,085
All values that do not have an Oil value or are Null should be skipped. Any help is appericated.


Подробнее здесь: https://stackoverflow.com/questions/791 ... ct-numbers