In this article, we are going to see how to convert SQL Query results to a Pandas Dataframe using pypyodbc module in Python.
We may need database results from the table using different queries to work on the data and apply any machine learning on the data to analyze the things and the suggestions better. We can convert our data into python Pandas dataframe to apply different machine algorithms to the data. Let us see how we can the SQL query results to the Pandas Dataframe using MS SQL as the server.
pypyodbc: It is a pure Python Cross-Platform ODBC interface module. To Install pypyodbc module to access the ODBC databases using this command in the terminal.
pip install pypyodbc
Creating Database
Step 1: Create a Database
CREATE DATABASE Lazyroar;
Step 2: Using the database
USE GeeksForGeeks
Step 3: Creating table student_marks and adding rows into the table
CREATE TABLE student_marks( stu_id VARCHAR(20), stu_name VARCHAR(20), stu_branch VARCHAR(20), total_marks INT )
Converting SQL Query to Pandas Dataframe
Example 1:
Connect to the MSSQL server by using the server name and database name using pdb.connect(). And then read SQL query using read_sql() into the pandas data frame and print the data.
Python3
import pypyodbc as pdb import pandas as pd connection = pdb.connect( """ Driver={{SQL Server Native Client 11.0}}; Server={0}; Database={1}; Trusted_Connection=yes;""" . format ( 'LAPTOP-LKHL8PKV' , 'GeeksForGeeks' ) ) query = """SELECT * FROM student_marks""" table = pd.read_sql(query, connection) print (table) |
Output:
Example 2: Query to get students of E.C.E branch from the table to the pandas data frame.
Python3
import pypyodbc as pdb import pandas as pd connection = pdb.connect( """ Driver={{SQL Server Native Client 11.0}}; Server={0}; Database={1}; Trusted_Connection=yes;""" . format ( 'LAPTOP-LKHL8PKV' , 'GeeksForGeeks' ) ) query = """SELECT * FROM student_marks WHERE stu_branch='E.C.E'""" table = pd.read_sql(query, connection) print (table) |
Output: