pyodbc insert into Access database with for loop-CodePudding

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

CodePudding user response：

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.