Fix Python – How to convert SQL Query result to PANDAS Data Structure?


Asked By – user1613017

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.


from sqlalchemy import create_engine

engine2 = create_engine('mysql://THE DATABASE I AM ACCESSING')
connection2 = engine2.connect()
dataid = 1022
resoverall = connection2.execute("
       sum(BLABLA) AS BLA,
       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'",

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.

Now we will see solution for issue: How to convert SQL Query result to 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.

This question is answered By – Daniel

This answer is collected from stackoverflow and reviewed by FixPython community admins, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0