This article mainly explains how to use the pymysql library for MySQL management operations.

Mainly explain how to use pymysql to add, delete, modify, and check actions, and attach corresponding examples.

Install pymysql

pip install PyMySQL

Common objects and API

Connection and Cursor objects are provided in pymysql to manage and operate MySQL.

Common objects

• Connection

Represents a socket connection with My SQL Server , and uses the connect method to create a connection instance.

• Cursor

Represents an object that interacts with the MySQL database using Connection.Cursor() on the current socket connection.

Common API

Commonly used APIs for Connection objects:

connect ( )    create a database connection instance  
 begin ( )      start a transaction  
 close ( )      send an exit message, and close the connection  
 commit ( )     submit the modification to the database  
 cursor ( )     create a cursor ( cursor ) instance  
 ping ( )       check whether the server is running  
 rollback ( )   roll back the current transaction  
 select_db ( db )    set the current db  
 show_warnings ( ) display warning information

Commonly used APIs for Cursor objects:

close ( )      close the current cursor
 execute ( )    execute a sql statement
 executemany ( )    execute a batch of sql statements
 fetchall ( )       fetch all data
 fetchone ( )       fetch a piece of data

A basic example

Let's look at a basic example to let everyone feel the capabilities of the pymysql library.

The basic premise is that you have installed the MySQL service locally or you have the permission to access a MySQL service remotely.

Use the following SQL to create a data table for the following example demonstration:

# - * - coding : utf -8  - * -

import pymysql
 import random

__author__ =  'Bitter leaves'


if __name__ ==  "__main__" :     
    print ( "PyMySQL basic example" )    

    # Create a connection instance
    conn = pymysql . connect ( 
        host = "10.68.3.88" , # mysql service ip address, if the service is on this machine, use localhost
        port = 3306 ,   # mysql service port
        user = "liyiming" ,   # username to access mysql
        password = "liyiming" ,   # Password to access mysql
        db = "zzb_pro" ,   # The database to connect to by default
        charset = "utf8"   # connection character set
     )

    try :        
        # Create a cursor object for interaction
        cursor = conn . cursor ( )        
        
        
        # Insert 10 test data first

        # Build sql for inserting data
        sql =  "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"

        # Generate 10 test data
        sql_data =  [ ]         
        for index in  range ( 0 ,  10 ) : 
            email =  "%.10f@126.com"  % random . random ( ) 
            password = random . random ( ) 
            sql_data . append ( ( email , password ) )        
        
        # Execute SQL , bulk insert data
        cursor . executemany ( sql , sql_data )        
        
        # Submit to the database
        conn . commit ( )        
        
        # Query 5 pieces of data
        sql =  "SELECT * FROM `users` LIMIT 5"

        # Execute sql
        cursor . execute ( sql )        
        
        # Get all the data found
        all_data = cursor . fetchall ( )        

        # Traverse and print out
        print ( " Get all the queried data" )         
        for data in all_data :             
            print ( "id: %d email: %s password: %s"  %  
                ( data [ 0 ] , data [ 1 ] , data [ 2 ] ) )        
            
        # Take 1 piece of data
        # cursor . execute ( sql ) 
        one_data = cursor . fetchone ( )         
        print ( "\ ntake 1 piece of data" )         
        print ( "id: %d email: %s password: %s"  %  
            ( one_data [ 0 ] , one_data [ 1 ] , one_data [ 2 ] ) )

    finally :        
        # Finally close the database connection
        conn . close ( )