如何使用Python和Pandas处理SQLite数据库

　　SQLite是一个数据库引擎，可以简化关系数据的存储和使用。与csv格式非常相似，SQLite将数据存储在单个文件中，可以轻松地与其他人共享。大多数编程语言和环境都支持使用SQLite数据库。Python也不例外，sqlite3自版本以来，Python已包含一个用于访问SQLite数据库的库2.5。

　　在如何使用Python和Pandas处理SQLite数据库中，我们将逐步介绍如何使用它sqlite3来创建，查询和更新数据库。我们还将介绍如何使用pandas包简化使用SQLite数据库的工作。我们将使用Python 3.5，但是同样的方法应该适用于Python 2。

　　如果您想学习SQL的基础知识，则可能想先阅读我们的SQL基础知识文章。

　　在开始之前，让我们快速看一下将要使用的数据。我们将查看航空公司的航班数据，其中包含有关航空公司，机场以及机场之间路线的信息。每条路线代表航空公司在源机场和目的地机场之间飞行的重复航班。

　　所有的数据是在一个名为SQLite数据库flights.db，其中包含三个表- airports，airlines和routes。您可以在此处下载数据。

　　这是airlines表格中的两行：

　　

 

　　如上所示，每一行是不同的航空公司，每一列都是该航空公司的属性，例如name和country。每个航空公司都有一个独特的id，因此我们可以在需要时轻松查找它。

　　这是airports表格中的两行：

　　

 

　　如您所见，每一行都对应一个机场，并包含有关机场位置的信息。每个机场都有一个独特的id，因此我们可以轻松查找它。

　　这是routes表格中的两行：

　　每个路由包含airline_id，其中id该飞行路线，以及航空公司source_id，这是机场的ID，该航线从起源，和dest_id，这是飞行的目的地机场的标识。

　　现在我们知道我们正在使用哪种数据，让我们从连接到数据库并运行查询开始。

　　在Python中查询数据库行

　　为了使用来自Python的SQLite数据库，我们首先必须连接到它。我们可以使用connect函数来做到这一点，该函数返回一个Connection对象：

　　

 

　　一旦有了Connection对象，就可以创建一个Cursor对象。游标使我们能够对数据库执行SQL查询：

　　

 

　　一旦有了Cursor对象，就可以使用它以适当命名的execute方法对数据库执行查询。下面的代码将从表中获取第一5行airlines：

　　

 

　　您可能已经注意到，我们没有将上述查询的结果分配给变量。这是因为我们需要运行另一个命令来实际获取结果。我们可以使用fetchall方法来获取查询的所有结果：

　　

 

　　如您所见，结果被格式化为元组列表。每个元组对应于我们访问的数据库中的一行。用这种方式处理数据是很痛苦的。我们需要手动添加列标题，并手动解析数据。幸运的是，pandas库有一个更简单的方法，我们将在下一部分中介绍。

　　在继续之前，最好关闭已打开的Connection对象和Cursor对象。这样可以防止SQLite数据库被锁定。当SQLite数据库被锁定时，您可能无法更新数据库，并且可能会出错。我们可以这样关闭游标和连接：

　　

 

　　映射机场

　　利用我们新发现的查询知识，我们可以创建一个图表，显示世界上所有机场的位置。首先，我们查询纬度和经度：

　　

 

　　上面的查询将从中检索latitude和longitude列airports，并将它们都转换为浮点数。然后，我们调用该fetchall方法以检索它们。

　　然后，我们需要通过导入matplotlib(Python的主要绘图库)来设置绘图。与底图软件包结合使用，这使我们只能使用Python创建地图。

　　我们首先需要导入库：

　　

 

　　然后，我们设置地图，并绘制将构成地图背景的大陆和海岸线：

　　

 

　　最后，我们在地图上绘制每个机场的坐标。我们从SQLite数据库中检索了一个元组列表。每个元组中的第一个元素是机场的经度，第二个元素是纬度。我们将经度和纬度转换为它们自己的列表，然后在地图上绘制它们：

　　

 

　　我们最终得到一张显示世界上每个机场的地图：

　　

 

　　您可能已经注意到，使用数据库中的数据有些麻烦。我们需要记住每个元组中的哪个位置对应于哪个数据库列，并手动解析出每个列的单独列表。幸运的是，pandas库为我们提供了一种使用SQL查询结果的简便方法。

　　将结果读入pandas DataFrame

　　我们可以使用pandas read_sql_query函数将SQL查询的结果直接读入pandas DataFrame中。下面的代码将执行与我们刚才相同的查询，但是它将返回一个DataFrame。与我们上面的查询相比，它具有几个优点：

　　1)它不需要我们在最后创建一个Cursor对象或调用fetchall。

　　2)它会自动从表中读取标题的名称。

　　3)它创建了一个DataFrame，因此我们可以快速浏览数据。

　　

 

　　如您所见，结果得到了格式正确的DataFrame。我们可以轻松地操作列：

　　

 

　　强烈建议尽可能使用此read_sql_query功能。

　　映射路线

　　现在我们知道了如何将查询读取到熊猫数据框，我们可以创建世界上每条航线的地图。我们首先从查询数据开始。以下查询将：

　　1)获取每个路线的源机场的纬度和经度。

　　2)获取每个路线的目标机场的纬度和经度。

　　3)将所有坐标值转换为浮点数。

　　4)将结果读取到DataFrame中，并将其存储到变量中routes。

　　

 

　　然后，我们设置地图：

　　

 

　　我们遍历第一3000行并绘制它们。以下代码将：

　　1)循环浏览中的第一3000行routes。

　　2)找出路线是否太长。

　　3)如果路线不太长：

　　a)在起点和终点之间画一个圆。

　　

 

　　我们最终得到以下地图：

　　

 

　　当我们使用pandas将SQL查询的结果转换为DataFrame而不是处理来自的原始结果时，上述方法效率更高sqlite3。

　　现在我们知道了如何查询数据库行，让我们继续进行修改。

　　修改数据库行

　　我们可以使用该sqlite3包通过插入，更新或删除行来修改SQLite数据库。创建连接的过程与查询表时的创建过程相同，因此我们将跳过该部分。

　　使用Python插入行

　　要插入一行，我们需要编写一个INSERT查询。以下代码将在airlines表中添加新行。我们指定9要插入的值，为中的每一列输入一个airlines。这将在表中添加新行。

　　

 

　　如果您现在尝试查询该表，实际上您将不会看到新行。相反，您会看到创建了一个名为的文件flights.db-journal。flights.db-journal将存储新行，直到准备好将commit其存储到主数据库中为止flights.db。

　　在提交事务之前，SQLite不会写入数据库。一个事务由1个或多个查询组成，这些查询全部一次对数据库进行更改。目的是使从意外更改或错误中恢复更加容易。事务使您可以运行多个查询，然后最终使用所有查询的结果更改数据库。这样可以确保如果其中一个查询失败，则不会部分更新数据库。

　　一个很好的例子是，如果您有两个表，其中一个表包含对人民银行帐户收取的费用(charges)，而另一个表则包含银行帐户中的美元金额(balances)。假设某位银行客户罗伯托(Roberto)想寄50美元给他的妹妹路易莎(Luisa)。为了使这项工作有效，银行需要：

　　1)在charges其中创建一行，说从Roberto的帐户中取出了$ 50并发送给了Luisa。

　　2)更新balances表中Roberto的行并删除$ 50。

　　3)更新balances表中Luisa的行并添加$ 50。

　　这些将需要三个单独的SQL查询来更新所有表。如果查询失败，我们将在数据库中保留错误数据。例如，如果前两个查询有效，则第三个查询失败，Roberto会赔钱，但Luisa不会。事务意味着除非所有查询成功，否则不会更新主数据库。这样可以防止系统陷入客户损失金钱的糟糕状态。

　　默认情况下，sqlite3当您执行任何修改数据库的查询时，将打开一个事务。您可以在此处了解更多信息。我们可以提交事务，并airlines使用commit方法将新行添加到表中：

　　

 

　　现在，当我们查询时flights.db，我们将看到包含测试飞行的额外行：

　　

 

　　将参数传递给查询

　　在上一个查询中，我们对要插入数据库的值进行了硬编码。在大多数情况下，当您将数据插入数据库时​​，它不会被硬编码，而是您要传递的动态值。这些动态值可能来自下载的数据，也可能来自用户输入。

　　使用动态数据时，可能很想使用Python字符串格式插入值：

　　

 

　　您要避免这样做!使用Python字符串格式插入值会使您的程序容易受到SQL Injection攻击。幸运的是，sqlite3有一种简单的方法可以在不依赖字符串格式的情况下注入动态值：

　　

 

　　?查询中的任何值都将替换为中的值values。第一个?将被替换为第一个项目values，第二个被替换为第二个项目，依此类推。这适用于任何类型的查询。这创建了一个SQLite参数化查询，避免了SQL注入问题。

　　更新行

　　我们可以使用以下execute方法修改SQLite表中的行：

　　

 

　　然后，我们可以验证更新是否发生：

　　

 

　　删除行

　　最后，我们可以使用以下execute方法删除数据库中的行：

　　

 

　　然后，通过确保没有行与查询匹配，我们可以验证删除操作的发生：

　　

 

　　建立表格

　　我们可以通过执行SQL查询来创建表。我们可以创建一个表格来表示航线上的每个每日航班，其中包括以下几列：

　　1)id - 整数

　　2)departure —日期，航班离开机场时

　　3)arrival —日期，航班到达目的地

　　4)number —文字，航班号

　　5)route_id —整数，航班飞行的路线ID

　　

 

　　创建表后，我们可以正常地将数据插入其中：

　　

 

　　查询表时，现在将看到以下行：

　　

 

　　用熊猫创建表

　　pandas包为我们提供了一种更快的创建表的方法。我们只需要首先创建一个DataFrame，然后将其导出到SQL表即可。首先，我们将创建一个DataFrame：

　　

 

　　然后，我们将能够调用to_sql方法以转换df为数据库中的表。我们将keep_exists参数设置为，replace以删除和替换任何名为的现有表daily_flights：

　　

 

　　然后，我们可以通过查询数据库来验证一切正常：

　　

 

　　用熊猫修改表

　　实际数据科学中最困难的部分之一是，每条记录所拥有的数据经常更改。使用我们的航空公司示例，我们可能决定airplanes在airlines表中添加一个字段，以指示每个航空公司拥有多少架飞机。幸运的是，有一种方法可以更改表以在SQLite中添加列：

　　

 

　　请注意，我们不需要调用commit -alter table查询会立即执行，并且不会放入事务中。现在，我们可以查询并看到额外的列：

　　

 

　　请注意，所有列都null在SQLite中设置为(None在Python中转换为)，因为该列还没有任何值。

　　用熊猫修改表

　　也可以使用Pandas来更改表，方法是将表导出到DataFrame，对DataFrame进行修改，然后将DataFrame导出到表：

　　

 

　　上面的代码将delay_minutes在daily_flights表中添加一列。

　　下一步

　　现在，您应该掌握如何使用Python和pandas处理SQLite数据库中的数据。我们介绍了查询数据库，更新行，插入行，删除行，创建表和更改表。它涵盖了所有主要的SQL操作，以及您日常使用的几乎所有内容。

　

摘自：https://www.aaa-cg.com.cn/data/2788.html