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?

Can you try and print the results object and tell whats the output? — Urjit, Aug 30 '11 at 23:57
@Magd i'm having the exact same issue, did you figure out what was wrong? — kefeizhou, Jan 17 '12 at 21:58

score 4 · Answer 1 · answered Dec 29 '12 at 16:40

4

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.

answered Dec 29 '12 at 16:40

Paolo Casciello

7,982
1
43
42

score 4 · Answer 2 · answered Jun 06 '13 at 20:27

4

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

answered Jun 06 '13 at 20:27

ronedg

1,248
11
12

score 1 · Answer 3 · answered Aug 31 '11 at 00:31

1

Try adding a conn.commit() to your query

answered Aug 31 '11 at 00:31

Derek

21,828
7
53
61

between the execute and fetchall, to be precise ;) – fileoffset Aug 31 '11 at 05:25
Will that be needed even if we are only reading from the database using pymssql?? – Urjit Aug 31 '11 at 21:53
2

>>> conn.commit() >>> >>> results = cursor.fetchall() Traceback (most recent call last): File "", 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 – Sep 01 '11 at 05:37
2

`SELECT` queries don't need a commit, doing a commit will only make `fetchall` to raise OperationalError – Amir Ali Akbari Dec 08 '12 at 15:48

score 0 · Answer 4 · answered Apr 28 '18 at 15:55

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()

score 0 · Answer 5 · answered May 18 '18 at 08:44

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

score 0 · Answer 6 · edited May 23 '17 at 12:07

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:

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!!
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

python and pymssql

6 Answers6