Pymysql basic usage in Python

the Pymysql module in Python performs database operations by acquiring MySQL database command-line cursors to execute database commandsAdvantages: The operation of the database statement is what you see, and what database statements are executed are clearCons: Cumbersome operation, more code volume

1. Basic use of Pymysql

#-*-coding:utf-8-*-#Author:wong DuImportPymysql#Create a link that is equivalent to creating a socketconn = Pymysql. Connection (host='10.0.0.100', port=3306, user='Root', passwd='123456', db='TestDB')#creating cursors, equivalent to entering the mysql> command operator interfacecursor =conn.cursor ()#build the table, as with the MySQL command line operationTry: Create_table= Cursor.execute (" "CREATE TABLE student (ID int not NULL PRIMARY key auto_increment,                                   Name Char (+) is not NULL, register_date date is not null DEFAULT "2018-05-09"); " ")exceptPymysql.err.InternalError as E:#print (Type (e))    Print("\033[31;1m%s; Do nothing...\033[0m"%e)#Inserting DataInsert = Cursor.execute ('INSERT into student (name,register_date) VALUES ("Junry", "2017-03-14");') Insert2= Cursor.execute ('INSERT into student (name,register_date) VALUES ("Hongfa", "2015-03-14");') Insert3= Cursor.execute ('INSERT into student (name,register_date) VALUES ("Jinglin", "2016-03-14");')#View Table Dataselect = Cursor.execute ('select * from student;') forLineinchCursor.fetchall ():Print(line)#Modifying table DataUpdate = Cursor.execute ('Update student Set name= "Junwei" where id=1') Select2= Cursor.execute ('select * from student;')Print(Cursor.fetchone ())#Delete table DataDelete = Cursor.execute ('Delete from student;') Select3= Cursor.execute ('select * from student;')ifCursor.fetchall ():Print(Cursor.fetchall ())Else:    Print("This is a empty table ...")#SubmitConn.commit () cursor.close ( )#Close CursorsConn.close ()#Close Connection#wait, wait, etc... 

Loop Insert Data

#-*-coding:utf-8-*-#Author:wong DuImportPymysql#Establish a connectionconn = Pymysql. Connect (host='10.0.0.100', port=3306, user='Root', passwd='123456', db='TestDB')#Creating Cursorscursor =conn.cursor ()#Loop Insert ListMany_list = [    ('Zhangsan','2011-11-11'),    ('Lisi','2012-11-11'),    ('Wangwu','2022-10-09'),]#Loop Insert (insert multiple items)Cursor.executemany ("INSERT into student (name, register_date) VALUE (%s,%s);", Many_list)#Modify Cursor PositionCursor.scroll (1, mode='relative')#relative move, default is relativeCursor.scroll (1, mode='Absolute')#Absolute Movement#Fetchone () Gets a row of data, Fetchmany (num) Gets the specified row data, Fetchall () gets all row dataCursor.execute ("select * from student;") forLineinchCursor.fetchall ():Print(line)#clear the data of the student tableCursor.execute ("Delete from student;")#SubmitConn.commit () cursor.close ( )#Close CursorsConn.close ()#Close Connection

Pymysql basic usage in Python