简书python数据分析基础reading_Python数据分析基础ReadingDay5_sqlite3

reading Foundations for Analysis with Python Day 5

《Python数据分析基础》封面

这篇笔记开始记录数据库的内容，会用两篇笔记分别讲述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是很有用的工具，需要多练习来掌握)。关于本系列笔记有任何建议欢迎留言讨论。