小窍门：为MySQL和Pandas准备的SQLAlchemy

2019 年 3 月 24 日 Python程序员

多年来我一直使用mysql-python库来连接mysql数据库。这些年来它对我很有用。但有时你需要有更快的速度或使用mysql-python时有更好的连接管理。这就是SQLAlchemy的用武之地。

在深入研究之前，如果你正在做那些不怎么依赖于速度的事情（例如：你并不关心花了1秒钟来连接数据库、操作数据、关闭数据库），那么你可以忽略掉这个窍门。即便如此，如果你有多个连接，连接的时间则会相加起来。

例如，我最近遇到一个问题，连接数据库、运行分析并出结果花费了4.5秒以上的时间。如果这是为你做的事情，这个结果并不糟糕。但是如果这是一个生产系统并且速度是必须的要求，4.5秒就显得太长了（真的太长了）。

当使用python的timer()函数做一些分析时，我发现4.5秒的时间中有50%以上用于建立数据库连接，因此我使用了SQLAchemy（https://www.sqlalchemy.org/）工具包并开始工作。
对于那些不了解SQLAlchemy的人，SQLAlchemy是一个“pythonSQL工具包和对象关系映射器”（ORM)，目的是当使用SQL数据库工作时会使事情变得简单起来。对我来说，ORM倾向于将事情变得更复杂，所以我倾向于使用普通的SQL查询，但是SQLAlchemy的SQL toolkit方面很有意义，并且在连接到SQL数据库时可以节省一些时间。

在我们深入SQLAlchemy之前，让我们花一些时间查看如何使用mysql-python连接器连接到SQL数据库（至少看我是如何操作的）。

首先，让我们设置import语句。为此，我们将引入库MySQLdb，pandas和pandas.io.sql，以此将SQL数据直接读入一个pandas dataframe中。

第二步，创建一个数据库连接、创建查询、运行此查询并最终关闭数据库。

从数据库mysql中使用mysql-python读取数据至pandas dataframe中，这是一个相当标准的方法。正如上面所讨论的，在我运行了4.5秒以上的时间之前，我就一直在使用这种方法。注意：在4.5秒及以上的时间中，包含了多项数据库调用和分析。在我试图改进性能的代码中，像上面这样的基本数据库调用大约运行0.45秒，而建立数据库连接是大部分时间。

为了改善性能－－特别是如果要对多个表进行多个调用，可以使用SQLAlchemy和pandas。如果你还未安装它，你将需要pip insstall sqlalchemy。现在，让我们设置导入：

现在你可以为SQLAlchemy设置与数据库连接的字符串，你可以像下面这样把所有的内容放在一起：

这其中，USER是你的用户名，PW是你的密码，DBHOST是数据库主机，DB是你想要连接的数据库。

要设置持久连接，请执行以下操作：

现在，你已经连接到数据库，可以开始了。无需担心游标或数据库连接的开启与关闭。SQLAlchemy为你保留了连接管理方面的内容。

现在你需要做的是关注你的SQL查询并将结果加载至pandas dataframe中。

以上就是所有的细节。并且......它更快。在上面的例子中，我的数据库设置/连接/查询/关闭的时间从0.45秒降低到0.15秒。运行时间取决于你查询的数据和数据库是否恰当，但是在本例中，除了mysql-python被SQLAlchemy替换并在pandas中使用了新的read_sql_query函数，其他所有内容都是相同的。

使用这种方法，获取数据、分析数据并返回数据所需的4.5秒以上的时间减少至1.5秒左右。并且仅仅转换了连接与管理的方法就获得了令人印象深刻的好处。