Hi everyone!!

Lately I have been studying a bit of Python that I intend to use at work for some projects and also for learning a new language. I would recommend for you to also learn it, because as you know Python is coming to SQL Server 2017. The thing is that with Python you can use in your Machine Learning models, build Websites using some other frameworks such as: Django or Flask and even automate trivial tasks of your daily basis.

However, something that I know from the top of my head now is: Python was not designed in the first hand to deal with Microsoft SQL Server as a backend database. You don’t find lot’s of examples, the documentation sometimes is a bit misleading and errors and more errors that you cannot even imagine. If you search about Python + MySQL, Python + PostgreSQL, you will find lots of examples out there. I am not saying this is bad or anything, but it is just the way it is.

Anyway, my idea is below to show how to connect Python and SQL Server. I am not going to show how to install Python or SQL Server, but I do assume you have some knowledge on it.

First of all, I’ve installed PyCharm Community Edition. 10 in 10 Python developers will recommend PyCharm for your development usage. Ah, I also use Visual Studio along with the Python Plugin and works very well.

Disclaimer here: If you are an Python Expert Developer and is reading this article, feel free to correct anything that I wrongly said in here. I am a beginner in this world and probably I might make mistakes along the road.

After you have created your project in PyCharm, you need to install the pyodbc and pyodbc-azure packages in the project. For doing so, in PyCharm you need to go to: File –> Settings –> YourProjectName –> Project Interpreter. Then you click in the + button in the right side.

Then look for pyodbc package and click to install.

After that you have pyodbc package installed in your machine.

Now you would ask. How to use it?

import pyodbc


conn = pyodbc.connect(
    r'DRIVER={ODBC Driver 13 for SQL Server};'
    r'SERVER=SQLHostName;'
    r'DATABASE=DatabaseName;'
    r'UID=SQLLogin;'
    r'PWD=Password'
    )

cursor = conn.cursor()

cursor.execute("select * from sys.database_files")
for row in cursor.fetchall():
    print(row)

Now, one point that you need to keep in mind is that everything is in a single file, this means: Connection, Query, processing the results, etc.. which is not a best practice when developing something. In my next blog post I will show how to inherit a database connection from another Python file and use it in your project.

I appreciate any feedback that you want to share. If you like it, please share this blog post with others.

Regards,
Marcos Freccia
Data Platform MVP