python爬虫获取各币种最新汇率并写入mysql表

# 导入所需的工具包
import datetime
from urllib.parse import quote_plus

import requests
from bs4 import BeautifulSoup
import numpy as np
import pandas as pd
from pymysql import connect
from sqlalchemy import create_engine,types

# 模拟请求头
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
}

# 网站地址
url = 'https://www.shishihuilv.com/zuixin/'

# 获取响应体
response = requests.get(url=url,headers=headers,timeout=60)


# 获取响应体中对应的标签内容
soup = BeautifulSoup(response.text, 'html.parser')
span_tags = soup.find_all('span')

span_contents = [span.get_text(strip=True) for span in span_tags]
span_contents = span_contents[0:-1]  # 最后一个元素为网页注脚，不需要

# 按照4个元素一组进行拆分
list_num = int(len(span_contents) / 4)
result = np.array_split(span_contents, list_num)
result = [arr.tolist() for arr in result]

# 定义DataFrame的列名
columns = ['currency_for_exchange','currency_code','last_exchange_rate','reverse_exchange_rate','rate_date']
df = pd.DataFrame(columns=columns)

# 为数据添加汇率的日期
for i in result:
    # 添加数据日期
    i.append(datetime.date.today())
    df.loc[len(df)] = i

# 这一步是为了处理密码中带有@符号
password = quote_plus('Abc@123')
# 替换成要链接的库的url、post和user_name，组合成字符串
connect_str = "mysql+pymysql://username:{}@127.0.0.0:3306/testdb".format(password)
# 创建执行引擎
engine = create_engine(connect_str)

# 指定列的数据类型
dtype = {
    'currency_for_exchange': types.VARCHAR(64),
    'currency_code':types.VARCHAR(32),
    'last_exchange_rate':types.DECIMAL(10,6),
    'reverse_exchange_rate':types.DECIMAL(10,6),
    'rate_date':types.DATE
}

# 将DataFrame的数据内容写入数据库表中
df.to_sql(
    name='ods_currency_exchange_rate_df',   # 要写入的表名，列数量要和字段数一致
    con=engine,
    if_exists='replace',
    index=False,
    dtype=dtype,
    chunksize=500
)