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?

To use executemany the data should be a list of tuples, you currently have a list of dictionaries. Try replacing data with list(data.items()). — norie, Apr 16 '21 at 10:11
@norie it's giving me this result > AttributeError: 'list' object has no attribute 'items' — Andri Wijaya, Apr 16 '21 at 10:22
Sorry, you do need a list of tuples but that isn't the correct way to get it - you need a loop or list comprehension. — norie, Apr 16 '21 at 10:39

el_oso · Accepted Answer · 2021-04-16T12:30:05.957

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

edited Apr 16 '21 at 12:30

answered Apr 16 '21 at 10:38

el_oso

1,021
6
10

It's giving me ```pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 2 parameters were supplied', 'HY000')``` I wonder it's coming from my SQL table ```CREATE TABLE SharepointTest (No INT, Name VARCHAR(5))``` – Andri Wijaya Apr 16 '21 at 10:52
I see that's the one called parameter markers, I tried it and there's no more error message but for some reason I can print it out by select * from table, but the data isn't inserted into my SQL server table. Do you have any clue why it behaves like that? – Andri Wijaya Apr 16 '21 at 12:20
1

You must commit the change. cnxn.commit() – el_oso Apr 16 '21 at 12:25
Thank you! Apparently I'm missing the parenthesis in the commit – Andri Wijaya Apr 19 '21 at 03:10

Confused about python data types to insert into database

1 Answers1