简介
做一个结合python3+php+mysql的简单应用,实现python爬取一部电影的豆瓣评分,我选取的是看不见的客人 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);
?>