Skip to content

Files

Latest commit

48a2448 · Jun 26, 2020

History

History
248 lines (168 loc) · 4.64 KB

63.md

File metadata and controls

248 lines (168 loc) · 4.64 KB

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

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


于 2020 年 1 月 7 日更新


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

方法 描述
fetchone() 此方法以元组形式返回一个记录,如果没有更多记录,则返回None
fetchmany(number_of_records) 此方法接受要提取的记录数,并返回元组,其中每个记录本身就是一个元组。 如果没有更多记录,则返回一个空元组。

使用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")