使用pandas访问mysql并制作视图

接着学习《利用python 进行数据分析》，看到了第六章数据加载内容，于是制作了一个使用pandas访问Mysql并通过多张表的外键构建视图的例子，分享下

用到的工具和库

本次练习仍然使用anaconda集成环境，并用到了sqlalchemy、pandas和pymysql等python库。其中sqlalchemy和pandas已经由anaconda内置了，需要自己安装pymysql库.打开anaconda prompt,命令如下：

conda install pymysql

如果不用sqlalchemy，用python的标准接口访问Mysql应该也可以，但看着书上略显麻烦。百度了下sqlalchemy，真是个强大的功能，主要是做OR映射用的，用来连接数据库，有点大材小用了。

访问数据库

按照之前的做法，封装了一个访问数据库的类，如下

# 使用 pymysql sqlalchemy pandas 进行mysql数据库操作
import sqlalchemy as sqla
import pymysql as psql
import pandas as pd

# mysql数据库连接类
class mysqlHandle_pandas():
    def __init__(self,username,password,host,port,dbname):
        self.connectStr='mysql+pymysql://{0}:{1}@{2}:{3}/{4}'.format(username,password,host,port,dbname)
        self.engine=sqla.create_engine(self.connectStr)
 # 通过sql语句查询数据，并存储到pandas的DataFrame
    def readUsingSql(self,str):
        #re是一个DataFrame对象
        re=pd.read_sql(str,self.engine)
        return re

多张表创建视图

通过多张表之间的关系，利用数据表相同的键进行数据关联，代码如下：
注意：该类只能合并1对1关系的表，1对多不行

# 将多个表根据外键关系合并成一个表
class joinTable():
# 初始化待合并的主表
 def __init__(self,masterTable):
  self.masterTable=masterTable;
  # 根据关联表、主表外键，要合并的列进行合并，合并后结果存放到主表中
 def joinTableWithFK(self,slaveTable,fk,columns):
 # 判断主表中是否包含fk名称的列
  if fk in self.masterTable.columns:
  #将主表的index改为关联的键名称
   self.masterTable.index = list(self.masterTable[fk]) 
    # 被连接表的index也设置为关联键名，这样pandas就可以自动对齐了
   slaveTable.index=slaveTable[fk] 
   # 循环被连接表的所有列，并去掉关联键名称那一列，防止最后合并的数据列重复了
   for nm in slaveTable.columns:
    if nm != fk:
    # 按列将被关联表的列数据合并到主表上
     self.masterTable=self.masterTable.assign(nm=slaveTable[nm]) 
     # 由于合并后的列名称都是nm,所以需要替换成被连接表真正的列名
     self.masterTable.rename(columns={'nm':nm},inplace=True)  
    else:
     continue
  else:
   print('no such fk in masterTable')
# 按列分组
 def  groupBy(self,grouplist):
  self.masterTable = self.masterTable.groupby(grouplist)  
# 获取合并后结果  
 def getJoinedTable(self):
  return self.masterTable

测试代码

1. 连接mysql数据库

#建立数据库连接
connect=mysqlHandle_pandas('me','me111','192.168.0.12','3306','production')
# 初始化主表
master =connect.readUsingSql('select ms_id,material_id,route_id,segment_id from material_segment')

结果如下：

2. 读取其他3张被关联表

slave =connect.readUsingSql('select material_id,material_name,uom_name from material_info')
slave2 =connect.readUsingSql('select route_id,route_name from route_info')
slave3=connect.readUsingSql('select segment_id,segment_name from segment_info')

3. 连接slave,根据material_id将material_name,uom_name合并到master；连接slave2,根据route_id将route_name合并到master;连接slave3,根据segment_id将segment_name合并到master

#初始化joinTable类
jtable= joinTable(master)
#合并slave
jtable.joinTableWithFK(slave,'material_id','')
#合并slave2
jtable.joinTableWithFK(slave2,'route_id','')
#合并slave3
jtable.joinTableWithFK(slave3,'segment_id','')
result=jtable.getJoinedTable()

最后结果如下：

操作得到的数据

1. 删除带有_id的列

#注意，使用drop函数返回一个新的dataframe对象，原来的不会变
result2=result.drop(['material_id','route_id','segment_id'],axis=1)

2. 根据material_name和route_name排序分组

# groupby直接修改原来数据，不需要创建新的dataframe
reslut2.groupby(['material_name','route_name'])

得到数据之后，可以进行各种各样的数据处理，明天继续吧。

感受

学习到本课程之后，慢慢发现标准python很强大，具备了各种各样的库，能实现各种功能。但是，pandas在标准基础上封装了，使某些功能更好用。学好标准python很重要，标准python学好了，即使不用各种框架，也能完成各种任务，但效率可能会低一些。
用了框架就像在瓦良格号基础上建辽宁舰一样，不学的话，就像从0开始建航母了。虽然从0开始效率很低，但是经历了这个过程，才能更深刻的理解这些技术。很多软件工程师从开始就是在成熟框架上开发的，对底层的东西了解很少，也就不容易成长为高级软件工程师吧。
pandasAPI参考:https://pandas.pydata.org/docs/reference/frame.html