Python operation MySQL Database

(1) What the Python DB API contains:                
(2) The process of accessing the database using the Python DB API:
(3) The main method of the Python DB API:

• Connect Connection class:
• conn = Pymsql.connect (host= "localhost", port=3306, user= "root", passwd= "root", db= "goods", charset= "UTF8") : Create a MySQL connection.
• cursor = Conn.cursor (): Creates a cursor for communication between Python and MySQL, which is the execution of an SQL statement.
• Conn.rollback (): Rollback allows the database to remain intact until the SQL statement execution of the cursor reference fails.
• Conn.commit (): Confirm that the database changes after the SQL statement executes.
• Conn.close (): Closes the connection.
• Cursor Cursors class:
• Cursor.execute (SQL): An argument is a string of SQL statements that is used to execute SQL statements in MySQL.
• Cursor.fetchone (): Returns the next line of the query result, in the form of a tuple, i.e. ((1 line), (2 lines), (3 line)).
• Cursor.fetchsize (size): Returns the next size row of the query result, in the form of a tuple.
• Cursor.fetchall (): Returns the remaining rows of the query result in the form of a tuple.
• Cursor.rowcount: The number of rows in the last execute that affected the database.
• Cursor.close (): Closes the cursor.

(4) python3.6 operation database---Simple additions and deletions:

ImportPymysqlif __name__=='__main__':#create a MySQL connectionconn = Pymysql. Connect (host ='127.0.0.1', port = 3306,user ='Root', passwd ='Root', db ='databasetest', CharSet ='UTF8')#Create cursors: Used for communication between Python and Mysql, that is, for executing SQL statements. cursor =conn.cursor ()Try:    #Add Databasesql ="INSERT into UserData VALUES (1, ' LBG '), (2, ' Zgj '), (3, ' Ldt ' )"cursor.execute (SQL)#Deleting a databaseSQL2 ="DELETE from UserData WHERE id=3"Cursor.execute (SQL2)#Update DatabaseSQL3 ="UPDATE userdata SET username= ' WJZ ' WHERE id=2"Cursor.execute (SQL3)#Querying the databaseSQL4 ="SELECT * from UserData"Num=Cursor.execute (SQL4)Print("The number of the UserData:%d"%num)#Fetchall Returns a tuple that contains the results of all queries.      forRowinchCursor.fetchall ():Print("ID:"+str (Row[0]) +"Name:"+row[1])exceptException as Reason:#SQL thing rollback: All the completed operations of the database in the transaction are undone and rolled back to the state at the start of the transactionConn.rollback ()Print('Transaction Failure', Reason)Else:    #SQL Thing confirmation: Database operations will not take effect until things are confirmedConn.commit ()Print('Transaction Success', Cursor.rowcount)finally:    #closing connections and cursorscursor.close () conn.close ( )

(5) python3.6 Operation Database---bank transfer:

ImportPymysqlImportSYSclassTransferMoney:def __init__(self,conn): Self.conn=ConndefTransfer (Self,id_out,id_in,money):Try: Self. Hasidaccount (id_out) self. Hasidaccount (id_in) self. Outidhasenoughmoney (Id_out,money) self. Submoney (Id_out,money) self. Addmoney (Id_in,money) conn.commit ()exceptException as E:self.conn.rollback ()RaiseedefHasidaccount (Self,userid): Cursor=self.conn.cursor ()Try: SQL="SELECT * from goods_change where id =%s"%userid cursor.execute (SQL) Res=Cursor.fetchall ()ifLen (res)! = 1:                RaiseException ("ID%s is not exist"%userid)Print("Has ID"+str (userid))finally: Cursor.close ()defOutidhasenoughmoney (Self,userid,change_money): Cursor=self.conn.cursor ()Try: SQL="Select money from goods_change where id =%s"%userid cursor.execute (SQL)ifInt (cursor.fetchone () [0]) <Int (Change_money):RaiseException ("ID%s has no enough money"%userid)Print("ID"+ STR (userid) +"Have enough money")        finally: Cursor.close ()defSubmoney (Self,userid,change_money): Cursor=self.conn.cursor ()Try: SQL="Update goods_change set money=money-%s where id=%s"%(Change_money,userid) cursor.execute (SQL)ifCursor.rowcount! = 1:                RaiseException ("ID%s is not exist"%userid)Print("ID"+ STR (userid) +"Sub"+ STR (Change_money) +"RMB")        finally: Cursor.close ()defAddmoney (Self,userid,change_money): Cursor=self.conn.cursor ()Try: SQL="Update goods_change set money=money+%s where id=%s"%(Change_money,userid) cursor.execute (SQL)ifCursor.rowcount! = 1:                RaiseException ("ID%s is not exist"%userid)Print("ID"+ STR (userid) +"Add"+ STR (Change_money) +"RMB")        finally: Cursor.close ()if __name__=='__main__': Conn= Pymysql. Connect (host='127.0.0.1', port=3306,user='Root', passwd='Root', db='Goods', charset='UTF8') Id_out= Sys.argv[1] id_in= Sys.argv[2] Money= Sys.argv[3] Trans=TransferMoney (conn)Try: Trans.transfer (Id_out,id_in,money)exceptException as Reason:Print('transaction failed:', Reason)finally: Conn.close ()

Python operation MySQL Database