[Python] Using Python to Import data from MySql into Ms.Excel ListObject

I am trying to automate a process where we need to import data from MySQL Database in to a ListObject (Ms.Excel Table).

Environment: Ms.Office Professional 2021 Windows 10 pro Python 3.12.1

The code is as below:

import mysql.connector
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from datetime import datetime
import mysql.connector
import xlwings as xw

today = datetime.now()
test_file_path = rf"E:\ReconTest\Reconcilation Reports\QF\2025\Jan\QF - January Internal Reconciliation Summary.xlsx"

QUERY = f"""
SELECT *
FROM dailyfiledto
WHERE Cust = 'QF' AND fdate = '{today.strftime('%Y-%m-%d')}'
"""

def mysql_to_excel_listobject(query,
excel_file, sheet_name, listobject_name,
start_row, start_col):
"""
Fetches data from MySQL, loads it into a Pandas DataFrame,
and writes it to a specified Excel ListObject.

Args:
query: SQL query to execute.
excel_file: Path to the Excel file.
sheet_name: Name of the sheet containing the ListObject.
listobject_name: Name of the ListObject.
start_row: Starting row index for data insertion (1-indexed).
start_col: Starting column index for data insertion (1-indexed).
"""

try:
# Connect to MySQL database
conn = mysql.connector.connect(
host="localhost",
user="root",
password="",
database="azm"
)
cursor = conn.cursor()

# Execute the SQL query
cursor.execute(query)


# Fetch all rows from the result
data = cursor.fetchall()
# print(str(len(data)))

# Create a Pandas DataFrame from the fetched data
df = pd.DataFrame(data)


# Open the Excel workbook
wb = xw.Book(excel_file)
sheet = wb.sheets[sheet_name]

# Get the ListObject by name
listobject = sheet.range(listobject_name).listobject

# Calculate the starting cell address for data insertion
start_cell = xw.Range(sheet, row=start_row, column=start_col).address

# Clear existing data within the ListObject (optional)
listobject.data_body_range.clear_contents()

# Write the DataFrame data to the ListObject
df.to_excel(
excel_file,
sheet_name=sheet_name,
startrow=start_row - 1, # Adjust for 0-based indexing
startcol=start_col - 1, # Adjust for 0-based indexing
index=False,
header=False,
engine='openpyxl' # Use openpyxl engine for better ListObject support
)

# Save the changes to the Excel file
wb.save()

except Exception as e:
print(f"Error: {e}")

finally:
# Close the database connection
if conn:
conn.close()


workbook = xw.Book(test_file_path)

new_sheet_name = today.strftime("%d-%b")
new_sheet = workbook.sheets[new_sheet_name]
list_object = new_sheet.tables[0]
print(list_object)
listobject_range = list_object.range

# Write data into the list object in the new sheet
# source_lo = workbook[new_sheet_name].tables[0]

source_lo = list_object
# print(source_lo)
start_row = listobject_range.row + 1
#print(start_row)
start_col = listobject_range.column
start_cell = new_sheet.range((start_row, start_col))
max_rows = listobject_range.shape[0] - 1

mysql_to_excel_listobject(QUERY,
test_file_path, "22-Jan", source_lo,
5, 2
)


The error i am getting is: Error: must be real number, not Table

Using print(), I did check that SQL query result in correct number of records, so there are no issues with database and connectivity, the problem is, the above error is not pointing out to any specific line or function code to inspect any further, this is where I am stuck.

Any guidance will be appreciated.

Ahmed

Continue reading...