读写txt,csv,excel,sqlite,mysql文件，常用的全在这了

1. 读写txt,csv
2. 读写excel
3. 读写sqlite
4. 读写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')