using a pandas dataframe without headers to write to mysql with to_sql
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.
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?