python and pymssql

Question

I'm new to python. I'm trying to query a MSSQL database.

---

import pymssql
conn = pymssql.connect(host='hostname', user='username', password='password', database='dbname')
cursor = conn.cursor()
sql = "select count(*) from T_Email with (nolock) where Transmit is null"
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
 print (row)

---

The query successfully runs is Microsoft SQL Server Management Studio, but my python script always returns nothing.

I verified I have network connectivity. I verified the username, password and database name. If I change the password, then the script will give an error.

I have tried results = cursor.fetchone(), but that didn't help.

Any suggestions?

• Urjit: Can you try and print the results object and tell whats the output?
• kefeizhou: @Magd i'm having the exact same issue, did you figure out what was wrong?

Answer 1

If you're using Ubuntu you may have used (like me) apt-get to install pymssql package.

This is a known bug of the bundled version: https://bugs.launchpad.net/ubuntu/+source/pymssql/+bug/918896

Try to install it manually using easy_install.

Answer 2

I had the same issue on Ubuntu 12.04, indeed the fix is doing the sequence:

$ apt-get purge python-pymssql
$ apt-get install freetds-dev
$ pip install Cython
$ pip install pymssql

Answer 3

Try adding a conn.commit() to your query

• fileoffset: between the execute and fetchall, to be precise ;)
• Urjit: Will that be needed even if we are only reading from the database using pymssql??
• user2430259: >>> conn.commit() >>> >>> results = cursor.fetchall() Traceback (most recent call last): File "<stdin>", line 1, in ? File "/usr/lib64/python2.4/site-packages/pymssql.py", line 301, in fetchall raise OperationalError, "No data available." pymssql.OperationalError: No data available. fetchone() doesn't work either
• Amir Ali Akbari: SELECT queries don't need a commit, doing a commit will only make fetchall to raise OperationalError

Answer 4

import pymssql
conn = pymssql.connect(
server="server",
port=port,
user="user",
password=password,
database="database")
conn
cursor = conn.cursor()
cursor.execute("select count(*) from T_Email with (nolock) where Transmit is null")
 for row in cursor.fetchall(): 
 print ("%s\n" % str(row))
conn.close()

Answer 5

Without sufficient information to reproduce the example, it's hard to say the specific problem you're having. However, here are a few guesses I have as for possible problems:

1. Maybe your actual column name (presuming your example above was just a mock up) is too long. See: http://code.google.com/p/pymssql/wiki/FAQ (look for Column names get silently truncated to 30 characters. (1.x only) ) This is a common one to trip folks up because it SILENTLY fails!!
2. If you are creating tables before querying into them, or other things that require commits, it can mess things up, even with autocommit turned on ( autocommit(1) ). See my answer to myself :) at pymssql ( python module ) unable to use temporary tables

Good luck!

Mike

Answer 6

Change the following lines in your code snippet

results = cursor.fetchall()
for row in results:
 print (row)

into

# results = cursor.fetchall()
for row in cursor:
 print (row)

pymssql has bug in cursor.fetchall()

For reference https://github.com/pymssql/pymssql/issues/141