For SQL Server management from Linux, I’ve used pymssql on Linux before, but realized lately that it is hard to configure with the heterogeneous environment I am in. So I am giving pyodbc a try. The install on Windows is simple. Listed below is some code that I put together quickly for some testing. It lists all files used by a SQL Server instance. I will test pyodbc on Linux against a SQL Server database and will write my findings in a few days.

How to reproduce:
1. Install Python 2.7 on Windows. I am not bothering with 64-bit Python at the moment;
2. Download and install pyodbc 2.7 for Windows;
3. Add c:\Python27 to your PATH variable

[sourcecode language=”Python”]
“”” The code below lists all files used by a SQL Server instance. Adjust instance and login credentials as necessary. ROWS means data file, LOG means log file “””
import pyodbc
cn = pyodbc.connect(‘DRIVER={SQL Server};SERVER=MyInstance;DATABASE=master;Trusted_Connection=yes’)
cursor = cn.cursor()
cursor.execute(“select name from sys.databases”)
rows = cursor.fetchall()
for row in rows:
sql = (“select type_desc, physical_name from %s.sys.database_files” % row.name)
cursor.execute(sql)
l1rows = cursor.fetchall()
print “Files for ” + row.name + ” :”
for l1row in l1rows:
print l1row.type_desc + ” ” + l1row.physical_name
[/sourcecode]