python3爬虫+php+mysql简单应用

ゝ一纸荒年。 2022-02-05 01:43 243阅读 0赞

### 文章目录 ###

*   *  简介
     *  步骤
     *   *  1、用python写简单爬虫获取电影评分，并将评分写入到数据库中
         *  2、终端数据库操作实例
         *  3、php读取MySQL数据，并显示
     *  如图，测试成功（略丑。。。）

## 简介 ##

做一个结合python3+php+mysql的简单应用，实现python爬取一部电影的豆瓣评分，我选取的是[看不见的客人 Il testimone invisibile (2018)][Il testimone invisibile _2018]，目前的评分是9.0分。

--------------------

## 步骤 ##

1、首先用python爬虫爬取评分数据

2、将数据存到mysql数据表中

3、用php读取mysql数据表

4、前端显示评分数据

以后就可以愉快地实现各种语言之间的数据交互啦。

--------------------

### 1、用python写简单爬虫获取电影评分，并将评分写入到数据库中 ###

import pymysql
    import requests
    import re
    
    html = requests.get('https://movie.douban.com/subject/30384019/').text
    score = re.findall(r'<strong class="ll rating_num" property="v:average">(.*)</strong>', html)
    score = score[0]
    
    # 数据库host和密码用自己的
    db = pymysql.connect(host='*.*.*.*', user='root', password='******', port=3306, db='test')
    cursor = db.cursor()
    
    #如果存在SCORE表，就删除
    cursor.execute("DROP TABLE IF EXISTS SCORE")
    sql = """CREATE TABLE SCORE ( score VARCHAR(40) NOT NULL )"""
    cursor.execute(sql)
    
    # SQL 插入语句
    sql = """INSERT INTO SCORE(score) VALUES (%s)"""
    try:
       # 执行sql语句
       cursor.execute(sql, score)
       # 提交到数据库执行
       db.commit()
    except:
       # 如果发生错误则回滚
       db.rollback()
        
    db.close()

### 2、终端数据库操作实例 ###

可以看到评分已经存入进去了

[root@VM_0_14_centos test]# python3 get_score.py
    [root@VM_0_14_centos test]# mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 82
    Server version: 5.7.26 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | RUNOOB             |
    | mysql              |
    | performance_schema |
    | sys                |
    | test               |
    +--------------------+
    6 rows in set (0.00 sec)
    
    mysql> use test
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables
        -> ;
    +----------------+
    | Tables_in_test |
    +----------------+
    | SCORE          |
    +----------------+
    1 row in set (0.00 sec)
    
    mysql> select * from SCORE;
    +-------+
    | score |
    +-------+
    | 9.0   |
    +-------+
    1 row in set (0.00 sec)
    
    mysql> exit
    Bye
    [root@VM_0_14_centos test]#

### 3、php读取MySQL数据，并显示 ###

<?php
    $dbhost = 'localhost:3306';  // mysql服务器主机地址
    $dbuser = 'root';            // mysql用户名
    $dbpass = 'nlp123.';          // mysql用户名密码
    $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
    if(! $conn )
    {
        die('连接失败: ' . mysqli_error($conn));
    }
    // 设置编码，防止中文乱码
    mysqli_query($conn , "set names utf8");
     
    $sql = 'SELECT score
            FROM SCORE';
     
    mysqli_select_db( $conn, 'test' );
    $retval = mysqli_query( $conn, $sql );
    if(! $retval )
    {
        die('无法读取数据: ' . mysqli_error($conn));
    }
    echo '<h2>MySQL测试python+php+mysql<h2>';
    echo '<table border="1"><tr><td>电影评分</td></tr>';
    while($row = mysqli_fetch_assoc($retval))
    {
        echo "<tr><td> {$row['score']}</td> ".
             "</tr>";
    }
    echo '</table>';
    mysqli_close($conn);
    ?>

## 如图，测试成功（略丑。。。） ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70]

[Il testimone invisibile _2018]: https://movie.douban.com/subject/30384019/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqaDI2MjIwNzUxMjc_size_16_color_FFFFFF_t_70]: /images/20220205/376b220ba0ec4375831a6cc31576bc79.png