Python3 operation database mysql-super brother serial (3)

table of Contents:
1. When will it be used
2. How to use
2.1 Install pymsql
2.2 Connect to the database
2.2.1 Connect to the database: 2.2.2
-Get cursor:
2.3 Operate the database
2.3.1-Method of executing sql
2.3.2-Create table operation
2.3.3-Query operation
2.3.4-Update operation
2.3.5-Delete operation
2.3.6-Insert operation

Then I said last time, last time (two) explained the new table and query operations, today we will continue to learn update and delete operations, because it will involve write operations, so we will talk about the usage of excutemany, look carefully.

2.3.4- Update operation

Update a piece of data

……#更新一条数据update=cursor.execute("update user_info set age=11 where id=2")print ('更新后受影响的行数为:',update)#注意:写操作(更新,修改,删除,新增)必须commit后才会生效,原因见下方conn.commit()#查询一条数据cursor.execute('select * from user_info where id = 2;')print('更新后的查询结果',cursor.fetchall())#关闭游标cursor.close()#关闭连接conn.close()

Output:

image

the reason:

This problem is related to MySQL's storage engine's support for transactions. There are many types of storage engines in MySQL, for example: MyISAM, InnoDB, etc. MyISAM does not support transaction processing, while InnoDB is a transactional database that supports transactions. My MySQL’s default storage engine is InnoDB, so operations on database data will be performed in the pre-allocated cache.

After commit, the data in the database will change.

Update multiple data

……#更新多条数据sql="update user_info set age = %s where id = %s"update=cursor.executemany(sql,[(21,2),(22,3)])#提交conn.commit()print ('更新后受影响的行数为:',update)#查询一条数据cursor.execute('select * from user_info where id = 2 or id = 3;')print('更新后的查询结果',cursor.fetchall())#关闭游标cursor.close()#关闭连接conn.close()

Output:

image

2.3.5- Delete operation

Delete a piece of data

……#删除操作#删除一条数据sql ="delete from user_info whereid = %s"delete = cursor.execute(sql,(3))print ('删除后受影响的行数为:',delete)#删除后查询数据sql ='select * from user_info where id= %s'cursor.execute(sql,3)print('删除后的查询结果',cursor.fetchall()) #关闭游标cursor.close()#关闭连接conn.close()

Output:

image

Delete multiple data

……#删除操作#删除多条数据sql ="delete from user_info whereid = %s"delete = cursor.executemany(sql,(4,5,6))print ('删除后受影响的行数为:',delete)#删除后查询数据sql ='select * from user_info where idin (4,5,6)'cursor.execute(sql)print('删除后的查询结果',cursor.fetchall()) #关闭游标cursor.close()#关闭连接conn.close()

Output:

image

Beijing Orange Technology, SuperTesting, Test Promotion Academy, focus on test promotion, annual salary sprint 40W!

Functional test students who want to transform test development can pay attention to the following public accounts, more dry goods are waiting for you!

If you have any technical problems, you can send a private message to WeChat: chenghaokeji666