1 Introduction

MySQL should be said to be the most commonly used database nowadays.There is no one. Python, as one of the most popular languages, naturally has to deal with mysql, and pymysql is the most used tool library.

2 Create library, table

Let's start with the creation of databases and data tables.With the database tables, the subsequent additions, deletions, and changes will have a basis.

2.1 Create a database

All pymysql operations on the database must first establish a connection with the database service, and then create a cursor to execute specific SQL statements. The method of creating a database is as follows:

# -*- coding: utf-8 -*- import pymysql
 conn = pymysql.connect ( # create a database connection 
    Host = ' 10.10.11.131 ' , # database hosts ip to connect to 
    the User = ' CHB ' , # database login user name 
    password = ' 123456! ' , # login user password 
    charset = ' UTF8 '  # coding, attention can not be written UTF8 )cursor = con.cursor()cursor .execute( " create database test_db character set utf8; " ) # Don't forget to close the cursor and database connection after execution cursor.close()conn.close()

After the above code is executed, a database named test_db is created:

2.2 Create a data table

# -*- coding: utf-8 -*- import pymysql
 conn = pymysql.connect ( # create a database connection 
    Host = ' 10.10.11.131 ' , # database hosts ip to connect to 
    the User = ' CHB ' , # database login user name 
    password = ' 123456! ' , # login user password 
    database = ' TEST_DB ' , # database name connection may be specified by subsequent cursor.execture ( 'User TEST_DB') 
    charset = ' UTF8 '  # coding, attention can not be written UTF8 )
 the Cursor = conn.cursor () # create a cursor
 # SQL statements to create the tables need to perform SQL = "" "create table book(bookid int auto_increment primary key,bookname VARCHAR(255) not null,authors VARCHAR(255) not null,year_publication YEAR not null);"""
 cursor.execute (sql) # use a cursor to perform sql
 # Don't forget to close the cursor and database connection after execution cursor.close()conn.close()

In this example of creating a data table, when creating a database service connection, we connect to the target database by specifying the parameters of the database='test_db' line of code.If you don't want to specify the database when creating a database service connection, you can use the cursor later.execture('user test_db') This method specifies the database.

From the example of creating databases and data tables above, it can be seen that when pymysql performs specific operations, it first creates a database service connection, and then creates a cursor through the connection.The cursor is used to execute specific SQL statements to complete specific database operations. Not only can you create databases and data tables, but you can also create indexes, views, etc.The method is the same, so I won't introduce them here.

 3 Addition, deletion and modification

3.1 Insert

The insert operation can be done through the execute and executemany methods of the cursor. Note: As long as the operation of modifying the data table (insert, update, delete) after using the execute method, you need to call the commit method again to modify the database to finally take effect.

The execute method inserts one record at a time, and executemany inserts multiple records at a time:

(1) execute: insert one record at a time

cursor.execute( ' insert into book(bookname, authors, year_publication) values("%s", "%s", %s); ' % ( ' Python from entry to giving up ' , ' Jobs ' , 2019 ))conn.commit()

The above writing method is to first generate a complete SQL statement with a string using %s as a placeholder, and then execute it. In fact, pymysql's cursor also comes with this function, and it seems more convenient, so we can also execute the execute method like this:

cursor.execute( ' insert into book(bookname, authors, year_publication) values(%s, %s, %s); ' , ( ' Python from entry to abandon ' , ' Jobs ' , 2019 ))conn.commit()

The advantage of this method is that the %s in the SQL statement does not need to be quoted (if quotes are added, the quotes will also be inserted into the data table as data).When the cursor executes this statement, it will determine whether to use the data type Put quotation marks.

(2) executemany: insert multiple records at once

data = [
    ( ' 21 days completely Getting the Java ' , ' Zuckerberg ' , 2018 ),
    ( ' Linux Learning Manual ' , ' Linas ' , 2017 ),
    ( ' MySQL from deleting the database to running away ' , ' Bill Gates ' , 2018 ),]cursor.executemany( ' insert into book(bookname, authors, year_publication) values("%s", "%s", %s); ' , data)conn.commit()

3.2 Update

cursor.execute( ' update book set authors=%s where bookname=%s; ' , [ " Ma Yun " , " Python from getting started to giving up " ])conn.commit()

3.3 Query

Query is the most important but also the most complicated operation. We are divided into two parts: fetch operation and cursor.

(1) fetch operation

Insert, update, and delete operations will only take effect after the commit operation is executed, and query operations will only take effect after the fetch operation is executed. The fetch operation includes 3 methods, namely fetchone(), fetchall(), and fetchmany().

• fetchall(): Query all the specified records

cursor.execute( ' select * from book where bookid <%s; ' , [4 ])books = cursor.fetchall() print (books)

The output is:

((1,'Python from entry to abandonment','Ma Yun', 2019), (2,'Python from entry to abandonment','Ma Yun', 2019), (3,'Complete entry to Java in 21 days','Zha Kerberg', 2018))

The output result is saved in the form of a tuple, and each record is also an element, which is determined by the cursor, which will be discussed later.

• fetchmany(size): query the specified number of records

cursor.execute( ' select * from book where bookid <%s; ' , [4 ])books = cursor.fetchmany(2 ) print (books)

The output is:

((1,'Python from entry to abandonment','Ma Yun', 2019), (2,'Python from entry to abandonment','Ma Yun', 2019))

It can be seen that fetchmany(size) is to fetch the first specified number of records that meet the query conditions. The size here refers to the number of records to be retrieved.

• fetchone(): fetch the first record

cursor.execute( ' select * from book where bookid <%s; ' , [4 ])books = cursor.fetchone() print (books)

Output result:

(1,'Python from entry to abandonment','Ma Yun', 2019)

fetchone() is equivalent to fetchmany(1), fetching the first record that meets the query conditions.

(2) Cursor

The cursors we used before are all using the default Cursor type.In addition, pymysql also provides DictCursor, SSCursor, SSDictCursor these types of cursors.

• DictCursor

The data returned by the previously used Cursor is saved in the form of tuples, while the DictCursor is saved in the form of a dictionary. The method to create this type of cursor is also very simple, just pass in the DictCursor class in the conn.sursor() method:

= conn.cursor Cursor (pymysql.cursors.DictCursor) # Create a dictionary cursor the cursor.execute ( ' SELECT * WHERE BookID from Book <% S; ' , [. 3 ])books = cursor.fetchall() print (books)cursor.execute( ' select * from book where bookid <%s; ' , [3 ])book_one = cursor.fetchone() print (book_one)

The output is as follows:

[{'bookid': 1,'bookname':'Python from getting started to giving up','authors':'马云','year_publication': 2019}, {'bookid': 2,'bookname':'Python from getting started To give up','authors':'马云','year_publication': 2019}]

{'bookid': 1,'bookname':'Python from getting started to giving up','authors':'马云','year_publication': 2019}

• SSCursor and SSDictCursor

SSCursor and SSDictCursor are called streaming cursors.This type of cursor does not return all data at once like the Cursor and DictCursor used above.Streaming cursors will return the query data one by one, so this type of cursor is suitable It is used in application scenarios with low memory, small network bandwidth, and large data volume.

The use of streaming cursors is similar to that of iterators.Each time one is taken in the recycle, one is generated:

= conn.cursor Cursor (pymysql.cursors.SSCursor) # Create a cursor stream the cursor.execute ( ' SELECT * from Book; ' )book = cursor.fetchone() while book: print (book)book = cursor.fetchone()

The output is as follows:

(1,'Python from entry to abandonment','Ma Yun', 2019)

(2,'Python from entry to abandonment','Ma Yun', 2019)

(3,'Introduction to Java in 21 Days','Zuckerberg', 2018)

(4,'Linux Learning Manual','Linas', 2017)

(5,'MySQL from deleting the database to running away','Bill Gates', 2018)

Note: Although streaming cursors also have a fetchall() method, the result of the call is the same as a normal cursor to return all the data, but it is best not to call, otherwise the advantages of streaming cursors will be lost. When using a streaming cursor, if the amount of data is large, the cursor is always in a loop traversal state.At this time, the database connection (conn) is occupied and cannot be used to execute other SQL.If you want to execute other SQL, then You must create another database connection.The time that the cursor occupies the database connection is limited.If the streaming cursor has been traversing, the database connection will be disconnected after 60 seconds, but you can pass in the parameter init_command=("SET when creating a database connection NET_WRITE_TIMEOUT=XX") to set this timeout time.

The difference between SSCursor and SSDictCursor is similar to the difference between Cursor and DictCursor, so I won't introduce it here.

3.4 Delete

Use pymysql to perform insert, update, and delete operations are similar, and finally commit is required:

cursor.execute( ' delete from book where bookid=%s; ' , [1 ])conn.commit()

At this time, the record with bookid 1 has been deleted, as shown in the following figure:

You can also use executemany() to delete multiple entries at once:

cursor.executemany( ' delete from book where bookid=%s; ' , [[2], [4 ]])conn.commit()

As shown in the figure below, the records with bookid 2 and 4 have been deleted:

 4 summary

Pymysql also provides a transaction mechanism, but I still have a question that I have not figured out, so I dare not write to the blog: When multiple database modification operations (multiple execute methods) are performed, one of the methods is abnormal, this is commit, all All will not succeed, so why rollback? If any senior knows the answer, please let me know, I am grateful!