Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

Python Charts Data

by suresh

In this section, we show the data that we use for drawing or generating Python charts using Pandas and matplotlib libraries. As you can see, we used three queries against SQL Server to get data from desired tables.

In all our Pandas DataFrame plot functions and matplotlib charts, we use either one of them or all of them (depending upon the scenario).

First, we are selecting all the columns from the Employees Table from our SQL Tutorial database. This table holds 8 columns and 15 rows.

The query behind the below image is:

SELECT [EmpID] ,[FirstName], [LastName],[Education], 
       [Occupation], [YearlyIncome], [Sales] ,[HireDate]
   FROM [SQL Tutorial].[dbo].[Employee]

OUTPUT

Python Charts Data 2

Now, let me show how to get this data into the Python workspace. To use the above data for Python charts, we have to convert that data to a DataFrame. It involves multiple steps, first, establish an ODBC connection with SQL Server and appropriate Database. Second, write a query to select the required fields from a Database Table. Finally, convert that data to Pandas DataFrame.

import pyodbc
import pandas as pd
import matplotlib.pyplot as plt

conn = pyodbc.connect('''Driver={SQL Server Native Client 11.0}; Server=PRASAD;
                      Database=SQL Tutorial ; Trusted_Connection=yes;''')

string = ( ''' SELECT EmpID ,FirstName, LastName ,Education, Occupation, YearlyIncome, Sales ,HireDate 
                    FROM Employee''')

query = pd.read_sql_query(string, conn)
data = pd.DataFrame(query)
print(data)

OUTPUT

Python Charts Data 3

Since we are selecting all the columns, we may use SELECT * to save some space in the source code. Remember, it isn’t very nice to use SELECT * queries in SQL Server. I suggest you refer to the SQL Select Statement to understand the same.

Here, We are using the AdventureWorksDW2014 database, and this is the data that we use in most of the charts that we generate using the Python matplotlib.

SELECT ProdCat.EnglishProductCategoryName, ProdSubCat.EnglishProductSubcategoryName, Geo.EnglishCountryRegionName, 
       Geo.StateProvinceName, Geo.City, Geo.PostalCode, Prod.EnglishProductName, Prod.Color, Prod.DealerPrice, 
       Cust.FirstName, Cust.LastName, Cust.Gender, Cust.YearlyIncome, Cust.EnglishEducation, Cust.EnglishOccupation, 
       Fact.OrderQuantity, Fact.TotalProductCost, Fact.SalesAmount, Fact.TaxAmt, Fact.OrderDate
FROM DimProductSubcategory AS ProdSubCat 
	INNER JOIN DimProduct AS Prod ON ProdSubCat.ProductSubcategoryKey = Prod.ProductSubcategoryKey 
   INNER JOIN DimProductCategory  AS ProdCat ON ProdSubCat.ProductCategoryKey = ProdCat.ProductCategoryKey 
   INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey 
   INNER JOIN DimCustomer AS Cust ON Fact.CustomerKey = Cust.CustomerKey
   INNER JOIN DimGeography AS Geo ON Cust.GeographyKey = Geo.GeographyKey

OUTPUT

Data For Python Charts 1

Let me show you the Adventure Works tables data for Python charts in Pandas DataFrame

import pyodbc
import pandas as pd
import matplotlib.pyplot as plt

conn = pyodbc.connect("Driver={SQL Server Native Client 11.0}; Server=PRASAD;"
                      "Database=AdventureWorksDW2014 ; Trusted_Connection=yes;")

string = ( ''' SELECT ProdCat.EnglishProductCategoryName, ProdSubCat.EnglishProductSubcategoryName, 
                    Geo.EnglishCountryRegionName, Geo.StateProvinceName, Geo.City, Geo.PostalCode, 
                    Prod.EnglishProductName, Prod.Color, Prod.DealerPrice, Cust.FirstName, Cust.LastName, 
                    Cust.Gender, Cust.YearlyIncome, Cust.EnglishEducation, Cust.EnglishOccupation, Fact.OrderQuantity,
                    Fact.TotalProductCost, Fact.SalesAmount, Fact.TaxAmt, Fact.OrderDate 
                    FROM DimProductSubcategory AS ProdSubCat 
                    INNER JOIN DimProduct AS Prod ON ProdSubCat.ProductSubcategoryKey = Prod.ProductSubcategoryKey 
                    INNER JOIN DimProductCategory  AS ProdCat ON ProdSubCat.ProductCategoryKey = ProdCat.ProductCategoryKey 
                    INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey 
                    INNER JOIN DimCustomer AS Cust ON Fact.CustomerKey = Cust.CustomerKey  
                    INNER JOIN DimGeography AS Geo ON Cust.GeographyKey = Geo.GeographyKey''')

query = pd.read_sql_query(string, conn)
data = pd.DataFrame(query)
print(data)

If you observe the below table, there are almost 20 columns and 60,398 rows. For the demonstration purpose, we can’t use all those columns so, we go with a maximum of 4 columns. I mean a shorter version of the above query with probably fewer joins. I hope you understand.

Data For Python Charts 4

In some cases, we might use the following table as well. So, let me show you the same

Data For Python Charts 5

Placed Under: Python

  • Download and Install Python
  • Python Arithmetic Operators
  • Python Assignment Operators
  • Python Bitwise Operators
  • Python Comparison Operators
  • Python Logical Operators
  • Python If Statement
  • Python If Else
  • Python Elif Statement
  • Python Nested If
  • Python For Loop
  • Python While Loop
  • Python Break
  • Python Continue
  • Python Dictionary
  • Python datetime
  • Python String
  • Python Set
  • Python Tuple
  • Python List
  • Python List Comprehensions
  • Python Lambda Function
  • Python Functions
  • Python Types of Functions
  • Python Iterator
  • Python File Handling
  • Python Directory
  • Python Class
  • Python classmethod
  • Python Inheritance
  • Python Method Overriding
  • Python Static Method
  • Connect Python and SQL Server
  • Python SQL Create DB
  • Python SQL Select Top
  • Python SQL Where Clause
  • Python SQL Order By
  • Python SQL Select Statement
  • Python len Function
  • Python max Function
  • Python map Function
  • Python print Function
  • Python sort Function
  • Python range Function
  • Python zip Function
  • Python Math Functions
  • Python String Functions
  • Python List Functions
  • Python NumPy Array
  • NumPy Aggregate Functions
  • NumPy Arithmetic Operations
  • Python Numpy Bitwise operators
  • Numpy Comparison Operators
  • Numpy Exponential Functions
  • Python Numpy logical operators
  • Python numpy String Functions
  • NumPy Trigonometric Functions
  • Python random Array
  • Python numpy concatenate
  • Python numpy Array shape
  • Python pandas DataFrame
  • Pandas DataFrame plot
  • Python Series
  • Python matplotlib Histogram
  • Python matplotlib Scatter Plot
  • Python matplotlib Pie Chart
  • Python matplotlib Bar Chart
  • Python List Length
  • Python sort List Function
  • Python String Concatenation
  • Python String Length
  • Python substring
  • Python Programming Examples

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy