reading Foundations for Analysis with Python Day 5
这篇笔记开始记录数据库的内容，会用两篇笔记分别讲述Python内置的sqlite3和MySQL。 我们已经有了txt、csv以及Excel等文件格式，上面2篇笔记也分别实践了csv和Excel的读写和处理操作，还是挺方便的，那么为什么要用数据库存数据呢？我的理解是相对于文件系统，数据库整体结构化、共享性高、冗余度小，并且基于DBMS（数据库管理系统）有很好的数据安全性、完整性、并发控制和恢复能力。
还有一个说法我觉得挺好：
Excel很容易遇到瓶颈，随着业务的发展，分析师接触的数据会越来越多。对大部分人的电脑，超过十万条数据，已经会影响性能。何况大数据时代就是不缺数据，这时候就需要学习数据库了。--来自秦路的数据分析系列文章
那么什么是数据库呢？
所谓数据库是长期存储在计算机内、有组织、可共享的数据集合。--《数据库系统概论》(第三版).萨师煊.高等教育出版社
本篇笔记先简单讲了什么是数据库，然后用Python3提供的sqlite3库建立内置的数据库，通过比较具体的应用场景讲对数据库的增加、修改和查询数据。数据库部分会有两篇笔记，下一篇会讲MySQL的连接和处理。相对来说，这部分的内容可能有一定的门槛，需要一定的先验知识，比较注重实践。完整地阅读《Python数据分析基础》的第4章会有更大的收获。
目录
- 结构化查询语言SQL
- sqlite3创建数据库
- 批量增加数据
- 更新表中记录
结构化查询语言SQL
SQL（Structured Query Language） 是用于访问和处理数据库的标准的计算机语言，最常用的操作是增删改查。SQL和编程语言不同，它不区分大小写，一般的书为了区别都是使用大写作为SQL的关键字，这篇笔记也是这样。要进一步实践SQL的语句可以参考w3school的教程，或者干脆去仔细阅读《SQL必知必会》。
sqlite3创建数据库
sqlite3是Python3内置的库，它可以使我们创建内存数据库。这就是说我们可以使用 Python 代码直接创建一个数据库和其中充满数据的表，而不用下载安装专门的数据库软件。这篇笔记先是用这种方式实践数据库操作，下一篇会讲用Python操作MySQL，到时候需要额外安装MySQL。
#sqlite3创建、增加、查询数据。下面几段代码基本也用到了这部分的框架
import sqlite3
con = sqlite3.connect(':memory:') # 创建SQLite3内存数据库
query = """CREATE TABLE sales
(customer VARCHAR(20),
product VARCHAR(40),
amount FLOAT,
date DATE);"""
con.execute(query) #执行query 句子的内容
con.commit()
# 在表中插入几行数据
data = [('Richard Lucas', 'Notepad', 3.30, '2014-01-02'),
('Jenny Kim', 'Binder', 3.15, '2014-03-15'),
('Svetlana Crow', 'Printer', 251.15, '2014-02-03'),
('Stephen Randolph', 'Computer', 629.60, '2014-02-20')]
statement = "INSERT INTO sales VALUES(?, ?, ?, ?)"
con.executemany(statement, data)
con.commit()
# 查询sales表中的所有内容
cursor = con.execute("SELECT * FROM sales")
rows = cursor.fetchall()
row_counter = 0
for row in rows: # 计算查询结果中行的数量
print(row)
row_counter += 1
print('Number of rows:',row_counter) #输出
输出：
('Richard Lucas', 'Notepad', 3.3, '2014-01-02')
('Jenny Kim', 'Binder', 3.15, '2014-03-15')
('Svetlana Crow', 'Printer', 251.15, '2014-02-03')
('Stephen Randolph', 'Computer', 629.6, '2014-02-20')
Number of rows: 4
上面的代码先是导入了sqlite3库，并且是在内存中创建的数据库，如果需要把该数据库持久化，则将第二句的':memory:'改为'文件夹路径/新数据库名称.db'。query是一个SQL命令，其作用是创建一个名为 sales 的表，并且有4个字段（字段类似于Excel的列，记录类似于Excel的行），之后使用连接对象的 execute()方法执行包含在变量 query 中的 SQL 命令，创建一个表叫 sales 。 commit() 保存上面的修改。之后分别是用SQL进行增加数据和查询。
execute() 方法运行一条 SQL 命令，并将命令结果赋给一个光标对象 cursor。光标对象有若干方法，例如，execute、executemany、fetchone、fetchmany 和 fetchall。fetchall() 方法能取出（或者说返回）结果集中的所有行。
如果熟悉SQL语言上面的代码比较容易理解。如果对SQL完全不了解建议看一些其他教程进行学习。即使不是专门做数据分析的岗位，有越来越多的产品和运营被要求使用SQL。而且从Excel到SQL是技能树的提升，因此建议看到这篇笔记的同学能抽些时间去了解SQL。
批量增加数据
通过手动改data列表来个性化数据库中的数据挺繁琐的，如果我们有保存了大量数据的csv或Excel文件怎么办呢？解决办法就是用前面学过的csv读取方法读取csv到列表里，再增加到数据库里。Excel文件可以转为csv再用前面的方法，或者直接读取Excel中的数据到dataframe里，再写入数据库。
下面代码的功能是向表中插入 CSV 文件中的数据，然后展示表中的数据。
def csvDataToDB(input_file):
import csv
import sqlite3
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) #执行create_table中的SQL语句
con.commit()
# 向Suppliers表中插入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])
c.execute("INSERT INTO Suppliers VALUES (?, ?, ?, ?, ?);", data)
con.commit()
# 通过查询输出Suppliers表
output = c.execute("SELECT * FROM Suppliers")
rows = output.fetchall()
for row in rows[:5]:
output = []
for column_index in range(len(row)):
output.append(str(row[column_index]))
print(output)
in_f='supplier_data.csv'
csvDataToDB(in_f) #调用
输出：
['Supplier X', '001-1001', '2341', '$500.00', '1/20/14']
['Supplier X', '001-1001', '2341', '$500.00', '1/20/14']
['Supplier X', '001-1001', '5467', '$750.00', '1/20/14']
['Supplier X', '001-1001', '5467', '$750.00', '1/20/14']
['Supplier Y', '50-9501', '7009', '$250.00', '1/30/14']
上面代码的思路就是创建了一个数据库叫Suppliers.db，然后用SQL语句添加了5个字段，之后用我们熟悉的csv库读取数据到列表data里，使用循环和glob的话，可以将该读取功能扩展到任意数目的文件，在循环中写入数据库，最后通过查询语句查看表中的内容，书中提示在加入的行太多时，不建议再输出来看，我觉得输出前面5行（5条记录）是比较好的展示，这个改一下代码的循环条件就好。
更新表中记录
上面说完了增加数据形成新的记录，SQL的操作增/删/改/查中，改也是很重要的内容，有时候，我们不需要向数据表中加载新数据，而是需要更新表中已有的行，因此用到了UPDATE 语句。
def updateDBrow(input_file):
#输入参数：需要读取的csv文件路径
import csv
import sqlite3
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 [3,4,1]:
data.append(float(row[column_index][1:])) if column_index==3 else 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[:3]:
output = []
for column_index in range(len(row)):
output.append(str(row[column_index]))
print(output)
in_f='supplier_data.csv'
updateDBrow(in_f)
输出：
('Richard Lucas', 'Notepad', 2.5, '2014-01-02')
('Jenny Kim', 'Binder', 4.15, '2014-01-15')
('Svetlana Crow', 'Printer', 155.75, '2014-02-03')
('Stephen Randolph', 'Computer', 679.4, '2014-02-20')
['Richard Lucas', 'Notepad', '2.5', '2014-01-02']
['Jenny Kim', 'Binder', '4.15', '2014-01-15']
['Svetlana Crow', 'Printer', '155.75', '2014-02-03']
删除特定的记录
上面用代码分别说明了创建数据库、增加数据、修改数据、查询和显示数据，书中就这样开始讲到MySQL了，没有讲到删除特定的数据，删除某一记录用到DELETE语句，下面给出一个简单的示例：
#删除符合条件的记录
def deleteDataRow():
import csv
import sqlite3
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', 4.50, '2014-01-02'),
('Jenny Kim', 'Binder', 2.15, '2014-01-15'),
('Svetlana Crow', 'Printer', 151.75, '2014-02-03'),
('Stephen Randolph', 'Computer', 679.40, '2014-02-20')]
statement = "INSERT INTO sales VALUES(?, ?, ?, ?)"
con.executemany(statement, 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)
#c_del = con.cursor()
con.execute("DELETE FROM sales WHERE product<10;")
# 查询sales表并显示出来
print('====after DELETE:====')
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)
deleteDataRow()
输出：
['Richard Lucas', 'Notepad', '4.5', '2014-01-02']
['Jenny Kim', 'Binder', '2.15', '2014-01-15']
['Svetlana Crow', 'Printer', '151.75', '2014-02-03']
['Stephen Randolph', 'Computer', '679.4', '2014-02-20']
====after DELETE:====
['Richard Lucas', 'Notepad', '4.5', '2014-01-02']
['Jenny Kim', 'Binder', '2.15', '2014-01-15']
['Svetlana Crow', 'Printer', '151.75', '2014-02-03']
['Stephen Randolph', 'Computer', '679.4', '2014-02-20']
从上面的系列代码可以看到，sqlite3实现数据库常用操作的代码还是很简洁的，下一篇笔记会讲MySQL的内容。对文件操作不太熟悉文件操作可以参考之前关于csv读取以及Excel文件读取的笔记。
本篇笔记的GitHub同步项目于readingForDS。(pandas是很有用的工具，需要多练习来掌握)。关于本系列笔记有任何建议欢迎留言讨论。
