python - 如何将使用 Beautifulsoup 抓取的数据移动到 MySQL 数据库？

Question

我是 Beautifulsoup 的新手，我一直在尝试将单个 td 项目加载到 MySQL 表中。如何分离每个 td 以便能够填充到 MySQL 表？

我曾尝试通过 td 标记来分解内容，但我也在那里做错了。

html_url = 'https://markets.wsj.com/'
html_doc = urllib.request.urlopen(html_url).read()

soup = BeautifulSoup(html_doc, 'html.parser')
markets = soup.find(id='majorStockIndexes_moduleId')

marketRows = markets.tbody.find_all('tr')

for row in marketRows:
    for column in row.find_all('td'):
        print(column.text)
        columnType = column.text

query = "INSERT INTO MarketData1 (recordID, stock, last, priceChange, percentChange) VALUES (NULL, %s, %s, %s, %s)"
arguments = (stockName, lastValue, priceChange, percentChange)

我正在尝试获取以下 td 类以匹配以下参数:

td 类“firstCol”- stockNametd 类“dataCol”-lastValuetd 类“dataCol priceDown - priceChangetd class "dataCol last priceDown - 百分比变化

Answer 1

这段代码会帮助你，你只需要配置你的 MySQL 凭据。

import urllib
import mysql.connector
from bs4 import BeautifulSoup

html_url = 'https://markets.wsj.com/'
html_doc = urllib.request.urlopen(html_url).read()

soup = BeautifulSoup(html_doc, 'html.parser')
markets = soup.find(id='majorStockIndexes_moduleId')

marketRows = markets.tbody.find_all('tr')


mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="root",
    database="mydatabase"
)
mycursor = mydb.cursor()


for column in marketRows:
    stockName = column.find('td',class_='firstCol').text.strip()
    lastValue = column.find('td',class_='dataCol').text
    priceChange = column.find('td',class_='priceDown').text
    percentChange = column.find('td',class_='last').text

    print("stockname :",stockName)
    print('lastValue :',lastValue)
    print("priceChange :",priceChange)
    print("percentChange :",percentChange)
    print()

    query = "INSERT INTO MarketData1 (recordID, stock, last, priceChange, percentChange) VALUES (NULL, %s, %s, %s, %s)"

    arguments = (stockName, lastValue, priceChange, percentChange)

    mycursor.execute(query, arguments)

    mydb.commit()