pandas读写mysql

Python读取MySQL数据库基本操作

[](//www.zhihu.com/people/ou-chen-

目录

收起

1.安装并加载相关库

2.初始化数据库连接

3.查询表名为“期权的基本信息”表数据与“期权的风险指标”表数据。

4.筛选数据。

5.排序

6.内连接与外连接

7.增加、删除、修改等基本操作。

8.将数据写入MySQL数据库。

**【实例讲解】**当前MySQL数据库有以下两组数据表，对其进行读取并做基本操作。

表-期权的基本信息

表-期权的风险指标

1.安装并加载相关库

#pip install pymysql  #安装读取数据库的库
import pandas as pd
import numpy as np
import pymysql
from sqlalchemy import create_engine
import time
from datetime import date

2.初始化数据库连接

按实际情况依次填写MySQL的用户名、密码、IP地址、端口、数据库名。

语法为create_engine(‘mysql+pymysql://用户名:密码@服务器地址:3306/数据库名’)

engine = create_engine('mysql+pymysql://root:123456@10.22.82.192:3306/test?charset=utf8')

具体连接对应以下信息：

3.查询表名为“期权的基本信息”表数据与“期权的风险指标”表数据。

# MySQL导入DataFrame
# 填写自己所需的SQL语句，可以是复杂的查询语句
sql_query = 'select * from 期权的基本信息;'
# 使用pandas的read_sql_query函数执行SQL语句，并存入DataFrame
option_inf = pd.read_sql_query(sql_query, engine)

#调整格式，对齐数据，显示完整数据
pd.set_option('display.unicode.ambiguous_as_wide', True)
pd.set_option('display.unicode.east_asian_width', True)
pd.set_option('display.max_columns', 1000)
pd.set_option('display.width', 1000)
pd.set_option('display.max_colwidth', 1000)

print(option_inf)

查询并输出表<期权的风险指标>.

sql_query2 = 'select * from 期权的风险指标;'
option_risk = pd.read_sql_query(sql_query2, engine)
print(option_risk)

4.筛选数据。

（1）筛选option_inf中“证券名称”的列。

#筛选option_inf中“证券名称”列。
option_inf['证券名称']

（2）筛选option_risk中Gamma为0的行。

#筛选option_risk中Gamma为0的行。
option_risk[option_risk['Gamma']==0]

（3）筛选option_risk中Delta>0.5且Gamma不等于0的数据。

#筛选option_risk中Delta>0.5且Gamma不等于0的数据
option_risk[(option_risk['Delta']>0.5)&(option_risk['Gamma']!=0)]

5.排序

（1）option_risk按照“收盘价”升序排序。

#option_risk按照“收盘价”升序排序。
option_risk.sort_values(by='收盘价',ascending=True)

（2）option_risk按照“Delta”、“证券代码”降序排序。

#option_risk按照"Delta","证券代码"降序排序。
option_risk.sort_values(['Delta','证券代码'],ascending=False)

【注】先按“Delta”降序排序，如果Delta值相等，再按“证券代码”排序。

6.内连接与外连接

（1）option_inf与option_risk求取交集

#option_inf与option_risk求取交集
print(pd.merge(option_inf,option_risk))

【注】哪个表在前面，就排在前面。

（2）option_inf与option_risk求取并集

#option_inf与option_risk求取并集
print(pd.merge(option_inf,option_risk,how='outer'))

（3）左连接

#左连接
print(pd.merge(option_inf,option_risk,on='证券代码',how='left'))

【注】以option_inf的“证券代码”为基准（4行），从左往右拼接。

（4）右连接

#右连接
print(pd.merge(option_inf,option_risk,on='证券代码',how='right'))

【注】以option_risk的“证券代码”为基准（5行），从左（option_inf）往右拼接。

7.增加、删除、修改等基本操作。

定义option_merge数据框。

option_merge=pd.merge(option_inf,option_risk,how='outer')
print(option_merge)

（1）将4，5索引行的“行权方式”改为欧式，“交割方式”改为实物交割。

option_merge["行权方式"].replace({np.nan:"欧式"},inplace=True)
print(option_merge)

option_merge.loc[[4,5],["交割方式"]]="实物交割"
print(option_merge)

（2）增加一行。

df=pd.DataFrame({"证券代码":["100102"],"证券名称":["50ETF"],"行权方式":["美式"]})
option_merge=option_merge.append(df,ignore_index=True)
print(option_merge)

（3）删除某行。

option_merge=option_merge.drop(6,axis=0)
print(option_merge)

（4）增加一列。

option_merge['新列名'] = [1,2,3,4,5,6]

（5）删除一列。

option_merge=option_merge.drop('新列名',axis=1)

8.将数据写入MySQL数据库。

将新的数据框option_merge命名为“期权的所有信息”写入MySQL数据库。

#写入MySQL数据库
db = pymysql.connect(
    host="10.22.82.192",
    user="root",password="123456",
    database="test",
    charset="utf8")

cursor=db.cursor() #获取游标
connect=create_engine('mysql+pymysql://root:123456@10.22.82.192:3306/test?charset=utf8')
pd.io.sql.to_sql(option_merge,"期权的所有信息",connect,schema="test",index=False,if_exists="append")

【注】创建的表-期权的所有信息，并不是utf8的格式，需要对数据库的格式进行修改。

（1）查看数据库软件DBeaver或Navicat，发现只建立了表<期权的所有信息>，表中并未导入数据。

（2）进入数据库软件DBeaver或Navicat中进行调整，格式调整为utf8的格式，从而支持中文。

（3）相关中文的字列也改为utf8的格式。

【注】同理，证券名称、行权方式、交割方式均改为utf8的格式。

（4）最后重新运行写入MySQL数据库的代码。

#写入MySQL数据库
db = pymysql.connect(
    host="10.22.82.192",
    user="root",password="123456",
    database="test",
    charset="utf8")

cursor=db.cursor() #获取游标
connect=create_engine('mysql+pymysql://root:123456@10.22.82.192:3306/test?charset=utf8')
pd.io.sql.to_sql(option_merge,"期权的所有信息",connect,schema="test",index=False,if_exists="append")

导入成功！

**【友情提示】**对于MySQL数据库中的相关命名规范等建议均使用英文，否则经常容易出错且不易发现问题。