mysql error python -mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

Question

I'm running a class in the pipeline to save my scrapy output to a database but i'm getting an error

File "C:\Users\BRAINBOX\Downloads\freeCodeCamp-part-6-main\freeCodeCamp-part6\bookscraper\pipelines.py", line 63, in process_item self.cur.execute(sql, row) File "c:\users\brainbox\appdata\local\programs\python\python38-32\lib\site-packages\mysql\connector\cursor.py", line 598, in execute stmt = RE_PY_PARAM.sub(psub, stmt) File "c:\users\brainbox\appdata\local\programs\python\python38-32\lib\site-packages\mysql\connector\cursor.py", line 127, in _call_ raise ProgrammingError( mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

This is the error i'm getting.

Here's the code below;

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import mysql.connector

# useful for handling different item types with a single interface
from itemadapter import ItemAdapter


class BookscraperPipeline:
    def process_item(self, item, spider):
        adapter = ItemAdapter(item)
        field_names = adapter.field_names()
        for field_name in field_names:
            value = adapter.get(field_name)
            if type(value) is tuple and "£" in value:
                adapter[field_name] = value[0].replace("£", "")
            elif type(value) is not tuple and "£" in value:
                adapter[field_name] = value.replace("£", "")
        return item


class SaveToMysqlPipeline:
    def __init__(self):
        self.con = mysql.connector.connect(
            host="localhost",
            user="root",
            password="09.02girl",
            database="books",
        )
        self.cur = self.con.cursor()
        self.cur.execute("""
        CREATE TABLE IF NOT EXISTS books(
        id int NOT NULL auto_increment,
        url VARCHAR(225),
        title text,
        product_type VARCHAR(255),
        price_excl_tax DECIMAL,
        price_incl_tax DECIMAL,
        tax DECIMAL,
        price DECIMAL,
        availability VARCHAR(255),
        num_reviews VARCHAR(255),
        stars VARCHAR(255),
        category VARCHAR(225),
        description text,
        PRIMARY KEY(id)
        )

        """)

    def process_item(self, item, spider):
        rows = (
            item['url'], item['title'], item['product_type'], item['price_excl_tax'], item['price_incl_tax'],
            item['tax'], item['availability'], item['num_reviews'], item['stars'], item['category'],
            item['description'], item['price'])
        sql = "INSERT INTO books(url, title, product_type, price_excl_tax, price_incl_tax,tax, price, availability,"\
              "num_reviews, stars, category, description) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        for row in rows:
            self.cur.execute(sql, row)
        self.con.commit()
        return item

    def closespider(self, spider):
        self.cur.close()
        self.con.close()

Answer 1

First, let's understand what rows is:

        rows = (
            item['url'], item['title'], item['product_type'], item['price_excl_tax'], item['price_incl_tax'],
            item['tax'], item['availability'], item['num_reviews'], item['stars'], item['category'],
            item['description'], item['price'])

it's a set of 12 values. Now, you loop rows like

        for row in rows:
            self.cur.execute(sql, row)

and attempt to execute the script for each individual value from the 12 values inside rows, so you pass a single parameter each time to execute().

However, your query expects exactly 12 parameters:

        sql = "INSERT INTO books(url, title, product_type, price_excl_tax, price_incl_tax,tax, price, availability,"\
              "num_reviews, stars, category, description) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

So, this method already has a row from rows (the name of item clearly suggests that) and hence, you should be able to execute it, like

self.cur.execute(sql, item['url'], item['title'], item['product_type'], item['price_excl_tax'], item['price_incl_tax'], item['tax'], item['availability'], item['num_reviews'], item['stars'], item['category'], item['description'], item['price'])

but don't forget to wrap a loop around calling process_item.