Python MySQLdb execute return int

Question

I'm using python 3.5.2 for my project. I installed MySQLdb via pip for Mysql connection.

Code:

import MySQLdb

class DB:
    def __init__(self):
        self.con = MySQLdb.connect(host="127.0.0.1", user="*", passwd="*", db="*")
        self.cur = self.con.cursor()

    def query(self, q):
        r = self.cur.execute(q)
        return r

def test():
    db = DB()
    result = db.query('SELECT id, name FROM test')
    return print(result)

def test1():
    db = DB()
    result = db.query('SELECT id, name FROM test').fetchall()
    for res in result:
        id, name = res
        print(id, name)

test()
test1()
#test output >>> '3'
#test1 output >>> AttributeError: 'int' object has no attribute 'fetchall'

Test table:

id     |     name
1      |    'test'
2      |    'test2'
3      |    'test3'

BlackMamba · Accepted Answer · 2017-01-23 08:11:38Z

Please read this link：http://mysql-python.sourceforge.net/MySQLdb.html

At this point your query has been executed and you need to get the results. You have two options:

r=db.store_result()

...or... r=db.use_result() Both methods return a result object. What's the difference? store_result() returns the entire result set to

the client immediately. If your result set is really large, this could be a problem. One way around this is to add a LIMIT clause to your query, to limit the number of rows returned. The other is to use use_result(), which keeps the result set in the server and sends it row-by-row when you fetch. This does, however, tie up server resources, and it ties up the connection: You cannot do any more queries until you have fetched all the rows. Generally I recommend using store_result() unless your result set is really huge and you can't use LIMIT for some reason.

def test1():
    db = DB()
    db.query('SELECT id, name FROM test')
    result = db.cur.fetchall()
    for res in result:
        id, name = res
        print(id, name)

al27091 · Accepted Answer · 2017-01-23 08:02:34Z

0

cursor.execute() will return the number of rows modified or retrieved, just like in PHP. Have you tried to return the fetchall() like so?

 def query(self, q):
   r = self.cur.execute(q).fetchall()
   return r

See here for more documentation: https://ianhowson.com/blog/a-quick-guide-to-using-mysql-in-python/

answered Jan 23, 2017 at 8:02

al27091

1115 bronze badges

1 Comment

dingx Over a year ago

this is wrong, it will return error like '' int can't be iterated'

Collectives™ on Stack Overflow

Python MySQLdb execute return int

2 Answers 2

...or... r=db.use_result() Both methods return a result object. What's the difference? store_result() returns the entire result set to

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

...or... r=db.use_result() Both methods return a result object. What's the difference? store_result() returns the entire result set to

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related