《python数据分析基础》第四章学习笔记_python数据分析基本教程前四章主要内容-优快云博客

本文详细介绍了使用Python进行数据分析，包括使用内置的sqlite3模块创建和操作内存数据库，以及与MySQL数据库的交互，如插入、更新数据表和处理CSV文件。

python数据分析第四章

数据库

要学习如何使用Python同数据库交互，首先要有个数据库，并且数据库中要有一张充满了数据的表。
首先，Python有个内置模块sqlite3，它可以使我们创建内存数据库。这就是说我们可以使用Python代码直接创建一个数据库和其中充满数据的表。
其次，下载并安装一个数据库系统。

4.1 Python内置sqlite3模块

第一个示例重点演示如何对SQL查询输出的行进行计数。要创建数据库中的表、在表中插入数据，以及在输出中获取数据并对行进行计数，在文本编辑器中输入下列代码，然后将文件保存为1db_count_rows.py:

#!/Users/chenbryant/anaconda3/bin/python
import sqlite3
# 创建SQLite3内存数据库
# 创建带有4个属性的sales表
con = sqlite3.connect(':memory:')
query = """CREATE TABLE sales
            (customer VARCHR(20),
            product VARCHAr(40),
            amount FLOAT,
            date DATE);"""
# execute（）方法：在数据库中创建sales表
con.execute(query)
# 将修改保存到数据库
con.commit()
# 在表中插入几行数据
data = [('Richard Lucas', 'Notepad', 2.50, '2014-01-02'),
        ('Jenny Kim', 'Binder', 4.15, '2014-01-15'),
        ('Svetlana Crow', 'Printer', 155.75, '2014-02-03'),
        ('Stephen Randolph', 'Computer', 679.40, '2014-02-20')]
# INSERT为SQL中的命令，可以将data中的数据行插入sales表。？是站位符
statement = "INSERT INTO sales VALUES(?, ?, ?, ?)"
# data中的别个数据元组执行变量statement中的SQL命令
con.executemany(statement, data)
con.commit()

# 查询sales表
# 用连接对象的execute（）方法运行一条SQL命令，并将命令结果赋给光标对象cursor
cursor = con.execute("SELECT * FROM sales")
# 用fetchall（）方法取出结果集中的所有行
rows = cursor.fetchall()

# 计算查询结果中行的数量
row_counter = 0
for row in rows:
    print(row)
    row_counter += 1
print('Number of rows: %d' % (row_counter))

4.1.1 向表中插入新纪录

创建一个新的Python脚本，这个脚本将创建一个数据表，向表中插入CSV文件中的数据，然后展示表中的数据。在文本编辑器中输入下列代码，然后将文件保存为2db_insert_row.py:

#!/Users/chenbryant/anaconda3/bin/python
import csv
import sqlite3
import sys
# CSV输入文件的路径和文件名
input_file = sys.argv[1]
# 创建SQLite3内存数据库
# 创建带有5个属性的Suppliers表
con = sqlite3.connect('Suppliers.db')
c = con.cursor()
create_table = """CREATE TABLE IF NOT EXISTS Suppliers
                    (Supplier_Name VARCHAR(20),
                    Invoice_Number VARCHAR(20),
                    Part_Number VARCHAR(20),
                    Cost FLOAT,
                    Purchase_Date DATE);"""
c.execute(create_table)
con.commit()

# 读取CSV
# 向Suppliers表中插入数据
file_reader = csv.reader(open(input_file, 'r'), delimiter=',')
header = next(file_reader, None)
for row in file_reader:
    data = []
    for column_index in range(len(header)):
        data.append(row[column_index])
    print(data)
    c.execute("INSERT INTO Suppliers VALUES (?, ?, ?, ?, ?);", data)
con.commit()
print('')
# 查询Suppliers表
output = c.execute("SELECT * FROM Suppliers")
rows = output.fetchall()
for row in rows:
    output = []
    for column_index in range(len(row)):
        output.append(str(row[column_index]))
    print(output)

第9行代码中提供了一个数据库名称，而不使用专门的关键字’:memory:'，来掩饰如何创建一个持久化数据库，当你重启计算机时，这个数据库不会被删除。
在命令行中输入下列命令：

./2db_insert_row.py supplier_data.csv

4.1.2 更新表中记录

上一个例子介绍了使用CSV输入文件向数据表中添加新行的方法，因为可以使用循环和glob，所以可以将这个方法扩展到任意数目的文件。下面示例将会介绍从CSV输入文件中读取新的数据来更新表中已有的行。实际上，为SQL语句组装一组值和为输入文件中的每一行执行SQL语句的技术与前一个例子是一样的，不同的是从INSERT语句变成了UPDATE语句。
在文本编辑器中输入下列代码，然后将文件保存为3db_update_row.py:

#!/Users/chenbryant/anaconda3/bin/python
import csv
import sqlite3
import sys
# CSV输入文件的路径和文件名
input_file = sys.argv[1]
# 创建SQLite3内存数据库
# 创建带有4个属性的sales表
con = sqlite3.connect(':memory:')
query = """CREATE TABLE IF NOT EXISTS sales
               (customer VARCHAR(20),
                product VARCHAR(40),
                amount FLOAT,
                date DATE);"""
con.execute(query)
con.commit()
# 向表中插入几行数据
data = [('Richard Lucas', 'Notepad', 2.50, '2014-01-02'),
          ('Jenny Kim', 'Binder', 4.15, '2014-01-15'),
          ('Svetlana Crow', 'Printer', 155.75, '2014-02-03'),
          ('Stephen Randolph', 'Computer', 679.40, '2014-02-20')]
for tuple in data:
    print(tuple)
statement = "INSERT INTO sales VALUES(?, ?, ?, ?)"
con.executemany(statement, data)
con.commit()
# 读取CSV文件并更新特定的行
file_reader = csv.reader(open(input_file, 'r'), delimiter=',')
header = next(file_reader, None)
for row in file_reader:
    data = []
    for column_index in range(len(header)):
        data.append(row[column_index])
    print(data)
    con.execute("UPDATE sales SET amount=?, date=? WHERE customer=?;", data)
con.commit()
# 查询sales表
cursor = con.execute("SELECT * FROM sales")
rows = cursor.fetchall()
for row in rows:
    output = []
    for column_index in range(len(row)):
        output.append(str(row[column_index]))
    print(output)

创建一个CSV文件：保存为data_for_updating.csv

amount	date	customer
4.25	5/11/2014	Riuchard Lucas
6.75	5/12/2014	Jenny Kim

最后在命令行中输入下列命令：

./3db_update_row.py data_for_updating.csv

4.2 MySQL数据库

自行下载MySQL
输入mysql -u root -p
输入SHOW DATABASES; 注意此命令以分号结尾，这样MySQL才知道你的命令已经输入完成。如果忘记分号，在下一行中输入分号然后回车。
要创建一个数据库，输入以下命令，然后按回车键：
CREATE DATABASE my_suppliers;
要选择my_suppliers数据库，输入以下命令，然后按回车键：
USE my_suppliers;
按了回车键之后，你就已经选择了my_suppliers数据库。现在可以创建数据表来保存数据了。

要创建一个数据表Suppliers，输入以下命令，然后按回车键：

 CREATE TABLE IF NOT EXISTS Suppliers 
 (Supplier_Name VARCHAR(20), 
 Invoice_Number VARCHAR(20), 
 Part_Number VARCHAR(20), 
 Cost FLOAT, 
 Purchase_Date DATE);

为了确保数据表创建正确，输入以下命令：
```
 DESCRIBE Suppliers;
```
要创建一个新用户，输入以下命令（用你要使用的用户名替换username,你还应该用自己的密码替换secret_password,来获得更高的安全性）
```
 CREATE USER 'username'@'localhost' IDENTIFIED BY 'secret_password';
```
我们已经创建了一个新用户，现在要为这个用户授予my_suppliers数据库的所有权限。通过授予用户所有的数据库权限，就使这个用户可以在数据库中的表上执行各种操作。
要向新用户授予所有的权限，输入以下两条命令：
```
 GRANT ALL PRIVILEGES ON my_suppliers.* TO 'username'@'localhost';
 FLUSH PRIVILEGES;
```
现在你可以同本地主机中的my_suppluers数据库中的Suppliers表进行交互了。

4.2.1 向表中插入新纪录

#!/Users/chenbryant/anaconda3/bin/python
import csv
import pymysql
import sys
from datetime import datetime, datetime

# CSV输入文件的路径和文件名
input_file = sys.argv[1]
# 连接MySQL数据库
con = pymysql.connect(host='localhost', port=3306, db='my_suppliers', user='root', passwd='root123a')
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'))
            # %Y: year is 2015; %y: year is 15
            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)

host:是数据库所在的机器的主机名。在本例中，MySQL服务器保存在你的计算机上，所以host是localhost。当你连接其他数据源时，服务器可能位于不同的机器上，所以你需要修改localhost，更换成服务器所在的机器的主机名。
port:是MySQL服务器的TCP/IP连接端口号。我们要使用的端口号是默认的端口号3306.和host参数一样，如果你不在本地主机上工作，而且你的MySQL服务器管理员为服务器设置了不同的端口号，那么你必须使用这个端口号去连接MySQL服务器。
db:是你想连接的数据库名称。本例题中，我们想连接my_suppliers数据库，因为它保存着我们要加载数据的表。
输入以下命令：

./4db_mysql_load_from_csv.py supplier_data.csv

在MySQL命令行客户端输入以下命令就可以看到Suppliers数据表中所有的数据：

SELECT * FROM Suppliers;

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

下面创建一个新的Python脚本，这个脚本会从Suppliers数据表中查询出一组特定记录，然后将输出写入CSV输出文件。在这个例子中，我们的目标是找出Cost列中的值大于700.00的所有记录，并将这些记录所有列中的值输出。输入下列代码并保存为5db_mysql_write_to_ﬁle.py：

#!/Users/chenbryant/anaconda3/bin/python
import csv
import pymysql
import sys
# CSV输出文件的路径和文件名
output_file = sys.argv[1]
# 连接数据库
con = pymysql.connect(host='localhost', port=3306, db='my_suppliers', user='root', passwd='root123a')
c = con.cursor()
# 创建写文件的对象，并写入标题行
filewriter = csv.writer(open(output_file, 'w', newline=''), delimiter=',')
header = ['Supplier Name', 'Incoice 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)

在命令行中输入：

/5db_mysql_write_to_ﬁle.py output_files/5output.csv

4.2.3 更新表中记录

下列代码保存为6db_mysql_update_from_csv.py：

#!/Users/chenbryant/anaconda3/bin/python
import csv
import pymysql
import sys
# CSV输入文件的路径和文件名
input_file = sys.argv[1]
# 连接数据库
con = pymysql.connect(host='localhost', port=3306, db='my_suppliers', user='root', passwd='root123a')
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)

再创建一个CSV输入文件：data_for_updating.csv

Cost	Purchase Date	Supplier Name
600	2014-01-22	Supplier X
200	2014-02-01	Supplier Y

命令行输入：

./6db_mysql_update_from_csv.py data_for_updating.csv

在MySQL命令行中输入下列代码就能查看更新后的数据表：

SELECT * FROM Suppliers;