Wednesday, December 18, 2013

Getting pymssql to work

The goal at the outset was to get pymssql working with peewee, more specifically so I can build a set of class objects from a remote sqlserver database.

When I first tried to use pymssql to actually retrieve data from a mssql server, I tried using pymssql 1.0.2 with FreeTDS 0.91.  Nothing came back. 

I tried queries like:

"select * from sys.tables"


"SELECT name FROM sysobjects WHERE (OBJECTPROPERTY(id, N'IsTable') = 1) AND (name NOT LIKE N'#%') AND (OBJECTPROPERTY(id, N'IsMSShipped') = 0) AND (OBJECTPROPERTY(id, N'IsSystemTable') = 0) ORDER BY name ASC;"

 I tried the same queries using tsql, and they returned results as expected.

The latter is the query that QCubed uses to get a list of tables from mssql, but the former works too.

I checked the traffic, and pymssql was connecting and sending queries just fine, and the remote server was sending results just fine, but nothing would show up in python.  Fetchall() returned nothing.

So I found this launchpad bug entry, and in it someone was using pymssql version 1.9.912  Removing 1.0.2 and installing 1.9.912 worked.  The other suggestion in the thread to pass multiple arguments in 1.0.2, did not work.  I downloaded the tarball from here, and 'python build' 'python install' worked.

Note1:  This was on scientific linux 5.7, python 2.7.2.  Work with pymssql & peewee follows.