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.
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.
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.
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.
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.
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.
If I check from SSMS, sure enough the program has worked.
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.
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.
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.
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?
LikeLike
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.
LikeLike
@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
LikeLike
Thanks for the link. I found a different from (can’t find it now) from a python dev that really hated the changes in P3.
LikeLike