Confused about python data types to insert into database

Question

I am trying to insert this value into SQL Server table and I'm not sure is this supposed to be a list or a dictionary.

For some context I am pulling the data from a Sharepoint list using shareplum with code like this

import json
import pandas
import pyodbc 
from shareplum import Site
from shareplum import Office365

authcookie = Office365('https://company.sharepoint.com', username='username', password='password').GetCookies()
site = Site('https://company.sharepoint.com/sites/sharepoint/', authcookie=authcookie)
sp_list = site.List('Test')
data = sp_list.GetListItems('All Items')

cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=Server;"
                      "Database=db;"
                      "Trusted_Connection=yes;")

cursor = cnxn.cursor()
insert_query = "INSERT INTO SharepointTest(No,Name) VALUES (%(No)s,%(Name)s)"
cursor.executemany(insert_query,data)
cnxn.commit

Here's the result when I used print(data)

[
    { 'No': '1', 'Name': 'Qwe' }, 
    { 'No': '2', 'Name': 'Asd' }, 
    { 'No': '3', 'Name': 'Zxc' }, 
    { 'No': '10', 'Name': 'jkl' }
]

If I tried to execute that code will shows me this message

TypeError: ('Params must be in a list, tuple, or Row', 'HY000')

What should I fix in the code?

Answer 1

convert your list of dictionaries to a list or tuple of the dictionary values.

I've done it below using list comprehension to iterate through the list and the values() method to extract the values from a dictionary

insert_query = "INSERT INTO SharepointTest(No,Name) VALUES (?, ?)" #change your sql statement to include parameter markers 
cursor.executemany(insert_query, [tuple(d.values()) for d in data])
cnxn.commit() #finally commit your changes