About Insert data to mysql database

Question

CODE:
import mysql.connector
from bs4 import BeautifulSoup
import requests
URL = "https://parade.com/937586/parade/life-quotes/"

web_page = requests.get(URL)

soup = BeautifulSoup(web_page.text, "html.parser")

quote = [(x.get_text(strip=True, separator=" ")) for x in soup.select(
        'span[data-parade-type="promoarea"] .figure_block ~ p')]

db = mysql.connector.Connect(
        host="127.0.0.1",
        user="root",
        password="Demon@",
        database="my_quotes",
        charset="utf8mb4"
    )

mycursor = db.cursor()

sql = "INSERT INTO quoteslist (id, Quotes) VALUES (%S,%S)"
mycursor.execute(sql, quote)
db.commit()
db.close()

OUTPUT ERROR:

line 32, in <module>mycursor.execute(sql, quote)
mysql.connector.errors.ProgrammingError: Not all parameters were 
used in the SQL statement

In this code, I done WebScraping all quotes in URL and I want to store data to msql.So, I create connection python server to mysql server and i created DATABASE , created table column Quotes and id, When come to insert data, I try using both execute or executemany to store data multiple rows. But its getting error, Can anyone suggest me which line code went wrong.

See image showing NULL in cloumn Not insert data

Answer 1

In your original code, the 'quote' variable is just a list of strings. However, your insert requires two VALUES. Therefore you need to split the string into its component parts - i.e. the ID and the actual quote. However, there is a problem in this particular dataset because the first token in a quote isn't necessarily a number - so you need to allow for that. Try this:-

import mysql.connector as MYSQL
import requests
URL = 'https://parade.com/937586/parade/life-quotes/'
SQL = 'INSERT INTO quoteslist (id, Quotes) VALUES (%s,%s)'
CONFIG = {
    'user': 'root',
    'password': 'Demon@',
    'host': '127.0.0.1',
    'database': 'my_quotes',
    'charset': 'utf8mb4'
}
SELECT = 'span[data-parade-type="promoarea"] .figure_block ~ p'
GT = {'strip': True, 'separator': ' '}
with requests.Session() as session:
    web_page = session.get(URL)
    web_page.raise_for_status()
    soup = BeautifulSoup(web_page.text, "html.parser")
    quote = [(x.get_text(**GT)) for x in soup.select(SELECT)]
    with MYSQL.Connect(**CONFIG) as db:
        mycursor = db.cursor()
        for q in quote:
            idx = q.split()[0]
            if idx[0].isdigit():
                text = q[len(idx):].strip()
                params = (idx.replace('.', ''), text)
                mycursor.execute(SQL, params)
        db.commit()

Note that the first token in a quote (when it's a viable index number) ends with period ('.'), so I've removed that