使用`fetchone()`和`fetchmany()`获取记录

原文： https://thepythonguru.com/fetching-records-using-fetchone-and-fetchmany/

于 2020 年 1 月 7 日更新

到目前为止，我们一直在使用游标对象的fetchall()方法来获取记录。 一次性访问所有记录的过程并非十分有效。 结果，MySQLdb 具有游标对象的fetchone()和fetchmany()方法来更有效地获取记录。

使用fetchone()

from __future__ import print_function
import MySQLdb as my
try:
    db = my.connect(host="127.0.0.1",
                    user="root",
                    passwd="",
                    db="world"
                    )
    cursor = db.cursor()
    sql = "select * from city where id < 10"
    number_of_rows = cursor.execute(sql)
    print(cursor.fetchone())  # fetch the first row only
    db.close()
except my.DataError as e:
    print("DataError")
    print(e)
except my.InternalError as e:
    print("InternalError")
    print(e)
except my.IntegrityError as e:
    print("IntegrityError")
    print(e)
except my.OperationalError as e:
    print("OperationalError")
    print(e)
except my.NotSupportedError as e:
    print("NotSupportedError")
    print(e)
except my.ProgrammingError as e:
    print("ProgrammingError")
    print(e)
except :
    print("Unknown error occurred")

使用fetchone()遍历结果

from __future__ import print_function
import MySQLdb as my
try:
    db = my.connect(host="127.0.0.1",
                    user="root",
                    passwd="",
                    db="world"
                    )
    cursor = db.cursor()
    sql = "select * from city where id < 10"
    number_of_rows = cursor.execute(sql)
    while True:
        row = cursor.fetchone()
        if row == None:
            break
        print(row)
    db.close()
except my.DataError as e:
    print("DataError")
    print(e)
except my.InternalError as e:
    print("InternalError")
    print(e)
except my.IntegrityError as e:
    print("IntegrityError")
    print(e)
except my.OperationalError as e:
    print("OperationalError")
    print(e)
except my.NotSupportedError as e:
    print("NotSupportedError")
    print(e)
except my.ProgrammingError as e:
    print("ProgrammingError")
    print(e)
except :
    print("Unknown error occurred")

使用fetchmany()

from __future__ import print_function
import MySQLdb as my
try:
    db = my.connect(host="127.0.0.1",
                    user="root",
                    passwd="",
                    db="world"
                   )
    cursor = db.cursor()
    sql = "select * from city where id < 10"
    number_of_rows = cursor.execute(sql)
    print(cursor.fetchmany(2))  # fetch first 2 rows only
    db.close()
except my.DataError as e:
    print("DataError")
    print(e)
except my.InternalError as e:
    print("InternalError")
    print(e)
except my.IntegrityError as e:
    print("IntegrityError")
    print(e)
except my.OperationalError as e:
    print("OperationalError")
    print(e)
except my.NotSupportedError as e:
    print("NotSupportedError")
    print(e)
except my.ProgrammingError as e:
    print("ProgrammingError")
    print(e)
except :
    print("Unknown error occurred")

使用fetchmany()遍历结果

from __future__ import print_function
import MySQLdb as my
try:
    db = my.connect(host="127.0.0.1",
                    user="root",
                    passwd="",
                    db="world"
                   )
    cursor = db.cursor()
    sql = "select * from city where id < 10"
    number_of_rows = cursor.execute(sql)
    while True:
        two_rows = cursor.fetchmany(2)
        if not two_rows:
            break
        print(two_rows)
    db.close()
except my.DataError as e:
    print("DataError")
    print(e)
except my.InternalError as e:
    print("InternalError")
    print(e)
except my.IntegrityError as e:
    print("IntegrityError")
    print(e)
except my.OperationalError as e:
    print("OperationalError")
    print(e)
except my.NotSupportedError as e:
    print("NotSupportedError")
    print(e)
except my.ProgrammingError as e:
    print("ProgrammingError")
    print(e)
except :
    print("Unknown error occurred")