【Python for Everybody】15 Databases

Relational Databases

SQL

SQL for Single Table–创建、插入、删除、更新、查询、排序

文档参考：
https://www.py4e.com/lectures3/Pythonlearn-15-Database-Handout.txt

-- 创建表
CREATE TABLE "Users" ("name" TEXT, "email" TEXT) 
/*创建名为Users的表，指明包含列name和email，及对应的数据类型为text
TEXT文本，INTEGER整数，NUMERIC小数，BLOB二进制对象如图片或音乐，
REAL不精确的双精度浮点数*/
-- 插入
INSERT INTO Users (name, email) VALUES ('Chuck', 'csev@umich.edu');/*连续运行要加分号*/
INSERT INTO Users (name, email) VALUES ('Colleen', 'cvl@umich.edu');
INSERT INTO Users (name, email) VALUES ('Ted', 'ted@umich.edu');
INSERT INTO Users (name, email) VALUES ('Sally', 'a1@umich.edu');
INSERT INTO Users (name, email) VALUES ('Ted', 'ted@umich.edu');
INSERT INTO Users (name, email) VALUES ('Kristen', 'kf@umich.edu')
-- 删除
DELETE FROM Users WHERE email='ted@umich.edu'
-- 更新
UPDATE Users SET name="Charles" WHERE email='csev@umich.edu'
-- 查询全部
SELECT * FROM Users
-- 查询部分行
SELECT * FROM Users WHERE email='csev@umich.edu'
-- 查询并升序排序
SELECT * FROM Users ORDER BY email
-- 查询并降序排序
SELECT * FROM Users ORDER BY name DESC

import sqlite3

conn = sqlite3.connect('emaildb.sqlite')  # access file，若无就创建该文件
cur = conn.cursor()  #有点像open()，但是cursor是指open然后将SQL command发送给cursor，然后它再帮你发送给sqlite3得到你想要的回应

cur.execute('DROP TABLE IF EXISTS Counts')  # 如果存在文件，就舍弃掉原来的table，防止blow up，这一步是保证我们拥有一个新文件start it fresh

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  # 选出以From: 开头的行
    pieces = line.split()
    email = pieces[1]  # 选出email部分
    cur.execute('SELECT count FROM Counts WHERE email = ? ', (email,))  
# 其中?是占位符，执行过程中email会自动填入?；(email,)是只有一个元素的turple；这一行是查看现有的表
    row = cur.fetchone()  # 读取一行
    if row is None:  # 如果表中没有该email就插入
        cur.execute('''INSERT INTO Counts (email, count)
                VALUES (?, 1)''', (email,))
    else:  # 如果有该email，就count+1
        cur.execute('UPDATE Counts SET count = count + 1 WHERE email = ?',
                    (email,))
    conn.commit() # 确保每一行都被执行，将数据写入main memory或disk

# 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):  # 在python中执行sql文件都要加上cur.execute()
    print(str(row[0]), row[1])  # 转换成str在python中输出 

cur.close()  # 关闭sqllite

从twitter API中爬取一个推特账户的most recent friend信息存储到sql中

from urllib.request import urlopen
import urllib.error
import twurl
import json
import sqlite3
import ssl

TWITTER_URL = 'https://api.twitter.com/1.1/friends/list.json'

conn = sqlite3.connect('spider.sqlite') # 创建sqlite文件
cur = conn.cursor()

cur.execute('''
            CREATE TABLE IF NOT EXISTS Twitter
            (name TEXT, retrieved INTEGER, friends INTEGER)''')

# Ignore SSL certificate errors
ctx = ssl.create_default_context()
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE

while True:
    acct = input('Enter a Twitter account, or quit: ')
    if (acct == 'quit'): break