Coursera课程《Using Databases with Python》 密歇根大学

Week2 Basic Structured Query Language

15.1 Relational Databases

Terminology

• 数据库（Database） - 包含很多个表
• 关系或表（Relation or Table） - 包含很多元组和属性
• 元组或行（Tuple or Row） - 一组数据，它们一般代表着一个“实体”
• 属性或列（Attribute or Column） - 与行所展示的实体相关的许多元素之一

SQL

SQL语言，是结构化查询语言(Structured Query Language)的简称。SQL语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

15.2 Using Databases

Two Roles in Large Projects

• Application Developer应用开发员 - 创建应用的逻辑、外观和感受 - 解决应用有关的问题
• Database Administrator数据库管理员 - 在程序运作过程中管理和调试数据库
• 通常两类人都参与“数据模型（Data model）”的构建

Large Project Structure

Data Analysis Structure

Common Database Systems

有三个主要的数据库管理系统被广泛使用：

• Oracle - 大型，商业化，企业级，非常tweakable(?) - 一般用于企业
• Mysql - 更简洁但是也更快捷和可扩展 - 商业的开源项目 - 一般用于网页
• SqlServer - 很好 - 来自Microsoft（以及Access）

还有一些更小的项目，但是免费而且开源，比如说HSQL，SQLite，Postgress，...

15.3 Single Table CRUD

SQLite Browser

SQLite是一个非常常用的数据库，它是免费的，而且又快又小。

SQLite Browser允许我们直接操作SQLite文件，网站是http://sqlitebrowser.org/

SQLite已经植入了包括Python在内的很多语言。

安装好了之后，然后我们新建一个数据库，随意存在一个自己找得到的地方就行。新建了之后，弹出来的对话框暂且不管，关掉它。

Start Simple - A Single Table

现在我们来新建一个表。

选择执行SQL这个标签，输入下面的SQL代码。

CREATE TABLE Users(
name VARCHAR(128),
email VARCHAR(128)
)

这样，我们就创建了一个叫User的表，以及有两列。其中一列是name，最多可以有128个字符；另外一列是email，也是最多有128个字符。也就是说，我们定好了一个表的框架。

然后我们可以向这个表里添加一点数据。在浏览数据的标签页里，使用新建记录可以添加我们想要的数据。而这种方式是用户交互的方式，有点像我们使用Excel。但是这些其实相当于是应用在给我们写SQL，也就是SQL日志里显示的这些。

SQL Insert

向一个表里加一行新的记录的SQL语句如下

INSERT INTO Users(name,email) VALUES('Kristin','[email protected]')

仍然在执行SQL标签页里去执行这句语句，可以看到我们这样就新建了一条记录。

SQL Delete

在一个表里删除一条特定的记录的SQL语句如下

DELETE FROM Users WHERE email='[email protected]'

执行这句，我们可以发现刚刚添加的这条记录就被删除了。

这个WHERE有点像if语句，找到符合条件的记录。

SQL Update

对一个表内已有的数据进行修改的SQL语句如下

UPDATE Users SET name='Charles' WHERE email='[email protected]'

执行这句，就可以把找到对应email的name改为Charles。这里仍然使用到了WHERE。

Retrieving Records: Select

select语句可以取到一部分记录，同样使用WHERE就能进行筛选。

SELECT * FROM Users
SELECT * FROM Users WHERE email='[email protected]'

Sorting with ORDER BY

我们可以在SELECT语句中加上ORDER BY语句，这样我们的结果就能按一定的顺序排列。

SELECT * FROM Users ORDER BY email
SELECT * FROM Users ORDER BY name

Worked Example: Counting Email in a Database

import sqlite3

conn = sqlite3.connect('emaildb.sqlite')
cur = conn.cursor()

cur.execute('DROP TABLE IF EXISTS Counts')

cur.execute('''
CREATE TABLE Counts (email TEXT, count INTEGER)''')

fname = input('Enter file name: ')
if (len(fname) < 1): fname = 'mbox-short.txt'
fh = open(fname)
for line in fh:
    if not line.startswith('From: '): continue
    pieces = line.split()
    email = pieces[1]
    cur.execute('SELECT count FROM Counts WHERE email = ? ', (email,))
    row = cur.fetchone()
    if row is None:
        cur.execute('''INSERT INTO Counts (email, count)
                VALUES (?, 1)''', (email,))
    else:
        cur.execute('UPDATE Counts SET count = count + 1 WHERE email = ?',
                    (email,))
conn.commit()

# https://www.sqlite.org/lang_select.html
sqlstr = 'SELECT email, count FROM Counts ORDER BY count DESC LIMIT 10'

for row in cur.execute(sqlstr):
    print(str(row[0]), row[1])

cur.close()

上面是一个建表并且更新或增加表项的过程，这个过程就会在当前目录下新建一个emaildb.sqlite的sql文件。
需要注意的是第26行的commit()，只有在执行它之后才会将结果写入文件，所以放到循环外卖执行会快很多。

作业：Counting Email in a Database

注意：一定要把mbox.txt下载下来，而不是复制网页文本。不然最后统计的邮件会出错。

import sqlite3
import re

conn = sqlite3.connect('emaildb.sqlite')
cur = conn.cursor()

cur.execute('''
DROP TABLE IF EXISTS Counts''')

cur.execute('''
CREATE TABLE Counts (org TEXT, count INTEGER)''')

fname = input('Enter file name: ')
if (len(fname) < 1): fname = 'mbox-short.txt'
fh = open(fname)
for line in fh:
    if not line.startswith('From: '): continue
    all_org = re.findall("@(.+)\s", line)
    org = all_org[0]
    cur.execute('SELECT count FROM Counts WHERE org = ? ', (org,))
    row = cur.fetchone()
    if row is None:
        cur.execute('''INSERT INTO Counts (org, count)
                VALUES (?, 1)''', (org,))
    else:
        cur.execute('UPDATE Counts SET count = count + 1 WHERE org = ?',
                    (org,))
conn.commit()

# https://www.sqlite.org/lang_select.html
sqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10'

for row in cur.execute(sqlstr):
    print(str(row[0]), row[1])
cur.close()