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

How to convert SQL Query result to PANDAS Data Structure?(如何将 SQL 查询结果转换为 PANDAS 数据结构?)

问题描述

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

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.

这篇关于如何将 SQL 查询结果转换为 PANDAS 数据结构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！