《Using Databases with Python》 Week2 Basic Structured Query Language 课堂笔记

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','kf@umich.edu')

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

SQL Delete

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

DELETE FROM Users WHERE email='kf@umich.edu'

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

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

SQL Update

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

UPDATE Users SET name='Charles' WHERE email='huangyingjing@whu.edu.cn'

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

Retrieving Records: Select

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

SELECT * FROM Users

SELECT * FROM Users WHERE email='huangyingjing@whu.edu.cn'

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()

巴特西