[Python's pymysql library learning] 2 Relevant knowledge of cursor (nanny level graphics and text + 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

Data type read

Effect of converting read data to dictionary type

General reading effect

Implementation idea of converting read data into dictionary type

Set the core api of the data type
conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
cursor=pymysql.cursors.DictCursor indicates that the read data is of dictionary type

The read data is converted into dictionary type implementation code

# @Time    : 2022/1/16 16:44
# @Author: Nanli
# @FileName: 2 Knowledge of cursor 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(cursor=pymysql.cursors.DictCursor) 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

About cursor reset

It's cursor on the Internet Scroll (0), but I found that it didn't work. I reported an error that the subscript crossed the boundary. I was a little confused
I used the simplest and simplest method to re cursor Execute (sql) executes the sql query statement once and obtains the data again (but the running efficiency is reduced)
If you have a better way, leave a message in the comment area and share it!

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 Back-end pymysql

Added by DjSiXpAcK14 on Mon, 17 Jan 2022 23:20:17 +0200

Programming VIP