Query don't execute with SQL Server Database

[Leandro Severino]

my code.py

class Index:
"""
Class Index Controller.
Show the report pages.
"""
def GET(self):
records = model.get_records()
return render.index(records)

========================================================

my model.py

import web

db = web.database(dbn='mssql', host='the_host', db='the_database',
user='the_user', pw='the_password')

def get_records():
records = db.query("SELECT * FROM varigvem.pesquisa_satisfacao
ORDER BY empresa")
for row in records:
print "%s" % str(row['empresa'])
return records

========================================================

python2.7 code.py
http://0.0.0.0:8080/
ERR: SELECT * FROM varigvem.pesquisa_satisfacao ORDER BY empresa
Traceback (most recent call last):
File "/home/leandro/tmp/report_marketing/web/application.py", line
242, in process
return self.handle()
File "/home/leandro/tmp/report_marketing/web/application.py", line
233, in handle
return self._delegate(fn, self.fvars, args)
File "/home/leandro/tmp/report_marketing/web/application.py", line
415, in _delegate
return handle_class(cls)
File "/home/leandro/tmp/report_marketing/web/application.py", line
390, in handle_class
return tocall(*args)
File "/home/leandro/tmp/report_marketing/code.py", line 22, in GET
records = model.get_records()
File "/home/leandro/tmp/report_marketing/model.py", line 6, in
get_records
records = db.query("SELECT * FROM varigvem.pesquisa_satisfacao
ORDER BY empresa")
File "/home/leandro/tmp/report_marketing/web/db.py", line 611, in
query
self._db_execute(db_cursor, sql_query)
File "/home/leandro/tmp/report_marketing/web/db.py", line 562, in
_db_execute
out = cur.execute(sql_query.query(paramstyle), sql_query.values())
File "/usr/lib/pymodules/python2.7/pymssql.py", line 192, in execute
self._source.execute_query(operation, args[0])
ValueError: 'params' arg can be only a tuple or a dictionary.

127.0.0.1:34069 - - [18/Aug/2011 09:20:37] "HTTP/1.1 GET /" - 500
Internal Server Error

Any idea ?

[Andre Smit]

I reported a problem with web.database(mssql) a few days back:

https://groups.google.com/forum/#!topic/webpy/v0qWZEkXOKA


appears to be a similar problem. My queries aren't returning any data either.

[Andre Smit]

In the interim you can use pymssql - eg:

import os, sys, web, json
import pymssql

prog_dir = os.path.dirname(os.path.abspath(__file__))
sys.path.append(prog_dir)

urls = (
    '/'           , 'Index',
    '/districts'  , 'Districts',
)

app = web.application(urls, globals())
application = app.wsgifunc()

templates_dir = os.path.join(prog_dir,'templates/')
render = web.template.render(templates_dir)

conn = pymssql.connect(host='host', user='user', password='pwd', database='db')

class Index:
    def GET(self):
        return render.index()

class Districts:
    def GET(self):
        res=[]
        cur = conn.cursor()
        cur.execute('SELECT DISTNAM FROM DB_DISTRICTS')
        columns = tuple( [d[0].decode('utf8') for d in cur.description] )
        for row in cur:
            res.append(dict(zip(columns, row)))
        d = {'items':res}
        web.header('Content-Type', 'application/json')
        return json.dumps(d)

if __name__ == "__main__":
    app.run()

[Leandro Severino]

Great !, thank you so much my friend. You solved my problem.

best regards,
Leandro.

[Anand Chitipothu]

2011/8/19 Andre Smit <freev...@gmail.com>:

Looks like you figured out a way to fix it. Can you fix it in web.db
and send me a pull request?

Anand

[Andre Smit]

I've checked but can't track down the problem. The mssql cursor gets the description OK but no data. Also I've only recently started using the mssql module so not sure how long this problem exists.

[花包]

check web.py db.py lineno 660,

change if not self.ctx.transactions : to if not self.ctx.transactions and self.dbname!="mssql": 

then OK

在 2011年8月30日星期二UTC+8下午8时17分42秒，Andre Smit写道：