This article has participated in the weekend study program, click the link to see details: Weekend study

Aiomysql.connect () has an autocommit parameter, which defaults to False. You can set it to True so you don’t need to call connection.mit () manually. Commit () = commit();

Conn = await aiomysQL.connect (host='127.0.0.1', port=3306, user='root', password='password', db='test', Autocommit =True, loop=loop) g_pool = await aiomysQL. create_pool(host='127.0.0.1', port=3306, user='root', password='password', db='test', autocommit=True, minsize=1, maxsize=10, loop=loop)Copy the code

SQL > cursor.execute(SQL); SQL > cursor.execute(SQL); SQL > cursor.execute(SQL); SQL > cursor.execute(SQL) If you don’t add db.mit (), the program will not report an error, but the database will not operate, read various tutorials have no explanation about this point

In MySQL, transactions are only supported for databases or tables that use the Innodb database engine. Transaction processing can be used to maintain database integrity by ensuring that batches of SQL statements are either all executed or none executed. Transactions manage INSERT, UPDATE, and DELETE statements

There are two main methods for MYSQL transaction processing:

BEGIN starts a transaction, ROLLBACK the transaction rolls back, and COMMIT the transaction confirms.

SET AUTOCOMMIT=0 to disable auto-commit, SET AUTOCOMMIT=1 to enable auto-commit.

By default on the MySQL command line, transactions are committed automatically, that is, the COMMIT operation is performed immediately after the SQL statement is executed. Therefore, to explicitly START a TRANSACTION, use the commands BEGIN or START TRANSACTION, or execute the command SET AUTOCOMMIT=0, which disables automatic commit using the current session.

Db.mit () statement is required by default in the MySQL database. It is not the same in python as in MySQL command line.

Database changed
MariaDB [test]> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
Copy the code

Python query database

#! /usr/bin/python # -*- coding: Utf-8 -* -import MySQLdb = mysqldb. connect("localhost", "testuser", "test123", "TESTDB", SQL = "SELECT * FROM EMPLOYEE \ WHERE INCOME > %s" Fetchall () results = cursor.fetchall() for row in results: Fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4 "fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \ (fname, lname, age, sex, income ) except: print "Error: Unable to fecth data" # disable db connection db.close()Copy the code

Insert python into database

#! /usr/bin/python # -*- coding: Utf-8 -* -import MySQLdb = mysqldb. connect("localhost", "testuser", "test123", "TESTDB", Cursor = db.cursor() # SQL = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)""" try: SQL = cursor.execute(SQL) SQL = cursor.execute(SQL) # Rollback in case there is any error db.rollback() # Rollback in case there is any error db.rollback()Copy the code