Sunday, September 30, 2012

Connect to MS SQL Server using Python

Steps to take to connect to a MS SQL Server using Python. In this case using the python ODBC module pyodbc.
1. Download pyodbc from, get the newest 32-bit version (even if you have 64-bit OS), since you need to have a paid license to use the 64-bit version. The 32-bit version is free.
2. Check what ODBC client you have for MS SQL Server. Go to Control Panel -> Administrative Tools -> Data Sources (ODBC) -> Drivers tab (on Windows 7). You will hopefully see a list of drivers to choose from. I had SQL Server and SQL Server Native Client 10.0. You will need the whole name in the next step.
3. Here is a sample program to fetch data from a table:
# -*- coding: utf-8 -*-
import pyodbc

Driver={SQL Server Native Client 10.0};

db = pyodbc.connect(CONNECTION_STRING)
c = db.cursor()
c.execute ('SELECT * FROM users')
rs = c.fetchall()
for r in rs:
    print r[0]

Notice how we put the driver name from step 2. into the Driver parameter, the Server parameter should point to your server and the Database should be the name of your database. The triple quotations is just to let us have the connection string on multiple lines in the code, you could use a single quotation mark and have it all on the same line.


No comments:

Post a Comment