Why would a SQL Server DBA be interested in Python?

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:

The input file – a list of the servers, in a simple text file
The dedicated database and table for storage
Loading PyODBC/pymssql
Preparation of the connection string
Building the connection string for every server based on the input
Executing the query
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.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[tbl_DbInventory](

[ServerName] varchar(100) NOT NULL,

[database_id] [int] NOT NULL,

[dbName] [varchar](25) NULL,

[Status] [varchar](10) NULL,

[state_desc] [nvarchar](60) NULL,

[DataFiles] [int] NULL,

[DataMB] [int] NULL,

[LogFiles] [int] NULL,

[LogMB] [int] NULL,

[Useraccess] [nvarchar](60) NULL,

[Recoverymodel] [nvarchar](60) NULL

) ON [PRIMARY]

Python SQL Driver

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

Install pyodbc module
or
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.

cd c:\Python

pip install pymssql-2.1.3-cp36-cp36m-win32.whl

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.

with open('c:\server.txt', 'r') as infile :

lines = infile.read().splitlines()

for line in lines:

print (line)

Setup the Connection String

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:

SQL Server Authentication, which needs:

Instance Name
Database
Username
Password

DBInstance = 'HQDBT01'

DB = 'SQLShackDemo'

username = 'SQLShackDemo'

password = 'SQLShackDemo$'

cnxn_1 = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL

Server};SERVER='+DBInstance+';PORT=1443;DATABASE='+DB+';UID='+username+';PWD='+

password)

Windows Authentication, which needs:
- Instance Name
- Database
- Trusted_connection Parameter
1
2
3
4

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL
Server};SERVER='+server+';PORT=1443;DATABASE='+database+';Trusted_Connection=YES')

Instantiate cursor

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

cursor_1 = cnxn_1.cursor()

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:

tsql = """

SELECT

@@SERVERNAME serverName,

database_id,

CONVERT(VARCHAR(25), DB.name) AS dbName,

CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],

state_desc,

(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,

(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],

(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,

(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],

user_access_desc AS [User access],

recovery_model_desc AS [Recovery model]

FROM sys.databases DB

ORDER BY dbName;"""

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.

with cursor.execute(tsql):

row = cursor.fetchone()

while row:

print (str(row[0])+" "+str(row[1])+" "+str(row[2])+ " "+str(row[3])+" "+str(row[4])+" "+str(row[5])+" "+str(row[6])+" "+str(row[7])+" "+str(row[8])+" "+str(row[9])+" "+str(row[10]))

tsql_1 = "INSERT INTO [tbl_DbInventory] (ServerName,database_id, dbName, Status, state_desc, DataFiles, DataMB, LogFiles, LogMB, Useraccess, Recoverymodel) VALUES (?,?,?,?,?,?,?,?,?,?,?);"

with cursor_1.execute(tsql_1,str(row[0]),row[1],str(row[2]),str(row[3]),str(row[4]),row[5],row[6],row[7],row[8],str(row[9]),str(row[10])):

print ('Successfuly Inserted!')

row = cursor.fetchone()

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.

import pyodbc

server = 'HQDBT01'

database = 'SQLShackDemo'

username = 'SQLSHackDemo'

password = 'SQLSHackDemo$'

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+';PWD='+ password)

cursor = cnxn.cursor()

#Update Query

print ('Updating the servernames')

tsql = "UPDATE [tbl_DbInventory] SET ServerName = ?"

with cursor.execute(tsql,'SQLSHACK'):

print ('Successfuly Updated!')

#Select Query

print ('Reading data from tbl_DbInventory table')

tsql = "SELECT * FROM tbl_DbInventory;"

cur=cursor.execute(tsql)

for row in cur:

print(row)

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

import pyodbc

server = 'HQDBT01'

database = 'SQLShackDemo'

username = 'SQLShackDemo'

password = 'SQLShackDemo$'

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+';PWD='+ password)

cursor = cnxn.cursor()

#Delete Query

print ('Deleting the FULL recovery model databases ')

tsql = "DELETE FROM tbl_DbInventory WHERE [Recoverymodel] = ?"

with cursor.execute(tsql,'FULL'):

print ('Successfuly Deleted!')

#Select Query

print ('Reading data from tbl_DbInventory table')

tsql = "SELECT * FROM tbl_DbInventory;"

cur=cursor.execute(tsql)

for row in cur:

print(row)

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.

import pymssql

conn = pymssql.connect(server='hqdbt01', user='SQLShackDemo', password='SQLShackDemo$', database='SQLShackDemo')

cursor = conn.cursor()

cursor.execute('select servername,dbname,status from [tbl_DbInventory];')

row = cursor.fetchone()

while row:

print (str(row[0]) + " " + str(row[1]) + " " + str(row[2]))

row = cursor.fetchone()

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.

Appendix (A)

import pyodbc

DBInstance = 'HQDBT01'

DB = 'SQLShackDemo'

username = 'SQLShackDemo'

password = 'SQLShackDemo$'

cnxn_1 = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+DBInstance+';PORT=1443;DATABASE='+DB+';UID='+username+';PWD='+ password)

cursor_1 = cnxn_1.cursor()

with open('c:\server.txt', 'r') as infile :

lines = infile.read().splitlines()

for line in lines:

server = line

database = 'Master'

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';PORT=1443;DATABASE='+database+';Trusted_Connection=YES')

cursor = cnxn.cursor()

print ('Reading data from table')

tsql = """

SELECT

@@SERVERNAME serverName,

database_id,

CONVERT(VARCHAR(25), DB.name) AS dbName,

CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],

state_desc,

(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,

(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],

(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,

(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],

user_access_desc AS [User access],

recovery_model_desc AS [Recovery model] FROM sys.databases DB

ORDER BY dbName;"""

with cursor.execute(tsql):

row = cursor.fetchone()

while row:

print (str(row[0])+" "+str(row[1])+" "+str(row[2])+ " "+str(row[3])+" "+str(row[4])+" "+str(row[5])+" "+str(row[6])+" "+str(row[7])+" "+str(row[8])+" "+str(row[9])+" "+str(row[10]))

tsql_1 = "INSERT INTO [tbl_DbInventory] (ServerName,database_id, dbName, Status, state_desc, DataFiles, DataMB, LogFiles, LogMB, Useraccess, Recoverymodel) VALUES (?,?,?,?,?,?,?,?,?,?,?);"

with cursor_1.execute(tsql_1,str(row[0]),row[1],str(row[2]),str(row[3]),str(row[4]),row[5],row[6],row[7],row[8],str(row[9]),str(row[10])):

print ('Successfuly Inserted!')

row = cursor.fetchone()

Author
Recent Posts

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

Latest posts by Prashanth Jayaram (see all)

Stairway to SQL essentials - April 7, 2021
A quick overview of database audit in SQL - January 28, 2021
How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021

Technical overview

Implementation

Python psmssql driver

Wrapping Up

Appendix (A)

Related posts: