Using Python with Azure

Guest Post by Gerald Britton

Welcome to our weekly MVP series where we discuss some of the most useful solutions, tips & tricks and how to’s on integrating OSS & Microsoft technologies. In case you have any questions, don’t hesitate and drop us a comment below or reach out to us via Twitter and Facebook! Enjoy!

1 Introduction

Python is a mature, open source language. It has found broad application in an increasingly diverse community, including business, government and academia, where it supports operations automation (though sophisticated scripting), data mining and analysis (often combined with other packages such as the open source R statistical analysis language) and web site management (as one of the P’s in the LAMP stack). In addition, Python is used by both OS/X and most Linux distributions to manage program packages and system updates.

I first came to Python out of an interest in my family history. A series of interesting documents fell into my lap from various family sources (including relatives I hadn’t known existed) and I got hooked! Before too long, the information I had collected outgrew my ability to manually track and catalog. I began to seek out a software package that could help. I eventually stumbled upon a package called Gramps and imported what I had into my first Gramps database. As a developer, I was naturally curious about the development of the software and quickly found that it was an open-source, cross-platform project developed in Python. Before long I was actively contributing to the project and continue to follow it with great interest to this day.

While Python has long had many excellent IDEs, on Aug 19, 2011, the first stable release of Python Tools for Visual Studio became available. Visual Studio is of course a very mature and capable IDE (Integrated Development Environment) and thousands of developers and designers use it every day to support their work in desktop, mobile, web and database applications among other things. Adding Python to its abilities suddenly makes Visual Studio an attractive IDE for experienced Pythonistas and beginners alike.

Since I wanted to exploit this new capability of Visual Studio and am also a SQL Server specialist, I thought it would be interesting to see if I could use Python and other open source components to access Microsoft Azure. This article summarizes my experience and findings.

2 Getting Started

To start off, we need a current copy of Visual Studio. If you don’t have one, you the Community Edition from Visual Studio Community 2015. Once installed, you can use nuget to install the Python Tools for Visual Studio. To do that, launch Visual Studio, click on the Tools menu and select Extensions and Updates. From the Extensions and Updates window click on the Online tab and type Python Tools for Visual Studio. Click on the Install button when the search is complete. Restart Visual Studio if required.

Now that we have the ability to write Python programs using Visual Studio, let’s start a New Project. From the New Project menu, select the Python tab and Python Application as the template to use. You can use any name you like for your project but let’s call this one Python_Azure. When the project finishes initializing, you should see an empty line in the program Python_Azure.py. Just to try out the basics, type in:

print 'Hello, world'

You can run this little program by hitting F5. If all goes well, you should see a console window pop up like this:

If you do, then you are successfully running Python in Visual Studio! If you get different results, go back and review the installation steps and try again.

Python Tools for Visual Studio also gives us an interactive window that you can use to try out program snippets. Simply hit Alt+I to activate this window or select it from View/Other Windows menu. Once again, enter the immortal words 'Hello, world' at the three chevrons prompt, hit enter and you should see the phrase printed in the interactive window followed by a new prompt. Note that you can send any selected code from the editor to the interactive window by pressing Ctrl+E twice, or selecting “Send to Interactive” from the context menu.

3 Connecting to Azure

For the purposes of this exercise, we’re going to connect to a copy of AdventureWorks stored on Microsoft Azure. The kind folks at SQL Server Central have made this database freely available for all to use. For details, see Connecting to AdventureWorks on Azure. To set up the connection, we’ll use the ODBC Connection Editor. From your Windows desktop, simply hit the Windows key and type ODBC, then hit enter. You should see a window like this:

You may see your own User Data Sources or none at all. We’re going to define a new one, however, so click on Add… Choose ODBC Driver for SQL Server as in this screenshot:

Then click Finish. (We’re not really finished!). One the following screen, fill in the details as shown:

The server name (for easy copying and pasting) is:

mhknbn2kdz.database.windows.net

Click Next and select SQL Server Authentication like this:

The password is:

sqlf@m1ly

Click Next and select the “Change the default database to:” checkbox, then enter:

AdventureWorks2012

The window should look like this (If you see the Application Intent drop down, select READONLY):

Click Next then Finish and finally Test Data Source. If all goes well, you should see:

4 Using pyodbc

Now that we have a connection to Azure, we need to install a package to access it from Python. One such package is pyodbc. We can install this directly from the Visual Studio Python Interactive window like this:

 import subprocess

subprocess.call(['pip', 'install', 'pyodbc'])

If all goes well, you will now have the pyodbc module installed. Let’s import it:

import pyodbc

Now that we have pyodbc in our namespace, we can connect to AdventureWorks in Azure and run a simple query:

# Connect to AdventureWorks on Azure

connection_string = 'DSN=AdventureWorks_Azure;UID=sqlfamily;PWD=sqlf@m1ly'

conn = pyodbc.connect(connection_string)

# Define the query we want to execute

query = '''

SELECT per.FirstName, per.LastName, emp.JobTitle

FROM Person.Person per

JOIN HumanResources.Employee emp

ON per.BusinessEntityID = emp.BusinessEntityID

ORDER BY LastName, FirstName

OFFSET 0 ROWS

FETCH FIRST 5 ROWS ONLY;

'''

# Get a cursor object and run the query

curs = conn.cursor()

curs.execute(query)

# Print out the results obtained from the query

for row in curs:

print row.FirstName, row.LastName, '-', row.JobTitle

Note that you can run this in the Python Interactive window (Ctrl+A, Ctrl+E, Ctrl+E, as described above) or by clicking Start on the toolbar. If successful, you should see five rows of output.

5 Summary

We’ve shown a simple example of using an open source language – Python in this case – to access a SQL database hosted in Microsoft Azure. Of course this is just the tip of the iceberg. Building upon the techniques introduced in this article, you can access the entire suite of Python packages and use them to construct any application of interest.