Python爬虫报错mysql.connector.errors.ProgrammingError:Failed processing format-parameters

Python爬取QQ音乐排行榜:

import requests
from lxml import etree
import mysql.connector

def get_data(headers,proxy):
    url="https://y.qq.com/n/ryqq/toplist/4.html"
    response=requests.get(url=url,headers=headers,proxies=proxy)
    response.encoding='utf-8'
    content=response.text
    #print(content)
    tree=etree.HTML(content)
    rank=tree.xpath('//ul[@class="songlist__list"]/li/div/div[1]/text()')
    sing_name=tree.xpath('//ul[@class="songlist__list"]/li/div/div[3]//a[2]/text()')
    singer=tree.xpath('//ul[@class="songlist__list"]/li/div/div[4]/a/text()')
    sing_list=[]
    for i in range(0,len(rank)):
        sing_list.append((rank[i],sing_name[i],singer[i]))
    return sing_list

def save_database(sing_list):
    # 获取数据库连接
    #conn=mysql.connector.connect(host='localhost',user='root',passwd='123456',database='python_spider')
    conn=mysql.connector.connect(host='localhost',user='root',passwd='123456',database='python_spider',charset='utf8',auth_plugin='mysql_native_password')
    # 获取cursor对象
    mycursor=conn.cursor()
    sql="insert into song_table(`id`,`song_name`,`singer_name`) values(%s,%s,%s)"
    #执行sql语句   批量插入使用 executemany() 方法，该方法的第二个参数是一个元组列表,包含了我们要插入的数据
    mycursor.executemany(sql,sing_list)
    # 提交
    conn.commit()
    print(mycursor.rowcount,"条记录插入成功!!!")

def main():
    # 伪装成浏览器
    headers = {
        'User-Agent': "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:34.0) Gecko/20100101 Firefox/34.0",
        'Cookie': '_uab_collina=166839904856664323878992; JSESSIONID=491823E27055266D3D48C7047505BA4B; BIGipServerotn=703595018.38945.0000; RAIL_EXPIRATION=1668726507287; RAIL_DEVICEID=gUl5YxY-IySUom0yAfjDBG2jprj1FqcmbQMOjAVrQCmVzKhiq41HKchsGQ5NbMZ3cL_rd_wdplvwP_7d4zAx5gIFChQnXZy_RSbsZ00JrrzYHYra3sN8zAk7LhaPFIw1IrjaU8uMYemXBhcEdHJCKBLx5VNREN3H; BIGipServerpool_passport=132383242.50215.0000; guidesStatus=off; highContrastMode=defaltMode; cursorStatus=off; fo=4rj6fj6nmaia1muu0d-kCh2NNUNVi4dm1_6tWayu--9M0Nfj4wn-dlm8XI4s5QNjr--LaneAdoz78rSV-xZcKdhG-2K4hTjJg2LSRlHE65IBRX5SX7mnUBE4_9WpTpvb0dEjoz-p__glmmjAr_-hml4Mp8nYQZ3I1v4q-hK9YYe4uGkixgCnwdhUMWc; route=c5c62a339e7744272a54643b3be5bf64; _jc_save_fromStation=%u6B66%u6C49%2CWHN; _jc_save_fromDate=2022-11-14; _jc_save_toDate=2022-11-14; _jc_save_wfdc_flag=dc; _jc_save_toStation=%u5317%u4EAC%2CBJP'
    }
    proxy = {
        'http': '223.96.90.216:8085'
    }
    sing_list=get_data(headers,proxy)
    print(sing_list)
    save_database(sing_list)
if __name__ == '__main__':
    main()

报错:

raise errors.ProgrammingError(
mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python '_elementunicoderesult' cannot be converted to a MySQL type

因为executemany() 方法的第二个参数是一个元组列表,我们已经满足了这个条件了,所以我们应该查看一下列表中的每个元组的元素的类型,在下面添加一条语句print(type(sing_list[1][0]),type(sing_list[1][1]),type(sing_list[1][2]))

def main():
    # 伪装成浏览器
    headers = {
        'User-Agent': "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:34.0) Gecko/20100101 Firefox/34.0",
        'Cookie': '_uab_collina=166839904856664323878992; JSESSIONID=491823E27055266D3D48C7047505BA4B; BIGipServerotn=703595018.38945.0000; RAIL_EXPIRATION=1668726507287; RAIL_DEVICEID=gUl5YxY-IySUom0yAfjDBG2jprj1FqcmbQMOjAVrQCmVzKhiq41HKchsGQ5NbMZ3cL_rd_wdplvwP_7d4zAx5gIFChQnXZy_RSbsZ00JrrzYHYra3sN8zAk7LhaPFIw1IrjaU8uMYemXBhcEdHJCKBLx5VNREN3H; BIGipServerpool_passport=132383242.50215.0000; guidesStatus=off; highContrastMode=defaltMode; cursorStatus=off; fo=4rj6fj6nmaia1muu0d-kCh2NNUNVi4dm1_6tWayu--9M0Nfj4wn-dlm8XI4s5QNjr--LaneAdoz78rSV-xZcKdhG-2K4hTjJg2LSRlHE65IBRX5SX7mnUBE4_9WpTpvb0dEjoz-p__glmmjAr_-hml4Mp8nYQZ3I1v4q-hK9YYe4uGkixgCnwdhUMWc; route=c5c62a339e7744272a54643b3be5bf64; _jc_save_fromStation=%u6B66%u6C49%2CWHN; _jc_save_fromDate=2022-11-14; _jc_save_toDate=2022-11-14; _jc_save_wfdc_flag=dc; _jc_save_toStation=%u5317%u4EAC%2CBJP'
    }
    proxy = {
        'http': '223.96.90.216:8085'
    }
    sing_list=get_data(headers,proxy)
    #print(sing_list)
    print(type(sing_list[1][0]),type(sing_list[1][1]),type(sing_list[1][2]))  
    #print(type(sing_list[0][0]))
    save_database(sing_list)
if __name__ == '__main__':
    main()

输出结果为:

<class 'lxml.etree._ElementUnicodeResult'> <class 'lxml.etree._ElementUnicodeResult'> <class 'lxml.etree._ElementUnicodeResult'>

由此可知列表中每个元组的元素的类型是lxml.etree._ElementUnicodeResult.
所以我们只需要将列表中的每个元组的元素的类型转换成你想要的类型就可以了,如int或str
如下:

sing_list.append((int(rank[i]),str(sing_name[i]),str(singer[i])))