Connecting to SQL Server with pypyodbc

Question

I need to send unicode to SQL Server with Python 2.7. I failed with pymssql. I'm now trying to get pypyodbc working (as opposed to pyodbc), as it gives working unicode examples. The problem is that the connection string in the example doesn't look like anything I recognize. I looked at this, and, after a little trial and error, constructed this string:

conn = pypyodbc.connect("DRIVER={SQL Server};SERVER='MyServer';UID='me';PWD='MyPassword';DATABASE='db'")

Got back a DatabaseError focused on the connection string:

C:\Anaconda\lib\site-packages\pypyodbc.pyc in __init__(self, connectString, autocommit, ansi, timeout, unicode_results, readonly, **kargs)
---> 2 conn = pypyodbc.connect("DRIVER={SQL Server};SERVER='MyServer';UID='me';PWD='password';DATABASE='db'")

C:\Anaconda\lib\site-packages\pypyodbc.pyc in __init__(self, connectString, autocommit, ansi, timeout, unicode_results, readonly, **kargs)
---> 2273         self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)

C:\Anaconda\lib\site-packages\pypyodbc.pyc in connect(self, connectString, autocommit, ansi, timeout, unicode_results, readonly)
---> 2321         check_success(self, ret)

C:\Anaconda\lib\site-packages\pypyodbc.pyc in ctrl_err(ht, h, val_ret, ansi)
---> 919                 raise DatabaseError(state,err_text)

DatabaseError: (u'08001', u'[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.')

I know my credentials are correct because I've used them to connect successfully using pymssql. What am I missing?

Answer 1

Remove the single quotes from the server, uid, pwd, and database attributes of the connection string:

conn = pypyodbc.connect("DRIVER={SQL Server};SERVER=MyServer;UID=me;PWD=password;DATABASE=db")

Since pypyodbc mentions compatibility with pyodbc, take a minute to look over the pyodbc connection string docs and pyodbc.connect() examples. I use this syntax in pyodbc:

cnxn = connect(driver='{SQL Server}', server='localhost', database='test', uid='me', pwd='me2')

Answer 2

Leaving out the port number (1433) in the connection string, threw errors at me from a Linux client (but not Windows 7). It's probably a configuration issue but I didn't have time to chase it.

Putting this out there, in case it helps someone else.