抓取金山词霸翻译例句信息

模仿一个视频，尝试获取金山词霸翻译例句信息：
import requests
from bs4 import BeautifulSoup
import json
from lxml import etree
from openpyxl import Workbook
import io
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36'
}

word = input("请输入要翻译的单词:")
filename=word
params = {
    'w': str(word)
}
url = 'https://www.iciba.com/word'
resp = requests.get(url,params=params).text

soup = BeautifulSoup(resp, 'html.parser')
scripts = soup.find_all('script')
print(scripts)

发现返回了一堆html代码，仔细看需要的内容在一个scripts标签之内

继续获取，并一路定位到例句所在位置

尝试保存句子及翻译信息到excel，excel名用搜索的单词；

并下载mp3到本地，mp3名字用随机生成的：

wb = Workbook()
ws = wb.active
list=[]
save_path='C:/Users/ntt/PycharmProjects/个人学习/MP3'
for item in sentence:
    for word in item['sentences']:
        print(word['en'],word['cn'],word['ttsUrl']) 
        response = requests.get(word['ttsUrl'])
        mp3 = io.BytesIO(response.content)
        file_name = word['ttsUrl'].split('/')[-1]
        savepath=save_path + '/' + file_name #截取最后一个/的随机名称
        with open(savepath, 'wb') as file:
            file.write(mp3.read())
        list = [word['en'],word['cn'],word['ttsUrl']]
        ws.append(list)

wb.save(f"{filename}.xlsx")#保存为搜索翻译单次的excel文件
wb.close() 

如此并不尽兴，再尝试写入数据库，安装windows，mysql8.0

import mysql.connector
def insertdb():
    # 连接MySQL数据库
    conn = mysql.connector.connect(
        host='localhost',  # 主机名
        user='root',  # 用户名
        password='xx',  # 密码
        database='jinshanciba'  # 数据库名
    )
    # 创建游标对象
    cursor = conn.cursor()
    # 执行CREATE TABLE语句
    sql = """
    CREATE TABLE IF NOT EXISTS test (
        id INT PRIMARY KEY AUTO_INCREMENT,
        en VARCHAR(500),
        cn VARCHAR(500),
        url VARCHAR(500)
    )
    """
    cursor.execute(sql)
    # 提交事务和关闭连接
    conn.commit()
    conn.close()

insertdb()
报错：mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

这个错误是因为你的MySQL服务器使用了 “caching_sha2_password” 认证插件，而你的Python MySQL驱动程序（mysql-connector）不支持该认证插件。

解决方法是pip install --upgrade mysql-connector-python

经过反复试错，调查写出下边函数确认可用，然后每次循环时候调用write_to_db(en, cn, url)即可，%s, %s, %s占槽位，和参数个数类型要匹配。效率并不高，大批量时还可以executemany。

import pymysql
def write_to_db(en,cn,url):
    db = pymysql.connect(host='localhost',  # 主机名。。。)
    cursor = db.cursor()
    try:
        insert_query = """
        INSERT INTO jinshanciba.sentences (en,cn,url) VALUES (%s, %s, %s)
        """
        data = (en,cn,url)
        cursor.execute(insert_query, data)
        db.commit()
    except mysql.connector.Error as error:
        print('Failed to insert into MySQL table {}'.format(error))
    cursor.close()  # db提交
    db.close() #db游标处理