python爬取数据导入mysql,然后PHP渲染到前台页面

爱被打了一巴掌 2022-12-26 04:54 125阅读 0赞

# 获取数据，存储数据，展示数据 #

获取数据使用python爬虫  
存储数据使用mysql  
展示数据使用html+php

## 获取数据，存储数据 ##

import requests
    import pymysql
    import re
    from bs4 import BeautifulSoup
    
    #向mysql数据库中创建EM表
    def create():
        db=pymysql.connect("127.0.0.1","python","000000","python");
    
        cursor=db.cursor();
        cursor.execute("DROP TABLE IF EXISTS EM");
    
        sql='''create table EM(
            id int primary key auto_increment,
            logo char(255),
            price char(255),
            auther char(255) 
        )
        '''
        cursor.execute(sql);
        db.close();
    #这是向表中插入爬取的数据
    def insert(value):
        db=pymysql.connect("127.0.0.1","python","000000","python")
    
        cursor=db.cursor()
        sql="insert into EM(logo,price,auther)values(%s,%s,%s)"
        try:
            cursor.execute(sql,value)
            db.commit()
            print("插入数据成功")
        except:
            db.rollback()
            print("插入数据失败")
        db.close()
    
    #创建表EM
    create()
    
    
    # re匹配需要的数据
    pertern = re.compile(
        r'<img.*?data-original="(.*?)".*?<span class="search_now_price">(.*?)</span>.*?<a.*?单品作者.*?title="(.*?)">.*?</a>',
        re.S)
    
    #反爬措施
    headers = { 
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.110 Safari/537.36'
    }
    #需要爬取的页面
    url = 'http://category.dangdang.com/cp01.19.34.00.00.00.html'
    res=requests.get(url=url,headers=headers)
    print(res.status_code)
    soup=BeautifulSoup(res.text,'html.parser')  #转换为bs4格式的
    data=soup.find_all('ul',attrs={ 'class':'bigimg'})
    data=str(data)
    item=re.findall(pertern,data)
    for i in item:
        print(i)
        insert(i)

//爬取该页面的书籍图片，书籍价格，以及书籍作者，然后  
存储到数据库表EM中

## 前端界面展示 ##

存放的页面

[http://47.94.33.88/pc/pachong.php][http_47.94.33.88_pc_pachong.php]

## 连接MySQL，然后查询表EM,通过循环遍历到,table ##

<?php
        $servername="127.0.0.1";	//这是mysql的地址
        $username="python";
        $password="000000";
        $DB="python";
    
        $conn=new mysqli($servername,$username,$password,$DB);
    
        if($conn->connect_error){ 
            die("连接失败:". $conn->connect_error);
        }
        $conn=new mysqli($servername,$username,$password,$DB);
    ?>
    <!doctype html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport"
              content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
        <meta http-equiv="X-UA-Compatible" content="ie=edge">
        <style type="text/css">
            table
            { 
                border-collapse: collapse;
                margin: 0 auto;
                text-align: center;
            }
            table td, table th
            { 
                border: 1px solid #cad9ea;
                color: #666;
                height: 30px;
            }
            table thead th
            { 
                background-color: #CCE8EB;
                width: 100px;
            }
            table tr:nth-child(odd)
            { 
                background: #fff;
            }
            table tr:nth-child(even)
            { 
                background: #F5FAFA;
            }
            img{ 
                width: 50px;
                height: 50px;
            }
        </style>
    
        <title>Document</title>
    </head>
    <body>
        <table>
            <tr>
                <th>id</th>
                <th>logo</th>
                <th>price</th>
                <th>auther</th>
            </tr>
    <?php
                $sql="select * from EM";
                $result=$conn->query($sql);
    
                if($result->num_rows>0)
               { 
                    //输出数据
                    while($row=$result->fetch_assoc()){ 
                        echo "<tr>";
                           echo "<td>" . $row['id'] ."</td>";
                           echo "<td>"."<img src=". $row['logo'] ." >"."</td>";
                           echo "<td>" . $row['price'] ."</td>";
                           echo "<td>" . $row['auther'] ."</td>";
    
                        echo "</tr>";
                   }
                }
            ?>
        </table>
    </body>
    </html>

[http_47.94.33.88_pc_pachong.php]: http://47.94.33.88/pc/pachong.php