Insert ALL columns from dataframe to Access Table (dynamically)

Question

I'm happily using the following code to append a dataframe to an ms access table, but this requires hard-coding the fields.

I'd like to make this dynamic (I have other code that ensures the integrity of the access table is fit to receive the data and there is a 1:1 relationship between dataframe and table fields.

current code:

str_table = 'tblData'
cur.executemany(f"INSERT INTO [{str_table}] (Field1, Field2, Field2), VALUE (?,?,?)",
        df.itertuples(index=False))

I'm looking for something akin to removing the field names and using *:

str_table = 'tblData'
cur.executemany(f"INSERT INTO [{str_table}] (*), VALUE (?)",
        df.itertuples(index=False)) 

Is this possible, or do I need to script assessing the columns in the dataframe to create the full list of fields and values in the code?

Answer 1

You may use pd.columns to get all columns for Fields.

Value(?,?,?,...) => Count the length of pd.columns to make the ?,?,? string

"?,"*len(pd.columns)[:-1]