这是我参与2022首次更文挑战的第4天，活动详情查看：2022首次更文挑战

Python联动CSV和MySQL

一、确保MySQL可以使用

安装好MySQL后（最简单的安装方法就是官网下载安装包然后一步步安装），就该操作操作了～

% mysql --version
% zsh: command not found: mysql

出现command not found: mysql多半是因为你安装mysql后还没有加环境变量，所以出现这个错误。 解决方法：

# 1、输入下面命令，进入环境变量文件（注意不要%）
% vim ~/.bash_profile 
# 2、按A键进入编辑状态，粘贴下面内容
export PATH=$PATH:/usr/local/mysql/bin
# 3、按ESC键，然后按 shift + :
# 输入 wq 即可保存编辑内容
# 4、运行下面代码重启配置文件
% source ~/.bash_profile

再输入下面命令，就可以看到mysql版本相关信息啦～

% mysql --version
% mysql  Ver 8.0.22 for macos10.15 on x86_64 (MySQL Community Server - GPL)

二、简单搞点数据到CSV

2.1 页面查看与分析

这里，我们爬取下阿里云天池的学习赛页面数据

中文	英文	数据类型	备注
赛事名称	raceName	string	字符串
赛事ID	raceId	string	531842
赛事简介	brief	string	长字符串
赛事开始时间	currentSeasonStart	date	2020-11-19 17:00:00
赛事结束时间	currentSeasonEnd	date	2021-01-14 23:55:00
赛事状态	raceState	int	1 正在进行 0 已结束
参赛队伍数	teamNum	int	整数
赛事类型	type	int	整数
赛事奖金	currency	int	整数
赛事奖金符号	currencySymbol	string	字符串

嘿嘿，规划好自己需要的数据，就可以开始进行页面分析了，按住F12，对页面进行一顿刷新，刷新，刷新，看Network中的页面数据加载情况，一个个找，哐的一下你发现一个listBrief?pageNum=1&pageSize...，这就是缘分啊，数据都在这里。 点开Headers查看Request URL，你会发现，这竟然是一个数据接口，舒服呀～

这样的话，数据获取起来就简单多了～!

2.2 写几行代码

先导入一系列需要用到的包～

'''
爬取阿里云旗下大数据平台天池的 学习赛有哪些，及相关数据
地址：https://tianchi.aliyun.com/competition/gameList/coupleList

'''

import warnings
warnings.filterwarnings("ignore")
# 忽略警告
import requests
# 导入页面请求模块
from fake_useragent import UserAgent
# 导入随机生成请求头的模块
import json
# 导入json数据处理包
import pandas as pd
# 导入数据处理模块
import pymysql
# 导入数据库处理模块

如果运行发现no module name，直接pip insatll 即可，不用客气。

'''
爬取数据
'''
def get_data():
    # 请求头
    headers = {
            "User-Agent": UserAgent(verify_ssl=False,use_cache_server=False).random
        }
    # 存储数据用的列表
    csv_data = []
    
    # 表头，自己需要的数据项
    head_csv = ['raceName', 'raceId', 'brief', 'currentSeasonStart', 'currentSeasonEnd', 'raceState', 'teamNum', 'type', 'currency', 'currencySymbol']
    # 经过页面分析一共有4页，32条数据，循环爬取
    for i in range(1,5):
        # 经过页面分析找到的数据api及规律
        competition_api = 'https://tianchi.aliyun.com/competition/proxy/api/competition/api/race/listBrief?pageNum=%d&pageSize=10&type=2'%i
        # print(competition_api)
        # 发送get请求
        response_data = requests.get(competition_api,headers=headers)
        # 将获取的到的数据转化成为json格式，然后即可像处理字典一样处理数据啦
        json_data = response_data.text
        json_data = json.loads(json_data)
        # 打印观察数据
        # print(type(json_data['data']['list']))
        
        # 循环遍历每条数据，取出自己需要的数据
        for i in json_data['data']['list']:
            one_csv_data = []
            one_csv_data.append(i['raceName'])
            one_csv_data.append(i['raceId'])
            one_csv_data.append(i['brief'])
            one_csv_data.append(i['currentSeasonStart'])
            one_csv_data.append(i['currentSeasonEnd'])
            one_csv_data.append(1 if i['raceState']=='ONGOING' else 0)
            one_csv_data.append(i['teamNum'])
            one_csv_data.append(i['type'])
            one_csv_data.append(i['currency'])
            one_csv_data.append('人民币' if i['currencySymbol']=='￥' else '美元')
            csv_data.append(one_csv_data)
    return head_csv,csv_data

data = get_data()

整个代码逻辑很清晰哈，通过发现的数据接口，获取数据，转化为json格式数据然后进行遍历取出自己需要的数据存储到列表先。

紧接着我们就可以趁热，将数据存储到CSV文件啦，代码如下：

'''
将列表数据存储为csv格式文件
'''
def save_to_cvs(data):
    # 解析数据
    head_csv = data[0] # 表头
    csv_data = data[1] # 表中数据
    # 将数据转化成DataFrame格式
    csv_data =pd.DataFrame(columns=head_csv,data=csv_data,)
    # print(csv_data)
    # 调用to_csv函数将数据存储为CSV文件
    csv_data.to_csv('tianchi_competition.csv',index=False, encoding='gbk')
    
save_to_cvs(data)

数据如下：

三、Python、CSV、MySQL三军联动

3.1 关于pymysql

pymysql是Python中一个可以连接mysql的一个包（模块），需要python版本为3及以上，使用简单，很早之前写爬虫文章就有介绍过，如：xxx 中也是利用pymysql将医疗器械相关数据写入mysql中。

3.2 mysql基本操作代码

'''
新建一个数据库
'''
def create_database(database, cursor):
    # 创建database, 如果存在则删除database，然后新建一个
    cursor.execute("drop database if exists %s;"%database)
    cursor.execute("create database %s;"%database)
    print("成功创建数据库：%s"%database)
    
'''
在制定数据库中新建一个数据表
'''
def create_table(table_name, table_sql, database, cursor):
    # 选择 database 数据库
    cursor.execute("use %s;"%database)
    # 创建一个名为table的数据库
    # 如果存在table这个表则删除
    cursor.execute("drop table if exists %s;"%table_name)
    # 创建table表
    cursor.execute(table_sql)
    print("成功在数据库{0}中创建数据表：{1}".format(database,table_name))
    

'''
在指定数据库的数据表中增、删、查、改
'''
def sql_basic_operation(table_sql, database, cursor):
    # 选择 database 数据库
    cursor.execute("use %s"%database)
    # 执行sql语句
    cursor.execute(table_sql)
    print("sql语句执行成功")
    
'''
后面会继续丰富相关功能，并将加入web框架flask中进行联动
'''

3.3 连接并将数据存储到MySQL

• 连接MySQL,并新建一个数据库

# 连接数据库 并添加cursor游标
conn = pymysql.connect(host = "localhost",port = 3306,user = "你的用户名", password = "你的MySQL密码",charset="utf8")
cursor = conn.cursor()
# 数据表名称
database = "tianchi"
# 创建数据库
create_database(database, cursor)

• 创建一个数据表

table_name = "competition"
#  创建数据表，设置raceId为主键
table_sql = """create table %s(
                 raceId int not null auto_increment,
                 raceName  varchar(200),
                 brief varchar(1000),
                 currentSeasonStart date,
                 currentSeasonEnd date,
                 raceState int,
                 teamNum int,
                 type int,
                 currency int,
                 currencySymbol char(3),
                 PRIMARY KEY(raceId));
                 """%table_name
create_table(table_name, table_sql, database, cursor)

• 从CSV中读取数据

'''
读取csv文件数据
'''
def read_csv(filepath):
    # 读取csv文件，直接调用pandas的read_csv函数即可
    data = pd.read_csv(filepath, encoding='gbk')
    # print(type(data)) # <class 'pandas.core.frame.DataFrame'>
    sql_data = []
    # 遍历DataFrame格式数据，取出一行行的数据
    for row in data.itertuples():
        # print(type(row))  # <class 'pandas.core.frame.Pandas'>
        # print(getattr(row,'raceName')) # 零基础入门推荐系统 - 新闻推荐
        sql_data.append([getattr(row,'raceId'), getattr(row,'raceName'), getattr(row,'brief'), getattr(row,'currentSeasonStart'), getattr(row,'currentSeasonEnd'),
                        getattr(row,'raceState'), getattr(row,'teamNum'), getattr(row,'type'), getattr(row,'currency'), getattr(row,'currencySymbol')])
    return sql_data

filepath = 'tianchi_competition.csv'
sql_data = read_csv(filepath)

• 遍历数据，存储到MySQL

# 循环插入数据
for row in sql_data:
    # 拼接插入sql语句
    sql_insert = '''insert into {0} values({1},'{2}','{3}','{4}','{5}',{6},{7},{8},{9},'{10}');
    '''.format(table_name,row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9])                                                                                    
    # print(sql_insert)
    sql_basic_operation(sql_insert, database, cursor)
    
conn.commit()

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

3.4 查看MySQL中数据存储情况

• 终端进入mysql

mysql -hlocalhost -uroot -p

• 选择数据库，查看表数据

# 选择数据库：tianchi
use tianchi;
# 查看数据表competition中数据
select * from competition;

这样就完成了python、csv、mysql三者之间的联动哈。

四、为了学习，小分析

一个小需求： 学习赛中目前正在进行的学习赛参与人数前五的学习赛ID和学习赛名称、参与人数、简介

• Python

filepath = 'tianchi_competition.csv'
# 读取csv文件，直接调用pandas的read_csv函数即可
data = pd.read_csv(filepath, encoding='gbk')
# 学习赛中目前正在进行的学习赛且参与人数前五的学习赛ID和学习赛名称、参与人数、简介
# 1、去除已结束的比赛
data = data[data['raceState']==1]
# 2、根据teamNum进行排序，降序
data = data.sort_values(by='teamNum', axis=0, ascending=False)
# 3、取前5，并输出指定列
data.head(5).ix[:, ['raceId', 'raceName', 'teamNum', 'brief']]

\

• SQL

select raceId, raceName, teamNum, brief 
from competition 
where raceState=1 
order by teamNum DESC 
limit 5;

\

• Excel step1 选中raceState列，点击排序和筛选，选择筛选 setp2 选择raceState值为1的，表示进行中的比赛 step3 按住control键，选中并右键，选着隐藏不需要的列 step4 选中teamNum列，点击排序和筛选，按降序排序 step5 复制前5条数据到新的表，完工

从操作简易难度上来说，SQL可能是最方便的了，一条sql语句搞定，从普适性来说，Excel门槛低，所见即所得，要什么取什么。

Python呢就比较方便，特别是对于我这种使用Python多的学习者，另外Python的灵活性显然也要高很多。

五、简单总结下

简简单单，洋洋洒洒，2.5k字，百行代码，不知道大家是否有学会基本的爬虫以及Python联动CSV和MySQL的技巧，学会了记得点赞+在看+转发呀～

如果本期点赞超过30，后面我还将会加入Flask框架，做一个简单的数据可视化系统，大家可能会想这和CSV有什么关系呢？直接mysql加flask不就好了嘛，可以算作是支持数据导入功能嘛，现在看起来还很简陋，先完善其形，后面再优化其内核。

点赞 在看 留言 转发 ，四连支持，原创不易。好的，那么下期见，我是爱猫爱技术，更爱思思的老表⁽⁽ଘ( ˙꒳˙ )ଓ⁾⁾