[Python's pymysql library learning] 1. Analyze fetchone(), fetchmany(), fetchall() (nanny graphics + implementation code)

Welcome to pay attention "pymysql library learning of Python" series , continuously updating
Welcome to pay attention "pymysql library learning of Python" series , continuously updating

Realization effect

Realization idea

In fact, half of the code is to create a database, create a data table and insert data. I have written all the environment configuration parts. You can run all the code k maliciously and use it directly.
You can only run this part of the code once (but I have added if to judge whether it has been established, which doesn't matter)

1.fetchone():Get a row of data, which is used when it is difficult to read a row of data. It can alleviate the memory pressure, but the efficiency is the lowest
2.fetchmany(x):obtain x Row data, x by int Type, in fetchone()and fetchall()Between memory pressure and efficiency, flexible use
3.fetchall(): Read all data at one time. Use it when the amount of data is relatively small. Although the efficiency is the highest, if the amount of data is large, it will get stuck
4.Elegant comprehensive application when reading a large amount of data. Here is an elegant writing method using iterators

Connect to the database on the host

#######Connect to the database on the host#######
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='root',
    port=3306,
    autocommit=False,
    charset="utf8mb4"
)

cursor = conn.cursor()  # Create cursor

Create sql for database

########To create a database sql#######
# (use if to judge whether the database already exists. It will be created only when the database does not exist, otherwise an error will be reported.)
cursor.execute("CREATE DATABASE IF NOT EXISTS python")
cursor.execute("USE python")  # Switch to the new python database

Create data table

########To create a database sql#######
# (use if to judge whether the database already exists. It will be created only when the database does not exist, otherwise an error will be reported.)
cursor.execute("CREATE DATABASE IF NOT EXISTS python")
cursor.execute("USE python")  # Switch to the new python database

insert data

########Create data table#######
cursor.execute('drop table if EXISTS student')
# sql for creating tables
sql = '''
    create table student(
    sno int(8) primary key auto_increment,
    sname varchar(30) not null,
    sex varchar(5) ,
    age int(2),
    score float(3,1)
    )
'''
cursor.execute(sql)

Read data (our focus in this section)

########insert data#######
sql = '''
    insert into student(sname,sex,age,score) values(%s,%s,%s,%s)
'''
add_data_list = [('dynasty', 'man', 25, 94.6), ('Mahan', 'man', 27, 91.1), ('Zhang long', 'man', 21, 88.1), ('Zhao Hu', 'man', 22, 97.1)]  # Batch insert data in list form
cursor.executemany(sql, add_data_list)
cursor.close()

########Read data#######
conn = pymysql.connect(host='localhost', user='root', password='root', database='python', port=3306, autocommit=False)
# # Read the student's SnO, sname, sex, age and score from excel and put them into the database
try:
    with conn.cursor() as cursor:  # Create a cursor, where conn.cursor()==cursor
        # 1.fetchone(): get a row of data, which is used when it is difficult to read a row of data. It can relieve the memory pressure, but the efficiency is the lowest
        print("1.fetchone():Get a row of data")
        cursor.execute(
            'select sno,sname,sex,age,score from student'
        )#Every time you read all the data, the cursor is placed at the end, so you have to rewrite cursor every time you test the three functions execute
        row = cursor.fetchone()#Read a row of data
        while row:#As long as the data is read, the row is not None, that is, it is not false
            print(row)
            row = cursor.fetchone()

        # 2.fetchmany(x): get x rows of data. X is of type int. the memory pressure and efficiency between fetchone() and fetchall() are flexible
        print("2.fetchmany(x):obtain x Row data")
        cursor.execute(
            'select sno,sname,sex,age,score from student'
        )
        row = cursor.fetchmany(2)  # Read a row of data
        while row:#As long as the data is read, the row is not None, that is, it is not false. By default, it means that the data is not read
            print(row)
            row = cursor.fetchmany(2)

        # 3.fetchall(): read all data at one time. It is used when the amount of data is small. Although the efficiency is the highest, if the amount of data is large, it will get stuck
        print("3.fetchall(): Read all data at once")
        cursor.execute(
            'select sno,sname,sex,age,score from student'
        )
        row = cursor.fetchall()  # Reading all data seems the simplest, but it should be noted that this is based on a small amount of data and hardware can keep up.
        print(row)

        # 4. Elegant comprehensive application when reading a large amount of data. Here is an elegant writing method using iterators
        print("4.Elegant integrated application when reading large amounts of data")
        cursor.execute(
            'select sno,sname,sex,age,score from student'
        )
        for row in iter(lambda:cursor.fetchone(),None):#Read row row by row, if cursor Fetchone() is None and ends the for loop
            print(row)



except pymysql.MySQLError as err:  # Catch exception
    print(err)  # If an exception occurs, an error message is printed
finally:
    conn.close()  # Close the connection anyway to save resources

Implementation code

# @Time    : 2022/1/16 16:44
# @Author: Nanli
# @FileName: one Analyze three kinds of fetch py
import pymysql
import openpyxl  # User operation excel Library

#######Connect to the database on the host#######
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='root',
    port=3306,
    autocommit=False,
    charset="utf8mb4"
)

cursor = conn.cursor()  # Create cursor

########To create a database sql#######
# (use if to judge whether the database already exists. It will be created only when the database does not exist, otherwise an error will be reported.)
cursor.execute("CREATE DATABASE IF NOT EXISTS python")
cursor.execute("USE python")  # Switch to the new python database

########Create data table#######
cursor.execute('drop table if EXISTS student')
# sql for creating tables
sql = '''
    create table student(
    sno int(8) primary key auto_increment,
    sname varchar(30) not null,
    sex varchar(5) ,
    age int(2),
    score float(3,1)
    )
'''
cursor.execute(sql)

########insert data#######
sql = '''
    insert into student(sname,sex,age,score) values(%s,%s,%s,%s)
'''
add_data_list = [('dynasty', 'man', 25, 94.6), ('Mahan', 'man', 27, 91.1), ('Zhang long', 'man', 21, 88.1), ('Zhao Hu', 'man', 22, 97.1)]  # Batch insert data in list form
cursor.executemany(sql, add_data_list)
cursor.close()

########Read data#######
conn = pymysql.connect(host='localhost', user='root', password='root', database='python', port=3306, autocommit=False)
# # Read the student's SnO, sname, sex, age and score from excel and put them into the database
try:
    with conn.cursor() as cursor:  # Create a cursor, where conn.cursor()==cursor
        # 1.fetchone(): get a row of data, which is used when it is difficult to read a row of data. It can relieve the memory pressure, but the efficiency is the lowest
        print("1.fetchone():Get a row of data")
        cursor.execute(
            'select sno,sname,sex,age,score from student'
        )#Every time you read all the data, the cursor is placed at the end, so you have to rewrite cursor every time you test the three functions execute
        row = cursor.fetchone()#Read a row of data
        while row:#As long as the data is read, the row is not None, that is, it is not false
            print(row)
            row = cursor.fetchone()

        # 2.fetchmany(x): get x rows of data. X is of type int. the memory pressure and efficiency between fetchone() and fetchall() are flexible
        print("2.fetchmany(x):obtain x Row data")
        cursor.execute(
            'select sno,sname,sex,age,score from student'
        )
        row = cursor.fetchmany(2)  # Read a row of data
        while row:#As long as the data is read, the row is not None, that is, it is not false. By default, it means that the data is not read
            print(row)
            row = cursor.fetchmany(2)

        # 3.fetchall(): read all data at one time. It is used when the amount of data is small. Although the efficiency is the highest, if the amount of data is large, it will get stuck
        print("3.fetchall(): Read all data at once")
        cursor.execute(
            'select sno,sname,sex,age,score from student'
        )
        row = cursor.fetchall()  # Reading all data seems the simplest, but it should be noted that this is based on a small amount of data and hardware can keep up.
        print(row)

        # 4. Elegant comprehensive application when reading a large amount of data. Here is an elegant writing method using iterators
        print("4.Elegant integrated application when reading large amounts of data")
        cursor.execute(
            'select sno,sname,sex,age,score from student'
        )
        for row in iter(lambda:cursor.fetchone(),None):#Read row row by row, if cursor Fetchone() is None and ends the for loop
            print(row)



except pymysql.MySQLError as err:  # Catch exception
    print(err)  # If an exception occurs, an error message is printed
finally:
    conn.close()  # Close the connection anyway to save resources

summary

If you like, give me a 👍， Pay attention! Share more interesting Python knowledge!

If you find that you are far away from the @mzh original work, you must mark the original link for reprint

Crated: 2022-1-15

Keywords: Python pymysql

Added by rdub on Thu, 20 Jan 2022 19:47:51 +0200

Programming VIP

[Python's pymysql library learning] 1. Analyze fetchone(), fetchmany(), fetchall() (nanny graphics + implementation code)

Realization effect

Realization idea

Implementation code

summary

Popular Keywords