Connecting to MSSQL Server using pypyodbc on OS X

Question

I'm having problems making a basic connection to a MSSQL Database Server using pypyodbc on OS X.

I have installed both unixodbc and freetds via Homebrew

brew install unixodbc
brew install freetds

I then installed pypyodbc

mkvirtualenv test
pip install pypyodbc

When I try to make a connection it fails with:

$ python -i test.py
Traceback (most recent call last):
  File "test.py", line 20, in <module>
    c = p.connect(dsn)
  File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 2434, in __init__
    self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)
  File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 2483, in connect
    check_success(self, ret)
  File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 988, in check_success
    ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
  File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 966, in ctrl_err
    raise DatabaseError(state,err_text)
pypyodbc.DatabaseError: (u'01000', u"[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found")
>>>

My basic test.py looks like this:

#!/usr/bin/env python
# -*- coding: utf-8 -*-


import pypyodbc as p


settings = {
    "driver": "FreeTDS",
    "hostname": "mssql.local",
    "username": "testuser",
    "password": "testpass",
    "database": "testdb",
    "port": 1433
}


dsn = "DRIVER={{{driver:s}}};SERVER={hostname:s};PORT={port:d};DATABASE={database:s};UID={username:s};PWD={password:s};CHARSET=UTF8;TDS_Version=8.0".format(**settings)  # noqa

c = p.connect(dsn)

xs = c.execute("SELECT name FROM master..sysdatabases").fetchall()

There are several similar questions but they don't' seem to be addressing the problem I'm having which seems to be related to a driver issue.

$ python -i test.py
Username: IRMA_RO
Password:
Traceback (most recent call last):
  File "test.py", line 15, in <module>
    c = p.connect("DSN=na-dev;UID={0:s};PWD={1:s}".format(username, password))
  File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 2434, in __init__
    self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)
  File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 2483, in connect
    check_success(self, ret)
  File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 988, in check_success
    ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
  File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 975, in ctrl_err
    err_list.append((from_buffer_u(state), from_buffer_u(Message), NativeError.value))
  File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/site-packages/pypyodbc.py", line 482, in UCS_dec
    uchar = buffer.raw[i:i + ucs_length].decode(odbc_decoding)
  File "/Users/xxxxxxxx/.virtualenvs/symplectic_cleanup/lib/python2.7/encodings/utf_32.py", line 11, in decode
    return codecs.utf_32_decode(input, errors, True)
UnicodeDecodeError: 'utf32' codec can't decode bytes in position 0-1: truncated data
>>>

There seems to be a bug report reported here relating to this: https://code.google.com/p/pypyodbc/issues/detail?id=31

Whilst this may not solve the problem in this question with ``pyodbc`` or ``pypyodbc`` (*pure Python*) I have found another solution that seems ot have worked out-of-the-box for me from http://stackoverflow.com/questions/11678696/sql-server-python-and-os-x using [pymssql](https://pypi.python.org/pypi/pymssql) and the tutorial: http://pymssql.sourceforge.net/examples_pymssql.php — James Mills, Jul 02 '14 at 03:42

Dan R · Accepted Answer · 2014-09-05T14:16:06.417

I'm not sure if you solved your problem, but I just fought a similar issue and fixed it. I was getting the same "'utf32' codec can't decode bytes in position 0-1: truncated data" error.

What you need to double check is that in the odbc.ini file, the ServerName for the section is the same name in the freetds.conf file.

For instance:

odbc.ini

[old_flood]
Driver = FreeTDS
Description = RTS Old Flood
ServerName = old_flood       <----- this must be the same as the name in freetds.conf

freetds.conf

[old_flood]                  <----- This is the same as the ServerName above
    host = <server ip address>
    port = <server port>
    tds version = 8.0

Anyway hope this helps.

--------More info-------- You also need to make sure the DB user you created has proper access to the Database you are trying to read/write to. For me, I just needed read access. You set this up in the Security node of the SQL Server Management Studio. Right click on the user then go to User Mapping. Check the appropriate databases, make sure to highlight the Database, then at the bottom set the database role.

Sadly I gave up on the python odbc drivers and used [pymssql](https://pypi.python.org/pypi/pymssql) instead. But I'll accept your answer anyway as you've probably verified that it works :) — James Mills, Sep 07 '14 at 01:37
That error is so non specific that I may or may not have gone down the same road as you. Anyway, if you pick it up to try again, maybe this would help. — Dan R, Sep 08 '14 at 11:43

Connecting to MSSQL Server using pypyodbc on OS X

1 Answers1