Python: Accessing a SQL database

If you really want to do data work, you need to be able to connect to a database. In this example I will show you how to connect to and query data from MS SQL Server with the AdventureWorks2012 database installed.

This lesson assumes some very basic knowledge of SQL. If SQL is a complete mystery, head over to my SQL page: SQL  If you check out the first 4 intro lessons, you will know everything about SQL you need to know for this lesson.

Install pyodbc

To connect to the database, we need to install pyodbc. Go to your Anaconda terminal and type: pip install pyodbc

sqlpython.jpg

Now open up your jupyter notebook and start a new notebook

Connect to Database

import pyodbc

cnxn is our variable – it is commonly used as a shorten version of connection

syntax: pyodbc.connect(‘DRIVER={SQL Server}; SERVER=server name; DATABASE=database name;UID = user name; PWD = password’)

finally cursor =cnxn.cursor() creates a cursor for us. In SQL, a cursor is used to step through your results one row at a time.

sqlpython1.jpg

cursor.execute(place sql query here)  – this is how you pass a sql query – note query goes in quotes

tables = cursor.fetchall() – fetch all the rows in your query results

sqlpython2

We can now iterate through the rows in tables.

sqlpython3.jpg

I don’t like the layout of this. Also we can’t really work with the data.

Pandas Dataframe

first import pandas

  • d= [] – create empty dictionary
  • d.dappend({‘Name’:row.Name, ‘Class’: row.GroupName}) – fill dictionary 1 row at a time
  • df = pd.DataFrame(d) – convert your dictionary to a dataframe

sqlpython4.jpg


If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more Python content: Python

Advertisements

Python: Read CSV and Excel with Pandas

Earlier is showed you how to use the Python CSV library to read and write to CSV files. While CSV does work, and I still use elements of it occasionally, you will find working with Pandas to be so much easier.

Practice Files Excel: Linear Regression Example File 1

CSV: heightWeight_w_headers

Let’s start with our CSV file. Unzip it and place it in a folder where you will be able to find it.

If you want to make your life a little easier, when you open your Jupyter Notebook, type pwd

This will give you your present working directory. If you place your csv file in this directory, all you have to do is call it by name.

csv1

I am not going to do that however. I want to show you how to work with directories.

Get Full Directory Path

For Windows Users, if you hold down the Shift key while right clicking on your file, you will see an option that says: Copy as Path

csv2

Read the CSV file

First import pandas as pd

Then assign a variable = pd.read_csv(file name) – paste the full path of your CSV file here.

variable.head() = the first 5 rows from your data frame.

pandaCSV

Write CSV file

Okay, let’s write a CSV file. First, let’s add some rows to current dataframe.

We will do this be first creating a new dataframe with 3 rows of data.

pandaCSV1.jpg

Now we will use the .append() function to append this new dataframe to end of our existing dataframe.

The ignore_index = True argument tells the new rows to continue using the index of the main dataframe. Otherwise, the new rows would start counting at 0 again. If you want to try it, just leave out the argument: HgtWgt_df.append(df2)

pandaCSV2.jpg

Now, we just use the df.to_csv() command to write our appended dataframe to a new CSV file

pandaCSV3

And there is our new file

pandaCSV4.jpg

Read Excel File

I’d love to be able to wow you with how complicated reading an Excel file is, but the difference between the Excel file reading and CSV is one word – excel.

pandaexcel.jpg

The only caveat is if your Excel file has multiple sheets. By default pd.read_excel() goes to sheet 1. If you want it to read sheet 4 instead, you would add: pd.read_excel(filename, sheetname= 4)

Write to Excel File

pandaexcel1.jpg

Bonus note

If you haven’t already, please check out my earlier CSV lesson: Python: Working with CSV Files

This will really give you an appreciation for how powerful and time saving pandas really is.


 

If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more Python content: Python