如何使用pythons字典将数据插入SQLite

Question

我需要一些用python将数据插入SQLite的帮助。在

我有这部分代码用于将数据插入表中，sqlDataDict是字典的名称：

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)

我也有一些字典，在循环后字典的键和值的数量不同

一旦看起来像这样：

^{pr2}$

举个例子来说：

{'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'}

当我在dict中没有一些数据时，我得到的信息是“你没有为绑定提供值”

实际上dict的大小和每次循环后的数据都不一样。 在dict中没有特定数据时，如何通过？在

编辑： 完整代码：

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

我想这就是你想要的：

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()))