pyodbc insert into Access database with for loop

Question

Trying to use python run code to write data to MS Access database showing some syntax error. can someone help me? thanks.

df = pd.read_excel(path, sheet_name='Sheet1')
acc_cnxn = pyodbc.connect(conn_str)
acc_crsr = acc_cnxn.cursor()

for index, row in df.iterrows():
    acc_crsr.executemany("""INSERT INTO testing 
                        (
                            [Col. Version],
                            Version,Entity,
                            Segment,
                            LOB,
                            [Week Starting],
                            Week,
                            Day,
                            Date,
                            Interval,
                            [col beta],
                            [col alpha],
                        ) 
                        values
                        (
                            ?,
                            ?,
                            ?,
                            ?,
                            ?,
                            ?,
                            ?,
                            ?,
                            ?,
                            ?,
                            ?,
                            ?
                            );""", row.Col. Version, row.Version, row.Entity, row.Segment, row.LOB, row.Week Starting, row.Week, row.Day, row.Date, row.Interval, row.col beta, row.col alpha)

acc_crsr.commit()

showing this error: SyntaxError: invalid syntax

Answer 1

You cannot use the dot operator to access columns with a space or special character in the name, you should be using row["Col. Version"] in this case.