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.

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.

Answer 2

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

Answer 3

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.

Answer 4

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]

Answer 5

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.

Answer 6

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