旗下产业: A产业/ A实习/ A计划
全国统一咨询热线:010-5367 2995
首页 > 热门文章 > 大数据分析 > 如何使用Python和Pandas处理SQLite数据库

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

时间:2020-10-09来源:www.aaa-cg.com.cn点击量:作者:Sissi
时间:2020-10-09点击量:作者:Sissi




  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表格中的两行:

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

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

  这是airports表格中的两行:

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

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

  这是routes表格中的两行:
 

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

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

  在Python中查询数据库行
 

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

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

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

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

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

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

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

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

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

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

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

  映射机场
 

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

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

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

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

  我们首先需要导入库:

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

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

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

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

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

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

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

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

  将结果读入pandas DataFrame
 

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

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

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

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

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

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

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

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

  映射路线
 

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

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

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

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

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

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

  然后,我们设置地图:

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

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

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

  2)找出路线是否太长。

  3)如果路线不太长:

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

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

  我们最终得到以下地图:

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

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

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

  修改数据库行
 

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

  使用Python插入行
 

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

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

  如果您现在尝试查询该表,实际上您将不会看到新行。相反,您会看到创建了一个名为的文件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方法将新行添加到表中:

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

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

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

  将参数传递给查询
 

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

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

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

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

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

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

  更新行
 

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

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

  然后,我们可以验证更新是否发生:

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

  删除行
 

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

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

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

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

  建立表格
 

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

  1)id - 整数

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

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

  4)number —文字,航班号

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

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

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

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

  查询表时,现在将看到以下行:

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

  用熊猫创建表
 

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

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

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

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

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

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

  用熊猫修改表
 

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

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

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

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

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

  用熊猫修改表
 

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

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

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

  下一步
 

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

  如果您想了解有关如何使用Python和SQL的更多信息,可以在AAA教育上查看我们的交互式SQL课程。


 

预约申请免费试听课

填写下面表单即可预约申请免费试听!怕钱不够?可先就业挣钱后再付学费! 怕学不会?助教全程陪读,随时解惑!担心就业?一地学习,可推荐就业!

©2007-2021/北京漫动者教育科技有限公司版权所有
备案号:京ICP备12034770号

©2007-2022/ www.aaa-cg.com.cn 北京漫动者数字科技有限公司 备案号: 京ICP备12034770号 监督电话:010-53672995 邮箱:bjaaa@aaaedu.cc

京公网安备 11010802035704号

网站地图