Database-python operation MySQL (pymsql)

Pymsql is a module that operates MySQL in Python and is used almost the same way as MySQLdb

One: Installation Pymysql

PIP3 Install Pymysql

Two: Using Pytmysql

#-*-coding:utf-8-*-__author__='Shisanjun'ImportPymysql#Create a connectionConn=pymysql.connect (host="192.168.0.121", port=3306,user="Admin", password="Admin", db="test2")#Creating CursorsCursor=conn.cursor ()#the number of rows that execute SQL and also affect it backEffct_row=cursor.execute ("Update student set name= ' Shi ' where name= ' Shisanjun '")Print(Effct_row)#executes SQL and returns the number of rows affected, multiple records executedEffct_row=cursor.executemany ("INSERT into student (Name,age,sex) value (%s,%s,%s)",[("Tianshi", 23,"F"),("Xiatian", 24,"F")]) conn.commit ()#get the latest self-increment IDPrint(Cursor.lastrowid) Cursor.execute ("SELECT * FROM Student")#get the first row of dataPrint(Cursor.fetchone ())#get top N rows of dataPrint(Cursor.fetchmany (3))#Get all dataPrint(Cursor.fetchall ()) Conn.commit () Cursor.close () conn.close ()

49(1,'Shi', 23,'M')((2,'shisanjun2', 23,'M'), (4,'Shisanjun3', 24,'F'), (5,'Shisanjun3', 25,'F'))((6,'Shi', 25,'F'), (7,'Shi', 26,'F'), (8,'Shi', 26,'F'), (9,'Tianshi', 23,'F'), (10,'Xiatian', 24,'F'))

Note: In order to fetch data, you can use Cursor.scroll (Num,mode) to move the cursor position, such as:

• Cursor.scroll (1,mode= ' relative ') # moves relative to the current position
• Cursor.scroll (2,mode= ' absolute ') # relative absolute position movement

Three: Fetch data type

About the data obtained by default is the Ganso type, if you want or the dictionary type of data

#-*-coding:utf-8-*-__author__='Shisanjun'ImportPymysql#Create a connectionConn=pymysql.connect (host="192.168.0.121", port=3306,user="Admin", password="Admin", db="test2")#Creating CursorsCursor=conn.cursor (cursor=pymysql.cursors.DictCursor) R=cursor.execute ("SELECT * FROM Student") Result=Cursor.fetchall ()Print(R)Print(Result) Conn.commit () Cursor.close () conn.close ()9[{'stu_id': 1,'name':'Shi',' Age': 23,'Sex':'M'}, {'stu_id': 2,'name':'shisanjun2',' Age': 23,'Sex':'M'}, {'stu_id': 4,'name':'Shisanjun3',' Age': 24,'Sex':'F'}, {'stu_id': 5,'name':'Shisanjun3',' Age': 25,'Sex':'F'}, {'stu_id': 6,'name':'Shi',' Age': 25,'Sex':'F'}, {'stu_id': 7,'name':'Shi',' Age': 26,'Sex':'F'}, {'stu_id': 8,'name':'Shi',' Age': 26,'Sex':'F'}, {'stu_id': 9,'name':'Tianshi',' Age': 23,'Sex':'F'}, {'stu_id': 10,'name':'Xiatian',' Age': 24,'Sex':'F'}]

Database-python operation MySQL (pymsql)