How Do I Efficiently Convert SQL Query Results into a Pandas DataFrame?

Converting SQL Query Results to Pandas Data Structures

Problem:

A user requires assistance in converting the results of an SQL query to a Pandas data structure. The user has attempted to print the query results but obtained no useful information.

Solution:

To convert SQL query results to Pandas, the following steps can be taken:

1. Establish a connection to the database using SQLAlchemy's create_engine() and connect() functions.
2. Execute the SQL query using the connection.execute() method and store the results in a variable (e.g., resoverall).
3. Fetch all rows from the query results using the resoverall.fetchall() method. This returns a list of tuples, where each tuple represents a row.
4. Create a Pandas DataFrame from the list of tuples. To do this, use DataFrame(resoverall.fetchall()), which automatically assigns column names based on the tuple indexes.
5. Optionally, you can assign specific column names using df.columns = resoverall.keys(), where resoverall.keys() returns a list of column names.

Example Code:

from sqlalchemy import create_engine
from pandas import DataFrame

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)
df = DataFrame(resoverall.fetchall())
df.columns = resoverall.keys()

The above is the detailed content of How Do I Efficiently Convert SQL Query Results into a Pandas DataFrame?. For more information, please follow other related articles on the PHP Chinese website!