A Python SQL Server App

I got a link recently from Microsoft on building apps easily for SQL Server. At the top of the page, they ask you to pick a language and OS. Since I’ve done a little Python and I used Windows, I chose that option.

The page looks to walk you through a simple app. I decided to try this out and see if it works, following the instructions. Since I already had SQL 2016 installed (and newly upgraded to SP1), I just connected and verified it was there.

2016-11-22 11_50_06-powershell

I didn’t want to go back to Python 2.7, and I’d rebuilt this machine for testing, so I used Chocolatey to quickly get Python 3.5. I’ll have to adjust code to match this version.

2016-11-22 11_52_38-cmd - choco  install python (Admin)

One python was installed, I needed to get virtualenv and then create a folder. I used my regular git folder since it’s really, really simple to get a project with version control running. Unfortunately, when I went to get pyodbc, I had an error.

2016-11-22 12_18_53-cmd (Admin)

This is going to use C++ tools to build the pyodbc files, so I need to get those installed. I can do that from Visual Studio.

Once I run those and install them, I can now get pyodbc. There is a build error, but I seem to have the module. My next step is to create my db. I also create a login that is db_owner for this.

2016-11-22 12_41_22-powershell

Now it’s time for the code. That’s easy enough, so I copy the code into VSCode and change the instance and user information.

2016-11-22 12_42_11-crud.py - Visual Studio Code

I also had to replace the print statements with parenthesis around the quoted items, which is required in Python 3.x. Once that’s done, I start the program and it appears to work.

2016-11-22 12_39_25-cmd (Admin)

If I check from SSMS, sure enough the program has worked.

2016-11-22 12_39_54-SQLQuery1.sql - localhost_SQL2016.SampleDB (PLATO_Steve (62))_ - Microsoft SQL S

The second part of the tutorial uses the Django Web Framework to setup a simple site. Once again, I get a pyodbc error and there’s nothing that works. Fortunately I found the issue is with Python 3.5 and not having the proper binaries in the default PyPI directory. I resolved it with this link.

2016-11-22 13_26_45-crud.py - Visual Studio Code

However, there were other errors, which I suspect are related to Python 2.7 v Pyhton 3.5. Rather than solve those, I went on to the columnstore demo. In this, you create a table with 5mm rows and then run a query against it from Python. I did that, then created the columnstore index, then ran it again. The results are below.

2016-11-22 13_46_23-columnstore.py - Visual Studio Code

Note: I had to create the variable, tsql, and used this line of code:

tsql = “SELECT SUM(Price) FROM Table_with_5M_rows”

Python Works

Despite some issues, which are probably my fault, this is a good introduction to how you might use Python with SQL Server. Since we now have SQL Server on Linux coming, and there are lots of Python developers, this might be a good place for some of you to learn a bit about connectivity with Python if you ever need troubleshoot that kind of setup.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

4 Responses to A Python SQL Server App

  1. Meet Bhagdev says:

    Hi Steve Jones,
    I work on the SQL Server team and wrote the content for the website you reference above (https://www.microsoft.com/en-us/sql-server/developer-get-started/). I have taken your feedback and tried to address it in our content. Our code now works with Python 2.7 and Python 3.x.

    pyodbc should also install seamlessly for you. Can you take a look and let us know of your feedback?

    • way0utwest says:

      Meet, I’ll try to get to this. I know the pyodbc was updated after I wrote this, and it should work in p3.5. I’ve been debating if I think P3.5 is better than 2.x, and the more I look at it, the more I start to think that 2.x is a better version of Python, and since so many of the examples and learning I’ve seen are based on that, I’ll likely go back to just 2.x.

  2. Meet Bhagdev says:

    @Steve, great question! Python 2.x is still widely used in the Python community and has a larger share of the developers. That being said new features and improvements are being added to Python 3.x and less support will be given to Python 2.x in the coming months. Here is a good read on the differences and recommendations: https://www.digitalocean.com/community/tutorials/python-2-vs-python-3-practical-considerations-2

Comments are closed.