0

I read into a dataframe from an Excel file of 71k rows (via pandas.read_excel()) and then want to insert it into a database on a local SQL Server via turbodbc.

Code:

query = 'INSERT INTO сonnTable (ID, SiteRootID, ElementID, ElementType, Username, URL, DateTime, Type, Source, SourceName, Details, AppID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'    
cursor.executemany(query, [df.index.values+1, df['SiteID'].values, df['ElementID'].values,df['ElementType'].values,df['UserID'].values,df['Location'].values,df['DateTimeGMT'].values,df['Event'].values,df['Source'].values,df['SourceName'].values,df['EventData'].values,df['AppID'].values])

When I execute the query, it throws out the following error:

turbodbc.exceptions.DatabaseError: Invalid number of parameters (expected 12, got 71625)

Where's the cause of this error? I'm a newbie and this is my 1st attempt to perform such a task.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
evictorov
  • 33
  • 1
  • 12
  • The error message is pretty clear. You're trying to pass 71625 parameters to your query, while the query expect only 12 parameter. – Ilyes Oct 05 '19 at 13:54
  • `executemany()` expects an iterable of argument sequences, or in other words rows. You've passed it a sequence of 12 **columns**, that seem to have 71625 values each. You want to pass a list of 71625 rows of 12 columns instead. All in all maybe you should take a look at `DataFrame.to_sql()`. – Ilja Everilä Oct 05 '19 at 14:03

0 Answers0