如何将 SQL 查询结果转换为 PANDAS 数据结构?

2021-11-20 00:00:00 python pandas data-structures mysql

对此问题的任何帮助将不胜感激.

Any help on this problem will be greatly appreciated.

所以基本上我想对我的 SQL 数据库运行查询并将返回的数据存储为 Pandas 数据结构.

So basically I want to run a query to my SQL database and store the returned data as Pandas data structure.

我附上了查询代码.

我正在阅读有关 Pandas 的文档，但我无法确定查询的返回类型.

I am reading the documentation on Pandas, but I have problem to identify the return type of my query.

我尝试打印查询结果，但没有提供任何有用的信息.

I tried to print the query result, but it doesn't give any useful information.

谢谢！！！！

from sqlalchemy import create_engine

engine2 = create_engine('mysql://THE DATABASE I AM ACCESSING')
connection2 = engine2.connect()
dataid = 1022
resoverall = connection2.execute("
    SELECT 
       sum(BLABLA) AS BLA,
       sum(BLABLABLA2) AS BLABLABLA2,
       sum(SOME_INT) AS SOME_INT,
       sum(SOME_INT2) AS SOME_INT2,
       100*sum(SOME_INT2)/sum(SOME_INT) AS ctr,
       sum(SOME_INT2)/sum(SOME_INT) AS cpc
    FROM daily_report_cooked
    WHERE campaign_id = '%s'",
    %dataid
)

所以我有点想了解我的变量resoverall"的格式/数据类型是什么?以及如何将其与 PANDAS 数据结构结合起来.

So I sort of want to understand what's the format/datatype of my variable "resoverall" and how to put it with PANDAS data structure.

推荐答案

这是完成工作的最短代码:

Here's the shortest code that will do the job:

from pandas import DataFrame
df = DataFrame(resoverall.fetchall())
df.columns = resoverall.keys()

您可以像 Paul 的回答那样更巧妙地解析类型.

You can go fancier and parse the types as in Paul's answer.

相关文章