为什么SQL Server DBA对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:

如果我们关注有关SQL的技术进步的博客和出版物，则会发现最近对Python的引用数量有所增加。 通常，这使我们想到：

• Why so much emphasis on Python these days?
  这些天为什么这么强调Python？
• Isn’t knowing PowerShell scripting sufficient for the automation requirements of today?
  知道PowerShell脚本不足以满足当今的自动化要求吗？
• Is it the time DBAs started learning a programming language such as Python in order to handle their day-to-day tasks more efficiently?
  现在是DBA开始学习诸如Python之类的编程语言以更有效地处理其日常任务的时候了吗？
• 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?
  所有这些都是因为范式在发生变化吗？ 当前的Microsoft特定语言（例如PowerShell）不能应对这种变化吗？

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

当SQL 2017发布时，它使数据库管理员对两件事大为震惊：

• SQL Server became a cross-platform product
  SQL Server成为跨平台产品
• SQL Server started supporting the enrichment of Machine Learning capabilities
  SQL Server开始支持丰富的机器学习功能

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.

尽管TSQL和PowerShell cmdlet具有足够的灵活性以使数据库活动更加顺畅，但使该平台成为一种通用的平台，SQL的重要性日益提高，并且该产品向Linux开放，使更多的管理员开始研究SQL可以提供的功能。

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.

在使用分析工具时，Python是一种通用语言，被认为是上下文中可用的最佳语言之一。 实际上，Python完全具有与大量数据进行交互，处理复杂数学和数据操作/清理的能力。

“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?”

“好的，因此Python是Linux管理员常用的语言之一。 但是，PowerShell是否不是在MIT许可证下开源的，也可以在Linux上使用？ 在Linux上使用SQL尚无帮助吗？ 为什么还要添加对Python的支持？ 我们如何开始在那里？”

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

事实证明，Python不难学习。 此外，学习Python是添加到箭袋中的另一个箭头。 为什么不具备附加功能，并保持开放精神？ 让我们开始吧，看看如何使用Python来实现一些常规任务

技术概述 (Technical overview)

This article will provide an overview of the following technical implementations

本文将概述以下技术实现

• Prepare a connection string
  准备连接字符串
• Read a file
  读取文件
• Define complex SQL
  定义复杂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:

在本文中，我们将针对SQL和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
   加载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.

现在让我们继续创建专用表。 下面SQL用于在数据库上创建专用表。 列出的列包含数据库的一些高级详细信息。

 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
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]
GO
 

Python SQL Driver

Python SQL驱动程序

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

我们使用开源API模块将Python与SQL Server桥接。 在继续之前，这是前提条件：

1. pyodbc modulepyodbc模块
   or
   要么
2. pymssql module pymssql模块

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.

单击相应驱动程序的下载链接，并确保选择正确的WHL文件。 该文件对于不同的平台是不同的。 下载WHL文件后，将其放置在正确的文件夹中。 就我而言，我下载了文件并将其保存在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.

现在，让我们继续安装模块。 打开命令提示符并运行以下命令。 确保从放置WHL文件的位置运行它。

 
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.

需要pyodbc / pymssql模块才能通过ODBC连接到SQL Server。 这是Python如何与不同操作系统配合使用的示意图。

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.

Python默认情况下为我们提供了操作文件的基本功能和方法。 您可以使用文件对象执行大多数文件操作。

 
with open('c:\server.txt', 'r') as infile :
        lines = infile.read().splitlines()
for line in lines:
    print (line)
 

Setup the Connection String

设置连接字符串

1. 1. • 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)
       
      

      
      
      
      
   2. • Instance Name
        实例名称
      • Database
        数据库
      • Trusted_connection Parameter
        Trusted_connection参数

       
      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.

查询字符串文字放在三引号中。 最好将复杂SQL查询嵌入到引号中。 这样，我们可以绕过Python严格的缩进样式。

Pass the query string into the cursor method:

将查询字符串传递给cursor方法：

 
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.

TSQL字符串是针对游标的当前作用域执行的。 然后，使用while循环构造对数据进行转换，以将数据馈送到专用实例中。

 
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.

完整的代码位于附录（A）中 。 另外，以下示例显示了缩进在Python中的重要性。

The output of SQL table

SQL表的输出

Execute Update and Select T-SQLs

执行更新并选择T-SQL

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

在以下示例中，“ 服务器名称 ”列被赋予值SQLShack 。 稍后，我们使用select语句检索数据。

 
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.

现在让我们来看一些示例“执行删除并选择T-SQL”。

 
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驱动程序 (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.

pymssql.connect方法用于连接到SQL实例和数据库。 在我们的示例中，我们查询库存表并检索数据。

 
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.

这是我们刚刚发出的select语句的输出。

结语 (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.

Python有很多优点，因此值得关注。 除了免费和开源软件（FOSS）之外，它更易于学习。 即使不是Python程序员的人也很容易阅读。 Python可以用来编写全面的应用程序和服务器软件，但是作为数据库管理员，我们会发现它更有趣，因为它在编写快速实用程序脚本时非常方便。 当然，它的使用不仅限于此。 正如我们在文章中看到的，事实证明Python在机器学习中越来越有用。 我还在文章如何在SQL Server 2017中使用Python来获取高级数据分析中谈到了在Analytics（分析）中使用Python 。

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.

考虑到那里有很多Python开发人员，现在有了可用于LinuxSQL Server，如果您需要对这种设置进行故障排除，那么对于我们中的一些人来说，这可能是一个学习使用Python的好地方在不远的将来。

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.

当然，SQL数据库管理员已经利用PowerShell来管理数据库和数据库服务器，并且非SQL管理员可能在Python方面占上风。 但是，当有多种管理SQL Server的方法时，知道不止一种方法来通过API实现近乎完美的管理就不会有什么坏处。

附录（A） (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()
 

翻译自: https://www.sqlshack.com/sql-server-dba-interested-python/