{"id":657,"date":"2009-08-27T00:00:00","date_gmt":"2009-08-27T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/python-for-the-sql-server-dba\/"},"modified":"2022-04-27T21:24:52","modified_gmt":"2022-04-27T21:24:52","slug":"python-for-the-sql-server-dba","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/python-for-the-sql-server-dba\/","title":{"rendered":"Python for the SQL Server DBA"},"content":{"rendered":"
\n

What is Python?<\/h2>\n

Python is a dynamic, interpreted language which was originally designed by Guido van Rossum with code readability as one of its primary goals.\u00a0 As part of that, one its most unusual features is the use of meaningful white space. Python uses code blocks that are shown and enforced through indentation.\u00a0 Although some of its extensions and libraries are specific to one operating system, the core language is available for all major operating systems and most Python scripts will run unaltered from one system to another.<\/p>\n

Why would a SQL Server DBA be interested in Python?<\/h2>\n

The Microsoft T-SQL programming language that is built into SQL Server along with the capabilities of included tools such as SSIS and SSRS form a remarkably versatile platform without any third party tools whatsoever.\u00a0 In fact, T-SQL itself is Turing complete.<\/p>\n

However, they are not the best tool for all tasks.\u00a0 For instance, SQL Server by itself does not have full support for regular expressions. While it is fully capable of interacting with the file system there are generally better tools for any complex manipulation of the operating system.\u00a0 Pure SQL is also not the strongest option for complex mathematics or complicated file munging.<\/p>\n

Python is one tool capable of handling those tasks.\u00a0 It is certainly not the only option, with Microsoft C# and PowerShell also being excellent options for some of these tasks.\u00a0 Perl and Lisp are amongst many other options, each with their own strong points.\u00a0\u00a0<\/p>\n

Python has several strong points which make it worthy of attention.\u00a0 It is free and open source software, but far more significantly it is easy to learn and easy to read even for people who are not Python programmers.\u00a0 It is capable of being used to write full scale applications and server software, but for most DBAs it is more significant that it is excellent for jotting off quick utility scripts to solve a specific problem.\u00a0 For instance, it is excellent for writing scripts to reorganize files into a new directory structure and\u00a0 automatically verify that all the automated backup files actually are where they are supposed to be each morning.\u00a0<\/p>\n

How Python can be used with SQL Server<\/h2>\n

\u00a0Python can be used to parse data that is not in a standard format so it can be imported into SQL Server.<\/p>\n

This is one of my primary uses for it for in connection with SQL. \u00a0I have received numerous files of data that was either structured in a nonstandard format or completely unstructured but with data such as e-mail addresses that need to be pulled out en mass.\u00a0 While it is possible to do these in SSIS or even pull it into a SQL Temp Table line by line and then parse it using pure T-SQL it is generally easier and faster to use Python or occasionally C# depending on the file type.\u00a0<\/p>\n

For instance, a file that is structured like this:<\/p>\n

a, a,\r\nb, b, b,\r\nc, c, c, c\r\nd, d, d\r\ne\r\n<\/pre>\n
… does not work easily with tools such as bulk insert or SSIS. [2] However Python can be used to easily determine the needed number of commas and add them to all lines, such as:<\/p>\n
#Standardizes the number of commas per line prior to being read by \r\n#programs such as BCP and required for compliance with RFC4180 for CSV files\r\n#initialize the variables, change FileNames as appropriate\r\nInFileName = r'C:\\test\\testimport.csv'\r\nOutFileName = r'C:\\test\\testimport2.csv'\r\nNumCommas = 0 \r\n\r\nFile = open(InFileName)\r\n\r\nfor line in File:\r\n&nbsp;&nbsp;&nbsp;&nbsp;if line.count(',') > NumCommas:\r\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NumCommas = line.count(',')\r\n\r\n#return to the start of the file\r\nFile.seek(0)\r\n\r\nOutFile = open(OutFileName, 'w')\r\nfor line in File:\r\n&nbsp;&nbsp;&nbsp;&nbsp;OutFile.write(line.rstrip() + ',' * (NumCommas - line.count(',')) + '\\n')\r\n\r\nOutFile.close()\r\nFile.close()\r\n\r\n<\/pre>\n
Afterwards the output file is quite amenable to Bulk Insert.<\/p>\n
There are several choices to actually connect with SQL Server within python.\u00a0 The options include the default odbc which comes as a standard library, the win32com client tools, mxODBC<\/a> (commercial product) and pyODBC<\/a>. While the odbc module will handle almost any requirement, the other options add additional features which can simplify development.<\/p>\n
Pyodbc, for example, permits access of values by name and makes cursors iterable.\u00a0 These changes from the default odbc package make it much easier to use.\u00a0 As an example,\u00a0 pyodbc can be used to connect to a SQL Server and then extract all e-mail addresses from a free form memo field.<\/p>\n
import pyodbc as p\r\nimport re #RegEx library\r\n\r\nserver = 'TWISEMAN-PC'\r\ndatabase = 'test'\r\ntable = 'salesData'\r\nfield = 'memo'\r\n\r\nconnStr = ( r'DRIVER={SQL Server};SERVER=' +\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0server + ';DATABASE=' + database + ';' +\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Trusted_Connection=yes'\u00a0\u00a0\u00a0\u00a0)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n#e-mail regex examples available at\r\n#http:\/\/www.regular-expressions.info\/email.html\r\npattern = r'\\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\\.[A-Z]{2,4}\\b'\r\nemailMatcher = re.compile(pattern, re.I)\r\nemails = [] #create a list\r\n\r\nconn = p.connect(connStr)\r\ndbCursor = conn.cursor()\r\nsql = ('select ' + field + ' as memo ' \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0' from '+ table+\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' where ' + field + ' like \\'%@%\\'') \r\ndbCursor = conn.cursor()\r\ndbCursor.execute(sql)\r\nfor row in dbCursor:\r\n\u00a0\u00a0\u00a0\u00a0newEmails = emailMatcher.finditer(row.memo)\r\n\u00a0\u00a0\u00a0\u00a0if newEmails is not None:\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0emails.extend([addr.group() for addr in newEmails])\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\nconn.close()\r\nprint(\u00a0emails)<\/pre>\n
A similar script can scan an unstructured text document to extract e-mail addresses and insert them into a SQL table.\u00a0 Of course, it is often more useful to be able to call stored procedures.\u00a0 This can be done with pyodbc by sending the exec command for the procedure with all parameters through the execute method.\u00a0 Many of the libraries automatically begin transactions, so those most be committed or rolled back in the code, though some such as pyodbc permit a connection to be opened in autocommit mode.<\/p>\n
How to acquire Python<\/h2>\n