CODEWITHSUNDEEP
pythonsqlsql-serverpymssql
user2430259
user2430259

Reputation:

python and pymssql

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?

Upvotes: 15

Views: 27951

Answers (6)

Kunj
Kunj

Reputation: 1

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

Upvotes: 0

user9576045
user9576045

Reputation: 

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

Upvotes: 1

ronedg
ronedg

Reputation: 1266

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

Upvotes: 4

Paolo Casciello
Paolo Casciello

Reputation: 8202

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.

Upvotes: 4

Mike Williamson
Mike Williamson

Reputation: 3148

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

Upvotes: 0

Derek
Derek

Reputation: 23228

Try adding a conn.commit() to your query

Upvotes: 1

Related Questions