您的位置: 首页  >  IT文章  >  Android端疫情数据统计与现实——Android连接Mysql

Android端疫情数据统计与现实——Android连接Mysql

分类: IT文章 • 2023-11-20 17:07:06

 一：题目要求

Android端疫情数据统计与现实——Android连接Mysql

二：项目分析

 数据爬取：借助python爬虫语句，将疫情数据爬取存储到MySQL；

 web端：结合前两周的项目，添加世界范围的疫情数据，以及简单的查询功能；

 移动端：Android开发，连接MySQL数据库，实现简单的查询功能；

三：项目结构

mysql：

Android端疫情数据统计与现实——Android连接Mysql

pycharm：

Android端疫情数据统计与现实——Android连接Mysql

 eclipse：

Android端疫情数据统计与现实——Android连接Mysql

 Android studio

Android端疫情数据统计与现实——Android连接Mysql

四：源代码

python：

import json

import numpy as np
import pymysql
import requests
from bs4 import BeautifulSoup
import datetime

url = 'https://ncov.dxy.cn/ncovh5/view/pneumonia?from=timeline&isappinstalled=0'  #请求地址
headers = {'user-agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.131 Safari/537.36'}#创建头部信息
response =  requests.get(url,headers = headers)  #发送网络请求
#print(response.content.decode('utf-8'))#以字节流形式打印网页源码
content = response.content.decode('utf-8')
#print(content)
soup = BeautifulSoup(content, 'html.parser')
listA = soup.find_all(name='script',attrs={"id":"getAreaStat"})
#世界确诊getAreaStat
listB = soup.find_all(name='script',attrs={"id":"getListByCountryTypeService2true"})
#listA = soup.find_all(name='div',attrs={"class":"c-touchable-feedback c-touchable-feedback-no-default"})
account = str(listA)
world_messages = str(listB)[95:-21]
messages = account[52:-21]
messages_json = json.loads(messages)
print(world_messages)
world_messages_json = json.loads(world_messages)
valuesList = []
cityList = []
worldList = []
now_time = datetime.datetime.now().strftime('%Y-%m-%d')

for k in range(len(world_messages_json)):
    worldvalue = (now_time,
             world_messages_json[k].get('countryType'),world_messages_json[k].get('continents'),world_messages_json[k].get('provinceId'),world_messages_json[k].get('provinceName'),
             world_messages_json[k].get('provinceShortName'),world_messages_json[k].get('cityName'),world_messages_json[k].get('currentConfirmedCount'),world_messages_json[k].get('confirmedCount'),
             world_messages_json[k].get('suspectedCount'),world_messages_json[k].get('curedCount'),world_messages_json[k].get('deadCount'),world_messages_json[k].get('locationId'),
             world_messages_json[k].get('countryShortCode'),)
    worldList.append(worldvalue)
for i in range(len(messages_json)):
    #value = messages_json[i]
    value = (now_time,messages_json[i].get('provinceName'),messages_json[i].get('provinceShortName'),messages_json[i].get('currentConfirmedCount'),messages_json[i].get('confirmedCount'),messages_json[i].get('suspectedCount'),messages_json[i].get('curedCount'),messages_json[i].get('deadCount'),messages_json[i].get('comment'),messages_json[i].get('locationId'),messages_json[i].get('statisticsData'))
    valuesList.append(value)
    cityValue = messages_json[i].get('cities')
    #print(cityValue)
    for j in range(len(cityValue)):
        cityValueList = (cityValue[j].get('cityName'),cityValue[j].get('currentConfirmedCount'),cityValue[j].get('confirmedCount'),cityValue[j].get('suspectedCount'),cityValue[j].get('curedCount'),cityValue[j].get('deadCount'),cityValue[j].get('locationId'),messages_json[i].get('provinceShortName'))
        #print(cityValueList)
        cityList.append(cityValueList)
    #cityList.append(cityValue)
db = pymysql.connect("localhost", "root", "123456", "mytest", charset='utf8')
cursor = db.cursor()
array = np.asarray(valuesList[0])
#sql_clean_world = "TRUNCATE TABLE world_map"
sql_clean_city = "TRUNCATE TABLE city_map"
sql_clean_json = "TRUNCATE TABLE province_data_from_json"
sql_clean_province = "TRUNCATE TABLE province_map"
sql_clean_world = "TRUNCATE TABLE world_map"
sql1 = "INSERT INTO city_map values (%s,%s,%s,%s,%s,%s,%s,%s)"
sql_world = "INSERT INTO world_map values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
#sql = "INSERT INTO province_map values (0,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') "
sql = "INSERT INTO province_map values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) "
#sql = "INSERT INTO province_map (provinceName,provinceShortName,correntConfirmedCount,confirmedCount,suspectedCount,curedCount,deadCount,comment,locationId,statisticsData) values (0,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') "
#sql = """INSERT INTO province_map (provinceName,provinceShortName,correntConfirmedCount,confirmedCount,suspectedCount,curedCount,deadCount,comment,locationId,statisticsData) values ('湖北省', '湖北', 43334, 64786, 0, 18889, 2563, '', 420000, 'https://file1.dxycdn.com/2020/0223/618/3398299751673487511-135.json')"""
value_tuple = tuple(valuesList)
cityTuple = tuple(cityList)
worldTuple = tuple(worldList)
print(worldTuple)
print(tuple(value_tuple))
try:
    #cursor.execute(sql_clean_city)
    #cursor.execute(sql_clean_province)
    #cursor.executemany(sql, value_tuple)
    #cursor.executemany(sql1,cityTuple)
    db.commit()
except:
    print('执行失败，进入回调1')
    db.rollback()

try:
    #cursor.execute(sql_clean_city)
    #cursor.execute(sql_clean_province)
    #cursor.execute(sql_clean_world)
    #cursor.executemany(sql, value_tuple)
    #cursor.executemany(sql1,cityTuple)
    cursor.executemany(sql_world, worldTuple)
    db.commit()
except:
    print('执行失败，进入回调2')
    db.rollback()
try:
    #cursor.execute(sql_clean_city)
    #cursor.execute(sql_clean_province)
    cursor.executemany(sql, value_tuple)
    #cursor.executemany(sql1,cityTuple)
    db.commit()
except:
    print('执行失败，进入回调3')
    db.rollback()

try:
    #cursor.execute(sql_clean_city)
    #cursor.execute(sql_clean_province)
    #cursor.executemany(sql, value_tuple)
    #cursor.executemany(sql1,cityTuple)
    db.commit()
except:
    print('执行失败，进入回调4')
    db.rollback()
#print(messages_json)
#print(account[52:-21])
# soupDiv = BeautifulSoup(listA,'html.parser')
# listB = soupDiv.find_all(name='div',attrs={"class":"c-gap-bottom-zero c-line-clamp2"})
#for i in listA:
    #print(i)
#listA[12]
#print(listA)


db.close()
View Code

android：

package com.example.myapplication;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库工具类：连接数据库用、获取数据库数据用
 * 相关操作数据库的方法均可写在该类
 */
public class DBUtils {

    private static String driver = "com.mysql.jdbc.Driver";// MySql驱动

    private static String user = "root";// 用户名

    private static String password = "123456";// 密码

    private static Connection getConn(String dbName) {

        Connection connection = null;
        try {
            Class.forName(driver);// 动态加载类
            String ip = "192.168.0.101";// 写成本机地址，不能写成localhost，同时手机和电脑连接的网络必须是同一个

            // 尝试建立到给定数据库URL的连接
            connection = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + dbName,
                    user, password);

        } catch (Exception e) {
            e.printStackTrace();
        }

        return connection;
    }
    public static List<information>  search(String condition, String country_name){
        List<information> list = new ArrayList<>();
        Connection connection = getConn("mytest");
        String sql = "";
        //System.out.println(condition);
        //选择条件
        if(condition.equals("国家")){
            //模糊查询
            sql = "select * from world_map where provinceName like ?";
        }
        if(condition.equals("时间")){
            sql = "select * from world_map where date like ?";
        }

        System.out.println(country_name);
        if(connection !=null){
            try {
                PreparedStatement ps = connection.prepareStatement(sql);
                if(ps!=null){
                    ps.setString(1,"%"+country_name+"%");
                    ResultSet rs = ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            information worldData = new information();
                            worldData.setCountryname(rs.getString("provinceName"));
                            worldData.setConfirmed(rs.getString("confirmedCount"));
                            worldData.setSuspected(rs.getString("suspectedCount"));
                            worldData.setDead(rs.getString("deadCount"));
                            worldData.setHealed(rs.getString("curedCount"));
                            worldData.setLastUpdateTime(rs.getString("date"));
                            list.add(worldData);
                        }
                        connection.close();
                        ps.close();
                        return list;
                    }else{
                        return null;
                    }
                }else{
                    return null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        }else{
            return null;
        }


    }

}
DBUtils 
package com.example.myapplication;
public class information {
    private int id;
    private String countryname;
    private String confirmed;
    private String suspected;
    private String dead;
    private String healed;
    private String lastUpdateTime;


    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getCountryname() {
        return countryname;
    }

    public void setCountryname(String countryname) {
        this.countryname = countryname;
    }

    public String getConfirmed() {
        return confirmed;
    }

    public void setConfirmed(String confirmed) {
        this.confirmed = confirmed;
    }

    public String getSuspected() {
        return suspected;
    }

    public void setSuspected(String suspected) {
        this.suspected = suspected;
    }

    public String getDead() {
        return dead;
    }

    public void setDead(String dead) {
        this.dead = dead;
    }

    public String getHealed() {
        return healed;
    }

    public void setHealed(String healed) {
        this.healed = healed;
    }

    public String getLastUpdateTime() {
        return lastUpdateTime;
    }

    public void setLastUpdateTime(String lastUpdateTime) {
        this.lastUpdateTime = lastUpdateTime;
    }
}
information 
package com.example.myapplication;

import android.annotation.SuppressLint;
import android.app.Activity;
import android.os.Bundle;
import android.os.Handler;
import android.os.Message;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.TextView;

import java.util.List;

@SuppressLint("Registered")
public class MainActivity extends Activity {

    private EditText et_name;
    private Button btn_get_data;
    private TextView tv_data;
    private RadioGroup rg_check;
    private RadioButton rb_date;
    private RadioButton rb_country;
    private String condition;

    @SuppressLint("HandlerLeak")
    private Handler handler = new Handler(){
        @Override
        public void handleMessage(Message msg) {

            switch (msg.what){
                case 0x11:
                    String s = (String) msg.obj;
                    tv_data.setText(s);
                    break;
                case 0x12:
                    String ss = (String) msg.obj;
                    tv_data.setText(ss);
                    break;
            }

        }
    };

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        // 控件的初始化
        btn_get_data = findViewById(R.id.btn_get_data);
        tv_data = findViewById(R.id.tv_data)
        ;
        et_name = findViewById(R.id.et_name);
        rb_date = findViewById(R.id.rb_date);
        rb_country = findViewById(R.id.rb_country);
        rg_check = findViewById(R.id.rg_select);


        rg_check.setOnCheckedChangeListener(new RadioGroup.OnCheckedChangeListener() {

            public void onCheckedChanged(RadioGroup group, int checkedId) {
                //如果‘时间’这个单选按钮被选中了
                if(rb_date.getId()==checkedId){
                    //弹出吐司通知
                    //Toast.makeText(MainActivity.this, rb_date.getText().toString(), Toast.LENGTH_LONG).show();
                    //获取选中按钮对应的文本信息
                    condition = rb_date.getText().toString().trim();
                }else if(rb_country.getId()==checkedId){
                    //Toast.makeText(MainActivity.this, rb_country.getText().toString(), Toast.LENGTH_LONG).show();
                    condition = rb_country.getText().toString().trim();
                }
            }
        });
        //如果没有选择默认按时间查询
        if (condition == null){
            condition = rb_date.getText().toString().trim();
        }
        setListener();
    }

    /**
     * 设置监听
     */
    private void setListener() {

        // 按钮点击事件
        btn_get_data.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                // 创建一个线程来连接数据库并获取数据库中对应表的数据
                new Thread(new Runnable() {
                    @Override
                    public void run() {
                        String name = et_name.getText().toString().trim();
                        //调用数据库帮助类中的方法取数据
                        List<information> list = DBUtils.search(condition,name);
                        Message message = handler.obtainMessage();
                        if (list != null) {
                            String s = "";
                            for (int i = 0; i < list.size(); i++) {
                                s += "国家：" + list.get(i).getCountryname() + "
";
                                s += "最新更新时间:" + list.get(i).getLastUpdateTime() + "
";
                                s += "确诊人数为:  " + list.get(i).getConfirmed() + "
";
                                s += "治愈人数为:  " + list.get(i).getHealed() + "
";
                                s += "死亡人数为:  " + list.get(i).getDead() + "
" + "
";
                            }
                            //0x11、0x12消息的定位标志
                            message.what = 0x12;
                            message.obj = s;
                        } else {
                            message.what = 0x11;
                            message.obj = "查询结果为空";
                        }
                        handler.sendMessage(message);

                        // 发消息通知主线程更新UI
                    }
                }).start();
            }
        });

    }

}
MainActivity 
<?xml version="1.0" encoding="utf-8"?>
<ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity"
    android:padding="15dp">

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent">

        <RadioGroup
            android:id="@+id/rg_select"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:paddingLeft="20dp"
            android:layout_marginTop="20dp">

            <RadioButton
                android:id="@+id/rb_date"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="时间"
                android:textSize="20sp"
                android:checked="true"/>

            <RadioButton
                android:id="@+id/rb_country"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="国家"
                android:textSize="20sp"/>

        </RadioGroup>
        <EditText
            android:id="@+id/et_name"
            android:padding="10dp"
            android:textSize="16sp"
            android:gravity="center"
            android:hint="输入要查询的内容"
            android:layout_below="@id/rg_select"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" />

        <Button
            android:id="@+id/btn_get_data"
            android:layout_margin="15dp"
            android:textSize="16sp"
            android:text="查询"
            android:layout_below="@id/et_name"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" />

        <TextView
            android:id="@+id/tv_data"
            android:padding="10dp"
            android:textSize="16sp"
            android:gravity="center"
            android:text="内容"
            android:layout_below="@+id/btn_get_data"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" />
    </RelativeLayout>

</ScrollView>
activity_main.xml

五：运行截图

Android端疫情数据统计与现实——Android连接MysqlAndroid端疫情数据统计与现实——Android连接Mysql

六：遇到的问题

 1：AVD模拟器无法启动，出现 emulator：PANIC:cannot find AVD system path.please define ANDROID_SDK_ROOT 错误。

    解决方法： 确保环境变量正确，若仍出现此错误采取以下方法：

         删除 AVD 里所有的镜像。在系统环境变量里设置：

        Android端疫情数据统计与现实——Android连接Mysql

       Android端疫情数据统计与现实——Android连接Mysql

            然后重启 Android Studio，使环境变量生效。

            再次打开 Tools -> AVD Manager 安装一个镜像，启动即可。

            然后就可以正常启动模拟器了。

2：Android无法连接到数据库 ：Communications link failure

   解决方法：将ip地址改为本机ip地址。若仍出现此错误，将jdbc 的jar包更换为低版本。

七：时间表

阶段 预估时间 实耗时间 出现问题 解决方案
爬取数据 30min 25min 获取数据无法转换为json格式 打印输出爬取数据，删除多余字符
编写web查询 15min 18min    
绘制世界地图 1h 2h 找不到world.js资源；地图无法显示 借助网络查询
Android连接MySQL 1h 6h 连接数据库失败 将jdbc-connector-java.jar驱动换为低版本
编写Android查询

1h

 45min    

相关推荐