如何将SQL查询结果转换为PANDAS数据结构？ [英] How to convert SQL Query result to PANDAS Data Structure?

问题描述

对这个问题的任何帮助将不胜感激。所以基本上我想运行查询到我的SQL数据库，并将返回的数据存储为熊猫数据结构。我已附上查询代码。我正在阅读有关熊猫的文档，但是我有问题来确定我的查询的返回类型。我试图打印查询结果，但没有给出任何有用的信息。
谢谢!!!

 从sqlalchemy导入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数据结构。

解决方案

以下是执行此工作的最短代码：

 从pandas导入DataFrame 
 df = DataFrame（resoverall.fetchall（））
 df.columns = resoverall.keys（）
  

您可以像保罗的答案一样去爱好和解析类型。 p>

Any help on this problem will be greatly appreciated. So basically I want to run a query to my SQL database and store the returned data as Pandas data structure. I have attached code for query. 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. Thanks!!!!

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)

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()

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

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