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

2020-02-11

  上一篇博客主要讲了使用Python的内置模块sqlite3来创建内存数据库，这篇博客以及之后的博客主要讲MySQL数据库。

准备工作

  我们需要安装Python的MySQLdb扩展包，在Python 2中是MySQL-python，在Python 3中是mysqlclient。我使用的是Anaconda，这个扩展包随着安装Anaconda的时候就捆绑安装好了。
  我们还需要安装MySQL。msi版安装教程可以参考我之前写的博客：《MySQL Community 8.0.19.0 msi版安装教程》，zip版安装教程请自行查阅。

在MySQL中创建新数据库

  打开MySQL 8.0 Command Line Client，输入root密码，进入MySQL。
在这里插入图片描述
  输入命令SHOW DATABASES;，然后按Enter键，我们可以看到，在MySQL数据库系统中已经有了4个数据库。这些数据库使MySQL数据库系统能够运行，并包含系统用户的权限信息。注意：在输入命令SHOW DATABASES;的时候，必须要输入分号。
在这里插入图片描述
  接下来我们来创建自己的数据库。输入命令CREATE DATABASE my_suppliers;，然后按Enter键。我们可以再运行一次SHOW DATABASE;命令，这时我们可以看到我们刚刚创建的my_suppliers数据库。
在这里插入图片描述
  要使用my_suppliers数据库，必须先选择它。输入命令USE my_suppliers;，然后按Enter键。这样我们就已经选择了my_suppliers数据库。
在这里插入图片描述

  接下来我们创建一个数据表Suppliers。输入以下命令，然后按Enter键： 

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS Suppliers
(Supplier_Name VARCHAR(20),
Invoice_Number VARCHAR(20),
Part_Number VARCHAR(20),
Cost FLOAT,
Purchase_Date DATE);

在这里插入图片描述

  我们来解释一下上面的命令。
  如果数据库中不存在数据表Suppliers，这个命令就创建数据表Suppliers。这个表有5个列。其中，前3个列是可变字符VARCHAR型字段。20表示为这个字段中的数据分配20个字符。如果输入这个字段的数据大于20个字符，那么数据将被截断。如果数据少于20个字符，那么这个字段就为数据分配一个更小的空间。
  第4列是一个浮点数FLOAT字段。浮点数字段保存浮点数近似值。在本例中，第4列包含的是货币值，所以可以用NUMERIC定点确定值类型字段替代FLOAT类型字段。不使用FLOAT，也可以使用NUMERIC(11, 2)。其中11是数值的精度，也就是为数值保存的数位总数（包括小数点后面的位数）。2是小数位数，即小数点后面的数位总数。
  第5列是一个日期DATE字段。DATE字段用来保存日期，形式为YYYY-MM-DD，没有时间部分。
  为了确保数据表创建正确，输入命令DESCRIBE Suppliers;，然后按Enter键。
在这里插入图片描述
  我们看到了一个表格，其中列出了创建的列的名称，每列的数据类型以及列中的值是否可以为NULL。
  接下来我们创建一个新用户。输入命令CREATE USER 'username'@'localhost' IDENTIFIED BY 'secret_password';（请注意用要使用的用户名替换username，用自己的密码替换secret_password），然后按Enter键。
  输入以下两条命令，然后在每条命令后面按Enter键，来向新用户授于所有权限。同样地，用要使用的用户名替换下面的username。 

1
2
GRANT ALL PRIVILEGES ON my_suppliers.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

  这样我们就可以同本地主机中的my_suppliers数据库中的Suppliers表进行交互了。
在这里插入图片描述

向表中插入新记录

  下面的Python脚本会将数据从CSV文件中插入到我们的数据表，然后展示表中的数据。我们还是以supplier_data.csv为例。 

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
33
34
35
36
37
38
39
#!/usr/bin/env python3

import csv
import MySQLdb
import sys
from datetime import datetime, date

# 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()

# 向Suppliers表中插入数据
file_reader = csv.reader(open(input_file, 'r', newline=''))
header = next(file_reader)
for row in file_reader:
    data = []
    for column_index in range(len(header)):
        if column_index < 4:
            data.append(str(row[column_index]).lstrip('$').replace(',', '').strip())
        else:
            a_date = datetime.date(datetime.strptime(str(row[column_index]), '%m/%d/%Y'))
            a_date = a_date.strftime('%Y-%m-%d')
            data.append(a_date)
    print(data)
    c.execute("""INSERT INTO Suppliers VALUES (%s, %s, %s, %s, %s);""", data)
con.commit()
print("")

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

  第12行代码使用MySQLdb模块的connect()方法连接my_suppliers，即前面我们创建的MySQL数据库。在连接时，我们需要指定一些通用参数。host是数据库所在的机器的主机名，在这里，MySQL服务器保存在我们的计算机上，所以host是localhost。port是MySQL服务器的TCP/IP连接端口号，这里我们使用的端口号是默认的端口号3306。db是想要连接的数据库名称。user是进行数据库连接的用户的用户名，passwd即为密码。在这里我们作为“root”用户进行连接，使用的密码就是在安装MySQL服务器时创建的密码。我们此前新建了一个新用户，如果想使用新用户，我们只需要把用户名和密码相应地替换掉即可。
  我们在命令行窗口中运行这个脚本，得到输出结果。
在这里插入图片描述
  这个输出结果证明了数据被成功地加载到了Suppliers表中，并被成功读出。
  我们打开MySQL命令行客户端，输入命令SELECT * FROM Suppliers;，可以看到一个表格，其中列出了Suppliers数据表中所有的列以及每列中的12行数据。
在这里插入图片描述