Tutorials
Hands-On Python
Hands-On Python
  • Hands-On Python Tutorial For Real-World Business Analytics Problems
  • Preface
    • Section I. A Note From The Author
    • Section II. Tutorial Overview
    • Section III. What Is The Preflight Checklist?
    • Section IV. Supplimentery Material
  • Preflight Checklist
    • Section V. Select Your Difficulty Setting
    • Section VI. Download Anaconda
    • Section VII. Download PyCharm (Optional)
    • Section VIII. Download SQL Server Developer Edition
    • Section IX. Configure Database Environment
    • Section X. Download The Source Code
    • Section XI. Starting JupyterLab
    • Section XII. How To Get Help With This Tutorial
  • Language Basics
    • Lesson 1. Obligatory Hello World
    • Lesson 2. Code Comments
    • Lesson 3. Data Types
    • Lesson 4. Variables
    • Lesson 5. String Concatenation
    • Lesson 6. Arithmetic Operators
    • Lesson 7. Making Decisions
    • Lesson 8. Control Flow With if-elif-else
    • Lesson 9. Control Flow With while
    • Lesson 10. Data Structures Part I: List
    • Lesson 11. Data Structures Part II: Tuples
    • Lesson 12. Data Structures Part III: Dictionaries
    • Lesson 13. Looping With for
    • Lesson 14. Functions
    • Lesson 15. Importing Modules
    • Lesson 16. Python Programming Standards
  • Advanced Topics
    • Lesson 17. Functional Programing With map
    • Lesson 18. Generators
    • Lesson 19. Comprehensions
    • Lesson 20. Basic File Operations
    • Lesson 21. Working With Data In Numpy
    • Lesson 22. Working With Data In Pandas
    • Lesson 23. Working With JSON
    • Lesson 24. Making File Request Over HTTP And SFTP
    • Lesson 25. Interacting With Databases
    • Lesson 26. Saving Objects With Pickle
    • Lesson 27. Error Handling
    • Lesson 28. Bringing It All Together
  • Solutions To Real World Problems
    • Lesson 29. Download A Zip File Over HTTP
    • Lesson 30. Looping Over Files In A Directory
    • Lesson 31. Convert Comma Delmited Files To Pipe Delimited
    • Lesson 32. Combining Multiple CSVs Into One File
    • Lesson 33. Load Large CSVs Into Data Warehouse Staging Tables
    • Lesson 34. Efficiently Write Large Database Query Results To Disk
    • Lesson 35. Working With SFTP In The Real World
    • Lesson 36. Executing Python From SQL Server Agent
Powered by GitBook
On this page
  • Examples
  • Now you try it!
  1. Advanced Topics

Lesson 25. Interacting With Databases

Our focus is analytics not application development. As such, the discussion that you would expect from other generic tutorials is totally not going to happen right now.

Instead, we’re going to keep our eyes on the prize and focus on those task common to business analytics.

A large difference between app dev and analytics is the volume of data that you work with. Applications are frequently making changes to the database one record at a time. In analytics, you are frequently dealing with large fractions of the total organizations data assets. It requires a different mindset.

Below is a standard scenario that you most likely will be dealing with on a daily basis. You need to do an analysis so you use JupyterLab to connect into the database and get what you need.

Examples

Example #1: Git It Yo Self Service

If you know SQL, you do not have to wait for someone to get you your data. You can just grab it yourself from the database.

The process you see below is fairly common across all programming languages.

  1. Set up your query.

  2. Set up your connection.

  3. Execute your query.

  4. Go to town.

import os
import pyodbc as db
import time
import pandas as pd

#Prep query
sql = 'SELECT BusinessEntityID, AnnualSales, AnnualRevenue, SquareFeet, NumberEmployees' 
sql = sql + ' ' + 'FROM Sales.vStoreWithDemographics'

#Set up the connection.
connection_string = 'DSN=AdventureWorks;'
conn = db.connect(connection_string)

#Execute the query.
result_set = pd.read_sql(sql, conn, index_col = 'BusinessEntityID')

#Review the data.
#Set option used here to make describe output easier to read.
pd.set_option('display.float_format', lambda x: '%.2f' % x)
result_set.describe()

Now you try it!

Don't copy and past. Type the code yourself!

PreviousLesson 24. Making File Request Over HTTP And SFTPNextLesson 26. Saving Objects With Pickle

Last updated 3 years ago