Python中将字典列表批量提交到SQLite3数据库的实现问题

问题现象 #

用Python实现亚马逊商品数据爬取后，尝试将爬取得到的字典列表批量写入SQLite3数据库时，调用executemany方法执行插入失败，爬取得到的数据本身输出正常。

原始核心代码 #

from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as ec
import sqlite3
from datetime import datetime


con = sqlite3.connect('amazon.db')
cur = con.cursor()

cur.execute('''CREATE TABLE IF NOT EXISTS productcrawl (date TEXT, asin TEXT PRIMARY KEY, name TEXT, price TEXT, category TEXT, score REAL, reviews INTEGER, purchase TEXT, bullet TEXT, variations text)''')

asin_set = ['B015WGDX6E','B083BZVXFH']

PATH = "C:\Program Files (x86)\chromedriver.exe"
driver = webdriver.Chrome(PATH)
products = []
print('Crawling products:')
for i in asin_set:
    url = 'https://www.amazon.de/gp/product/{}'.format(i)
    driver.get(url)
    product_info = {}
    product_info['date'] = datetime.today().strftime('%Y-%m-%d')
    product_info['asin'] = i
    WebDriverWait(driver, 10).until(ec.visibility_of_element_located((By.XPATH, '//span[@id="productTitle"]')))
    try:
        name = driver.find_element_by_xpath('//span[@id="productTitle"]')
        product_info['name'] = name.text.strip()
    except:
        product_info['name'] = 0
    try:
        price = driver.find_element_by_xpath("//*[@id='price_inside_buybox']")
        product_info['price'] = price.text
    except:
        product_info['price'] = 0
    try:
        category = driver.find_element_by_xpath("//*[@id='nav-subnav']")
        product_info['category'] = category.text.split("\n", 1)[0]
    except:
        product_info['category'] = 0
    try:
        score = driver.find_element_by_xpath("//*[@id='reviewsMedley']/div/div[1]/div[2]/div[1]/div/div[2]/div/span/span")
        product_info['score'] = score.text.split(" ", 1)[0]
    except:
        product_info['score'] = 0
    try:
        reviews = driver.find_element_by_xpath("//*[@id='acrCustomerReviewText']")
        product_info['reviews'] = reviews.text.strip().split(" ", 1)[0]
    except:
        product_info['reviews'] = 0
    try:
        purchase = driver.find_element_by_xpath("//*[@id='submit.buy-now-announce']")
        product_info['purchase'] = purchase.text
    except:
        product_info['purchase'] = "out-of-stock"
    try:
        bullet = driver.find_elements_by_xpath("//*[@id='feature-bullets']/ul")
    except:
        product_info['bullet'] = 0
    finally:
        def enquiry(bullet):
            if len(bullet) == 0:
                return 0
            else:
                return 1
        if enquiry(bullet):
            product_info['bullet'] = "displaying"
        else:
            product_info['bullet'] = "not-dispalying"
    try:
        variations = driver.find_elements_by_xpath("//*[@id='color_name_0']")
        product_info['variations'] = variations
    except:
        product_info['variations'] = 0
    finally:
        def enquiry2(variations):
            if len(variations) == 0:
                return 0
            else:
                return 1
        if enquiry2(variations):
            product_info['variations'] = "displaying"
        else:
            product_info['variations'] = "not-displaying"
    products.append(product_info)  # Append scrape to dictionary
    print(str(len(products)) + ' . ', end='')  
#cur.executemany("INSERT OR IGNORE INTO productcrawl VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", products)
#con.commit()

print('DONE!')
print(products)
driver.quit()

运行输出（数据正常） #

Crawling products:
1 . 2 . DONE!
[{'date': '2021-09-19', 'asin': 'B015WGDX6E', 'name': 'Bosch Professional 12V System Akkuschrauber GSR 12V-15 (inkl. 2x2,0 Akku + Ladegerät, 39tlg.-Zubehör-Set, in Tasche) - Amazon Exclusive', 'price': '108,99 €', 'category': 'Baumarkt', 'score': '4,8', 'reviews': '16.218', 'purchase': 'Jetzt kaufen', 'bullet': 'displaying', 'variations': 'not-displaying'}, {'date': '2021-09-19', 'asin': 'B083BZVXFH', 'name': 'Kitchen Helpis® 2X Universal Waschbeckenstöpsel, 40mm Abflussstopfen mit Haarfangkette, Stöpsel Waschbecken verhindert Verstopfung & Gerüche, Abfluss Stöpsel schwarz/Chrom, Abflusstopfen', 'price': '14,99 €', 'category': 'KÜCHE, HAUSHALT & WOHNEN', 'score': '4,3', 'reviews': '505', 'purchase': 'Jetzt kaufen', 'bullet': 'not-dispalying', 'variations': 'displaying'}]

Process finished with exit code 0

问题原因 #

SQLite3的executemany方法要求传入的第二个参数是由值序列组成的列表，每个序列对应一行插入数据，匹配SQL语句中的?占位符。原代码传入的是字典列表，不符合参数要求，因此执行失败。

解决方案 #

方案1（简单修改，适配原有SQL写法） #

将代码中

products.append(product_info)  # Append scrape to dictionary

替换为

products.append(list(product_info.values()))

product_info.values()会按照字典键的定义顺序返回所有值，转为列表后刚好匹配插入语句的10个占位符顺序，不会修改原有数据。修改后取消插入和提交代码的注释即可正常运行。

方案2（更稳妥，避免顺序错位） #

如果担心字典键顺序和表字段顺序不一致导致数据错位，可以直接使用命名占位符写法，无需修改列表追加逻辑，直接传入原字典列表即可：

# 取消原插入语句注释，替换为以下写法
cur.executemany("INSERT OR IGNORE INTO productcrawl VALUES (:date, :asin, :name, :price, :category, :score, :reviews, :purchase, :bullet, :variations)", products)
con.commit()

内容的提问来源于stack exchange，提问作者Jan