Metabase统计日用水电量

获取剩余水电信息

原理：使用selenium库自动化爬取租房水电信息

在linux中配置定时运行脚本，定期爬取信息存储在本地csv文件中，并同步到数据库中，作为大数据可视化平台数据源。

本人的租房后端数据为剩余水电量

数据库初始化语句

CREATE TABLE utility_usage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_number INT,
    remaining_water FLOAT,
    remaining_electricity FLOAT,
    date DATE
);

python程序

from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.by import By
from selenium.webdriver.common.keys import Keys
import time
import re
import csv
from datetime import datetime
import mysql.connector

def extract_numbers(text):
    # 使用正则表达式匹配所有数字
    numbers = re.findall(r'\d+\.\d+|\d+', text)
    # 将匹配到的数字连接成一个字符串
    return ''.join(numbers)

def extract_info():

    chrome_options = Options()
    chrome_options.add_argument("headless")
    chrome_options.add_argument('--no-sandbox')
    chrome_options.add_argument('--disable-gpu')
    chrome_options.add_argument('--disable-dev-shm-usage')
    chrome_options.add_argument("--window-size=1920x1080")

    # 初始化浏览器
    driver = webdriver.Chrome(options=chrome_options)

    # 打开目标网站
    driver.get('https://xxxxx/login')  # 请替换为你的目标网站URL

    # 使用XPATH定位输入框
    username_field = driver.find_element(By.XPATH, '//input[@type="tel" and @placeholder="请输入手机号"]')
    password_field = driver.find_element(By.XPATH, '//input[@type="password" and @placeholder="请输入密码"]')  # 请替换为实际的密码输入框的XPATH
    #
    username_field.send_keys('xxxxxx')  # 请替换为你的用户名
    password_field.send_keys('xxxxxxx')  # 请替换为你的密码
    password_field.send_keys(Keys.RETURN)

    # 点击登录按钮
    login_button = driver.find_element(By.XPATH, '//button[contains(@class, "van-button") and .//span[text()="登录"]]')
    login_button.click()

    # 等待页面加载
    time.sleep(2)

    # 点击“我的”
    my_button = driver.find_element(By.XPATH, '//div[contains(@class, "van-tabbar-item") and .//span[text()="我的"]]')  # 请替换为实际的“我的”按钮的XPATH
    my_button.click()

    # 等待页面加载
    time.sleep(2)

    # 点击“缴费充值”
    recharge_button = driver.find_element(By.XPATH, '//div[contains(@class, "tabcoloritem") and .//p[text()="缴费充值"]]')  # 请替换为实际的“缴费充值”按钮的XPATH
    recharge_button.click()

    # 等待页面加载
    time.sleep(2)
    #
    # 点击“充值”
    top_up_button = driver.find_element(By.XPATH, '//div[contains(@class, "tabTitle2Grey") and text()="充值"]')  # 请替换为实际的“充值”按钮的XPATH
    top_up_button.click()

    # 等待页面加载
    time.sleep(5)

    # 获取“房间号”、剩余水、剩余电信息
    room_number = driver.find_element(By.XPATH, '//div[contains(@style, "font-size: 14px") and contains(@style, "font-weight: 600")]').text
    remaining_water = driver.find_element(By.XPATH, '//p[contains(@class, "red") and contains(text(), "吨")]').text
    remaining_electricity = driver.find_element(By.XPATH, '//p[contains(@class, "red") and contains(text(), "度")]').text

    room_number = extract_numbers(room_number)
    remaining_water = extract_numbers(remaining_water)
    remaining_electricity = extract_numbers(remaining_electricity)

    # 关闭浏览器
    driver.quit()
    # 获取当前日期
    current_date = datetime.now().strftime('%Y-%m-%d %H:%M')
    info = [int(room_number), float(remaining_water), float(remaining_electricity), current_date]

    return info

def append_to_csv(info, csv_file_path):
    # 打开CSV文件，准备写入
    with open(csv_file_path, mode='a', newline='') as file:
        writer = csv.writer(file)
        # 写入信息
        writer.writerow(info)


def insert_into_mysql(info, db_config):
    # 连接到MySQL数据库
    connection = mysql.connector.connect(**db_config)
    cursor = connection.cursor()

    # 插入数据的SQL语句
    insert_query = "INSERT INTO utility_usage (room_number, remaining_water, remaining_electricity, date) VALUES (%s, %s, %s, %s)"

    # 执行插入操作
    cursor.execute(insert_query, info)

    # 提交事务
    connection.commit()

    # 关闭连接
    cursor.close()
    connection.close()

if __name__ == "__main__":
    # CSV文件路径
    csv_file_path = 'output.csv'

    # MySQL数据库配置
    db_config = {
        'host': 'xxx.xxx.xxx.xxx',
        'user': 'xxxx',
        'password': 'xxxx',
        'database': 'xxxxx'
    }

    # 提取信息
    info = extract_info()

    # 写入CSV文件
    append_to_csv(info, csv_file_path)

    # 插入到MySQL数据库
    insert_into_mysql(info, db_config)

    print(info)