Convert SQL query result to Pandas DataFrame using Python

In this article, I’ll show you How to convert a SQL query result to pandas DataFrame using python script. Python has a pandas library that provides the high-level interface to the data that exist in SQL Database.

Convert SQL query result to Pandas DataFrame using Python

 

Businesses can use this data analysis tool for data visualization and data analytics. For the demo purpose, I have used SQLite Database to convert a SQL query to pandas DataFrame you can use any database that supports PEP 0249.

The simplest way to convert a SQL query result to pandas data frame we can use pandas “pandas.read_sql_query()” method of python.

Syntax –

df = pandas.read_sql_query(Sqlquery, connection)

Suppose you have a product Table and you want to pull all its data and convert the same in data frame use the below script –

Example –

df = pandas.read_sql_query(SELECT * FROM product, con)

It fetches all the data from products and returns the pandas data frame.

Convert a SQL query result to Pandas Data Frame using Python Example –

Create a Table in memory having the below schema –

import sqlite3
connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
cursor.execute("CREATE TABLE product(id INTEGER PRIMARY KEY, Prodname TEXT, price INTEGER)")
cursor.execute("INSERT INTO product VALUES(1, 'MacBook', 1500)")
cursor.execute("INSERT INTO product VALUES(2, 'iPad', 550)")
cursor.execute("INSERT INTO product VALUES(3, 'iPhone', 1050)")
cursor.execute("INSERT INTO product VALUES(4, 'iWatch', 200)")
connection.commit()

Now create the SQL query to fetch the data from the product table –

query = "SELECT * FROM product"

Now execute the query using the “pandas.read_sql_query()” method and store the same into Pandas Dataframe

df = pd.read_sql_query(query, connection)

Print the data frame to see the result –

Print(df)

Output –

   id  Prodname     Price
0   1  MacBook      1500
1   2  iPad         550
2   3  iPhone       1050
3   4  iWatch       200

You can manipulate this data frame data further for different purposes.

Can pandas read a SQL view to convert the same into a data frame?

Yes, the same method instead of SQL table uses the view name from SQL database. Below is the query that fetches data from SQL view and returns the collection to the pandas data frame.

df = pandas.read_sql_query(SELECT * FROM my_sqlview, connection)

I hope now you understand how you can convert a SQL query result to a Pandas Data Frame using Python and that result can be used later for data analytics and data visualization.