How to insert data into SQLite with pythons dictionary

Question

I need some help with inserting data into SQLite with python.

I have this part of code for inserting data into table, sqlDataDict is name of Dictionary:

cur.execute(''' INSERT INTO ProductAtt (imgID, productName, col1, col2, col3, col4, col5, col6, 
              col7, col8, col9, col10, col11, col12, col13, col14) 

          VALUES (:imgID, :productName, :col1, :col2, :col3, :col4,:col5, :col6, :col7, :col8, :col9, 
                  :col10, :col11, :col12, :col13, :col14 )''', sqlDataDict)

I also have sets of dictionaries where dictionary don’t have same number of keys and values after looping,

Once look like this:

{'imgID': '451841', 'productName': 'product1', 'col1': 'data1', 'col2': 'data2', 'col3': 'data3', 'col4': 'data4', 'col5': 'data5',
         'col6': 'data6', 'col7': 'data7', 'col8': 'data8', 'col9': 'data9', 'col10': 'data10', 'col11': 'data11', 'col12': 'data12', 'col13': 'data13',
          'col14': 'data14'}

Once for example like this:

{'imgID': '451841', 'productName': 'product1', 'col1': 'data1', 'col4': 'data4', 'col5': 'data5',
         'col6': 'data6', 'col7': 'data7', 'col8': 'data8', 'col9': 'data9', 'col10': 'data10', 'col11': 'data11', 'col13': 'data13',
          'col14': 'data14'}

When in dict I don’t have some of data I got massage "You did not supply a value for binding”

Actually dict is not same size and with same data after each loop. How can I pass when I don’t have particular data in dict?

EDIT: Complete code:

import requests
from bs4 import BeautifulSoup
import sqlite3

###---> Connection to SQLite database
conn=sqlite3.connect('G:Folder/attributes.db')
cur=conn.cursor()

###---> DROP existing tables in databese
cur.execute('DROP TABLE IF EXISTS ProductAtt')

###--->Creating tabel's in databese
cur.execute('''CREATE TABLE ProductAtt (imgID INTEGER PRIMARY KEY, productName TEXT,
Col1 REAL, Col2 REAL, Col3 TEXT, Col4 TEXT, Col5 TEXT, Col6 REAL,
Col7 TEXT, Col8 TEXT, Col9 TEXT, Col10 TEXT, Col11 TEXT, 
Col12 TEXT, Col13 TEXT, Col14 TEXT, Col15 TEXT)''')


input_file=('G:Folder/urls.txt')

###---> Loop linkova iz file-a
with open(input_file) as line:
    url=line.readlines()        
    #print(url)  #---> TEST print line

###---> BeautifulSoup for each URL from *.txt file
    for singleUrl in url:
        r=requests.get(singleUrl)
        soup=BeautifulSoup(r.content, "lxml")
        #print (soup)   #---> TEST print line

#==============================================================================
#  Retrieves image name [imgName] and product name [productName]                
#==============================================================================
        get_dataImg = soup.find_all("div", {"class": "product-image"})

###---> Image name
        for imgNameJpg in get_dataImg:
            imgNameJpg = imgNameJpg.a['href'].split('/')[-1]
            imgName = imgNameJpg.split('.')[0]            
            #print("imageID:", imgName)   #---> TEST print line

###--->Product name        
        get_dataName =soup.find_all("span", {"class": "product-name"})        
        for productName in get_dataName:
            productName = productName.text
            print("productName:", productName)   #---> TEST print line

###--->Dictionary imageID and productName

        nameData = {"imgID": imgName, "productName": productName}
        #print(nameData)  #---> TEST print line

#==============================================================================
# Product attributes [productAttributes] i [productValues]        
#==============================================================================        
        get_attributeName = soup.find(True, {"class": ["product-attributes", "product-attribute-value"]}).find_all('li')

###---> Dictionary        
        allDataDict = {}       
        for attData, attValues in get_attributeName:
            attData=attData.split(':')[0]
            attData=attData.split(' ')[0]
            #print(attData)     #---> TEST print line
            data = {attData: attValues.text}                        
            allDataDict.update(data)
        #print(allDataDict)     #---> TEST print line    

#==============================================================================
# New Dictionary, two in one, nameData and allDataDict INTO sqlDataDict      
#==============================================================================
        sqlDataDict = dict(list(nameData.items()) + list(allDataDict.items()))
        #print(values)   #---> TEST print line

#==============================================================================
# INTO SQLite       
#==============================================================================                

        columns = ','.join(sqlDataDict.keys())
        placeholders= ','.join('?' * len(sqlDataDict))
        sql = 'INSERT INTO ProductAtt ({}) VALUES ({})'.format(columns, placeholders)
        cur.execute(sql, sqlDataDict.values())
        conn.commit()

Answer 1

I suppose this is what you're looking for:

values = {'col1':'val1','col2':'val2'}    

columns = ', '.join(values.keys())
placeholders = ', '.join('?' * len(values))
sql = 'INSERT INTO Media ({}) VALUES ({})'.format(columns, placeholders)
cur.execute(sql, tuple(values.values()))