标题

实验 - 使用 Python 和 SQLite

目标

  • 第 1 部分:创建数据库
  • 第 2 部分:输入数据和查询数据库
  • 第 3 部分:使用 Python 实现进程自动化</b >

场景/背景

在本实验中,您将学习如何连接关系型数据库、执行查询以及通过 Jupyter Notebook 检索数据。您还将学习如何将数据从 Microsoft Excel 电子表格传输到 SQLite 数据库。

为您提供的数据包含英国范围内不同地方管理机构有关 ping 操作、上传和下载速度的各种测量数据。目前,数据存储在 .csv 文件中,但您将创建一个数据库并在其中填入测量数据。

所需资源

  • 1 台接入互联网的 PC
  • Raspberry Pi 版本 2 或更高版本
  • Python 库:sqlite3 和 csvkit

SQL 刷新

第 1 部分:创建数据库

我们将使用 SQLite 连接数据库。SQLite 是一个实现独立事务性 SQL 数据库引擎的库,无需 SQL 服务器。在 Raspberry Pi 中,可以安装一个名为 sqlite3 的简单 SQLite 实用程序。SQLite 大大简化了 SQL 数据库进程。我们还要使用 cvskit,这是一套可将各种数据库转换为 .csv(逗号分隔值)格式的工具。

第 1 步:建立工作环境。

我们需要使用 sqlite3csvkit 来完成本实验。以下命令可以直接在 Raspberry Pi 终端上输入并执行。您可以通过在 Web 浏览器的“主页”选项卡中点击“新建”>“终端”来访问终端。“主页”选项卡包含用于启动此实验的实验列表。

注意:建议将终端选项卡与此实验并排放置。这样,您可以轻松地从实验切换到终端窗口。

a) 安装 SQLite。

下面的第一个命令将使用存储库服务器更新并同步 Pi 软件包列表。这是为了确保使用最新版本的软件包。在软件包列表更新为最新之后,第二个命令将安装 sqlite3,这是一个包含 sqlite3 实用程序的 Pi SQLite 软件包。

注意:由于 Raspberry Pi 必须与软件包存储库服务器通信才能下载和安装软件包,因此这些命令可能需要一段时间才能执行完毕。

通过点击代码单元格左侧的播放图标或按 选择单元格来运行单元格。运行代码单元格将执行您的 Raspberry Pi 中包含的命令。

注意:在 Jupyter Notebook 中,以感叹号 (!) 开头的行是将由 Raspberry Pi 的终端执行的命令。

# apt-get update
# apt-get -y install sqlite3

完成安装过程后,通过运行下面的代码单元格安装 cvskit
注意:安装可能需要一段时间。

# pip install csvkit

现在,您的 Raspberry Pi 已安装有 sqlite3cvskit,随时可供使用。

第 2 步:使用 SQLite。

现在 sqlite3csvkit 已安装完毕,我们可以开始使用它们来创建数据库及表。表是以行和列的矩阵形式呈现的一组信息。数据库是表的集合。请考虑下表:

</table>

上表包含有关八个人的信息,以行和列方式呈现。

注意:为确保文件位置的一致性,请考虑使用 /home/pi/notebooks/myfiles 目录。要改用该目录,请使用 cd 命令,如下所示:

# cd /home/pi/notebooks/myfiles

在开始使用数据库之前,请使用 sqlite3 创建一个名为 phonebook.db 的新 SQLite 数据库。要创建新数据库,请在终端窗口中执行以下命令:

# sqlite3 phonebook.db
SQLite version 3.8.7.1 2016-02-06 13:59:56 Enter ".help" for usage hints. sqlite>

sqlite3 实用程序显示一些版本信息及其提示符 sqlite>。此提示符表示数据库 phonebook.db 已成功创建,sqlite3 已准备对其执行 SQL 命令。

在数据库中创建一个表。在 sqlite3 提示符后,发出下面的命令以创建一个名为 coworkers 的表。coworkers 表有 3 个列(或字段):workid、name 和 title。请注意,该命令还指定每个字段中可以填充的数据的类型和数量。workid 字段支持整数值。name 字段支持 20 个 varchar 字符。title 字段支持 20 个 varchar 字符。number 字段也是一个整数。

sqlite> create table coworkers(workid integer,name varchar(20),title varchar(20),number integer);

同一个数据库可以包含多个表。从 sqlite3 提示符中,使用下面的第一个命令在 phonebook.db 数据库中创建第二个表。该命令会创建一个名为“departments”的表。与“coworkers”表类似,“departments”表也包含三个字段:deptid idnamenumber

第二个命令离开 sqlite3 提示符并退回终端。

sqlite> create table department(deptid integer,name varchar(20),number integer);
sqlite> .quit

注意上面单元格 quit 命令之前的点“.”。通常情况下,sqlite3 会将用户输入的行传递给 SQLite 库以供执行。但是,以点开头的输入行会被 sqlite3 实用程序本身截取和解释。这些命令也称为点命令,通常用于更改查询的输出格式,或执行某些预先打包的查询语句。

SQLite 数据库之所以有用,是因为数据库及其所有表都包含在一个 .db 文件中并存储在磁盘上。从 Pi 的终端,使用 ls 命令列出包含 phonebook.db 数据库及其表的文件。使用 -l 切换为显示长列表格式,以显示文件的权限、所有权、大小以及日期和时间。

# ls -l phonebook.db
-rw-r--r-- 1 root root 3072 Feb 7 01:29 phonebook.db

第 2 部分:输入数据和查询数据库

第 1 步:将数据输入数据库。

再次输入 sqlite3 提示符,同时选择 phonebook.db 数据库。

# sqlite3 phonebook.db

SQLite version 3.8.7.1 2016-02-06 13:59:56 Enter ".help" for usage hints. sqlite>

使用下面的命令将一个条目添加到“coworkers”表中:

sqlite> insert into coworkers values(101,'Han Solo','Bounty Hunter',5556667578);

上面的命令会在 coworkers 表中插入一个包含四个值的行,如下所示:

  • 101 位于 workid
  • Han Solo 位于 name
  • Bounty Hunter 位于 title
  • 5556667578 位于 number
  • 虽然上面的命令非常简单,但并不能很好地扩展。另一个选项是将 .csv 文件直接导入数据库。表结构是基于 .csv 文件的结构创建的。

    以下步骤将创建要导入数据库的 .csv 文件。在生产环境中,.csv 文件可能已经存在。

    a) 选择下面显示的文本并将其复制到剪贴板。在选择文本后,右键点击所选文本,然后选择“复制”将文本复制到剪贴板。

    workid,name,title,number
    101,Han Solo,Bounty Hunter,5556667578
    102,Leia Skywalker,Princess,5556542398
    103,Luke Skywalker,Jedi,5558963267
    104,Obi-Wan Kenobi,Jedi,5558963276
    105,Anakin Skywalker,Sith Lord,5553477621
    106,Jabba The Hutt,Gangster,5556613456
    107,Greedo,Debt Collector,5552360918
    108,R2D2,Astro Droid,5555210125
    109,C3PO,Protocol Droid,5556633345

    b) 从 Pi 的终端启动 nano。Nano 是一个命令行文本编辑器,非常简单易用。使用以下命令启动 nano 并创建一个名为“coworkers.csv”的新文本文件

    # nano coworkers.csv

    c) 将复制的文本粘贴到 nano 窗口中。确保删除文件中的任何空行。

    d) 文本粘贴到“coworkers.csv”文件且已清除空行之后,按 CONTROL + X 退出 nano。

    e) 当 Nano 询问您是否要保存文件时,按“Y”(是)。

    f) 要检查您新创建的 CSV 文件的内容,请使用 cat 命令,如下所示:

    # cat coworkers.csv

    您也可以通过此 Jupyter Notebook 自动创建相同的 CSV 文件。要使用 Jupyter Notebook 自动创建 CSV 文件,请运行以下代码单元格:

    In [16]:
    # Code Cell 1
    !test -e coworkers.csv && rm coworkers.csv
    !echo "workid,name,title,number" >> coworkers.csv
    !echo "101,Han Solo,Bounty Hunter,5556667578" >> coworkers.csv
    !echo "102,Leia Skywalker,Princess,5556542398" >> coworkers.csv
    !echo "103,Luke Skywalker,Jedi,5558963267" >> coworkers.csv
    !echo "104,Obi-Wan Kenobi,Jedi,5558963276" >> coworkers.csv
    !echo "105,Anakin Skywalker,Sith Lord,5553477621" >> coworkers.csv
    !echo "106,Jabba The Hutt,Gangster,5556613456" >> coworkers.csv
    !echo "107,Greedo,Debt Collector,5552360918" >> coworkers.csv
    !echo "108,R2D2,Astro Droid,5555210125" >> coworkers.csv
    !echo "109,C3PO,Protocol Droid,5556633345" >> coworkers.csv
    

    再次使用 cat 命令验证文件创建及其内容。

    # cat coworkers.csv

    第 2 步:将数据导入数据库。

    首先,从 phonebook 数据库中删除“coworkers”表。因为表结构将由 .csv 文件的结构定义,而且我们的表中只有一个条目,所以较容易删除整个表。

    从终端中,输入 sqlite3 提示符,同时选择 phonebook.db 数据库:
    # sqlite3 phonebook.db SQLite version 3.8.7.1 2016-02-06 13:59:56 Enter ".help" for usage hints. sqlite>

    使用 .tables 命令列出数据库中当前存在的所有表:
    sqlite>.tables
    coworkers department

    数据库中当前存在两个表,coworkersdepartment

    使用 drop SQL 命令删除“coworkers”表:
    sqlite> drop table coworkers;

    再次发出 .tables 命令,以列出数据库中当前存在的所有表:
    sqlite>.tables
    department

    请注意,“coworkers”表已从数据库中删除。
    离开 sqlite3 提示符并返回终端:
    sqlite>.quit

    从终端中,使用 csvsql 实用程序在 phonebook 数据库中创建一个新表,并检索“coworkers.csv”文件中包含的数据。csvsqlcsvkit 软件包的一部分,在本实验之前已经安装。

    # csvsql --db sqlite:////home/pi/notebooks/myfiles/phonebook.db --insert /home/pi/notebooks/myfiles/coworkers.csv

    输入 sqlite3 提示符,同时选择 phonebook 数据库并发出 .tables 命令以列出数据库中的所有表:
    # sqlite3 phonebook.db SQLite version 3.8.7.1 2016-02-06 13:59:56 Enter ".help" for usage hints. sqlite>
    sqlite>.tables
    coworkers department

    coworkers 表会再次出现在数据库中。

    第 3 步:查询数据库。

    从终端中,输入 sqlite3 提示符,同时选择 phonebook.db 数据库:
    # sqlite3 phonebook.db SQLite version 3.8.7.1 2016-02-06 13:59:56 Enter ".help" for usage hints. sqlite>

    列出 coworkers 表的所有内容:

    sqlite> select * from coworkers; 101|Han Solo|Bounty Hunter|5556667578 102|Leia Skywalker|Princess|5556542398 103|Luke Skywalker|Jedi|5558963267 104|Obi-Wan Kenobi|Jedi|5558963276 105|Anakin Skywalker|Sith Lord|5553477621 106|Jabba The Hutt|Gangster|5556613456 107|Greedo|Debt Collector|5552360918 108|R2D2|Astro Droid|5555210125 109|C3PO|Protocol Droid|5556633345 sqlite>

    在上面所示的 select SQL 命令中,星号 (*) 会选择 coworkers 表中的所有字段。要显示包含 Jedi 职务的条目的名称,请使用以下命令:

    sqlite> select name from coworkers where title='Jedi'; Luke Skywalker Obi-Wan Kenobi sqlite>

    您能否建立一个查询,以显示“coworkers”表中 bounty hunters 的姓名?

    *在此处输入您的答案。*

    第 3 部分:使用 Python 实现进程自动化

    使用 Python 连接到数据库并运行一些查询来查看这些数据的特征。

    第 1 步:导入模块。

    对于这个步骤,我们将需要以下模块:

    sqlite3
    pandas
    pyplot

    以下是导入所需 Python 模块的代码行:

    # this is the library to talk to the database
    import sqlite3
    import pandas as pd
    # this is a library for data visualization
    from matplotlib import pyplot as plt
    # this is to say that we want our plots to be part of this page, and not opened in a new window
    %matplotlib inline

    第 2 步:连接数据库。

    在通过 Python 将查询发送到数据库之前,必须建立数据库连接。Python sqlite3 对象的 connect() 方法可帮助连接数据库。

    注意:尽管本实验中使用的 Python 对象和命令行实用程序目前具有相同的名称 sqlite3,但它们的使用范围有所不同。后者必须从 Python 调用,而前者需要 Linux 终端才能执行。

    要使用 sqlite3 Python 对象打开连接,请调用对象 sqlite3connect 方法。此方法会获取一个包含需要连接的数据库名称的字符串。

    在本例中,该命令为:

    sqlite3.connect('/home/pi/notebooks/myfiles/phonebook.db')

    该函数会返回连接,它将存储在名为 conn 的变量中。将连接的详情存储在变量中可以在需要时轻松引用该连接。包含变量分配和建立连接的代码行如下:

    conn = sqlite3.connect('/home/pi/notebooks/myfiles/phonebook.db')

    现在已经建立了与数据库的连接,其详情已通过 conn 变量存储并可供访问,我们需要一种方法来浏览数据库。在这种情况下,我们将使用另一种名为 cursor()sqlite3 对象方法。作为数据库中的可移动指针,光标是一种控制结构,使我们能够浏览表和记录。光标之所以重要,是因为它能够指定要读取或写入的单元格(表、列和行)。要创建光标,请在建立的数据库连接中调用方法 cursor()。由于我们将连接的详情使用 conn 变量存储到 phonebook 数据库中,因此应使用该变量创建光标,如下所示:

    conn.cursor()

    使用另一个变量来存储新创建的光标对象的详情。下面的代码行会将光标的详情存储在 cur 变量中:

    cur = conn.cursor()

    以下是迄今为止的完整 Python 程序:

    In [18]:
    # Code Cell 2
    # this is the library to talk to the database
    import sqlite3
    import pandas as pd
    # this is a library for data visualisation
    from matplotlib import pyplot as plt
    # this is to say that we want our plots to be part of this page, and not opened in a new window
    %matplotlib inline
    
    # establishing the connection to the database and storing the details in conn...
    conn = sqlite3.connect('/home/pi/notebooks/myfiles/phonebook.db')
    
    # creating a cursor and storing its details in cur...
    cur = conn.cursor()
    
    第 3 步:执行查询。

    要对数据库执行查询,请使用 execute() 方法。如同 cursor 对象方法的写入过程,这种方法将像输入一样获取包含我们感兴趣的查询的字符串。为了使程序更干净,我们将查询存储在名为 query 的变量中。执行方法的调用如下所示:

    query = "SELECT name FROM coworkers;"
    cur.execute(query)

    运行下面的单元格进行查询并将结果存储在 cur 中。

    In [21]:
    # Code Cell 3
    query = "SELECT * FROM coworkers;"
    cur.execute(query)
    
    Out[21]:
    <sqlite3.Cursor at 0x6d42eb20>

    查询的结果存储在 cur 对象中。为了实现结果的可视化,我们必须迭代 cur 对象,打印出每一行。

    运行下面的单元格,迭代 cur 并打印其内容:

    In [22]:
    # Code Cell 4
    for row in cur:
        print row
    
    (101, u'Han Solo', u'Bounty Hunter', 5556667578L)
    (102, u'Leia Skywalker', u'Princess', 5556542398L)
    (103, u'Luke Skywalker', u'Jedi', 5558963267L)
    (104, u'Obi-Wan Kenobi', u'Jedi', 5558963276L)
    (105, u'Anakin Skywalker', u'Sith Lord', 5553477621L)
    (106, u'Jabba The Hutt', u'Gangster', 5556613456L)
    (107, u'Greedo', u'Debt Collector', 5552360918L)
    (108, u'R2D2', u'Astro Droid', 5555210125L)
    (109, u'C3PO', u'Protocol Droid', 5556633345L)
    
    练习

    1) 提供查询所有 princesses 姓名的代码。

    *在此处输入您的答案。*

    2) 提供查询所有 princesses 和 debt collectors 姓名的代码。

    *在此处输入您的答案。*

    3) 提供查询所有 Jedi 姓名和数量的代码。

    *在此处输入您的答案。*

    4) 提供查询 droid 姓名的代码。

    *在此处输入您的答案。*

    © 2017 思科和/或其附属公司。版权所有。本文档所含内容为思科公开发布的信息。

    IDNameGenderRaceForce Sensitive
    001HanMaleHumanNo
    002LeiaFemaleHumanYes
    003LukeMaleHumanYes
    004Obi-WanMaleHumanYes
    005AnakinMaleHumanYes
    006JabbaMaleHuttNo
    007GreedoMaleRodianNo
    008R2D2N/ADroidNo
    009C3PON/ADroidNo