Using IronPython to talk to SQL Server

,

IronPython? What's that?

IronPython is an implementation of the Python programming and scripting language built atop .NET.  It was originally created by Jim Hugunin, then kept going by Microsoft until late 2010.  Since then, it has become an open source project.  Currently it is compatible with Python 2.7.

Why should I care?

Many students learn Python during introductory Computer Science courses (including my alma mater, the University of Toronto) before going on to do other things.  Python remains true to its mantra of "easy to learn; hard to forget" and is easier to learn than many curly-brace programming languages.  Adding .NET to the mix with IronPython captilizes on Python's main advantagfes while adding all the power of .NET.   In particular, we can access SQL Server Management Objects (SMOs) using Python.  

So, how do I get started?

Head on over to http://ironpython.net/download/ and download the latest copy of the language.  You might also like to install Python Tools for Visual Studio, so you can use VS (2012 and up) to create Python projects and programs.  PTVS is available via NuGet and the VS Extensions manager.

What can I do with it?

If you are using PTVS, create a new Python project and give it any name you like.  If not, any old editor will do: just create a directory for your code.  

For your first project, copy and paste this code into a file in the project called helpers.py

 
import re
# Pattern to match for objects with no __repr__ method
pattern = re.compile('^<.*>$')
def get_properties(obj):
    for member in filter(lambda x: not x.startswith('__'), dir(obj)):
        try:
            attr = getattr(obj, member)
            if not callable(attr):
                if not pattern.match(str(attr)):
                    yield member, attr
        except Exception as ex:
            # Discard attributes throwing exceptions
            continue
def print_properties(obj):
    for prop, value in get_properties(obj):
        print '{:50}: {}'.format(prop, value)

Next, in the same directory, create a file called smo.py.  It should look like this:

from helpers import print_properties
import clr
clr.AddReference("Microsoft.SqlServer.SMO")
import Microsoft.SqlServer.Management.Smo as Smo
# Local database
server = Smo.Server('(localdb)\mssqllocaldb')
print server.ServerType
print_properties(server.Databases['master'])

You can run this from the command line by changing to the directory you saved the files in and typing

ipy.exe smo.

(Note: You might have to specify the complete path to ipy.exe, dependint on you you installed it.) 

You should see output like this:

ActiveConnections                                 : 16
This method or property is accessible only while working against a version earlier than SQL Server 2012.
AnsiNullDefault                                   : False
AnsiNullsEnabled                                  : False
AnsiPaddingEnabled                                : False
AnsiWarningsEnabled                               : False
ArithmeticAbortEnabled                            : False

For your second try, add these lines to the program:

clr.AddReference("Microsoft.SqlServer.ConnectionInfo")
import Microsoft.SqlServer.Management.Common as Common
my_server = 'mhknbn2kdz.database.windows.net'
my_database = 'AdventureWorks2012'
my_userid = 'sqlfamily'
my_password = 'sqlf@m1ly'
conn = Common.ServerConnection()
conn.ConnectionString = 'server={};database={};user={};password={}'.format(my_server, my_database, my_userid, my_password)
server = Smo.Server(conn)
print server.ServerType
print_properties(server.Databases['AdventureWorks2012'])

This code should access the free, Azure-based copy of AdventureWorks and report on the basic database properties.  When I do this, I get:

ActiveConnections: unknown property.
This method or property is accessible only while working against a version earlier than SQL Server 2012.
AnsiNullDefault                                   : False
AnsiNullsEnabled                                  : False
AnsiPaddingEnabled                                : False
AnsiWarningsEnabled                               : False
ArithmeticAbortEnabled                            : False
AutoClose                                         : False
... and so on

What Now?

Now that you have some basic examples working, you can experiment with other properties. Try some of these:

for table in server.Databases['AdventureWorks2012'].Tables:
    print table.Name
for table in db.Tables:
    if table.Name == 'Store':
        print table.Name
        print_properties(table)
print_properties(db.Tables[9])

Summary

IronPython is a rich, easy-to-learn language built in .NET.  Among other things, this means you can use it to talk to SQL Server using SMO.

Rate

4.5 (2)

Share

Share

Rate

4.5 (2)