1. 导包
# 导包import numpy as npimport pandas as pd
2. CSV数据
data = np.random.randint(0,50,size=(10,5))df = pd.DataFrame(data=data,columns=["Python","C++","Java","NumPy","Pandas"])df
2.1 df.to_csv：保存到csv
# sep：分隔符，默认是逗号# header：是否保存列索引# index：是否保存行索引df.to_csv("08_Pandas数据加载.csv",sep=",",header=True,index=True)
2.2 df.read_csv：加载csv数据
pd.read_csv("08_Pandas数据加载.csv",sep=",",header=[0],index_col=0)# 不获取列：header=None，第一行的数据会作为内容显示pd.read_csv("08_Pandas数据加载.csv",sep=",",header=None,index_col=0)
2.3 pd.read_table
# read_table：默认分隔符sep='\t'pd.read_table("08_Pandas数据加载.csv",sep=",",index_col=0)3. excel数据
data = np.random.randint(0,50,size=(10,5))df = pd.DataFrame(data=data,columns=["Python","C++","Java","NumPy","Pandas"])df
3.1 df.to_excel()：保存到excel文件
# sheet_name：工作表名称# header：是否保存列索引# index：是否保存行索引df.to_excel("08_Pandas数据加载.xlsx",sheet_name="课程成绩",header=True,index=True)
3.2 pd.read_excel：读取excel
pd.read_excel("08_Pandas数据加载.xlsx",sheet_name="课程成绩",header=[0],index_col=0)pd.read_excel("08_Pandas数据加载.xlsx",sheet_name="课程成绩",header=[0,1],index_col=0)# sheet_name=0：读取第1个工作表pd.read_excel("08_Pandas数据加载.xlsx",sheet_name=0,header=0,index_col=0)# names：设置列名/替代原有的列名pd.read_excel("08_Pandas数据加载.xlsx",header=0,index_col=0,names=list("ABCDE"))
4. MySQL数据
4.1 需要安装pymysql
pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple
Defaulting to user installation because normal site-packages is not writeableLooking in indexes: https://pypi.tuna.tsinghua.edu.cn/simpleCollecting pymysql Downloading https://pypi.tuna.tsinghua.edu.cn/packages/e5/30/20467e39523d0cfc2b6227902d3687a16364307260c75e6a1cb4422b0c62/PyMySQL-1.1.0-py3-none-any.whl (44 kB) ---------------------------------------- 0.0/44.8 kB ? eta -:--:-- --------- ------------------------------ 10.2/44.8 kB ? eta -:--:-- --------- ------------------------------ 10.2/44.8 kB ? eta -:--:-- --------- ------------------------------ 10.2/44.8 kB ? eta -:--:-- -------------------------------------- 44.8/44.8 kB 200.7 kB/s eta 0:00:00Installing collected packages: pymysqlSuccessfully installed pymysql-1.1.0Note: you may need to restart the kernel to use updated packages.
4.2 需要安装sqlalchemy
https://pypi.tuna.tsinghua.edu.cn/simple
pip install sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple
Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simpleRequirement already satisfied: sqlalchemy in d:\anaconda3\lib\site-packages (1.4.39)Requirement already satisfied: greenlet!=0.4.17 in d:\anaconda3\lib\site-packages (from sqlalchemy) (2.0.1)Note: you may need to restart the kernel to use updated packages.
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple --target=d:\anaconda3\lib\site-packages sqlalchemy
Note: you may need to restart the kernel to use updated packages.Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simpleCollecting sqlalchemy Using cached https://pypi.tuna.tsinghua.edu.cn/packages/22/80/43ddb1ddeafdcbc3073c0e7a7d45b17678eeac4830c7e91bd6556527f311/SQLAlchemy-2.0.25-cp311-cp311-win_amd64.whl (2.1 MB)Collecting typing-extensions>=4.6.0 (from sqlalchemy) Using cached https://pypi.tuna.tsinghua.edu.cn/packages/b7/f4/6a90020cd2d93349b442bfcb657d0dc91eee65491600b2cb1d388bc98e6b/typing_extensions-4.9.0-py3-none-any.whl (32 kB)Collecting greenlet!=0.4.17 (from sqlalchemy) Using cached https://pypi.tuna.tsinghua.edu.cn/packages/47/79/26d54d7d700ef65b689fc2665a40846d13e834da0486674a8d4f0f371a47/greenlet-3.0.3-cp311-cp311-win_amd64.whl (292 kB)Installing collected packages: typing-extensions, greenlet, sqlalchemySuccessfully installed greenlet-3.0.3 sqlalchemy-2.0.25 typing-extensions-4.9.0WARNING: Target directory d:\anaconda3\lib\site-packages\greenlet already exists. Specify --upgrade to force replacement.WARNING: Target directory d:\anaconda3\lib\site-packages\sqlalchemy already exists. Specify --upgrade to force replacement.WARNING: Target directory d:\anaconda3\lib\site-packages\typing_extensions.py already exists. Specify --upgrade to force replacement.WARNING: Target directory d:\anaconda3\lib\site-packages\__pycache__ already exists. Specify --upgrade to force replacement.
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple --target=d:\anaconda3\lib\site-packages sqlalchemy --upgrade
Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simpleCollecting sqlalchemy Using cached https://pypi.tuna.tsinghua.edu.cn/packages/22/80/43ddb1ddeafdcbc3073c0e7a7d45b17678eeac4830c7e91bd6556527f311/SQLAlchemy-2.0.25-cp311-cp311-win_amd64.whl (2.1 MB)Collecting typing-extensions>=4.6.0 (from sqlalchemy) Using cached https://pypi.tuna.tsinghua.edu.cn/packages/b7/f4/6a90020cd2d93349b442bfcb657d0dc91eee65491600b2cb1d388bc98e6b/typing_extensions-4.9.0-py3-none-any.whl (32 kB)Collecting greenlet!=0.4.17 (from sqlalchemy) Using cached https://pypi.tuna.tsinghua.edu.cn/packages/47/79/26d54d7d700ef65b689fc2665a40846d13e834da0486674a8d4f0f371a47/greenlet-3.0.3-cp311-cp311-win_amd64.whl (292 kB)Installing collected packages: typing-extensions, greenlet, sqlalchemySuccessfully installed greenlet-3.0.3 sqlalchemy-2.0.25 typing-extensions-4.9.0Note: you may need to restart the kernel to use updated packages.
from sqlalchemy import create_engine
# 创建数据data = np.random.randint(0,150,size=(150,3))df = pd.DataFrame(data=data,columns=["Python","Pandas","PyTorch"])# 查看前5条数据df.head()
4.3 先连接MySQL
# mysql+pymysql：数据库类型+驱动# root:123456：数据库用户名和密码# localhost:3306：数据库地址和端口# pandas：数据库名conn = create_engine("mysql+pymysql://root:123456@localhost:3306/pandas")
4.4 df.to_sql：保存到MySQL
df.to_sql( name="score", # 数据库中的名字 con = conn, # 数据库连接对象 index=False, # 是否保存行索引 if_exists="append" # 如果表存在，则追加数据)
150
4.5pd.read_sql：从MySQL中加载数据
pd.read_sql( sql = "select * from score", # sql语句 con = conn # 数据库连接对象)
150 rows × 3 columns
pd.read_sql( sql = "select * from score", # sql语句 con = conn, # 数据库连接对象 index_col = "Python" # 指定行索引的列名)
150 rows × 2 columns