What's the simplest way to access mssql with python or ironpython?

Question

I've got mssql 2005 running on my personal computer with a database I'd like to run some python scripts on. I'm looking for a way to do some really simple access on the data. I'd like to run some select statements, process the data and maybe have python save a text file with the results.

Unfortunately, even though I know a bit about python and a bit about databases, it's very difficult for me to tell, just from reading, if a library does what I want. Ideally, I'd like something that works for other versions of mssql, is free of charge and licensed to allow commercial use, is simple to use, and possibly works with ironpython.

Doubloon

Answer 1

I use SQL Alchemy with cPython (I don't know if it'll work with IronPython though). It'll be pretty familiar to you if you've used Hibernate/nHibernate. If that's a bit too verbose for you, you can use Elixir, which is a thin layer on top of SQL Alchemy. To use either one of those, you'll need pyodbc, but that's a pretty simple install.

Of course, if you want to write straight SQL and not use an ORM, you just need pyodbc.

Lamkin

Answer 2

Everyone else seems to have the cPython -> SQL Server side covered. If you want to use IronPython, you can use the standard ADO.NET API to talk to the database:

import clr
clr.AddReference('System.Data')
from System.Data.SqlClient import SqlConnection, SqlParameter

conn_string = 'data source=<machine>; initial catalog=<database>; trusted_connection=True'
connection = SqlConnection(conn_string)
connection.Open()
command = connection.CreateCommand()
command.CommandText = 'select id, name from people where group_id = @group_id'
command.Parameters.Add(SqlParameter('group_id', 23))

reader = command.ExecuteReader()
while reader.Read():
    print reader['id'], reader['name']

connection.Close()

If you've already got IronPython, you don't need to install anything else.

Lots of docs available here and here.

Diatessaron

That example seems a bit bloated. – Leena

Unfortunately, ADO.NET is a bit more verbose than the corresponding Python DBAPI code (and this is true of the .NET class library in general). But it's not too bad, and easy enough to wrap in a DBAPI module. I've done this for working with SQLAlchemy. – Diatessaron

Very good point, but don't forget that SMO is another option. – Gunning

It's (apparently) possible to get SQLAlchemy working with IronPython #2999058 – Bourgeoisie

Answer 3

pyodbc comes with Activestate Python, which can be downloaded from here. A minimal odbc script to connect to a SQL Server 2005 database looks like this:

import odbc

CONNECTION_STRING="""
Driver={SQL Native Client};
Server=[Insert Server Name Here];
Database=[Insert DB Here];
Trusted_Connection=yes;
"""

db = odbc.odbc(CONNECTION_STRING)
c = db.cursor()
c.execute ('select foo from bar')
rs = c.fetchall()
for r in rs:
    print r[0]

Millwater

I am a major fan of Pyodbc. It is extremely simple and easy to use with sql server and many other data sources as well. – Gunning

Answer 4

I also successfully use pymssql with CPython. (With and without SQLAlchemy).

Celaeno

For the record, I believe that pymssql support in SQLAlchemy is deprecated. The recommended driver is pyodbc (that's not to say that pymssql won't work standalone). – Lamkin

Answer 5

http://adodbapi.sourceforge.net/ can be used with either CPython or IronPython. I have been very pleased with it.

Birdsall

Answer 6

PyPyODBC (http://code.google.com/p/pypyodbc) works under PyPy, Ironpython and CPython.

This article shows a Hello World sample of accessing mssql in Python.

PyPyODBC has almostly same usage as pyodbc, as it can been seen as a re-implemenation of the pyodbc moudle. Because it's written in pure Python, it can also run on IronPython and PyPy.

Actually, when switch to pypyodbc in your existing script, you can do this:

#import pyodbc               <-- Comment out the original pyodbc importing line

import pypyodbc as pyodbc    # Let pypyodbc "pretend" the pyodbc

pyodbc.connect(...)          # pypyodbc has 99% same APIs as pyodbc

...

Bostic

Answer 7

I've used pymssql with standard python and liked it. Probably easier than the alternatives mentioned if you're just looking for basic database access.

Sample code.

Cahan

Answer 8

If you are want the quick and dirty way with CPython (also works for 3.X python):

Install PYWIN32 after you install python http://sourceforge.net/projects/pywin32/files/pywin32/

Import the following library: import odbc

I created the following method for getting the SQL Server odbc driver (it is slightly different in naming depending on your version of Windows, so this will get it regardless):

def getSQLServerDriver():
    key = winreg.OpenKey(winreg.HKEY_LOCAL_MACHINE, r"SOFTWARE\ODBC\ODBCINST.INI")
    sqlServerRegExp =  re.compile('sql.*server', re.I | re.S)

    try:
        for i in range(0, 2048):
            folder = winreg.EnumKey(key, i)
            if sqlServerRegExp.match(folder):
                return folder.strip()
    except WindowsError:
        pass

Note: if you use the above function, you'll need to also import these two libraries: winreg and re

Then you use the odbc API 1 information as defined here: http://www.python.org/dev/peps/pep-0248/

Your connection interface string should look something like this (assuming you are using my above method for getting the ODBC driver name, and it is a trusted connection):

dbString = "Driver={SQLDriver};Server=[SQL Server];Database=[Database Name];Trusted_Connection=yes;".replace('{SQLDriver}', '{' + getSQLServerDriver() + '}')

This method has many down sides. It is clumsy because of only supporting ODBC API 1, and there are a couple minor bugs in either the API or the ODBC driver that I've run across, but it does get the job done in all versions of CPython in Windows.

Collagen