pymssql — simple MS SQL Python extension module
by 박준철 - Park joon-cheol - Park Joon-cheol (Initial Version Developer)
Andrzej Kukuła Andrzej Kukula (Old Developer)
Damien Churchill 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.

Architecture and configuration

pymssql on Windows

pymssql on Windows doesn't require any additional components to be installed. The only required library, ntwdblib.dll, is included with pymssql, and it's all that is needed to make connections to Microsoft SQL Servers. It is called DB Libary for C, and is documented here. This library is responsible for low level communication with SQL Servers, and is extensively used by _mssql module.

Typically you don't need any configuration in order to make it work. However I wrote a few paragraphs about more sophisticated configuration in Advanced information.

On Windows in addition to authenticating to SQL using user name and password, you can also authenticate using so called Windows Authentication, or Trusted Connection. In this mode SQL Server validates the connection using user's security token provided by the operating system. In other words, user's identity is confirmed by Windows, so SQL trusts it (hence trusted connection). It is suggested method of connecting, because is eliminates the need for hard coding passwords in clear text in scripts.
# An example on how to connect using Windows Integrated Authentication.
conn = _mssql.connect('sqlhost', trusted=True)
conn = pymssql.connect(host='sqlhost', trusted=True)

pymssql on Linux/*nix

Linux/*nix on this webpage refers to any operating system different than Microsoft Windows, i.e. Linux, BSD, Solaris, MacOS etc.
pymssql on these platforms require a low level driver that is able to speak to SQL Servers. This driver is implemented by FreeTDS package. FreeTDS can speak to both Microsoft SQL Servers and Sybase servers, however there is a problem with dates, which is described in more details on FreeTDS and Dates page.

You will need to configure FreeTDS. It needs nonempty configuration in order to work. Below you will find a simple examples, but for more sophisticated configurations you will need to consult FreeTDS User Guide.

# Minimal valid freetds.conf that is known to work.
[global]
    tds version = 7.0

You can connect using explicit host name and port number:

conn = _mssql.connect('sqlhost:portnum', 'user', 'password')
conn = pymssql.connect(host='sqlhost:portnum', user='user', password='password')


sSample FreeTDS configuration:

# The default instance on a host.
[SQL_A]
    host = sqlserver.example.com
    port = 1433
    tds version = 7.0

# Second (named) instance on the same host. The instance name is
# not used on Linux/*nix.
# This instance is configured to listen on TCP port 1444:
#  - for SQL 2000 use Enterprise Manager, Network configuration, TCP/IP,
#    Properties, Default port
#  - for SQL 2005 and newer use SQL Server Configuration Manager,
#    Network Configuration, Protocols for ..., TCP/IP, IP Addresses tab,
#    typically IPAll at the end.
[SQL_B]
    host = sqlserver.example.com
    port = 1444
    tds version = 7.0

An example of how to use the above configuration in _mssql:

conn_a = _mssql.connect('SQL_A', 'userA', 'passwordA')
conn_b = _mssql.connect('SQL_B', 'userB', 'passwordB')