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

[zxo102]

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:[('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

 

[Dennis Lee Bieber]

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

That SQL doesn't look like common SQL... ODBC may, or may not,
support multiple result sets, which is what you seem to be creating.
Especially with that non-standard looking "compute" clause.

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

Well, you are getting the first part that you seem to have asked
for:

select userID, grade...
These are the only output columns in your data set

group by grade
seems meaningless -- that means to put all records with a given
grade into a group

order by userId desc
Says put the output in descending userId, so user 2 will come out
first.

Here is what MySQL gives (MySQL does not have a compute clause)

Query
select userId, grade, count(userID), sum(grade) from A group by grade
order by userId desc
userId grade count(userID) sum(grade)
2 90 1 90
2 98 1 98
1 89 1 89
1 50 1 50

Note the I added count and sum to the select output columns...

Now, if I do a group by userId, MySQL gives:

Query
select userId, grade, count(userID), sum(grade) from A group by userId
order by userId desc
userId grade count(userID) sum(grade)
2 98 2 188
1 50 2 139

Notice the userId, the count, and the sum are correct -- but you
only get one "grade" (note: when I entered the data into the table, I
mixed the order)

Query
SELECT * FROM a
userId courseId grade
1 1001 50
2 1002 98
1 1002 89
2 1001 90

only the first grade per userId is displayed in the group by query

Normally a report of the type you want would be done
programmatically...

Query
select A.userId, A.grade, bCount, bSum , courseId from A inner join
(select userId, count(userId) as bCount, sum(grade) as bSum from A group
by userId) as B on A.userId = B.userId order by A.userId desc
userId grade bCount bSum courseId
2 90 2 188 1001
2 98 2 188 1002
1 50 2 139 1001
1 89 2 139 1002

Strange -- I didn't specify courseId as a select field, but MySQL
seems to have put that in when the Query Browser executed/exported...

Programatically, that query gets you all the desired data (ignore
courseID <G>); you would have to do something that would compare the
count/sum fields to the previous record, output the summary row when a
change takes place, and print the other fields in the meantime...

{untested -- pseudocode}

pcount, psum = None, None
print "\tuserId\tgrade"
print "===================================="
for (userId, grade, count, sum, courseId) in crsr.fetchone():
if (pcount and pcount != count) or (psum and psum != sum):
print "===================================="
print "\tCount = %s\tSum = %s" % (pcount, psum)
print "===================================="
print
print "\tuserId\tgrade"
print "===================================="
pcount = count
psum = sum
print "\t%s\t%s" % (userId, grade)
# do last summary
if (pcount and pcount != count) or (psum and psum != sum):
print "===================================="
print "\tCount = %s\tSum = %s" % (pcount, psum)
print "===================================="
print
--

 

[Dennis Lee Bieber]

Talking to myself...

{untested -- pseudocode}

Spent a half hour (having to set up privileges for the BestiariaCP
user to access "test", etc. -- since I'm caught with Python 2.3 [for
other reasons] and MySQLdb is using incompatible password algorithm with
my MySQL)...

Same test data base as before... Watch for wrapping:

-=-=-=-=-=-=-=-=-=-=-
import MySQLdb

def printUser(table):
print
print "-" * 40
print " " * 5, "userId", " " * 15, "grade"
print "-" * 40
for (u, g) in table:
print " " * 5, "%6s" % u, " " * 15, "%5d" % g

def printSummary(cnt, sm):
print "=" * 40
print " " * 5, "Count = %2d" % cnt, " " * 10, "Sum = %4d" % sm
print "=" * 40
print

con = MySQLdb.Connect(host="localhost",
user="BestiariaCP",
db="test")
crsr = con.cursor()

crsr.execute("""select A.userId, A.grade, bCount, bSum from A
inner join
(select userId, count(userId) as bCount, sum(grade)
as bSum
from A group by userId) as B
on A.userId = B.userId
order by A.userId desc""")

pcount = None
psum = None

tbl = []

while True:
row = crsr.fetchone()

if not row: break

(u, g, c, s) = row

if (pcount and pcount != c) or (psum and psum != s):
printUser(tbl)
tbl = []
printSummary(pcount, psum)
tbl.append( (u, g) )
pcount = c
psum = s

if tbl:
printUser(tbl)
printSummary(pcount, psum)

crsr.close()
con.close()
-=-=-=-=-=-=-=-=-=-=-=-=-

----------------------------------------
userId grade
----------------------------------------
2 90
2 98
========================================
Count = 2 Sum = 188
========================================


----------------------------------------
userId grade
----------------------------------------
1 50
1 89
========================================
Count = 2 Sum = 139
========================================

(use a fixed pitch font to get that to look nicer)

--

 

[zxo102]

Hi Dennis,

Thanks for your effort. I really appreciate it. It works for me
now.

Ouyang

 

[Dennis Lee Bieber]

Thanks for your effort. I really appreciate it. It works for me
now.

I'm tempted to make some slur against Microsoft about "nonstandard"
SQL <G>

I didn't dig up my limited Firebird documentation, and don't think
it has a help system that lists SQL syntax (I was able to start a few of
the compatible Interbase query programs -- I just don't have Firebird
running in the background). MaxDB (aka SAP-DB) query browser did bring
up an SQL help (I don't have that running either <G>; MySQL has been
enough for my little needs) -- It listed "compute" as a "non-reserved"
word; which may just mean that they know of some server that uses the
verb.


{Too many candidate RDBMs on this desktop: MySQL, Firebird, MaxDB, MSDE,
and "JET" (being generous)}
--

 

[Dennis Lee Bieber]

{Copy emailed direct}

Thanks for your effort. I really appreciate it. It works for me
now.

A minor tweak... I just realized that two people with identical
counts and sums would be treated as one group... That may not be what
you wanted... Consider the following variation (changed lines have # on
them)

-=-=-=-=-=-=-=-=-
import MySQLdb

def printUser(table):
print
print "-" * 40
print " " * 5, "userId", " " * 15, "grade"
print "-" * 40
for (u, g) in table:
print " " * 5, "%6s" % u, " " * 15, "%5d" % g

def printSummary(cnt, sm):
print "=" * 40
print " " * 5, "Count = %2d" % cnt, " " * 10, "Sum = %4d" % sm
print "=" * 40
print

con = MySQLdb.Connect(host="localhost",
user="BestiariaCP",
db="test")
crsr = con.cursor()

crsr.execute("""select A.userId, A.grade, bCount, bSum from A
inner join
(select userId, count(userId) as bCount, sum(grade)
as bSum
from A group by userId) as B
on A.userId = B.userId
order by A.userId desc""")

#pcount = None
#psum = None
pu = None #

tbl = []

while True:
row = crsr.fetchone()

if not row: break

(u, g, c, s) = row

if pu and pu != u: #(pcount and pcount != c) or (psum and psum !=
s):
printUser(tbl)
tbl = []
printSummary(pcount, psum)
tbl.append( (u, g) )
pcount = c
psum = s
pu = u #

if tbl:
printUser(tbl)
printSummary(pcount, psum)

crsr.close()
con.close()
-=-=-=-=-=-=-=-=-=-=-

The query has not changed, only the field that controls the
break-point has... Now it breaks on a change in userId

If you change the "order by" from userId to bSum, you can get the
groups in "score" order...
--