PyMySQL operates MySQL database

Article directory

• PyMySQL
• • PyMySQL overview
  • Install PyMySQL library
• Basic use of PyMySQL
• • API introduction
  • Query operation
  • insert operation
  • Batch insert operation
  • Delete operation
  • update operation
• SQL injection
• • What is SQL injection
  • solution
• Precautions
• • Protect sensitive information
  • Use connection pool
  • Exception handling
  • Prevent concurrency conflicts
  • Optimize query
  • digression

PyMySQL

PyMySQL Overview

PyMySQL is a pure Python MySQL client library for the Python programming language. It implements all versions of the MySQL database protocol and supports multi-threaded applications and Python 3.x versions. PyMySQL follows the Python standard DB API interface and provides many convenient functions, such as MySQL connection pool, SSL/TLS encrypted connection, SQL injection prevention, etc.

GitHub: https://github.com/PyMySQL/PyMySQL

Install PyMySQL library

Install PyMySQL library

pip3 install pymysql

View information about third-party packages

pip3 show pymysql

View the list of installed third-party packages

pip3 list

Basic use of PyMySQL

API introduction

Import PyMySQL library

 import pymysql

Establishing a connection to a MySQL database using PyMySQL

pymysql.connect(parameter list)
 
connect_db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='demo')

Get cursor object

 cursor =conn.cursor()

Execute SQL statement

 row_count = cursor.execute(sql)

Get query result set

 result = cursor.fetchall()

Submit the modification operation to the database

 conn.commit()

Rollback data

 conn.rollback()

Close cursor

 cursor.close()

close connection

 conn.close()

Query operation

# Guide package
importpymysql

#Create database connection object
# When connecting to the database, except for the port parameters, all other parameters must be specified using string types.
# host: the connected mysql host, if the local machine is 'localhost'
#port: The port of the connected mysql host, the default is 3306
# user: username for connection
# password: Password for connection
# database: name of database
# charset: The encoding method used for communication, utf8 is recommended
connect_db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', charset='utf8', database='demo')

# Get the cursor object
# A database connection object can create multiple cursor objects
# Under normal circumstances, only one will be created for operation at the same time.
# When the cursor object is created, a transaction environment will be enabled by default.
cur = connect_db.cursor()

#Write SQL statement
sql_str = '''select * from item'''

#Execute SQL statement
row_count = cur.execute(sql_str)
print(f'Query {row_count} records')
print()

# Get a query result
result = cur.fetchone()
print(result)
print()

# Get the specified number of records
result = cur.fetchmany(4)
for t in result:
    print(t)
print()

# Get all data
result = cur.fetchall()
for t in result:
    print(t)
print()

#Move cursor properties
cur.rownumber = 0
result = cur.fetchall()
print(cur.rowcount)
for t in result:
    print(t)

# Close the cursor object
cur.close()

#Close database object
connect_db.close()

10 records found

(1, datetime.datetime(2022, 8, 21, 19, 31, 11), 'https:img13.360buyimg.com/n1/s450x450_jfs/t1/175088/12/11173/264547/60aa5dd6Efe2b408b/b91c5bdaf6918ffa. jpg', 4299.0, 'Lenovo JD self-operated flagship store', 'i5 16G backlit keyboard', 'Lenovo laptop Xiaoxin Air14 Intel Core i5 14-inch thin and light notebook (i5 16G 512G high color gamut large battery) silver full screen business office notebook', 'https://item.jd.com/100011483893.html')

(2, datetime.datetime(2022, 8, 21, 19, 31, 11), 'https:img13.360buyimg.com/n1/s450x450_jfs/t1/113403/25/29544/70492/6302023dE572552c8/e7c452efb3616b7 0.jpg', 4999.0, 'Lenovo JD self-operated flagship store', 'i5 16G backlit keyboard', 'Lenovo laptop Xiaoxin Pro14 Intel Evo platform 14-inch gaming thin and light notebook (standard pressure i5 16G 512G 2.8K 90Hz eye protection screen) full screen office notebook ', 'https://item.jd.com/100014546493.html')
(3, datetime.datetime(2022, 8, 21, 19, 31, 11), 'https:img12.360buyimg.com/n1/jfs/t1/219048/2/15799/192377/623ed1d3Eb52b150d/f669f9891c915da0.jpg', 2798.0, 'HUWI Flagship Store', '16G operation + 1024G super-fast hard drive', ' HUWI National Bank [2022 Intel Core Optional] Metal Laptop Thin and Light This College Student Online Class Design Learning Business Office Game Portable Rose Gold-High-Quality Intel Quad-Core 12th generation + IPS screen narrow-edge full screen 16G operation + 1024G super-fast hard drive', 'https://item.jd.com/10044527344297.html')
(4, datetime.datetime(2022, 8, 21, 19, 31, 11), 'https:img12.360buyimg.com/n1/s450x450_jfs/t1/42776/10/17345/131277/626bc1e3Eb2b0278a/1dc51316f3dc18 98.jpg', 4799.0, 'Huawei JD self-operated official flagship store', 'i5 16G 512G Haoyue Silver', 'Huawei laptop MateBook D 14 2022 14-inch 11th generation Core i5 16G + 512G Ruiju graphics card thin and light notebook/eye protection full screen silver' , 'https://item.jd.com/100016960357.html')
(5, datetime.datetime(2022, 8, 21, 19, 31, 11), 'https:img14.360buyimg.com/n1/s450x450_jfs/t1/69468/27/17416/141766/626baaecE202ac55d/d65dd608458c8c0b.j pg', 3999.0, 'Huawei JD self-operated official flagship store', 'i5 8GB 512GB Haoyue Silver', 'Huawei laptop MateBook D 14 SE version 14-inch 11th generation Core i5 Ruije graphics card 8G + 512G thin and light notebook/HD eye protection anti-glare screen Silver', 'https://item.jd.com/100032149194.html')

(6, datetime.datetime(2022, 8, 21, 19, 31, 11), 'https:img14.360buyimg.com/n1/s450x450_jfs/t1/99321/22/30610/122437/62ac48e7Eaf3ef4ac/464d0f1f132c8d62.j pg', 9299.0, 'HP (HP) OMEN Shadow Elf JD self-operated flagship store', '[New 12th generation i7] full blood 3060 + 2K screen version', 'HP (HP) OMEN Shadow Elf 8Pro 16.1-inch gaming laptop (12th generation Core i7-12700H RTX3060 6G 16GDDR5 512G 2.5K 165Hz) ', 'https://item.jd.com/100021389129.html')
(7, datetime.datetime(2022, 8, 21, 19, 31, 17), 'https:img12.360buyimg.com/n1/jfs/t1/47859/1/18224/153861/62a45618E5078f925/534299d323f12a2c.jpg', 1358.0, 'Smart Camel Digital Flagship Store', '8G Memory + 128G Solid State Drive', ' [2022 Intel + Core i7] 15.6-inch laptop thin and light online course design learning business office game college student portable Smart Camel Youth Edition [11th generation Intel] full screen + anti-blue light 8G memory + 128G solid state drive', 'https://item.jd.com/10033087405727.html')
(8, datetime.datetime(2022, 8, 21, 19, 31, 17), 'https:img11.360buyimg.com/n1/s450x450_jfs/t1/195842/2/20561/168501/62ca4248Edde4fefe/bbe877dbc201290e.jpg ', 5699.0, 'ThinkPad JD.com self-operated flagship store', 'i5-12500H 2.8K 90Hz', 'Lenovo ThinkBook 14 + Intel Core i5 laptop brand new 2022 14-inch standard pressure thin and light notebook i5-12500H 16G 512G 2.8K 90Hz', ' https://item.jd.com/100020078921.html')
(9, datetime.datetime(2022, 8, 21, 19, 31, 17), 'https:img11.360buyimg.com/n1/s450x450_jfs/t1/115135/13/26808/193275/62f371acE21c52747/20f36c3f883277 03.jpg', 5399.0, 'HP East self-operated official flagship store', '[Fifth generation new] i5 16G 512 high color gamut', 'HP (HP) 66 fifth generation 14-inch thin and light laptop (Intel 12th generation Core i5-1240P 16G 512G high color Domain low-power screen 1 year door-to-door) ', 'https://item.jd.com/100020480561.html')
(10, datetime.datetime(2022, 8, 21, 19, 31, 17), 'https:img12.360buyimg.com/n1/s450x450_jfs/t1/7498/9/18671/72563/62fb8cc2E2a4bedc8/cf134acbac78ec40.jpg', 3999.0, 'Xiaomi JD self-operated flagship store', 'R5 16G 512G', 'Xiaomi RedmiBookPro 14-inch 2.5K high color gamut retina screen thin and light notebook computer (6-core R5 16G 512G-SSD fingerprint recognition DC dimming) ', 'https ://item.jd.com/100011526087.html')

Insert operation

# Guide package
importpymysql

# Connect to the database
connect_db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', charset='utf8', database='demo')

# Get the cursor object
cur = connect_db.cursor()

#Execute sql
sql_str = '''insert into tb_user(id,user_name,password,name,age,email) values(null,'0001','123456','Jack',20,'[email protected]'); '' '
cur.execute(sql_str)

# When adding, deleting or modifying the database, the operation will be performed in a transaction environment by default. After the operation is completed, a manual submission operation must be performed. If not submitted, the default operation of the program is to roll back.
connect_db.commit()

# Close the cursor object
cur.close()

#Close database connection
connect_db.close()

Batch insert operation

def main():
    # Create Connection connection
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', charset='utf8', database='demo')
    # Get Cursor object
    cursor = conn.cursor()
    #Insert data 10,000 times
    for i in range(10000):
        cursor.execute("insert into tb_user values(null,'000%d','123456','Jack',20,'[email protected]')" % i)
    # submit data
    conn.commit()

if __name__ == "__main__":
    main()

Delete operation

import pymysql
# Connect to the database
connect_db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', charset='utf8', database='demo')
# Get the cursor object
cur = connect_db.cursor()
sql_str = ''' delete from tb_user where user_name = '0001' '''
#Execute sql
cur.execute(sql_str)
# submit
connect_db.commit()
# Close the cursor object
cur.close()
#Close database connection
connect_db.close()

Update operation

import pymysql
# Connect to the database
connect_db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', charset='utf8', database='demo')
# Get the cursor object
cur = connect_db.cursor()
#Execute sql
sql_str = ''' update tb_user set password = '12345' where user_name = '0001' '''
cur.execute(sql_str)
# submit
connect_db.commit()
# Close the cursor object
cur.close()
#Close database connection
connect_db.close()

SQL injection

What is SQL injection

Users submit malicious data and SQL statements for string splicing, thereby affecting the semantics of the SQL statements and eventually causing data leakage.

import pymysql

connect_db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', charset='utf8', database='demo')
cur = connect_db.cursor()

select_id = input('Please enter a query ID:')
sql_str = ''' select * from tb_user where id = %s ''' % select_id
print(sql_str)

cur.execute(sql_str)
result = cur.fetchall()

print(result)
cur.close()
connect_db.close()

Using the legal rules of sql, data that should not be obtained is queried, which is also called SQL injection.

Please enter a query ID: 1 or 1=1
 select * from tb_user where id = 1 or 1=1
((1, 'zhangsan', '123456', '小白', 20, '[email protected]'), (2, 'lisi', '123456', '李思', 20, 'test2@qq .com'), (3, 'wangwu', '123456', '王五', 28, '[email protected]'))

Solution

SQL statement parameterization, that is, the parameters in the SQL language use %s to occupy the place. The parameters required for the %s placeholder in the SQL statement are stored in a list, and the parameter list is passed to the second parameter in the execute method.

sql_str = ''' select * from tb_user where id = %s ''' % select_id
print(sql_str)

# Use the second parameter of the execute method to solve the problem of SQL injection
cur.execute(sql_str, (select_id,))

Please enter a query ID: 1 or 1=1
 select * from tb_user where id = %s
((1, 'zhangsan', '123456', '小白', 20, '[email protected]'),)

Notes

Protect sensitive information

When connecting to a database, do not hardcode sensitive information (such as usernames and passwords) directly into the code. Instead, store this sensitive information in a secure manner, such as a configuration file or environment variable, and load it dynamically in your code.

1. Create the INI configuration file of config.ini

[mysql]
user=root
password=123456
host=localhost
port=3306
database=mydb

2. Read the configuration file

ConfigParser is a module in the Python standard library, which is used to read and parse INI format configuration files. Configuration files can be easily read and loaded into a Python program for further processing using this module.

from configparser import ConfigParser

# Read configuration file
config = ConfigParser()
config.read('config.ini')

# Get MySQL login credentials
MYSQL_USER = config.get('mysql', 'user') # Output: root
MYSQL_PASSWORD = config.get('mysql', 'password')

# Connect to MySQL database
db = mysql.connector.connect(host='127.0.0.1',
                             user=MYSQL_USER,
                             password=MYSQL_PASSWORD,
                             database='mydb')

Use connection pool

Under heavy load, frequently creating and releasing database connections can reduce performance and increase system overhead. Therefore, it is recommended to use connection pooling to manage database connections so that available connections are obtained from the connection pool when needed and returned to the pool after use.

Using a connection pool helps optimize the management and reuse of database connections and reduces the overhead of starting a new connection for each request or operation, thereby improving the performance and response speed of the system.

1. Install the PyMySQL library and pymysql-pool library

pip install pymysql
pip install pymysql-pool

2. Import the PyMySQL library and connection pool module.

import pymysql
from pymysqlpool import ConnectionPool

3. Create a connection pool object and specify connection parameters

db_config = {
    'max_connections': 10,
    'max_idle_time': 60,
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': 'password',
    'database': 'mydb'
}
pool = ConnectionPool(**db_config)

4. Obtain the connection object and cursor, and execute the SQL statement

with pool.get_conn() as conn:
    with conn.cursor() as cur:
        sql = "SELECT * FROM mytable WHERE name=%s"
        cur.execute(sql, ('John',))
        results = cur.fetchall()
        print(results)

5. Close the cursor and connection and return the connection to the connection pool

cur.close()
pool.return_conn(conn)

Exception handling

When interacting with a database, errors are inevitable. When an exception occurs, it needs to be caught and handled promptly to avoid program crashes or leakage of sensitive information.

try:
    #Operation database
except Exception as e:
    print("Error:", e)
    # Handle exceptions
finally:
    #Close connection and cursor
    cur.close()
    conn.close()

Prevent concurrency conflicts

When multiple processes or threads access and modify the same data at the same time, concurrency conflicts may occur, resulting in data inconsistency or errors. For this reason, it is recommended to use the database’s transaction support to ensure data consistency and correctness.

try:
    # Start transaction
    conn.begin()

    #Operation database

    # Submit transaction
    conn.commit()
except Exception as e:
    print("Error:", e)
    #Rollback transaction
    conn.rollback()
finally:
    #Close connection and cursor
    cur.close()
    conn.close()

Optimize query

When querying data, the number of queries should be reduced as much as possible. You can optimize and speed up queries by using indexes, batch insertion and other methods. Additionally, parameterized queries should be used in INSERT, UPDATE, and DELETE statements to prevent SQL injection attacks.

# Parameterized SQL query
sql = "SELECT * FROM tablename WHERE column1 = %s AND column2 = %s"
cur.execute(sql, ('value1', 'value2'))

———————————END——————- ——–

Digression

In the current era of big data, how can one keep up with the times without mastering a programming language? Python, the hottest programming language at the moment, has a bright future! If you also want to keep up with the times and improve yourself, please take a look.

Interested friends will receive a complete set of Python learning materials, including interview questions, resume information, etc. See below for details.


CSDN gift package:The most complete “Python learning materials” on the entire network are given away for free! (Safe link, click with confidence)

1. Python learning routes in all directions

The technical points in all directions of Python have been compiled to form a summary of knowledge points in various fields. Its usefulness is that you can find corresponding learning resources according to the following knowledge points to ensure that you learn more comprehensively.

2. Python essential development tools

The tools have been organized for you, and you can get started directly after installation!

3. Latest Python study notes

When I learn a certain basic and have my own understanding ability, I will read some books or handwritten notes compiled by my seniors. These notes record their understanding of some technical points in detail. These understandings are relatively unique and can be learned. to a different way of thinking.

4. Python video collection

Watch a comprehensive zero-based learning video. Watching videos is the fastest and most effective way to learn. It is easy to get started by following the teacher’s ideas in the video, from basic to in-depth.

5. Practical cases

What you learn on paper is ultimately shallow. You must learn to type along with the video and practice it in order to apply what you have learned into practice. At this time, you can learn from some practical cases.

6. Interview Guide

Resume template

CSDN gift package:The most complete “Python learning materials” on the entire network are given away for free! (Safe link, click with confidence)

If there is any infringement, please contact us for deletion.