reading Foundations for Analysis with Python Day 6
《Python数据分析基础》封面
上一篇笔记通过 sqlite3 创建了内存上的数据库，或者是在硬盘上的小数据库，然后对创建的数据库分别进行批量增加数据、更新表中记录、查询相关记录、删除特定记录。简单说就是增/删/改/查的内容，这些是本篇笔记的基础内容。回顾这部分内容请戳Python数据分析基础ReadingDay5_sqlite3。本篇笔记通过连接和操纵MySQL实现增/删/改/查。
目录
- MySQL简介
- 向表中插入新记录
- 查询一个表并将输出写入CSV文件
- 更新表中记录
MySQL简介
MySQL 是一个关系型数据库，由瑞典 MySQL AB 公司开发，目前属于 Oracle 旗下公司。MySQL 是最流行的关系型数据库管理系统。在开源数据库中它占着最大的份额。可以参考下面的数据库排名：
db_engines_ranking
实时排名可以参考DB_Ranking。
在Python3中一般连接和操作MySQL的方式是用mysqlclient库，书中说“如果你安装了 Anaconda Python，那么你就已经安装了这个扩展包”，但实际直接 import MySQLdb 时会报错： ModuleNotFoundError: No module named 'MySQLdb' ，通过pip安装一下就好。
anaconda_mysqldb_no_find.png
该库可以使 Python 与数据库进行交互， 所以我们使用它与在本节中创建的 MySQL 数据表进行交互。本篇笔记需要有MySQL数据库系统，《Python数据分析基础》在附录A中简要说明了MySQL的安装，如果没有安装MySQL建议看本书附录或者网上参照教程安装好环境。
pip_install_mysqlclient
向表中插入新记录
def csvDataToMySQL(input_file): #输入为csv文件路径
import csv
import MySQLdb
import sys
from datetime import datetime, date
# 连接MySQL数据库
con = MySQLdb.connect(host='localhost', port=3306, db='my_suppliers', user='用户名', passwd='数据库密码')
c = con.cursor()
# 向Suppliers表中插入数据
file_reader = csv.reader(open(input_file, 'r'), delimiter=',')
header = next(file_reader) #读取数据
for row in file_reader:
data = []
for column_index in range(len(header)):
if column_index < 4:
data.append(str(row[column_index]).lstrip('$').replace(',', '').strip())
#压制成格式化的data
else:
a_date = datetime.date(datetime.strptime(str(row[column_index]), '%m/%d/%Y'))
# %Y: year is 2016; %y: year is 15
a_date = a_date.strftime('%Y-%m-%d')
data.append(a_date)
print(data)
c.execute("""INSERT INTO Suppliers VALUES (%s, %s, %s, %s, %s);""", data)
con.commit()
# 查询Suppliers表，输出结果
c.execute("SELECT * FROM Suppliers")
rows = c.fetchall()
for row in rows:
row_list_output = [] #循环输出
for column_index in range(len(row)):
row_list_output.append(str(row[column_index]))
print(row_list_output)
csvDataToMySQL('supplier_data.csv')
上面的代码将数据从 CSV 文件中插入到MySQL的数据表中，然后展示表中的数据。MySQLdb 模块的 connect() 方法用于连接存在的数据库 my_suppliers，MySQL 建立的数据库就像一台独立计算机（服务器） ，你可以向数据库请求连接、发送数据和请求数据。在连接时，需要指定一些通用参数，包括 计算机主机名、端口号、输入用户名和密码。代码中的datetime 库用于处理输入文件中最后一列的日期数据进行处理和格式化。str(row[column_index]).lstrip('$').replace(',', '').strip() 部分处理掉美元符号$使得价格能变成数字格式。之后的代码和上一篇笔记非常相似，比较容易理解。
查询一个表并将输出写入CSV文件
数据表中有了数据之后，最常见的下一个步骤就是使用查询从表中取出一组数据，用来进行分析或满足某种商业需求。书中举的例子是：
你可能想知道哪些客户提供了最多的利润，或者哪些费用超过了具体的阈值。我们想找出 Cost 列中的值大于 1000.00的所有记录，并将这些记录所有列中的值输出。
下面的代码基于这个需求：
def mysqlDataToCsvFile(outFile):
import csv
import MySQLdb
# 连接MySQL数据库 my_suppliers
con = MySQLdb.connect(host='localhost', port=3306, db='my_suppliers',user='root', passwd='my_password')
c = con.cursor()
# 创建写文件的对象， 并写入标题行
filewriter = csv.writer(open(outFile, 'w', newline=''), delimiter=',')
header = ['Supplier Name','Invoice Number','Part Number','Cost','Purchase Date']
filewriter.writerow(header)
# 执行查询，写入csv
c.execute("""SELECT * FROM Suppliers WHERE Cost > 700.0;""")
rows = c.fetchall()
for row in rows:
filewriter.writerow(row)
filewriter.close()
mysqlDataToCsvFile('mysqlToCSVout2018.csv')
更新表中记录
说明两个例子讲了读取csv写入到数据库和读取数据库特定行到csv，用到了增加数据和查询数据的SQL，有些时候，我们不需要向表中加载新数据或做查询，而是需要更新表中已有的行。这个需求也比较容易实现，整体代码框架可以不变，只需要把INSERT 语句改变为 UPDATE 语句。具体是读取csv时，对于 CSV 输入文件中的每一行数据运行一次 SQL 语句进行对应和更新。还是关键要熟悉 UPDATE 语句，其他可以使用向表中插入新记录节的框架。
def updateMySQL(input_file):
import csv
import MySQLdb
#连接 my_suppliers
con = MySQLdb.connect(host='localhost', port=3306, db='my_suppliers',user='root', passwd='password')
c = con.cursor()
#读入csv
file_reader = csv.reader(open(input_file, 'r', newline=''), delimiter=',')
header = next(file_reader, None)
for row in file_reader:
data = []
for column_index in range(len(header)):
data.append(str(row[column_index]).strip())
print(data)
c.execute("""UPDATE Suppliers SET Cost=%s, Purchase_Date=%s WHERE Supplier_Name=%s;""", data)
con.commit()
updateMySQL('input_suppliers.csv')
MySQL的操作就记录到这里，对于数据分析师来说，一般会更经常地在命令行或者通过GUI用SQL语句操作数据库，当然通过Python等能更好地自动化查询和更新，因此本篇笔记是很重要的。回顾之前的内容，这系列笔记通过4篇笔记从简单到复杂讲了csv/Excel文件以及数据库的读取和写入。数据的输入和保存基本就是这些内容，理解实现这些目标的思路很重要。
本篇笔记的GitHub同步项目于readingForDS。关于本系列笔记有任何建议欢迎留言讨论。
网友评论