Microsoft SQL Server Express / Python HOWTO

  1. Background
  2. Download and Installation
  3. SQL Setup
    1. Allow TCP/IP
    2. Create Your Database
    3. Create the ODBC Data Source
  4. Code Some Python!

Background

We are probably going to be using Microsoft SQL Server at my work soon. I found out that Microsoft offers a free (as in beer) version of many of their development tools, so I decided to download the SQL server and get familiar with it. I like Python, but I had a bit of trouble getting Python to work with Microsoft SQL Server. (I guess this is because I am a Windows luser.) It turns out to be easy, so I thought I might spare someone else the pain that I had.

So, this is how to use Microsoft SQL Server Express with Python. We use ODBC for this.

This is not a HOWTO about Microsoft SQL Server Express, or about Python, or about SQL programming.

Download and Installation

Microsoft has it's various "express" products on a single page:

http://lab.msdn.microsoft.com/express/

These are time-limited beta versions. Hey, what do you want for free?

To get Microsoft SQL Server Express, you can either download it directly, or download one of the other products that also includes the server. I downloaded Visual C++ 2005 Express Edition Beta 2, and ran the installation for that. One of the optional components was the SQL Server, so I used that.

After the installation, I registered and got a product code.

You should also download the SQL Server Express Manager and install that. This is on the SQL Server Express page:

http://lab.msdn.microsoft.com/express/sql/default.aspx

To get Python, you can either use the one from the main Python web site:

http://www.python.org/download/

Or you can get ActivePython:

http://www.activestate.com/Products/ActivePython/

Installation is straightforward, I used defaults for everything.

SQL Setup

Allow TCP/IP

I'm not 100% sure, but you seem to need TCP/IP connections to use ODBC. To do this, run the "SQL Server 2005 Surface Area Configuration":

Start
    » All Programs
        » Microsoft SQL Server 2005 CTP
            » Configuration tools
                » SQL Server Surface Area Configuration



Click on "Surface Area Configuration for Services". In the window you get, click on "Remote Connections" and select "Local and Remote Connections", and choose "Using TCP/IP protocol only". You will end up with this:



Click "OK" and close the application.

Create Your Database

Run the "SQL Server Express Manager":

Start
    » All Programs
        » Microsoft SQL Server 2005 CTP
            » SQL Server Express Manager

The name of the server is ".\SQLEXPRESS":



Right-click on "Databases", and select "New Database...":



Enter in the name of your new database:



Click "OK". If you want to create or populate tables, you can do that with the Query Editor window with SQL. Otherwise we're done, so close the application.

Create the ODBC Data Source

The program you need for this is buried in the control panel, but you can run it directly by:

Start
    » Run...
       » Type: "odbcad32"



Click "Add...", select "SQL Server" as the driver, click the poorly-named "Finish" button.

Next, enter in any appropriate name and description, using the server ".\SQLEXPRESS":



Click "Next". On the following screen, click the box next to "Change the default database to" and select the name of the database you have created earlier:



Click "Next". On the following screen, click the still poorly named "Finish" button.

You can test your data source, or just click the yet again poorly named "OK" button, which actually finishes the process.

Now you are back at the main screen. Click the misnamed "OK" button to exit.

Code Some Python!

At this point, you're ready to code some Python. There is a commercial ODBC connector (free as in beer), but I was able to work with the one that came with Python. This is the "Batteries Included", language, isn't it? The standard odbc module implements the DB-API spec v1.0, but this is good enough for my purposes.

First, test to make sure things are working:
>>> import dbi
>>> import odbc
>>> conn=odbc.odbc("DSN=odbc-moo-cows")
Use the name of the ODBC Data Source you just added. If you don't get an exception here - congratulations! You are now using your Microsoft SQL Server Express with Python:
>>> cursor=conn.cursor()
>>> cursor.execute("CREATE TABLE foo (bar INT)")
-1
>>> cursor.execute("INSERT INTO foo (bar) VALUES (42)")
1
>>> cursor.execute("SELECT * FROM foo")
0
>>> print cursor.fetchall()
[(42,)]
>>> cursor.execute("DROP TABLE foo")
-1

That's it. Enjoy.

Shane Kerr
shane@time-travellers.org
2005-08-17