Getting this error while I try to insert data to MySQL->"TypeError: not all arguments converted during string formatting"

Question

I scrape some data from Amazon and I insert those data to 4 list. But when I am trying to insert those lists into a Database, I just get TypeError: not all arguments converted during string formatting.

But all the data are in string format. I tried using a tuple, but it is not working.

# Importing Requests and BeautifulSoup Module
import requests
from bs4 import BeautifulSoup
import pymysql

# Setting Base Url
base_url = "https://www.amazon.com/s/ref=lp_6503737011_pg_2?rh=n%3A16310101%2Cn%3A%2116310211%2Cn%3A2983386011%2Cn%3A6503737011&page="

# Setting range for pagination
pagination = list(range(1,3))

# Declaring Empty Data
name         = []
retailer     = []
price        = []
image_link   = []


# Looping through pagination
for num in pagination:
    url = base_url + str(num)
    # Connection Error Handler
    try:
        r = requests.get(url)
    except requests.exceptions.ConnectionError:
        r.status_code = "Connection refused"
        print("Connection Refused by the server")
    # Setting BeautifulSoup Object
    soup = BeautifulSoup(r.content, "html.parser")
    
    # Setting Div Class of Info
    g_data = soup.find_all("div", {"class": "s-item-container"})
   
    # Getting Every Data from Info Div
    for item in g_data:
            imgs = soup.findAll("img", {"class":"s-access-image"})
            for img in imgs:
                image_link.append(img['src'])

            name.append(item.contents[2].find_all('h2', {'class':'s-access-title'})[0].text)
            retailer.append(item.contents[2].find_all('span', {'class':'a-size-small'})[1].text)
            whole_number = str(item.contents[3].find_all('span', {'class':'sx-price-whole'})[0].text)
            fractional_number = str(item.contents[3].find_all('sup', {'class':'sx-price-fractional'})[0].text)
            
            price_1 = whole_number+"."+fractional_number
            price.append(price_1)

           
        

This is the code for scraping data. All is good to here.But when I try to insert data into database am getting problem.

import pymysql
db = pymysql.connect('localhost','root','','scrape')
cursor = db.cursor()

sql = """INSERT INTO wine(
NAME,RETAILER,PRICE,IMAGE_LINK) VALUES"
"""
cursor.executemany(sql, (name,retailer,price,image_link))

I am getting this error while running this code:

TypeError                                 Traceback (most recent call last)
<ipython-input-7-0fca81edd73c> in <module>()
      6 NAME,RETAILER,PRICE,IMAGE_LINK) VALUES"
      7 """
----> 8 cursor.executemany(sql, (name,retailer,price,image_link))

C:\Anaconda3\lib\site-packages\pymysql\cursors.py in executemany(self, query, args)
    193                                          self._get_db().encoding)
    194 
--> 195         self.rowcount = sum(self.execute(query, arg) for arg in args)
    196         return self.rowcount
    197 

C:\Anaconda3\lib\site-packages\pymysql\cursors.py in <genexpr>(.0)
    193                                          self._get_db().encoding)
    194 
--> 195         self.rowcount = sum(self.execute(query, arg) for arg in args)
    196         return self.rowcount
    197 

C:\Anaconda3\lib\site-packages\pymysql\cursors.py in execute(self, query, args)
    162             pass
    163 
--> 164         query = self.mogrify(query, args)
    165 
    166         result = self._query(query)

C:\Anaconda3\lib\site-packages\pymysql\cursors.py in mogrify(self, query, args)
    141 
    142         if args is not None:
--> 143             query = query % self._escape_args(args, conn)
    144 
    145         return query

TypeError: not all arguments converted during string formatting

I am not able to find any solution to solve this problem.

Answer 1

1. Your query is incomplete: you need placeholders, e.g. %s
2. .executemany() takes a container of containers as its second argument; typically this is a list of tuples

Change to:

sql = """INSERT INTO wine(NAME,RETAILER,PRICE,IMAGE_LINK) VALUES (%s,%s,%s,%s);"""

to_insert = [(a,b,c,d) for a,b,c,d in zip(name,retailer,price,image_link)]
cursor.executemany(sql,to_insert)