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

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

1 сообщение • Страница 1 из 1

Anonymous

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

Цитата

Сообщение Anonymous » 14 ноя 2024, 21:02

У меня есть код Python, который извлекает данные из таблицы Excel и записывает их в базу данных SQL. Типы данных базы данных SQL следующие. Имя скважины varchar, дата, число поплавкового масла. В столбцах выстраиваются названия лунок, например, B44 содержит название лунки. Где B78 содержит значение масла. Некоторые значения масла пусты или равны 0. Вот код. И что он возвращает.

Код: Выделить всё

import os
import pandas as pd

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

Вот что он выводит:

Код: Выделить всё

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

Все значения, которые не имеют значения Oil или имеют значение NULL, должны быть пропущены. Любая помощь приветствуется.

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

1731607372

Anonymous

У меня есть код Python, который извлекает данные из таблицы Excel и записывает их в базу данных SQL.  Типы данных базы данных SQL следующие.  Имя скважины varchar, дата, число поплавкового масла.  В столбцах выстраиваются названия лунок, например, B44 содержит название лунки.  Где B78 содержит значение масла.  Некоторые значения масла пусты или равны 0. Вот код.  И что он возвращает.
[code]import os
import pandas as pd

# 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)
[/code]
Вот что он выводит:
[code]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
[/code]
Все значения, которые не имеют значения Oil или имеют значение NULL, должны быть пропущены.  Любая помощь приветствуется. 

Подробнее здесь: [url]https://stackoverflow.com/questions/79189774/python-code-that-writes-to-sql-server-not-returning-correct-numbers[/url]