Migrating from 1.x to 2.x

Because of the DB-API standard and because effort was made to make the interface of pymssql 2.x similar to that of pymssql 1.x, there are only a few differences and usually upgrading is pretty easy.

There are a few differences though…

str vs. unicode

Note that we are talking about Python 2, because pymssql 1.x doesn’t work on Python 3.

pymssql 1.x will return str instances:

>>> pymssql.__version__
'1.0.3'
>>> conn.as_dict = True
>>> cursor = conn.cursor()
>>> cursor.execute("SELECT 'hello' AS str FROM foo")
>>> cursor.fetchall()
[{0: 'hello', 'str': 'hello'}]

whereas pymssql 2.x will return unicode instances:

>>> pymssql.__version__
u'2.0.1.2'
>>> conn.as_dict = True
>>> cursor = conn.cursor()
>>> cursor.execute("SELECT 'hello' AS str FROM foo")
>>> cursor.fetchall()
[{u'str': u'hello'}]

If your application has code that deals with str and unicode differently, then you may run into issues.

You can always convert a unicode to a str by encoding:

>>> cursor.execute("SELECT 'hello' AS str FROM foo")
>>> s = cursor.fetchone()['str']
>>> s
u'hello'
>>> s.encode('utf-8')
'hello'

Handling of uniqueidentifier columns

SQL Server has a data type called uniqueidentifier.

In pymssql 1.x, uniqueidentifier columns are returned in results as byte strings with 16 bytes; if you want a uuid.UUID instance, then you have to construct it yourself from the byte string:

>>> cursor.execute("SELECT * FROM foo")
>>> id_value = cursor.fetchone()['uniqueidentifier']
>>> id_value
'j!\xcf\x14D\xce\xe6B\xab\xe0\xd9\xbey\x0cMK'
>>> type(id_value)
<type 'str'>
>>> len(id_value)
16
>>> import uuid
>>> id_uuid = uuid.UUID(bytes_le=id_value)
>>> id_uuid
UUID('14cf216a-ce44-42e6-abe0-d9be790c4d4b')

In pymssql 2.x, uniqueidentifier columns are returned in results as instances of uuid.UUID and if you want the bytes, like in pymssql 1.x, you have to use uuid.UUID.bytes_le to get them:

>>> cursor.execute("SELECT * FROM foo")
>>> id_value = cursor.fetchone()['uniqueidentifier']
>>> id_value
UUID('14cf216a-ce44-42e6-abe0-d9be790c4d4b')
>>> type(id_value)
<class 'uuid.UUID'>
>>> id_value.bytes_le
'j!\xcf\x14D\xce\xe6B\xab\xe0\xd9\xbey\x0cMK'

Arguments to pymssql.connect

The arguments are a little bit different. Some notable differences:

In pymssql 1.x, the parameter to specify the host is called host and it can contain a host and port – e.g.:

conn = pymssql.connect(host='SQLHOST:1433')  # specified TCP port at a host

There are some other syntaxes for the host parameter that allow using a comma instead of a colon to delimit host and port, to specify Windows hosts, to specify a specific SQL Server instance, etc.

conn = pymssql.connect(host=r'SQLHOST,5000')  # specified TCP port at a host
conn = pymssql.connect(host=r'(local)\SQLEXPRESS')  # named instance on local machine [Win]

In pymssql 2.x, the host parameter is supported (I am unsure if it has all of the functionality of pymssql 1.x). There is also a parameter to specify the host that is called server. There is a separate parameter called port.

conn = pymssql.connect(server='SQLHOST', port=1500)

Parameter substitution

For parameter substitution, pymssql 2.x supports the format and pyformat PEP 249 paramstyles.

Note that for pyformat, PEP 249 only shows the example of a string substitution – e.g.:

%(name)s

It is not clear from PEP 249 whether other types should be supported, like:

%(name)d
%(name)f

However, in this mailing list thread, the general consensus is that the string format should be the only one required.

Note that pymssql 2.x does not support %(name)d, whereas pymssql 1.x did. So you may have to change code that uses this notation:

>>> pymssql.__version__
u'2.0.1.2'
>>> pymssql.paramstyle
'pyformat'

>>> cursor.execute("select 'hello' where 1 = %(name)d", dict(name=1))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "pymssql.pyx", line 430, in pymssql.Cursor.execute (pymssql.c:5900)
    if not self._source._conn.nextresult():
pymssql.ProgrammingError: (102, "Incorrect syntax near '('.
DB-Lib error message 20018, severity 15:\n
General SQL Server error: Check messages from the SQL Server\n")

to:

>>> cursor.execute("select 'hello' where '1' = %(name)s", dict(name='1'))
>>> cursor.fetchall()
[(u'hello',)]

or:

>>> cursor.execute("select 'hello' where 1 = %d", 1)
>>> cursor.fetchall()
[(u'hello',)]

Examples of this problem: