How Python queries MySQL

Python queries MySQL uses the Fetchone () method to get a single piece of data, using the Fetchall () method to get multiple data.

• Fetchone (): This method gets the next query result set. The result set is an object
• Fetchall (): Receives all the returned result rows.
• RowCount: This is a read-only property and returns the number of rows affected after the Execute () method is executed.

1: Get the result set using the Fetchone () method

ImportPymysql#Open a database connectiondb = Pymysql.connect ("localhost","Root","123456","Test")#get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL Query Statementssql ="SELECT * from EMPLOYEE WHERE INCOME > '%d '"% (1000)Try:    #Execute SQL statementcursor.execute (SQL)Print(cursor.rownumber) result=Cursor.fetchone () whileresult!=None:Print(result, cursor.rownumber) result=cursor.fetchone () result=Cursor.fetchone ()Print(result, cursor.rownumber) result=Cursor.fetchone ()Print(result, Cursor.rownumber)except:   Print("error:unable to fetch data")#To close a database connectionDb.close ()

Output Result:

0
(' Mac ', ' Mohan ', +, ' M ', 2000.0) 1
(' Marry ', ' Mohan ', +, ' M ', 3000.0) 2
(' Bob ', ' Mohan ', +, ' F ', 4000.0) 3
None 3
None 3

Conclusion: After executing the cursor.execute (SQL) statement, the cursor points to the position before the first record.

After the Cursor.fetchone () statement is executed, the Fetchone () method returns the next record that the cursor points to, and the cursor points to the next record in the current record.

When the cursor has pointed to the last record, the Cursor.fetchone () statement is executed again, and the result returns None,cursor no longer moving forward.

2:fetchall (): Receive all returned result rows

ImportPymysql#Open a database connectiondb = Pymysql.connect ("localhost","Root","123456","Test")#get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL Query Statementssql ="SELECT * from EMPLOYEE WHERE INCOME > '%d '"% (1000)Try:    #Execute SQL statementcursor.execute (SQL)#get list of all recordsResults =Cursor.fetchall () forRowinchResults:fname=Row[0] lname= Row[1] Age= Row[2] Sex= Row[3] Income= Row[4]        #Print Results        Print("fname=%s,lname=%s,age=%d,sex=%s,income=%d"%(fname, lname, age, sex, income))except:   Print("error:unable to fetch data")#To close a database connectionDb.close ()

How Python queries MySQL