Detailed tutorial on operating MySQL in python

Preface

PyMySQL is a pure Python library used to connect to MySQL databases and execute SQL statements. It is an alternative to MySQLdb, but unlike the latter, PyMySQL does not require C dependencies, so it is lighter and easier to install and use. The main purpose of this library is to interact with MySQL database through Python code, such as performing queries, inserting data, updating data, deleting data, etc.

1. Features of PyMySQL

The characteristics of this library are mainly as follows:

Pure Python implementation: No need to rely on C extension libraries, it can be easily used on various systems, such as Windows, Linux, macOS, etc.
Strong compatibility: Supports MySQL and MySQL versions, and is also compatible with MariaDB.
Ease of use: Provided withMySQLdbSimilar APIs are convenient for users toMySQLdbMigrate here.

2. Installation

PyMySQLCan be passedpipEasily install:

pip install pymysql

3. Basic usage

(I) Connect to MySQL database

In usePyMySQLBefore, you need to connect to the MySQL database first. When connecting to a database, you usually need to provide the database's host address, user name, password, database name and other information.

Example:

import pymysql
 
# Create a connectionconnection = (
    host='localhost',      # Database host address    user='your_username',  # Database username    password='your_password', # Database Password    database='your_dbname',   # Selected database    charset='utf8mb4',     # Specify the character set    cursorclass=  # Returns the data in dictionary format)
 
# Create a cursorcursor = ()
 
# Close cursor and connection()
()

(II) Data query

Use a cursor object to execute SQL queries and get data.

Example:

try:
    # Create a cursor    with () as cursor:
        # SQL Query        sql = "SELECT * FROM users WHERE age &gt; %s"
        (sql, (25,))  # Use parameterized queries to prevent SQL injection        # Get results        results = ()  # Return all results        for row in results:
            print(row)
finally:
    # Close the connection    ()

(III) Insert data

When performing an insert operation, you can useexecute()orexecutemany()method.

Example:

try:
    with () as cursor:
        # SQL Insert Statement        sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
        # Perform single insertion        (sql, ('John Doe', 30, '@'))
        
        # Perform multiple insertions        users = [
            ('Alice', 25, 'alice@'),
            ('Bob', 28, 'bob@')
        ]
        (sql, users)
    
    # Submit changes    ()
finally:
    ()

(IV) Update and delete data

Similar to inserting data, update and delete numbers are all through SQL statements andexecute()Functions are used to complete.

Update data

Example:

try:
    with () as cursor:
        # SQL Update Statement        sql = "UPDATE users SET email = %s WHERE name = %s"
        (sql, ('@', 'John Doe'))
    
    # Submit changes    ()
finally:
    ()

Delete data

Example:

try:
    with () as cursor:
        # SQL Delete Statement        sql = "DELETE FROM users WHERE name = %s"
        (sql, ('John Doe',))
    
    # Submit changes    ()
finally:
    ()

(V) Affairs Management

MySQL database supports transactions,PyMySQLAutomatic submission is enabled by default. If you need to manually control transactions, you can turn off automatic commits and commit or rollback at the right time.

Example:

try:
    # Turn off automatic submission    (False)
 
    with () as cursor:
        # SQL Insert Statement        sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
        (sql, ('Chris', 27, 'chris@'))
 
        # Human error, test rollback        raise Exception("Authorized trigger exception")
 
    # Submit transaction    ()
except Exception as e:
    print(f"An error occurred: {e}")
    # Roll back the transaction    ()
finally:
    ()

4. Cursor type

PyMySQLA variety of cursor types are provided, suitable for different scenarios:

Default cursor: Returns the result in the tuple format.
Dictionary Cursor (DictCursor): Returns the result in dictionary format, with the field name as the key.
SSCursor: Streaming cursor, used to avoid loading large amounts of data into memory at one time when processing large amounts of data.

Use different cursor types to passcursorclassParameter specification. For example:

# Dictionary Cursorconnection = (
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_dbname',
    charset='utf8mb4',
    cursorclass=  # Use dictionary cursor)

V. Safety

To prevent SQL injection attacks, be sure to use parameterized queries instead of splicing parameters directly into SQL strings.

Example:

# Unsafe writingsql = f"SELECT * FROM users WHERE name = '{name}'"  # It can easily lead to SQL injection 
# Safe writingsql = "SELECT * FROM users WHERE name = %s"
(sql, (name,))

6. Common error handling

PyMySQLCommon error handling in can be done by catching exceptions:

import pymysql
 
try:
    connection = (host='localhost', user='root', password='', database='test_db')
    cursor = ()
except  as e:
    print(f"Database connection failed: {e}")

7. Performance optimization

Using batch operations: If you insert data, useexecutemany()Batch insertion to reduce the number of database interactions.
Connection pool: In large-scale applications, third-party libraries can be used such asDBUtilsorSQLAlchemyThe connection pool is provided to optimize the reusability of database connections.
Streaming query: For large-scale queries, useSSCursorStreaming cursors to avoid loading all data into memory at once.

8. Summary

PyMySQLis a lightweight, easy-to-use Python library suitable for Python programmers to interact with MySQL databases. It allows you to perform SQL queries, insert, update and delete data efficiently, while also taking advantage of its advanced features such as transaction support and cursor control.

The above is the detailed content of the detailed tutorial on python operating MySQL. For more information about python operating MySQL, please follow my other related articles!