Dev D February 2016

Cannot validate query results

    query = "SELECT serialno from registeredpcs where ipaddress = "
    usercheck = query + "'%s'" %fromIP
    #print("query"+"-"+usercheck)
    print(usercheck)
    rs = cursor.execute(usercheck)
    print(rs)
    row = rs
    #print(row)
    #rs = cursor.rowcount()
    if int(row) == 1:
        query = "SELECT report1 from registeredpcs where serialno = "
        firstreport = query + "'%s'" %rs
        result = cursor.execute(firstreport)
        print(result)
    elif int(row) == 0:
        query_new = "SELECT * from registeredpcs"
        cursor.execute(query_new)
        newrow = cursor.rowcount()+1
        print(new row)

What I am trying to do here is fetch the serialno values from the db when it matches a certain ipaddress. This query if working fine. As it should the query result set rs is 0. Now I am trying to use that value and do something else in the if else construct. Basically I am trying to check for unique values in the db based on the ipaddress value. But I am getting this error

error: uncaptured python exception, closing channel smtpd.SMTPChannel connected 
192.168.1.2:3630 at 0x2e47c10 (**class 'TypeError':'int' object is not 
callable** [C:\Python34\lib\asyncore.py|read|83] 
[C:\Python34\lib\asyncore.py|handle_read_event|442] 
[C:\Python34\lib\asynchat.py|handle_read|171] 
[C:\Python34\lib\smtpd.py|found_terminator|342] [C:/Users/Dev-
P/PycharmProjects/CR Server Local/LRS|process_message|43])

I know I am making some very basic mistake. I think it's the part in bold thats causing the error. But just can't put my finger on to it. I tried using the rowcount() method didn't help.

Answers


Daniel Roseman February 2016

rowcount is an attribute, not a method; you shouldn't call it.


bruno desthuilliers February 2016

"I know I am making some very basic mistake" : well, Daniel Roseman alreay adressed the cause of your main error, but there are a couple other mistakes in your code:

query = "SELECT serialno from registeredpcs where ipaddress = "
usercheck = query + "'%s'" % fromIP
rs = cursor.execute(usercheck)

This part is hard to read (you're using both string concatenation and string formatting for no good reason), brittle (try this with `fromIP = "'foo'"), and very very unsafe. You want to use paramerized queries instead, ie:

# nb check your exact db-api module for the correct placeholder,
# MySQLdb uses '%s' but some other use '?' instead
query = "SELECT serialno from registeredpcs where ipaddress=%s"
params = [fromIP,]
rs = cursor.execute(query, params)

"As it should the query result set rs is 0"

This is actually plain wrong. cursor.execute() returns the number of rows affected (selected, created, updated, deleted) by the query. The "resultset" is really the cursor itself. You can fetch results using cursor.fetchone(), cursor.fetall(), or more simply (and more efficiently if you want to work on the whole resultset with constant memory use) by iterating over the cursor, ie:

for row in cursor:
    print row

Let's continue with your code:

row = rs
if int(row) == 1:
  # ...    
elif int(row) == 0:
  # ...

The first line is useless - it only makes row an alias of rs, and badly named - it's not a "row" (one line of results from your query), it's an int. Since it's already an int, converting it to int is also useless. And finally, unless 'ipadress' is a unique key in your table, your query might return more than one row.

Post Status

Asked in February 2016
Viewed 3,510 times
Voted 7
Answered 2 times

Search




Leave an answer