Using Databases with Python 笔记

Object Oriented Python

课程首先讲了一下Python的类的定义，它也是很通常的定义，

class PartyAnimal:
    x = 0

    def __init__(self):
        print('I am constructed')
    
    def party(self):
        self.x = self.x + 1
        print('So far', self.x)

    def __del__(self):
        print('I am destructed', self.x)

class cat(PartyAnimal):
    name = 'None'

    def __init__(self, name):
        self.name = name
        print('I am constructed')

    def eat(self):
        print(self.name, 'eat')


p = PartyAnimal()
p.party()
p = 102

print("eeeeeeeeeeeeeeeeeeeeeeeeeeeeee")

cc = cat("XIAOBAI")
cc.eat()
cc = 134

I am constructed
So far 1
I am destructed 1
eeeeeeeeeeeeeeeeeeeeeeeeeeeeee
I am constructed
XIAOBAI eat
I am destructed 0

所以新建类，使用class，继承类就在后面加上(要继承的类)，也就是class name(Inheritance)。

继承会继承成员变量和成员方法，以及构造器和析构器，当然也可以重写方法，总体就是十分正常的继承规则。

Basic Structured Query Language

这一部分将了基本的数据库语法，然后在python中书写sql，需要注意的是python中自带的是sqlite，是一个轻量级的sql的库，所以它可以直接做成一个包来调用。

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

上面是一个建表并且更新或增加表项的过程，这个过程就会在当前目录下新建一个emaildb.sqlite的sql文件。

需要注意的是第29行的commit()，只有在执行它之后才会将结果写入文件，所以放到循环外面执行会快很多。

Data Models and Relational SQL

这一部分讲了数据库的一些使用技巧。

在使用数据库时，数据量小的时候自然是无所谓，但是在数据量很大的时候，要遵循一个原则，那就是对于经常会重复出现的字符串，我们不能让它重复，因为它这样会浪费空间。

所以存储时，对于重复出现的字符串，将它单独建立为一个表，别的表使用它的下标来进行索引。但是这个索引是我们自己做的，所以查询时要有技巧。

例如音乐的专辑和歌手。显然一个歌手平均不止一张专辑，那么就将专辑和歌手拆成两个表，

序号	歌手名
1	周杰伦
2	林俊杰
3	Avril Lavigne

序号	专辑名	歌手名
1	八度空间	1
2	范特西	1
3	第二天堂	2
4	Let go	3
5	叶惠美	1
6	Under My Skin	3

这样也就是在专辑表中，我们就节约了存储空间，更为重要的是这样可以得到数据之间清晰的关系。

那么查询的时候该怎么办呢？可以使用下面语句来查询，

1	SELECT 专辑.专辑名, 歌手.歌手名 FROM 专辑 JOIN 歌手 ON 专辑.歌手名 = 歌手.序号

其中JOIN关键字的任务是将两个表合在一起查询，ON关键字限定了条件，进行筛选。这就能查询得到下面的结果:

专辑名	歌手名
八度空间	周杰伦
范特西	周杰伦
第二天堂	林俊杰
Let go	Avril
叶惠美	周杰伦
Under My Skin	Avril

下面是作业中使用python脚本建立数据库的过程，注意其中的关键字IGNORE，它的作用是如果当期数据存在，那就不插入，否则插入。在这个地方十分有用，因为索引不能随意变化。

import xml.etree.ElementTree as ET
import sqlite3

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

# Make some fresh tables using executescript()
cur.executescript('''
DROP TABLE IF EXISTS Artist;
DROP TABLE IF EXISTS Album;
DROP TABLE IF EXISTS Track;
DROP TABLE IF EXISTS Genre;

CREATE TABLE Artist (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name TEXT UNIQUE
);

CREATE TABLE Genre (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name TEXT UNIQUE
);

CREATE TABLE Album (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    artist_id  INTEGER,
    title TEXT UNIQUE
);

CREATE TABLE Track (
    id  INTEGER NOT NULL PRIMARY KEY 
        AUTOINCREMENT UNIQUE,
    title TEXT  UNIQUE,
    album_id  INTEGER,
    genre_id  INTEGER,
    len INTEGER, rating INTEGER, count INTEGER
);
''')


fname = input('Enter file name: ')
if ( len(fname) < 1 ) : fname = 'Library.xml'

# <key>Track ID</key><integer>369</integer>
# <key>Name</key><string>Another One Bites The Dust</string>
# <key>Artist</key><string>Queen</string>
def lookup(d, key):
    found = False
    for child in d:
        if found : return child.text
        if child.tag == 'key' and child.text == key :
            found = True
    return None

stuff = ET.parse(fname)
all = stuff.findall('dict/dict/dict')
print('Dict count:', len(all))
for entry in all:
    if ( lookup(entry, 'Track ID') is None ) : continue

    name = lookup(entry, 'Name')
    artist = lookup(entry, 'Artist')
    album = lookup(entry, 'Album')
    count = lookup(entry, 'Play Count')
    rating = lookup(entry, 'Rating')
    length = lookup(entry, 'Total Time')
    genre = lookup(entry, 'Genre')

    if name is None or artist is None or album is None or genre is None: 
        continue

    print(name, artist, album, genre, count, rating, length)

    cur.execute('''INSERT OR IGNORE INTO Artist (name) 
        VALUES ( ? )''', ( artist, ) )
    cur.execute('SELECT id FROM Artist WHERE name = ? ', (artist, ))
    artist_id = cur.fetchone()[0]

    cur.execute('''INSERT OR IGNORE INTO Genre (name) 
        VALUES ( ? )''', ( genre, ) )
    cur.execute('SELECT id FROM Genre WHERE name = ? ', (genre, ))
    genre_id = cur.fetchone()[0]

    cur.execute('''INSERT OR IGNORE INTO Album (title, artist_id) 
        VALUES ( ?, ? )''', ( album, artist_id ) )
    cur.execute('SELECT id FROM Album WHERE title = ? ', (album, ))
    album_id = cur.fetchone()[0]

    cur.execute('''INSERT OR REPLACE INTO Track
        (title, album_id, genre_id, len, rating, count) 
        VALUES ( ?, ?, ?, ?, ?, ? )''', 
        ( name, album_id, genre_id, length, rating, count ) )

conn.commit()

Many-to-Many Relationships in SQL

这一节很简单，讲了数据存在多对多关系时如何存储，这时的存储方式就是将数据分别存储，然后将它们之间的关系建成一个关系表。

代码省略。

Databases and Visualization

调用google的Geocoding这个API（这是google map的api，可以查询地理位置信息），获取到原始数据，然后将数据进行一下处理，写入where.js，然后调用html脚本进行画图。

后一课程

最后的第五个课程完全是一些小项目，但是他都给好了代码，相当于他带你简单过一遍这些项目的代码。

很多地方现在还理解不了，先不记录了，以后可以再回来重看。