pymssql - simple MS SQL Python extension module

pymssql — simple MS SQL Python extension module

by 박준철 - Park joon-cheol -

(Initial Version Developer)
Andrzej Kukuła

(Old Developer)
Damien Churchill

(Active Developer)

pymssql is the Python language extension module that provides access to Microsoft SQL Servers from Python scripts.
It is compliant with Python DB-API 2.0 Specification.
pymssql project has moved to Google Code website at http://code.google.com/p/pymssql/. This page is not maintained and will not be updated anymore. Its contents is outdated and relevant to old versions. Latest stable release on this site is 1.0.2.

pymssql examples (strict DB-API compliance):

import pymssql
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase')
cur = conn.cursor()
cur.execute('CREATE TABLE persons(id INT, name VARCHAR(100))')
cur.executemany("INSERT INTO persons VALUES(%d, %s)", \
    [ (1, 'John Doe'), (2, 'Jane Doe') ])
conn.commit()  # you must call commit() to persist your data if you don't set autocommit to True

cur.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
row = cur.fetchone()
while row:
    print "ID=%d, Name=%s" % (row[0], row[1])
    row = cur.fetchone()

# if you call execute() with one argument, you can use % sign as usual
# (it loses its special meaning).
cur.execute("SELECT * FROM persons WHERE salesrep LIKE 'J%'")

conn.close()

You can also use iterators instead of while loop. Iterators are DB-API extensions, and are available since pymssql 1.0.

Rows as dictionaries
Since pymssql 1.0.2 rows can be fetched as dictionaries instead of tuples. This allows for accessing columns by name instead of index.

import pymssql
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase', as_dict=True)
cur = conn.cursor()

cur.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in cur:
print "ID=%d, Name=%s" % (row['id'], row['name'])

conn.close()