【译】怎样用Python读取CSV文档

原文地址：https://blog.mclaughlinsoftware.com/2021/12/12/read-csv-with-python/
原文作者：MacLochlainns

2009年，我写过怎样用MySQL导入数据到指定位置的命令。去年，更新了用导入命令重置the secure_file-priv privilege的细节，但是通过如下例子，可以用一个简单的Python 3程序来代替这种方式。我们也可以用MySQL Shell工具集中的import_table导入新特性，此概念在8.0.17中引入，这篇博客中它以注释的形式展现。

• 示例要求：
  创建表：表名avenger
  文件：文件名avenger.csv
  创建Python脚本：readWriteData.py
  运行以上脚本，并对导入avenger.csv文件后的表进行查询

所有的代码在如下5个步骤中：

如果存在avenger表，先删除

DROP TABLE IF EXISTS avenger;

1. 创建avenger表；

CREATE TABLE avenger
( avenger_id    int unsigned PRIMARY KEY AUTO_INCREMENT
, first_name    varchar(20)
, last_name     varchar(20)
, avenger_name  varchar(20))
  ENGINE=InnoDB
  AUTO_INCREMENT=1001
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

2. 创建包含如下数据的avenger.csv文件；

Anthony,Stark,Iron Man
Thor,Odinson,God of Thunder
Steven,Rogers,Captain America
Bruce,Banner,Hulk
Clinton,Barton,Hawkeye
Natasha,Romanoff,Black Widow
Peter,Parker,Spiderman
Steven,Strange,Dr. Strange
Scott,Lange,Ant-man
Hope,van Dyne,Wasp

3. 创建Python 3脚本readWriteFile.py

# Import libraries.
import csv
import mysql.connector
from mysql.connector import errorcode
from csv import reader
 
#  Attempt the statement.
# ============================================================
#  Use a try-catch block to manage the connection.
# ============================================================
try:
  # Open connection.
  cnx = mysql.connector.connect( user='student'
                               , password='student'
                               , host='127.0.0.1'
                               , database='sakila')
  # Create cursor.
  cursor = cnx.cursor()
 
  # Open file in read mode and pass the file object to reader.
  with open('avenger.csv', 'r') as read_obj:
    csv_reader = reader(read_obj)
 
    # Declare the dynamic statement.
    stmt = ("INSERT INTO avenger "
            "(first_name, last_name, avenger_name) "
            "VALUES "
            "(%s, %s, %s)")
 
    # Iterate over each row in the csv using reader object
    for row in csv_reader:
      cursor.execute(stmt, row)
 
    # Commit the writes.
    cnx.commit()
 
    #close the connection to the database.
    cursor.close()
 
# Handle exception and close connection.
except mysql.connector.Error as e:
  if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif e.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print("Error code:", e.errno)        # error number
    print("SQLSTATE value:", e.sqlstate) # SQLSTATE value
    print("Error message:", e.msg)       # error message
 
# Close the connection when the try block completes.
else:
  cnx.close()

4. 运行readWriteFile.py文件

python3 readWriteFile.py

5. 查询avenger表

SELECT * FROM avenger;

返回值如下

+------------+------------+-----------+-----------------+
| avenger_id | first_name | last_name | avenger_name    |
+------------+------------+-----------+-----------------+
|       1001 | Anthony    | Stark     | Iron Man        |
|       1002 | Thor       | Odinson   | God of Thunder  |
|       1003 | Steven     | Rogers    | Captain America |
|       1004 | Bruce      | Banner    | Hulk            |
|       1005 | Clinton    | Barton    | Hawkeye         |
|       1006 | Natasha    | Romanoff  | Black Widow     |
|       1007 | Peter      | Parker    | Spiderman       |
|       1008 | Steven     | Strange   | Dr. Strange     |
|       1009 | Scott      | Lange     | Ant-man         |
|       1010 | Hope       | van Dyne  | Wasp            |
+------------+------------+-----------+-----------------+
10 rows in set (0.00 sec)