Python数据分析基础之数据库(3)

2020-02-11

查询一个表并将输出写入CSV文件

  数据表中有了数据之后，最常见的下一个步骤就是使用查询从表中取出一组数据进行分析。使用Python脚本可以从数据表中查询出一组特定记录。
  下面的代码会从Suppliers数据表中查询出Cost列中的值大于700.00的所有记录，并将这些记录所有列中的值输出。 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#!/usr/bin/env python3

import csv
import MySQLdb
import sys

# CSV输出文件的路径和文件名
output_file = sys.argv[1]

# 连接MySQL数据库
con = MySQLdb.connect(host='localhost', port=3306, db='my_suppliers', user='root', passwd='## your password ##')
c = con.cursor()

# 创建写文件的对象，并写入标题行
filewriter = csv.writer(open(output_file, 'w', newline=''), delimiter=',')
header = ['Supplier Name', 'Invoice Number', 'Part Number', 'Cost', 'Purchase Date']
filewriter.writerow(header)

# 查询Suppliers表，并将结果写入CSV输出文件
c.execute("""SELECT *
             FROM Suppliers
             WHERE Cost > 700.0;""")
rows = c.fetchall()
for row in rows:
    filewriter.writerow(row)

  在命令行窗口中运行这个脚本，得到CSV输出文件。
在这里插入图片描述

更新表中记录

  更新表中记录的方法与之前的插入新记录的方法类似，只是SQL语句有所变化，将INSERT语句变为了UPDATE语句。代码如下： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
#!/usr/bin/env python3

import csv
import MySQLdb
import sys

# CSV输入文件的路径和文件名
input_file = sys.argv[1]

# 连接MySQL数据库
con = MySQLdb.connect(host='localhost', port=3306, db='my_suppliers', user='root', passwd='## your password ##')
c = con.cursor()

# 读取CSV文件并更新特定的行
file_reader = csv.reader(open(input_file, 'r', newline=''), delimiter=',')
header = next(file_reader, None)
for row in file_reader:
    data = []
    for column_index in range(len(header)):
        data.append(str(row[column_index]).strip())
    print(data)
    c.execute("""UPDATE Suppliers SET Cost=%s, Purchase_Date=%s WHERE Supplier_Name=%s;""", data)
con.commit()

# 查询Suppliers表
c.execute("SELECT * FROM Suppliers")
rows = c.fetchall()
for row in rows:
    output = []
    for column_index in range(len(row)):
        output.append(str(row[column_index]))
    print(output)

  在UPDATE语句中，必须指定想要更新哪一条记录和哪一个列属性。在这里，我们想为一组特定的Supplier Names更新Cost值和Purchase Date值。UPDATE语句中需要几个值，就需要几个%s占位符表示出查询中的值的位置，CSV输入文件中数据的顺序也要同查询中属性的顺序一样。
  现在我们新建一个CSV输入文件：
在这里插入图片描述
  在命令行窗口中运行这个脚本，得到输出结果如下：
在这里插入图片描述
  输出结果的前两行展示了来自于CSV输入文件中除标题行之外的两行数据。在这两行下面，输出展示了执行更新之后从数据表中取出的12行数据，每行数据占一行。
  下面我们来确认一下MySQL数据表中与Supplier X和Supplier Y相关的8行数据是否已被更新。打开MySQL命令行客户端，输入命令SELECT * FROM Suppliers，然后按Enter键。
在这里插入图片描述
  下面的表格是更新后的数据表。这里我截取了本次和上一次的数据表，为的是有一个明显的对比。我们看到，Supplier X和Supplier Y相关的8行记录已经被更新为CSV输入文件中提供的数据。