Mysql练习题 - Python连接MySql数据库：PyMysql库和Pandas库 - 《MySQL 知识整理》

1 系统环境
2 使用PyMysql库链接Mysql
3 使用Pandas库链接Mysql
- 3.1 旧版本的pandas库中con参数使用pymysql库创建的connect对象
- 3.2 新版本的pandas库中con参数使用sqlalchemy库创建的create_engine对象

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
 43    北京欧应科技有限公司    ['五险一金', '扁平化管理', '创业型企业', '岗位晋升']    唯医网    150-500人    ['东大桥', 'CBD', '朝外']
 53    北京创锐文化传媒有限公司    ['技能培训', '节日礼物', '季度奖金', '岗位晋升']    聚美优品    2000人以上    ['东直门', '海运仓', '东四']
 62    北京字节跳动科技有限公司    ['扁平管理', '弹性工作', '大厨定制三餐', '就近租房补贴']    今日头条    2000人以上    ['双榆树', '大钟寺', '中关村']

3.2 新版本的pandas库中con参数使用sqlalchemy库创建的create_engine对象

导入需要的库：

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
   80307    深圳    6718    职能    高端职能职位    本科    社交网络,生活服务    业务分析，自由度高，项目有发展前景。    数据分析师    ['分析师', '数据分析', '数据']    8k-15k    1-3年    深圳市珍爱网信息技术有限公司    ['发展上升型', '朝阳行业', '创业氛围浓厚', '年终分红']    珍爱网    2000人以上    ['南头', '科技园', '桂庙路口']
   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

Python连接MySql数据库：PyMysql库和Pandas库

1 系统环境

2 使用PyMysql库链接Mysql

3 使用Pandas库链接Mysql

3.1 旧版本的pandas库中con参数使用pymysql库创建的connect对象

3.2 新版本的pandas库中con参数使用sqlalchemy库创建的create_engine对象