Python operating mysql Database (2)
In the previous article, we mainly introduced the python3 connection to the database, the creation of the database and the creation of tables, and then we tried to insert data into the table we just created and explore it.
# / usr/bin/env python#_*_coding:utf-8_*_# Import pymysql module import pymysql# Open database link connect=pymysql.connect (host= "192.168.186.157", port=3306,user= "winner", passwd= "123123", db= "pymysql", charset= "utf8", connect_timeout=3000) # use cursor method to obtain operation cursors cursor=connect.cursor () sql=''' insert into class (name,address) values ("JSP", "go"), ("winner", "back"), ("GOOD") "contine"), ("cursor", "execute") '' # use the execute method to operate the database cursor.execute (sql) # transaction commit # connect.commit () data=cursor.execute ("select * from class order by id desc") # use the fetchall method to get the operation result data=cursor.fetchmany (5) print (data) Note: the part of the transaction commit is commented out here, and the situation where the transaction is not committed is demonstrated.
Execution result (for the fourth time):
C:\ Users\ Administrator\ AppData\ Local\ Programs\ Python\ Python35\ python.exe C:/Users/Administrator/PycharmProjects/python/insertmysql.py ((12, 'cursor',' execute'), (11, 'GOOD',' contine'), (10, 'winner',' back'), (9, 'JSP',' go')) Process finished with exit code 0 checks the data in the database: mysql > select database () +-+ | database () | +-+ | pymysql | +-+ 1 row in set (0.00 sec) mysql > show tables +-+ | Tables_in_pymysql | +-+ | class | +-+ 1 row in set (0.00 sec) mysql > select * from class Empty set (0.00 sec) mysql > after checking the relevant tables in the database, we found that the data is empty at this time. Why? recall that we committed the comment transaction line connect.commit (), which involves the relevant knowledge of mysql database transactions. What will be the result if we try to add the transaction?
Execution results (display results after manual intervention):
C:\ Users\ Administrator\ AppData\ Local\ Programs\ Python\ Python35\ python.exe C:/Users/Administrator/PycharmProjects/python/insertmysql.py ((28, 'cursor',' execute'), (27, 'GOOD',' contine'), (26, 'winner',' back'), (25, 'JSP',' go'), (24, 'cursor',' execute'), (23, 'GOOD',' contine'), (22 'winner',' back'), (21, 'JSP',' go'), (20, 'cursor',' execute'), (19, 'GOOD',' contine'), (18, 'winner',' back'), (17, 'JSP',' go'), (16, 'cursor',' execute'), (15, 'GOOD',' contine'), (14, 'winner',' back') (13, 'JSP',' go')) Process finished with exit code 0
Query results of the database:
Mysql > select * from class +-+ | id | name | address | +-- + | 13 | JSP | go | 14 | winner | back | | 15 | GOOD | contine | 16 | cursor | execute | 17 | JSP | go | 18 | winner | back | 19 | GOOD | contine | 20 | cursor | execute | 21 | JSP | go | | 22 | winner | back | | 23 | GOOD | contine | | 24 | cursor | execute | | 25 | JSP | go | 26 | winner | back | | 27 | GOOD | contine | | 28 | cursor | execute | +-+ 16 rows in set (0.00 sec) mysql >
As a result, we find that the transaction relationship of the database is a very important part in the process of software development, so we need to be rigorous when dealing with transactions.
Commit the source code of the transaction:
# / usr/bin/env python#_*_coding:utf-8_*_# Import pymysql module import pymysql# Open database link connect=pymysql.connect (host= "192.168.186.157", port=3306,user= "winner", passwd= "123123", db= "pymysql", charset= "utf8", connect_timeout=3000) # use cursor method to obtain operation cursors cursor=connect.cursor () sql=''' insert into class (name,address) values ("JSP", "go"), ("winner", "back"), ("GOOD") "contine"), ("cursor", "execute") '' # use execute method to operate database cursor.execute (sql) # transaction commit connect.commit () data=cursor.execute ("select * from class order by id desc") # use fetchall method to get operation result data=cursor.fetchall () print (data)
