This is the 19th day of my participation in the Gwen Challenge in November. Check out the details: The last Gwen Challenge in 2021

TXT, Csv, Excel, JSON, SQL file reading (Python)

TXT file read and write

Create a TXT file

f=open(r'text.txt'.'r',encoding='utf-8')
s=f.read()
f.close()
print(s)
Copy the code

Open () is the method to open a file

The ‘text.txt’ file names are in the same folder so you can omit the path

If XXX/XXX /text. TXT is not in the same folder, add the path before the file name

Encoding: Sets the character encoding

Read () reads the contents of a file

Close () closes the file

with

The open() function opens a file. If the file is not closed, the file cannot be modified. If a file is opened and its contents are written, the file cannot be saved if the file is not closed.

In Python, with is provided in conjunction with the open() function method

Use with with with the open() method without having to write close()

with open(r'text.txt'.'r',encoding='utf-8') as f:
    s=f.read()
print(s)
Copy the code

write

with open(r'text.txt'.'w') as f:
    f.write('qwertyuiop')
Copy the code

Write more lines

with open(r'text.txt'.'w') as f:
    text=['asdfghjk\n'.'xcvbnmrtyui\n'.'123456789\n']
    f.writelines(text)
Copy the code

In the open(r’text.txt’,’w’) function, the ‘w’ parameter means write, which overwrites the original contents of the file

File open mode

  • R read-only Default read-only mode
  • W only writes in the original file write, overwrites the original file
  • A only write does not overwrite the original file, the end of the append
  • Wb write Write in binary format, used when saving pictures
  • R + read/write does not overwrite the original file and appends at the end
  • W + read/write write in the original file, overwrite the original file
  • A + read/write does not overwrite the original file and appends to the end

CSV file reading and writing

read_csv( )

Read text.csv from the current directory

import pandas as pd
a=pd.read_csv(r'text.csv')
print(a)
Copy the code

Set the field

import pandas as pd
a=pd.read_csv(r'text.csv',names=['id'.'name'])
print(a)
Copy the code

Specify the corresponding index column

import pandas as pd
a=pd.read_csv(r'text.csv',names=['id'.'name'],index_col='id')
print(a)
Copy the code

import pandas as pd
a=pd.read_csv(r'text.csv',names=['id'.'name'],index_col=0)
print(a)
Copy the code

Get the specified column

import pandas as pd
a=pd.read_csv(r'text.csv',names=['id'.'name'],usecols=[0])
print(a)
b=pd.read_csv(r'text.csv',names=['id'.'name'],usecols=['id'])
print(b)
Copy the code

write

to_csv( )

import pandas as pd
data={'id': ['1'.'2'.'3'].'name': ['gh'.'jk'.'ty']}
a=pd.DataFrame(data)
a.to_csv(r'text.csv')
Copy the code

Set write column

import pandas as pd
data={'id': ['1'.'2'.'3'].'name': ['gh'.'jk'.'ty']}
a=pd.DataFrame(data)
a.to_csv(r'text.csv',columns=['id'])
Copy the code

Set write mode

Mode w for write (overwrite) a for append

import pandas as pd
data={'id':['1','2','3'],'name':['gh','jk','ty']}
a=pd.DataFrame(data)
a.to_csv(r'text.csv')
a.to_csv(r'text.csv',mode='a')
Copy the code

Whether to write the column name field

header

import pandas as pd
data={'id': ['1'.'2'.'3'].'name': ['gh'.'jk'.'ty']}
a=pd.DataFrame(data)
a.to_csv(r'text.csv')
a.to_csv(r'text.csv',mode='a',header=False)
Copy the code

The second write does not write the column name

Remove the index

index=None

import pandas as pd
data={'id': ['1'.'2'.'3'].'name': ['gh'.'jk'.'ty']}
a=pd.DataFrame(data)
a.to_csv(r'text.csv',index=None)
a.to_csv(r'text.csv',mode='a',header=False,index=None)
Copy the code

Excel file reading and writing

read_excel( )

Parameters:

Sheet_name =’name’ specifies the name of the table to be read.

Index_col specifies the corresponding index column and subscript the field name or field list.

Usecols is used to retrieve the specified column

Names is the Settings column field

Header specifies the row to use for the field name

Nrows specifies the number of rows to fetch

Skiprows skips specific lines, and skipfooter skips the last n lines

import pandas as pd
a=pd.read_excel(r'text.xlsx')
print(a)
Copy the code

Select the table

sheet_name

Create a new table

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=1)
print(a)
Copy the code

Set index column

index_col

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=0,index_col=[0])
print(a)
Copy the code

Get the specified column

usecols

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=0,usecols=[0])
print(a)
Copy the code

Set column fields

names

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=0,names=['ID'.'NAME'.'CLASS'])
print(a)
Copy the code

Specify the name of a behavior field

header

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=0,header=1)
print(a)
Copy the code

Sets the number of rows to get

nrows

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=0,nrows=2)
print(a)
Copy the code

Skip n lines

Skiprows skips the first n lines

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=0,skiprows=1)
print(a)
Copy the code

Skipfooter skips the last n lines

import pandas as pd
a=pd.read_excel(r'text.xlsx',sheet_name=0,skipfooter=3)
print(a)
Copy the code

write

import pandas as pd
data={'id': [1.2.3.4].'name': ['A'.'B'.'C'.'D']}
a=pd.DataFrame(data)
a.to_excel(r'text.xlsx')
Copy the code

Write more table

import pandas as pd
data={'id': [1.2.3.4].'name': ['A'.'B'.'C'.'D']}
a=pd.DataFrame(data)
writer = pd.ExcelWriter(r'text.xlsx')
a.to_excel(writer,sheet_name='1')
a.to_excel(writer,sheet_name='2')
writer.save()
writer.close()
Copy the code

Write new table

import pandas as pd
import openpyxl
book = openpyxl.load_workbook(r'text.xlsx')
writer=pd.ExcelWriter(r'text.xlsx')
writer.book=book
writer.sheets=dict((ws.title,ws) for ws in book.worksheets)
data={'id': [5.2.8.4].'name': ['H'.'B'.'C'.'D']}
a=pd.DataFrame(data)
a.to_excel(writer,sheet_name="3")
writer.save()
writer.close()
Copy the code

JSON file reading and writing

Read_json ()

import pandas as pd
a=pd.read_json(r'text.json',encoding='utf8')
print(a)
Copy the code

serialization

import pandas as pd
a=pd.read_json(r'text.json',encoding='utf8')
b=pd.json_normalize(a.data)
print(a)
print(b)
Copy the code

write

to_json( )

Force_ascii is the data encoding format. The default value is True, and Chinese characters are written in Unicode format. If it is False, Chinese characters are written in ANSI format.

import pandas as pd
data={'id': [1.2.3].'name': ['a'.'b'.'c']}
a=pd.DataFrame(data)
a.to_json('text.json',force_ascii=False)
Copy the code

SQL file reading

import pymysql
con = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user='root',
    password='123456',
    db='test03',
    charset='utf8'
)
Create a cursor
cursor=con.cursor()
Execute SQL statement
cursor.execute("select * from test")
# explain all returned results
res=cursor.fetchall()
print(res)
con.close()
Copy the code

Pandas Reads the contents of the MySQL database

import pymysql
import pandas as pd
con = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user='root',
    password='123456',
    db='test03',
    charset='utf8'
)
sql="select * from test"
pd=pd.read_sql_query(sql,con)
print(pd)
Copy the code