Error "[42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name") while trying to insert multiple rows in python

Question

I am trying to insert multiple rows from dataframe to SQL Server with below code. But I am getting error message:

[ODBC SQL Server Driver][SQL Server]Invalid column name 'Investment'.")

SQL table has three columns - Investment, variable, value1.

df = pd.read_excel(path, sheet_name='final')
print ("Column headings:")
df = df.melt(id_vars = 'Investment')
print(df)
for r in df.columns.values:
    df[r] = df[r].map (str)
    df[r] = df[r].map (str.strip)
tuples = [tuple (x) for x in df.values]
new_list = chunks(tuples, 1000)
query = """insert into Equity_Indicators(Investment, variable, value1) values (?, ?, ?)"""
cursor.executemany (query, new_list[0])

Values in new_list :

[('2000-01-31 00:00:00', 'VTL US Equity', '4.2572'), ('2000-02-29 00:00:00', 'VTL US Equity', '4.2572'), ('2000-03-31 00:00:00', 'VTL US Equity', '4.4384'), ('2000-04-30 00:00:00', 'VTL US Equity', '4.937'), ('2000-05-31 00:00:00', 'VTL US Equity', '4.6218')]

Answer 1

it seems that you got an error sql. first of all,you can print the sql and check it.

Answer 2

I faced this error when there is some invalid column name in my query or some incorrect field mapping in my model