使用PyMysql库和Pandas库链接Mysql
1 系统环境
- 系统版本：Win10 64位
- Mysql版本： 8.0.15 MySQL Community Server - GPL
- pymysql版本： 0.7.9
- pandas版本：0.20.3
- sqlalchemy版本：1.1.13
- 代码编辑IDE： Jupyter1.0.0
2 使用PyMysql库链接Mysql
直接导入Pymysql库：
import pymysql
然后建立数据库连接：
conn = pymysql.connect( host='localhost', user='root', password='XXXXXX', database='data', port=3306, charset='utf8' )
此处会报一个keyError:255的异常：
- 异常原因：Mysql8.0更新了很多字符集，但是这些字符集长度超过255了，所以旧版的PyMysql不支持长度超过255的字符。
- 解决办法：更新PyMySQL包，使用conda upgrade pymysql命令更新失败，直接使用python -m pip install --upgrade pymysql更新成功，PyMySQL版本更新到0.9.3，该异常消除。
获取数据库游标：
cur = conn.cursor()
用游标执行SQL语句，将数据加载到内存：
sql = 'SELECT * FROM company' cur.execute(sql)
从内存取数赋值到变量：
data = cur.fetchall()
# 完成取数后关闭游标和数据库连接
cur.close()
conn.close()
data
((43, '北京欧应科技有限公司', "['五险一金', '扁平化管理', '创业型企业', '岗位晋升']", '唯医网', '150-500人', "['东大桥', 'CBD', '朝外']"), (53, '北京创锐文化传媒有限公司', "['技能培训', '节日礼物', '季度奖金', '岗位晋升']", '聚美优品', '2000人以上', "['东直门', '海运仓', '东四']"), ....... )
每条记录以元组的形式存放在一个大的元组内。此时，游标已经移动到数据的末尾，已经无法再继续取数。
遍历元素：
for item in data[0]: print(item)
43 北京欧应科技有限公司 ['五险一金', '扁平化管理', '创业型企业', '岗位晋升'] 唯医网 150-500人 ['东大桥', 'CBD', '朝外']
3 使用Pandas库链接Mysql
Pandas库连接Mysql数据库的核心方法：
pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)
常用参数：
- sql: string SQL query or SQLAlchemy Selectable (select or text object) to be executed, or database table name.
- con: SQLAlchemy connectable(engine/connection) or database string URI or DBAPI2 connection (fallback mode) Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.
3.1 旧版本的pandas库中con参数使用pymysql库创建的connect对象
导入需要的库：
import pymysql import pandas as pd
建立数据库连接：
conn = pymysql.connect( host='localhost', user='root', password='XXXXXX', database='data', port=3306, charset='utf8' )
创建SQL查询语句：
sql = 'SELECT * FROM company'
使用pandas读取数据库：
df = pd.read_sql(sql, conn)
df
companyId companyFullName companyLabelList companyShortName companySize businessZones 0 43 北京欧应科技有限公司 ['五险一金', '扁平化管理', '创业型企业', '岗位晋升'] 唯医网 150-500人 ['东大桥', 'CBD', '朝外'] 1 53 北京创锐文化传媒有限公司 ['技能培训', '节日礼物', '季度奖金', '岗位晋升'] 聚美优品 2000人以上 ['东直门', '海运仓', '东四'] 2 62 北京字节跳动科技有限公司 ['扁平管理', '弹性工作', '大厨定制三餐', '就近租房补贴'] 今日头条 2000人以上 ['双榆树', '大钟寺', '中关村']
3.2 新版本的pandas库中con参数使用sqlalchemy库创建的create_engine对象
导入需要的库：
import pymysql import pandas as pd from sqlalchemy import create_engine
创建create_engine对象(格式类似于URL地址)：
engine = create_engine('mysql+pymysql://root:XXXXXXXd@localhost:3306/data?charset=utf8')
创建SQL查询语句：
sql = 'SELECT * FROM company'
使用pandas读取数据库：
df = pd.read_sql(sql, engine)
df
companyId companyFullName companyLabelList companyShortName companySize businessZones 0 43 北京欧应科技有限公司 ['五险一金', '扁平化管理', '创业型企业', '岗位晋升'] 唯医网 150-500人 ['东大桥', 'CBD', '朝外'] 1 53 北京创锐文化传媒有限公司 ['技能培训', '节日礼物', '季度奖金', '岗位晋升'] 聚美优品 2000人以上 ['东直门', '海运仓', '东四'] 2 62 北京字节跳动科技有限公司 ['扁平管理', '弹性工作', '大厨定制三餐', '就近租房补贴'] 今日头条 2000人以上 ['双榆树', '大钟寺', '中关村']
4 使用Pandas库读写Mysql数据库
用Pandas从Mysql的data数据库中加载company表和dataanalysis表，然后将两张表以companyId列进行合并，然后分组统计各个城市各个公司的个数，最后将结果写入data数据库的newtable表(若不存在，新建）中。
导入库：
import pymysql import pandas as pd from sqlalchemy import create_engine
因为涉及到多次读写，所以将读写操作都封装为函数。
封装读取函数：
def read_from_mysql(sql, db_name='data'): engine = create_engine('mysql+pymysql://root:XXXXXXXX@localhost:3306/{db_name}?charset=utf8'.format(db_name=db_name)) df = pd.read_sql(sql, engine) return df
参数说明：
- sql: 一个用于在数据库上执行的SQL语句。
- db_name: 将要在哪个数据库上进行操作，默认为data数据库。
加载company表：
sql_company = 'SELECT * FROM company'
df_company = read_from_mysql(sql_company, 'data')
df_company.head(2)
companyId companyFullName companyLabelList companyShortName companySize businessZones 0 43 北京欧应科技有限公司 ['五险一金', '扁平化管理', '创业型企业', '岗位晋升'] 唯医网 150-500人 ['东大桥', 'CBD', '朝外'] 1 53 北京创锐文化传媒有限公司 ['技能培训', '节日礼物', '季度奖金', '岗位晋升'] 聚美优品 2000人以上 ['东直门', '海运仓', '东四']
加载dataanalysis表:
sql_dataanalyst = 'SELECT * FROM dataanalyst' df_dataanalyst = read_from_mysql(sql_dataanalyst) df_dataanalyst.head(2)
positionId city companyId firstType secondType education industryField positionAdvantage positionName positionLables salary workYear 0 80307 深圳 6718 职能 高端职能职位 本科 社交网络,生活服务 业务分析，自由度高，项目有发展前景。 数据分析师 ['分析师', '数据分析', '数据'] 8k-15k 1-3年 1 100561 北京 62 技术 DBA 本科 移动互联网,数据服务 过亿用户+优厚薪资期权+三餐+住房补 数据抓取和处理（高级）工程师 ['数据'] 20k-40k 不限
将company表和dataanalysis表以companyId为键进行合并：
result = df_dataanalyst.merge(df_company, on='companyId')
result.head(2)
positionId city companyId firstType secondType education industryField positionAdvantage positionName positionLables salary workYear companyFullName companyLabelList companyShortName companySize businessZones 0 80307 深圳 6718 职能 高端职能职位 本科 社交网络,生活服务 业务分析，自由度高，项目有发展前景。 数据分析师 ['分析师', '数据分析', '数据'] 8k-15k 1-3年 深圳市珍爱网信息技术有限公司 ['发展上升型', '朝阳行业', '创业氛围浓厚', '年终分红'] 珍爱网 2000人以上 ['南头', '科技园', '桂庙路口'] 1 899950 深圳 6718 设计 用户研究 本科 社交网络,生活服务 大公司 福利好 团队棒 数据分析师（资源策略） ['分析师', '数据分析', '策略', '数据'] 8k-15k 1-3年 深圳市珍爱网信息技术有限公司 ['发展上升型', '朝阳行业', '创业氛围浓厚', '年终分红'] 珍爱网 2000人以上 ['南头', '科技园', '桂庙路口']
统计合并表中各个城市各个公司的个数：
result = result.groupby(['city', 'companyFullName'])['positionId'].count().reset_index()
result.head(2)
city companyFullName positionId
0 上海 CMC Wiseme HK Limited 2
1 上海 Striking.ly, Inc. 1
封装写入函数：
def write_to_sql(df, tb_name, db_name='data'): engine = create_engine('mysql+pymysql://root:175458778sd@localhost:3306/{db_name}?charset=utf8'.format(db_name=db_name))
#if_exists来控制写入的方式，index控制是否写入索引 df.to_sql(tb_name, con=engine, if_exists='append', index=False)
参数说明：
- df: 将要写入到数据库中的数据，Series或者DataFrame对象。
- tb_name: 要写入到哪张表中。
- db_name: 要写入到哪个数据库中，默认为data数据库。
将result写入到数据库：
write_to_sql(result, 'newtable')
查看数据库：
read_from_mysql('SHOW TABLES')
Tables_in_data 0 company 1 dataanalyst 2 newtable 3 order 4 user
结果显示，数据已经写入到newtable表中:
read_from_mysql('SELECT * FROM newtable LIMIT 3')
city companyFullName positionId
0 上海 CMC Wiseme HK Limited 2
1 上海 Striking.ly, Inc. 1
2 上海 VIKI PRIVATE LIMITED 1