I have a dataframe created from an excel sheet (the source).
The excel sheet will not have a header row.
I have a table in mysql that is already created (the target). It will always be the exact same layout as the excel sheet.
source_data = pd.read_excel(full_path, sheet_name=sheet_name, skiprows=ignore_rows, header=None)
db_engine = [function the returns my mysql engine]
source_data.to_sql(name=table_name, con=db_engine, schema=schema_name, if_exists='append', index=False)
This fails with an error due to pandas using numbers as column names in the insert statement..
[SQL: INSERT INTO [tablename] (0, 1) VALUES (%(0)s, %(1)s)]
error=(pymysql.err.OperationalError) (1054, "Unknown column '0' in 'field list'
how can i get around this? Is there a different insert method i can use? do i really have to load up the dataframe with the proper column names from the table?
Maybe after importing the data into Pandas, you can rename the columns to something that is not a number, e.g. "First", "Second", etc. or [str(i) for i in range(len(source_data))]
This would resolve the issue of SQL being confused by the numerical labels.
using a pandas dataframe without headers to write to mysql with to_sql