Help: why python odbc module can not fetch all over?

zxo102 zxo102 at gmail.com
Thu Mar 23 06:56:43 CET 2006


Hi everyone,
    I need your help for using python odbc module. I have a simple
table defined as

create table A (
  userId char(10),
  courseid char(10),
  grade  integer,
  primary key(userId,courseId)
)

  userId    courseId   grade
     1          1001         50
     1          1002         89
     2          1001         90
     2          1002         98

  in SQL server. Now I run the following query in SQL server Query
Analyzer:

select userId, grade from A  group by grade, userId order by userId
DESC compute count(userId), sum(grade) by userId

and got the results in two parts for each user in the Query Analyzer:
The results are shown like the following pattern

    userId         grade          <---- part1 results for user 1
  .  1               50
     1               89
-------------------------------------
     cnt             sum           <--- part2 result for user 1
      2               139
===================
     userId         grade         <--- part1 results for user 2
       2              90
       2              98
---------------------------------------
      cnt              sum         <--- part2 results for user 2
       2                188
====================

But when I use python odbc to fetch the results from the database in
SQL server, I can only get part1 of results for user 2. All other
results are gone, for example, part2 for user 2 and all for user 1.

    userId         grade          <---- part1 results for user 2
  .  2                90
     2                98

Here is what I did:
>>> import odbc
>>> cc = odbc.odbc('dsn=student_info')
>>> c2=cc.cursor()
>>> c2.execute("select userId, grade from A  group by grade, userId order by userId DESC compute count(userId), sum(grade) by userId")
>>> r = c2.fetchall()
>>> print r
[('2', 90), ('2', 98)]

Any body knows what is going on with python odbc module and how to get
"cnt" and "sum" in part 2 and others?

Thanks for your help in advance. I really appreciate that.

Ouyang




More information about the Python-list mailing list