Python3 PyMySQL library
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.
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.
pip install PyMySQL
Common objects and API
Connection and Cursor objects are provided in pymysql to manage and operate MySQL.
Represents a socket connection with My SQL Server , and uses the connect method to create a connection instance.
Represents an object that interacts with the MySQL database using Connection.Cursor() on the current socket connection.
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 = "%.email@example.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 ( )