Python connection to MySQL PyMySQL driver

Preface

The first section of this article is used to introduce Python 3's use of PyMySQL to connect to databases and implement simple addition, deletion, modification, and querying
The second section is used to summarize the differences between two methods of clearing table data in databases.

1, PyMySQL

1. What is PyMySQL

In order for Python to connect to the database, you need a driver, which is a library used for interacting with the database
PyMySQL: This is a library that connects Python to MySQL, and it is a pure Python library (folder). PyMySQL is a pure Python implemented MySQL client operation library that supports transactions, access procedures, batch execution, and enables addition, deletion, modification, and querying.

2. Installation of PyMySQL

Use the following command to install:

pip install pymysql -i image 

3. The main process of PyMySQL

import pymysql
# 1. create link 
conn = pymysql.connect(  #assign value to conn connecting objects 
host='127.0.0.1',   #local IP address 
port=3306,  		#default port 
user='root',  		#user name 
password='123',  	#password 
database='db_01',   #connection database name 
charset='utf8'  	#encoding cannot be written utf-8)
# 2. generate a cursor object (equivalent to cmd open mysql middle mysql>)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  #automatically organize data into a dictionary 
# 3. definition SQL statement 
sql = 'select * from egg'
# 4. implement SQL statement 
ret = cursor.execute(sql)print(ret)  #the return value is execution SQL number of rows affected after statement 
# 5. 1. obtain all returned results 
res = cursor.fetchall() 
# 5. get the next query result set, which is an object 
res = cursor.fetchone()  
# 5. 3. obtain several result sets, with the number specified in parentheses 
res = cursor.fetchmany(3)  
#output list set dictionary 
print(res)

4. Adding, deleting, modifying, and querying PyMySQL

(1) Adding, deleting, modifying, and querying instances

sql1 = 'select * from egg'  #query data within the table 
sql2 = 'insert into egg(name,password) values(%s,%s)'  #insert data 
sql3 = 'update egg set name=jasonNB where id=1'  #modify data 
sql4 = 'delete from egg where id=2'  #delete data 

In the operation of adding, deleting, modifying, and querying the database:
The level of inquiry is the lowest, so it can be directly operated The highest level of addition, deletion, and modification is required. Pymysq does not have permission to operate and requires a second confirmation before the addition, deletion, and modification can be performed
Therefore, a second confirmation is required for the addition, deletion, and modification operations to take effect.

(2) Secondary confirmation (manual testing)

cursor.execute(sql2,('tony',123))  #insert data 
conn.commit()  #secondary confirmation 
cursor.execute(sql3)  #modify data 
conn.commit()  #secondary confirmation 
cursor.execute(sql4)#delete data 
conn.commit()  #secondary confirmation 

(3) Secondary confirmation (automation)

conn = pymysql.connect(  #assign value to conn connecting objects 
host='127.0.0.1',   #local IP address 
port=3306,  		#default port 
user='root',  		#user name 
password='123',  	#password 
database='db_01',   #connection database name 
charset='utf8',  	#coding 
autocommit=True     #involving automatic secondary confirmation of additions, deletions, and modifications 

5. Generate a cursor object

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  #automatically organize data into a dictionary 
sql1 = 'select * from egg'
sql2 = 'insert into egg(name,password) values(%s,%s)'
sql3 = 'update egg set name="jaonNB" where id=2'
sql4 = 'delete from egg where id=1
cursor.execute(sql2,('laike',123))  #insert data 
cursor.execute(sql3) #modify data 
cursor.execute(sql4) #delete data 
conn.close() #proactively closing links to release resources 

2, The difference between truncate and delete

1. Basic Grammar

1.1 truncate

truncate table table_name (table name) ;
#illustrate :
TRUNCATE TABLE order;

1.2 delete

delete from table_name (table name) where....;
#illustrate :
DELETE FROM order;

1.3 Differences in Grammar

1. After truncate, only the table name can be added, and the table can be deleted directly without being able to 'where'
2. Delete can be followed by adding 'where' to delete rows.

2. Memory Space

2.1 truncate

Delete the contents of the table, do not delete the table structure, and free up space, that is, truncate. After deleting data, rewriting the data will start from 1.

2.2 delete

Delete the contents of the table, do not delete the table structure, and do not free up space. That is, after deleting data, it will only continue from the last row before deletion.

3. Processing speed

In the truncate operation, the ID starts directly from 1, that is, all are cleared; To delete, you need to first obtain the current number of rows in order to continue writing; So truncate deletion is faster than delete.