python怎么将相互关联的数据构建为社区_SQL


根据维基百科的定义，关系数据库通过在表中存储行和列来建模数据。关系数据库的强大之处在于在于它能够有效地从表中检索数据，特别是在有多个表且表之间的关系错综复杂的情况之下。

知识点1 相关术语

python怎么将相互关联的数据构建为社区_数据_02


python怎么将相互关联的数据构建为社区_数据库_03

知识点2 SQL（Structured Query Language）

结构化查询语言，这种语言是我们向数据库发出指令的语言，有以下的几个功能：

  • 插入数据
  • 检索数据
  • 更新数据
  • 删除数据

拓扑结构：

python怎么将相互关联的数据构建为社区_数据_04


原先与数据库交互是直接交互的，现在可以通过直接和DB APP交互，所有复杂的内容都被hidden在了DB APP当中，所以简化了我们APP的工作内容，只要做一个Abstruction既可。

Python与SQL联合使用是魅力无穷的，由于SQL是很强大的与数据库交互的语言，但是它使用的前提是数据库的数据是较完好的。而Python对于不完好的数据有强大的处理能力。

因此，通常用Python来处理数据，利用SQL来存储和检索数据。

知识点3 数据库的使用

在进行本知识点的介绍时，分为大工程和小工程两个角度来进行介绍：

在大工程中主要有两个角色，分别是应用开发者（Application Developer）和数据库管理员（Database Administrator）

python怎么将相互关联的数据构建为社区_SQL_05


上图反映了Developer与DBA之间的关系，Developer通过编写程序与数据库进行交互，并提供给终端用户使用，但是Developer不被允许直接与数据库交互，因为有黑掉数据库的风险；DBA是数据库管理者，可以通过command直接与数据库进行交互。

python怎么将相互关联的数据构建为社区_数据_06


在我们的学习过程中，我们既作为Developer的角色，也作为DBA的角色，关系图如下图所示：

python怎么将相互关联的数据构建为社区_数据_07


关于Database model:

python怎么将相互关联的数据构建为社区_SQL_08


Database model/schema可以理解为一个constract（合同 ），是一个规定我们怎样去存储和检索数据库的一个合同。

知识点4 几种常见的数据库系统

  • Oracle，通常用于企业级的环境
  • MySQL，通常用于在线的网络网站；
  • SQLServer，微软；
  • HSQL\SQLite\Postgress，Postgress更像Oracle而不是MySQL，是Oracle的有力竞争者，MySQL虽然功能不及Oracle但是有极快的处理速度，所以适用于网络；
  • SQLite是一种嵌入式的，不像别的一样是一个单独的软件，很轻巧。

知识点5 SQLite工程实战

SQLite用一种特殊的形式读写，是一种经过高度优化的适用于数据的快速检索与存储的形式。
假设我们在SQLite创建了一个sql1，作为数据库管理员，需要做的是对于sql1的读和写，而不是直接对于数据库的操作。

SQLite的界面

python怎么将相互关联的数据构建为社区_数据库_09


SQLite常用指令及解读

python怎么将相互关联的数据构建为社区_数据库_10


这些指令集包括了数据库“CRUD”的功能（create\read\update\delete）：

  • 新建一个表格
    SQL代码如下：
CREATE TABLE Users( 
  name VARCHAR(128), 
  email VARCHAR(128)
)

SQLite是一个交互式的平台，在这段代码中，Users是表名，name和email分别是两列（Column），VARCHAR（128）规定了数据格式，限制了最多可以存多少个字，所以是有较为严格的要求的。有128、1024等等，这是由内存兼顾要达到最快的运行速度所决定的。
在新建表格中如果出现报错Users的表格已经存在，改为：

CREATE TABLE if not exists Users(
  • 使用INSERT插入数据：
INSERT INTO Users (name, email) VALUES ('Kristin', 'kf@umich.edu')
  • 使用DELETE删除数据的操作
DELETE FROM Users WHERE email='ted@umich.edu'

需要注意的是，如果没有使用WHERE，则所有的数据都会被删除。

  • 使用UPDATE更新数据的操作
UPDATE Users SET name='Charles' WHERE email='csev@umich.edu'
  • 使用SELECT检索数据-选择
SELECT * FROM Users
SELECT * FROM Users WHERE email='csev@umich.edu'

在这两段代码中，*代表的是全部的意思，第一句指的是选中表Users中的全部，第二局指的是选中Users中符合条件的全部内容。

  • 查询表Users的行数
SELECT COUNT(*) FROM Users
  • 使用ORDER BY排序
SELECT * FROM Users ORDER BY email
SELECT * FROM Users ORDER BY name DESC

DESC是倒序的含义

实战：Working Example

在数据库当中数有多少封邮件：

import sqlite3

conn = sqlite3.connect('emaildb.sqlite')
cur = conn.cursor()
'''
一般来说，使用数据库都需要用到以上的两步；
第一步的内容是进行连接，检查连接到数据库的通道；
第二步的工作类似于之前处理文件的handle。
'''
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,)) #这里的问号是一个占位符，占后面括号内的内容,这是一种防止SQL注入的基本方法，后面email中是一个元组。如果前面有两个问号的话，这个括号中就包含有两个元素了。
    row = cur.fetchone() #返回最上面的一条结果，如果没有的话，返回的是None
    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()

对于代码的详细解读以注释的形式来体现。

  • SQL注入：SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。
  • cur.execute()这句语句并不是实际检索数据库的语句，这里可以理解为打开的是一组记录集或者说是记录。