python - mariaDB:列计数与第 1 行的值计数不匹配

Question

我不明白这里有什么问题。我想构建一个网络抓取工具来抓取亚马逊并将价格和名称存入数据库。但由于某种原因，它告诉我列和值不匹配。我的数据库中确实有一个名为“时间戳”的附加列，我会在其中自动输入时间，但这是由数据库处理的。我正在使用 MariaDB。一位 friend 说我也可以使用 MariaDB 的 MySQL API。

附注preis = 价格，来自德国，有时会在英语和德语之间切换，以防万一有人想知道。

import requests, time, csv, pymysql
from bs4 import BeautifulSoup as bs

#URL = input("URL")
URL = "https://www.amazon.de/gp/product/B075FTXF15/ref=crt_ewc_img_bw_3?ie=UTF8&psc=1&smid=A24FLB4J0NZBNT"
def SOUPIT (tempURL):
    URL = tempURL
    page = requests.get(URL,headers={"User-Agent":"Defined"})
    soup = bs(page.content, "html.parser")

    raw_price = soup.find(id="priceblock_ourprice").get_text()
    price = raw_price[:-2]


    raw_name = soup.find(id="productTitle").get_text()
    name = raw_name.strip()

    for i in range(0,len(name)-1):
        if name[i] == "(":
            name = name[:i]
            break
    data = [name, price, time.strftime("%H:%M:%S"), time.strftime("%d.%m.%Y")]

    return(data)

data = SOUPIT(URL)

while True:

    data = SOUPIT(URL)

    db = pymysql.connect("localhost", "root", "root", "test")
    cursor = db.cursor()

    if (data == None):
        break
        print("break")
    else:
        name = data[0]
        preis = data[1]
        sql = """INSERT INTO amazon_preise (Name, Preis) VALUES ('{}',{})""".format(name,preis)
        cursor.execute(sql)
        db.commit()
        print("success")

    print(data)
    time.sleep(60)

错误信息:

Traceback (most recent call last):
  File "amazonscraper_advanced.py", line 43, in <module>
    cursor.execute(sql)
  File "C:\Users\...\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\cursors.py", line 170, in execute
    result = self._query(query)
  File "C:\Users\...\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\cursors.py", line 328, in _query
    conn.query(q)
  File "C:\Users\...\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Users\...\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result
    result.read()
  File "C:\Users\...\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "C:\Users\...\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\connections.py", line 684, in _read_packet
    packet.check_error()
  File "C:\Users\...\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "C:\Users\...\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1136, "Column count doesn't match value count at row 1")

Answer 1

该问题至少部分是由使用字符串格式将值插入 SQL 语句引起的。

这是抓取的数据:

>>> data = ['Sweatshirt Alien VS. Predator Z100088', '32,99', '14:08:43', '08.09.2019']
>>> name, preis, *_ = data

让我们创建 SQL 语句

>>> sql = """INSERT INTO amazon_preise (Name, Preis) VALUES ('{}',{})""".format(name,preis)

并显示它:

>>> sql
"INSERT INTO amazon_preise (Name, Preis) VALUES ('Sweatshirt Alien VS. Predator Z100088',32,99)"

观察VALUES子句包含三个个逗号分隔的值；这是因为该网页以德国风格显示货币，即用逗号分隔美分和欧元。当插入到 SQL 语句中时preis 变成两个值而不是一个。

解决此问题的正确方法是将 preis 从字符串转换为 float 或小数，并使用参数替换而不是字符串格式来插入值。

>>> fpreis = float(preis.replace(',', '.'))
>>> sql = """INSERT INTO amazon_preise (Name, Preis) VALUES (%s, %s)"""
>>> cursor.execute(sql, (name, fpreis))