Python的csv

Python的CSV模块是一个用于读写csv文件的库，它提供了将数据写入CSV（逗号分隔值）文件和从CSV文件中读取数据的功能。

CSV库使用示例

1、读取CSV文件：

import csv

with open('file.csv', 'r') as file:
    reader = csv.reader(file)
    for row in reader:
        print(row)

2、写入CSV文件：

import csv

with open('file.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    writer.writerow(['Name', 'Age', 'City'])
    writer.writerow(['John', '25', 'New York'])
    writer.writerow(['Peter', '31', 'London'])

3、指定分隔符：

import csv

with open('file.csv', 'r') as file:
    reader = csv.reader(file, delimiter='|')
    for row in reader:
        print(row)

4、指定引用符：

import csv

with open('file.csv', 'r') as file:
    reader = csv.reader(file, quotechar="'")
    for row in reader:
        print(row)

5、读取字典形式的CSV数据：

import csv

with open('file.csv', 'r') as file:
    reader = csv.DictReader(file)
    for row in reader:
        print(row['Name'], row['Age'], row['City'])

6、写入字典形式的CSV数据：

import csv

with open('file.csv', 'w', newline='') as file:
    fieldnames = ['Name', 'Age', 'City']
    writer = csv.DictWriter(file, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerow({'Name': 'John', 'Age': '25', 'City': 'New York'})
    writer.writerow({'Name': 'Peter', 'Age': '31', 'City': 'London'})

7、使用pandas读取CSV文件：

import pandas as pd

data = pd.read_csv('file.csv')
print(data.head())

8、将pandas数据写入CSV文件：

import pandas as pd

data = pd.read_csv('file.csv')
data.to_csv('new_file.csv', index=False)

9、从List中读取CSV数据：

import csv

data = [['Name', 'Age', 'City'],
        ['John', 25, 'New York'],
        ['Peter', 31, 'London']]

with open('file.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    writer.writerows(data)

10、从CSV文件中读取特定列的数据：

import csv

with open('file.csv', 'r') as file:
    reader = csv.reader(file)
    for row in reader:
        print(row[0], row[2])

11、写入特定列的数据：

import csv

with open('file.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    writer.writerow(['Name', 'City'])
    writer.writerow(['John', 'New York'])
    writer.writerow(['Peter', 'London'])

12、读取CSV文件中的特定行：

import csv

with open('file.csv', 'r') as file:
    reader = csv.reader(file)
    for i, row in enumerate(reader):
        if i == 1 or i == 3:
            print(row)

13、跳过特定行：

import csv

with open('file.csv', 'r') as file:
    reader = csv.reader(file)
    next(reader) # skip header row
    for row in reader:
        print(row)

14、将CSV文件转换为JSON格式：

import csv
import json

csv_file = open('file.csv', 'r')
json_file = open('new_file.json', 'w')

fieldnames = ('Name', 'Age', 'City')
reader = csv.DictReader(csv_file, fieldnames)
for row in reader:
    json.dump(row, json_file)
    json_file.write('\n')

15、从JSON文件中读取CSV数据：

import csv
import json

json_file = open('file.json', 'r')
csv_file = open('new_file.csv', 'w', newline='')

fieldnames = ('Name', 'Age', 'City')
writer = csv.DictWriter(csv_file, fieldnames=fieldnames)
writer.writeheader()

for line in json_file:
    row = json.loads(line)
    writer.writerow(row)

16、使用pandas将CSV数据转换为HTML格式：

import pandas as pd

data = pd.read_csv('file.csv')
html = data.to_html()
print(html)

17、将CSV数据导入MySQL数据库：

import csv
import MySQLdb

db = MySQLdb.connect(host='localhost', user='root', passwd='password', db='my_database')
cursor = db.cursor()

with open('file.csv', 'r') as file:
    reader = csv.reader(file)
    next(reader) # skip header row
    for row in reader:
        cursor.execute('INSERT INTO my_table(Name, Age, City) VALUES(%s, %s, %s)', row)

db.commit()
db.close()

18、从MySQL数据库中导出CSV数据：

import csv
import MySQLdb

db = MySQLdb.connect(host='localhost', user='root', passwd='password', db='my_database')
cursor = db.cursor()

query = "SELECT * FROM my_table"
cursor.execute(query)
rows = cursor.fetchall()

with open('file.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    writer.writerow(['Name', 'Age', 'City'])
    for row in rows:
        writer.writerow(row)

db.close()

19、打开CSV文件时自动识别编码：

import csv
import chardet

with open('file.csv', 'rb') as file:
    result = chardet.detect(file.read())
    encoding = result['encoding']
    file.seek(0)
    reader = csv.reader(file, delimiter=',', quotechar='"', encoding=encoding)
    for row in reader:
        print(row)

20、使用csv.DictWriter写入复杂结构的CSV数据：

import csv

data = [
    {
        'name': 'John',
        'age': 25,
        'address': {
            'city': 'New York',
            'state': 'NY'
        }
    },
    {
        'name': 'Peter',
        'age': 31,
        'address': {
            'city': 'London',
            'state': 'UK'
        }
    }
]

with open('file.csv', 'w', newline='') as file:
    fieldnames = ['name', 'age', 'city', 'state']
    writer = csv.DictWriter(file, fieldnames=fieldnames)
    writer.writeheader()
    for item in data:
        row = {
            'name': item['name'],
            'age': item['age'],
            'city': item['address']['city'],
            'state': item['address']['state']
        }
        writer.writerow(row)

使用CSV库的时候需要注意以下几个方面：

1. CSV文件必须使用逗号或制表符来分隔每个字段。
2. CSV文件的第一行通常为表头，包含了每个字段的名称。
3. 字段如果包含逗号或制表符等分隔符需要使用引号或转义字符进行处理。