How to convert a SQL query result to a Pandas DataFrame in Python

Pandas is a Python library that provides a high-level interface to the data in SQL databases. It is a widely used data analysis tool that is used to create data visualizations and data analysis tools.

In this tutorial, we will learn how to convert an SQL query result to a Pandas DataFrame in Python. We will use the SQLite database for simplicity but any database that conforms to PEP 0249 can be used. Hint: Use SQLAlchemy makes it possible to use any database supported by that library.

To convert an SQL query result to a Pandas DataFrame, we will use the pandas.read_sql_query() function.

Create the database in RAM

import sqlite3
connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
cursor.execute("CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
cursor.execute("INSERT INTO users VALUES(NULL, 'John', 42)")
cursor.execute("INSERT INTO users VALUES(NULL, 'Jane', 37)")
cursor.execute("INSERT INTO users VALUES(NULL, 'Jill', 30)")
cursor.execute("INSERT INTO users VALUES(NULL, 'Jack', 29)")
connection.commit()

Create a query to read the data from the database

query = "SELECT * FROM users"

Let us execute the query and store the result in a Pandas DataFrame

data = pd.read_sql_query(query, connection)

Print the DataFrame

print(data)

The output of the above code is as follows:

   id  name  age
0   1  John  42
1   2  Jane  37
2   3  Jill  30
3   4  Jack  29