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.
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.