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_file.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_file.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;