关系数据库基础

关系数据库是表的集合。每个表都是一个二维数据结构，组织成列（也称为字段）和行。列具有名称和数据类型，行表示单个数据条目。

大多数数据库系统支持的数据类型包括用于存储整数的INT数据类型、用于存储浮点实数的REAL数据类型和用于存储文本的STRING数据类型。

关系数据库中对数据库表的操作是通过使用一种称为SQL的特殊目的语言来执行的。在这些说明的后面，您将找到SQL命令的基本介绍。

开始使用sqlite

SQLite是一个流行的数据库系统，在Python中有很好的内置支持。与其他需要单独软件安装的数据库系统不同，SQLite系统是一个轻量级数据库系统，可以通过sqlite3 python包立即访问。

虽然完全通过Python代码可以完成我们想用SQLite做的赢博体育事情，但您可能会发现下载并安装一个单独的图形化GUI工具来使用SQLite数据库是有帮助的。一个不错的选择是SQLite Studio。

下面是一个如何在SQLite Studio中创建新数据库的示例。首先从Database菜单中选择Add a Database命令。在出现的对话框中，单击File部分中的绿色加号按钮以创建新的数据库文件。

这个数据库的新条目将出现在主窗口左侧的数据库视图中。双击该条目以展开Example数据库的视图。

右键单击数据库的tables部分，并选择创建新表的选项。

在右侧显示的表视图中键入新数据库的名称。要向表中添加列，请在表名称下方的列列表区域中双击。

如上所示，向示例表添加三列。要将更改提交到数据库，请单击表名称上方的绿色复选标记按钮。

您可以通过打开SQL编辑器窗口发出SQL命令来操作赢博体育程序中的数据库。从工具菜单中选择Open SQL editor打开编辑器窗口。

由于我们的样例数据库表开始时没有数据，因此我们将发出的第一个SQL命令是将新行插入样例表的命令。将以下代码粘贴到编辑器窗口中：

INSERT INTO sample(id,name,height) VALUES(1,'Joe Gregg',1.7)；

SQL插入语句命名要使用的数据库表，然后是要设置值的列列表。

要执行代码，请单击蓝色运行三角形。

编辑insert语句中的值，向样例表中添加更多的行。确保为每个新行提供一个不同的id值。

可以使用SQL选择语句查看样例表的当前内容。

SELECT id, name, height FROM sample；

您可以通过修改select语句中的列列表来显示列的子集：

SELECT name, height FROM sample；

可以通过添加WHERE子句将结果筛选到行子集。

SELECT id, name, height FROM sample where height > 1.0

你可以使用SQL update语句更新表中的一个条目：

更新样本集高度= 6.1,id = 2；

在Python中使用SQL

通过使用sqlite3包，您可以从Python程序与SQLite数据库进行交互。

进口sqlite3

使用数据库的第一步是打开数据库。这包括获取到数据库的连接和获取游标对象。

conn = sqlite3.connect('example.db') c = conn.cursor() print（“打开数据库成功”）

上面的示例代码假设数据库文件example.db与Python程序位于同一文件夹中。如果不是这种情况，则必须相应地更新db文件的路径。

要向数据库发出SQL命令，我们构造一个包含该命令的字符串，并将其传递给游标的execute（）方法。

sql = "INSERT INTO sample(id,name,height) VALUES(4,‘多比’，1.2)" c.execute（sql）

在对数据库的内容进行任何更改之后，您应该运行连接的commit（）方法将更改提交到数据库。

conn.commit ()

要查看select语句返回的结果，您需要执行该语句，然后使用游标的fetchall（）方法来获取结果。这将返回一个可迭代的元组列表，其中包含由select返回的行。

sql = "SELECT name, height FROM sample WHERE height > 1.0" .execute(sql) for name, height in c.fetchall(): print（name + ':' + str(height)）

当您完成对数据库的处理后，您应该关闭数据库连接。

conn.close ()

GDP图示例

对于今天的主要例子，我将在数据库的帮助下解决作业中的GDP绘图问题。

第一步是创建一个新的数据库文件GDP.db。这个数据库将包含一个表，gdp。这是那个表的结构。

列	类型	描述
代码	字符串	三个字母的国家代码
p95	真正的	一九九五年人口
p05	真正的	二零零五年人口
g95	真正的	1995年国内生产总值
g05	真正的	二零零五年本地生产总值
gpc95	真正的	1995年的人均GDP
gpc05	真正的	2005年人均国内生产总值
pg	真正的	人口增长
gg	真正的	人均国内生产总值增长

我们创建的第一个Python文件是一个程序，用于从gdp .csv文件中读取数据并将其存储在gdp表中。

#读取CSV文件并隔离我们需要的列#也做一个dropna（）删除任何丢失数据的行df = pd.read_csv(‘GDP.csv‘) df = df[[’国家代码’，‘1995’，‘2005’]].dropna() rows = df。conn = sqlite3.connect('GDP.db') c = conn.cursor() print("Opened database successfully") #执行insert语句在数据库中为每一个数据框中的#行创建一行。为行中的行：c.execute("INSERT INTO gdp（code,g95,g05) VALUES(?,?,?)“，行）#提交数据库更改conn.commit() print（”Records created successfully"） conn.close（）

下面是在数据库中插入新行的循环：

对于行中的行：c.execute("INSERT INTO gdp（code,g95,g05) VALUES(?,?,?)"，行）

这里的rows是一个numpy数组，包含数据帧中的赢博体育数据。该数组中的每一行都是一个numpy数组，包含三个值：国家代码、1995年的gdp和2005年的gdp。

为了将这些数据插入到数据库中，我们使用SQL插入语句。注意，插入语句有三个占位符。字符。execute（）方法将用取自第二个参数的值填充这些占位符。由于numpy数组中的每一行都按照它们出现的顺序包含我们需要的三个值，因此我们可以直接使用该行作为execute（）的第二个参数。或者，我们可以构造一个包含所需值的元组，并将其作为第二个参数传递。

我们编写的第二个程序从JSON文件中提取人口数据，并将该数据插入数据库。

将数据加载到一个列表中。Filename = ‘population_data. ’#连接数据库conn = sqlite3.connect(' gdp .db') c = conn.cursor() print(" open database successfully") #加载pop_data中的人口数据：如果entry['Year'] == '1995': c.execute（"UPDATE gdp SET p95 = ? "）WHERE code = ?", (entry['Value'],entry['Country code ']))) elif entry['Year'] == '2005': c.execute("UPDATE gdp SET p05 = ？WHERE code = ?"，(条目(“价值”),条目['国家代码']))conn.commit() #计算人均gdp和增长率c.execute(“更新gdp gpc95 = g95 / p95 g95不是零和p95不是零”)c.execute(“更新gdp gpc05 = g05 / p05 g05不是零和p05不是零”)c.execute(“更新设置pg = (p05-p95) / gdp p95 p95不是零和p05不是零”)c.execute(“更新设置gg = (gpc05-gpc95) / gdp gpc95 gpc95不是零和gpc05不是零”)conn.commit()打印(“更新记录成功”)conn.close ()

将人口数据加载到数据库中的循环只需要对JSON文件中的数据进行一次遍历。在这一关中，我们寻找1995年或2005年的条目。当我们找到这样的条目时，我们执行SQL updata语句来更新数据库中相应的行。插入语句中的where子句允许我们定位新数据应该放置的特定行。

在将人口数据插入到gdp表中之后，我们准备计算其余列的值。我们可以通过一系列SQL更新语句来实现这一点。update语句的强大之处在于它们可以同时更新许多行。我们唯一需要注意的是在更新中设置适当的where子句，以便更新仅针对有意义设置值的行。丢失的数据将在数据库中显示为NULL值，因此我们将更新限制为仅更新那些不具有NULL值的行。

一旦我们将赢博体育需要的数据插入到数据库中，我们就可以构建第三个程序，从数据库中提取数据来绘制图表。

导入sqlite3导入matplotlib。pyplot as plt #建立数据库连接conn = sqlite3.connect(' gdp .db') c = conn.cursor() print("Opened database successfully") #执行查询sql = "SELECT pg, gg FROM gdp WHERE pg IS NOT NULL and gg IS NOT NULL" c.execute(sql) #处理结果p = [] g = [] for pg, gg in c.fetchall()：如果gg < 20: p.append(pg) g.append(gg) #构造plot plot(p,g,'bs') plttitle（‘GDP增长vs人口增长’）plt.savefig('plot.png') print（'Saved plot'）

这是由此产生的情节：

编程任务

上面的按钮链接到一个包含一对CSV文件的存档。文件Chicago_Covid.csv包含按邮政编码组织的芝加哥Covid - 19病例和死亡数据。第二个文件Income_and_Population.csv包含同一地区的收入中位数和人口数据，也是按邮政编码组织的。

构建一个数据库来存储邮政编码、病例数、收入中位数和人口。然后编写三个程序：

1. 编写程序读取Chicago_Covid.csv文件，并将该文件中的病例数据插入数据库。
2. 编写第二个程序来读取Income_and_Population.csv文件，并用收入和人口数据中位数更新数据库中的数据。
3. 编写第三个程序，从数据库中读取数据，以构建数据集中邮政编码的收入中位数与感染率的关系图。感染率是指邮政编码地区每1000人中感染Covid - 19的人数。x轴是收入，y轴是感染率。