- 读写txt,csv
- 读写excel
- 读写sqlite
- 读写mysql
导入模块
#导入模块
import pandas as pd
from sqlalchemy import create_engine #创建数据库引擎
读写txt,csv
读取csv,txt
# 读取csv
file_path =r''
pd.read_csv(file_path)
输出csv
[[输出结果到csv]]
df.to_csv('F:/xx/Inbox/xls2.csv',sep=',')
[[输出结果到csv,2]]
In [5]: df.to_csv("e:\student01.csv",sep=",",columns=['姓名','班级'],index=True)
In [6]: df = pd.read_csv('e:\student01.csv')
’‘’
sep为分隔符
‘’‘
输出到txt
[[输出结果到txt]]
df.to_csv('F:/xx/Inbox/xls2.txt',sep=',')
读写excel
读取excel
# 读取excel
file_path =r'F:\XX\xx.xlsx'
[[读取excel]]
coln = ['货号','有效数量','有效金额']
xls1 = pd.read_excel(file_path,sheet_name='整理明细表',usecols =coln,header=2)
"""
read_excel常用参数:
sheet_name:表的名称
header:从第几行开始,0开始
usecols:读取哪些列 eg. usecols =["序号","货号","品名规格"]
"""
输出excel
[[输出为excel]]
df.to_excel(filepath,'sheetname')
输出到多个使用[[pd.Excelwriter]]
file_path=r'F:\INDEX\x1.xlsx' # excel文件地址
with pd.ExcelWriter(file_path,mode='a',engine=openpyxl) as writer: # 在已存在的表后添加sheet
df1.to_excel(writer,sheet_name='df1')
df2.to_excel(writer,sheet_name='df2')
# mode a :追加方式,在原文件后面添加表
# mode w :写方式,会覆盖原文件内容
读写sqlite
读取sqlite
import pandas as pd
from sqlalchemy import create_engine
eg = create_engine('sqlite:///F:/XX/xx2022.db')
sql ="""
select * from table_name
"""
df = pd.read_sql(sql,eg)
输出到sqlite
[[输出结果到sqlite]]
df.to_sql('res',eg,if_exists='replace')
# 'replace' 可替换
读写mysql
# 导入模块
import pandas as pd
from sqlalchemy import create_engine
import pymysql
#数据库信息
host = 'url'
port = '3306'
db = 'quant2022'
user = 'root'
password = 'password'
db_url = f'mysql+pymysql://{user}:{password}@{host}:{port}/{db}?charset=utf8'
engine = create_engine(db_url) #数据库引擎
#读取mysql
sql1 ="""
select * from pzh
"""
df1= pd.read_sql_query(sql1,engine)
# 写入mysql
pzh.to_sql(name='pzh',con=engine,if_exists='replace')