ShinMoon

Py1数据读写

3 min read

目录

  1. txt,
  2. csv
  3. excel
    1. xlwings
    2. pandas
    3. openpyxl,xlwt
  4. database
    1. sqlite
    2. mysql
    3. postgresql
  5. 批量读写
  6. 读取文件夹内的内容
  7. 读取最后一行内容

1.txt

with open

数据导入导出

  • 导入模块

    #导入模块
import pandas as pd 
from sqlalchemy import create_engine  #创建数据库引擎

  • 1. 读写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=',')

  • 2.读写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 :写方式，会覆盖原文件内容

  • 3.读写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'  可替换

  • 4.读写mysql

    # 导入模块
import pandas as pd
from sqlalchemy import create_engine
import pymysql
#数据库信息
host = 'rm-bp1eo2i7tji1x24z61o.mysql.rds.aliyuncs.com'
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')

  • 5. 读写postgresql

  • 5. 使用sqlite3,MySQLdb读写数据库sqlite,mysql,

    1. Python操作MySQL数据库的三种方法 1
    2. python操作sqlite

  • 6. 使用with open语句

    c='hello world!'
txt_path=r'F:\inbox\t1.txt'
with open(txt_path,'w',encoding='utf-8') as f:
f.write(c)
f.close

    open函数后面参数’a’,‘w’,‘r’的区别

  • 7. 处理模块

    import pandas as pd
from sqlalchemy import create_engine
import pymysql
 
 
def con_ali():
  host = 'rm-bp1eo2i7tji1x24z61o.mysql.rds.aliyuncs.com'
  port = '3306'
  db = 'xx2022'
  user = 'root'
  password = 'Jing-1332'
  db_url=f'mysql+pymysql://{user}:{password}@{host}:{port}/{db}?charset=utf8'
  return create_engine(db_url)
 
def con_sqlite():
   return create_engine('sqlite:///F:/XX/Database/xx2022.db')
 
 
def load_excel(file):
  df=pd.read_excel(file)
  return df
 
df1 = pd.read_excel(f1,sheet_name='Sheet1')
 
# read sqlite
sql ="""
select * from pzh
"""
 
# read mysql
sql ="""
select * from pzh
"""
df= pd.read_sql_query(sql,con_ali())
 
#%% write to alimysql
df.to_sql('tablename',con,if_exists='replace')

Graph View