Prashanth Jayaram

Why would a SQL Server DBA be interested in Python?

October 23, 2017 by

If we follow blogs and publications on the technological advancement with respect to SQL, we notice the increase in the number of references to Python, of late. Often, that makes us think:

  • Why so much emphasis on Python these days?
  • Isn’t knowing PowerShell scripting sufficient for the automation requirements of today?
  • Is it the time DBAs started learning a programming language such as Python in order to handle their day-to-day tasks more efficiently?
  • Why do so many job postings these days include “knowledge of scripting” as a requirement?
  • Is all of this happening because the paradigm is shifting? Can’t the current Microsoft-specific languages such as PowerShell handle the shift?

When SQL 2017 was released, it made database administrators raise their eyebrows about two things:

  • SQL Server became a cross-platform product
  • SQL Server started supporting the enrichment of Machine Learning capabilities

While TSQL, as well as PowerShell cmdlets, are flexible enough to make database activities smoother, making the platform a versatile one, the growing importance of SQL, and the product opening up to Linux enabled more administrators to start looking into what SQL can offer.

Python is a versatile language, when it comes to working with analytical tools, and is considered one of the best available languages in the context. Python is, in fact, fully capable of interacting with huge volumes of data, handling complex mathematics and data manipulation/cleaning.

“OK, so Python is one of the favorite languages used by Linux admins. But hasn’t PowerShell been open-sourced under the MIT License and made available for Linux as well? Has it not already help with using SQL on Linux? Why add support for Python as well? How are we to get started there?”

As it turns out, Python isn’t difficult to learn. Also, learning Python is another arrow added to the quiver. Why not have the additional capabilities, keeping with the spirit of openness? Let’s get started and see how some of our regular tasks can be implemented using Python

Technical overview

This article will provide an overview of the following technical implementations

  • Prepare a connection string
  • Read a file
  • Define complex SQL
  • Look at the looping constructs used to manipulate the string data to be stored in a dedicated table

A database generation operation has been used as the example for illustration. Let’s suppose that the inventory is stored in a central repository.

Implementation

In this article, we look at the following, with respect to SQL and Python:

  1. The input file – a list of the servers, in a simple text file
  2. The dedicated database and table for storage
  3. Loading PyODBC/pymssql
  4. Preparation of the connection string
  5. Building the connection string for every server based on the input
  6. Executing the query
  7. Fetching the output

Input file

The input file is a simple list of servers in a plain text file, each server name on a separate line.

Create Table

Let’s now proceed to create a dedicated table. The below SQL is used to create the dedicated table on the database. The columns listed have some of the high-level details of the databases.

Python SQL Driver

We use the open source API module to bridge Python with the SQL Server. Before we proceed, here are the prerequisites:

  1. Install pyodbc module
    or
  2. Install pymssql module

Click on the download links of the respective drivers and make sure you select the correct WHL file. The file is different for different platforms. Once you download the WHL file, place it in the right folder. In my case, I downloaded the file and saved it at C:\Python.

Now, let’s proceed to install the module. Open a command prompt and run the following command. Ensure to run it from where you placed the WHL file.


The pyodbc/pymssql module is needed to connect via ODBC to SQL Server. Here’s a schematic of how Python works with different OSes.

Read the Input File

Python, by default, provides us with the basic functions and methods to manipulate files. You can do most of the file manipulation using a file object.


Setup the Connection String

  1. Prepare the connection to the SQL Server database using SQL Server authentication or Windows authentication by passing the appropriate parameters such as the server name, user ID (UID) and password (PWD). Let’s take a look at each of these methods:
    1. SQL Server Authentication, which needs:
      • Instance Name
      • Database
      • Username
      • Password

    2. Windows Authentication, which needs:
      • Instance Name
      • Database
      • Trusted_connection Parameter

Instantiate cursor

The process of instantiation of cursor parameter is used to access the instance. It also helps traverse the data set.

Prepare query

The query string literals are placed in triple quotes. It’s better to embed the complex SQL query embed in quotes. This way, we can bypass the stringent indentation style of Python.

Pass the query string into the cursor method:


Execute the Query

The TSQL string is executed against the current scope of the cursor. The data is then transformed using the while loop construct, to fed the data into the dedicated instance.

The complete code is placed in the Appendix (A). Also, below is a sample showing the importance of indentation in Python.

The output of SQL table

Execute Update and Select T-SQLs

In the following example, the column Server name is given the value, SQLShack. Later on, we retrieve the data using select statements.


Let us now also look at some sample Execute Delete and Select T-SQLs.


Python psmssql driver

The pymssql.connect method is used to connect to SQL Instance and databases. In our example, we query the inventory table and retrieve the data.


And here is the output of the select statement we just issued.

Wrapping Up

Python has several strong points which make it worthy of attention. Apart from being free and open source software (FOSS), it is, far more significantly, easy to learn. It is also easy to read even for people who are not Python programmers. Python is capable of being used to write full-scale applications and server software, but as database administrators, we’ll find it more interesting that it is very handy when it comes to writing quick utility scripts. Of course, the use of it is not just limited to that. As we saw in the article, Python has been proving increasingly useful in Machine Learning. I’ve also talked about using Python in Analytics, in my article, How to use Python in SQL Server 2017 to obtain advanced data analytics.

Having the SQL Server now available for Linux, considering that there are a lot of Python developers out there, this might be a good place for some of us to learn a bit about utilizing Python, in case you need to troubleshoot that kind of a setup in the near future.

Of course, SQL database administrators have already been taking advantage of PowerShell to manage databases and database servers, and that non-SQL administrators might have an upper hand with Python. However, when there are multiple ways of managing SQL Servers, it cannot hurt to know more than one way to achieve near-perfect administration via APIs.

See more

Consider these free tools for SQL Server that improve database developer productivity.

References

Appendix (A)


Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram
Python

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

2,375 Views
  • Samir Behara

    Liked the last 3 articles you wrote. Good informative ones. Keep it coming Prashanth.

    • Prashanth Jayaram

      Thanks for your appreciation!. I’m glad that you liked my articles:).

  • Tina Myklebust

    Great article. What I would really love to see is a comparison, advantages vs disadvantages, strengths and weaknesses of Python vs PowerShell. Maybe you could consider that for a future topic.

    Also a getting started article about Python would be great – end to end, from install to “Hello, World!”

    • Prashanth Jayaram

      Hello Tina,
      Thanks for writing the great comment One thing for sure, PowerShell and Python are having their own scope when it comes to database administration. The foot-prints of Python in the IT world is there for many decades. The friendliness of Python is the strength where you can develop full-fledged software also the visibility is getting in the arenas of AI is immense.

      I’ve already started curating an article on “PowerShell, Python and SQL Administrators”. I hope it will answer many of your questions.

      The Wrapping Up section of this article has a link which covers a portion of the installation and execution of sample Python scripts. Please check.

      Thanks again for your time.

      Best Regards,
      Prashanth