Create a pooledDB, get a connection , and set setsession argument to autocommit = 1, its ok.
BUT when the connection is Not Available, pooledDB try to get new connection, the PooledDB argument setsession is not working, it will be change to autocommit 1 to 0.
Here an example code.
from DBUtils.PooledDB import PooledDB
import pymysql
pool = PooledDB(creator=pymysql,
maxconnections=4,
mincached=2,
maxcached=0,
blocking=True,
maxusage=None,
setsession=['SET AUTOCOMMIT = 1'], # Pay attention to this
host='127.0.0.1',
port=3306,
user='root',
password='pwd',
database='alerter',
charset='utf8'
)
insert_sql = "INSERT INTO `alerter`.`al_play_log` (`src_type`, `src_ip`, `led_style`, `led_color`, `led_flag`, `text`, `play_time`, `tone`) " \
"VALUES (4, '192.168.0.210', 15, '[\"#00FFFF\"]', '[0.05, 1]', 'content', 0, NULL)"
conn = pool.connection()
cursor = conn.cursor()
ret = cursor.execute(insert_sql)
conn.close()
print('new connection create,check log, then use mysql kill command to kill current connect.')
input()
conn = pool.connection()
cursor = conn.cursor()
ret = cursor.execute(insert_sql)
conn.close()
2022-05-16T03:13:48.758866Z 207 Connect root@localhost on alerter using TCP/IP
2022-05-16T03:13:48.760342Z 207 Query SET AUTOCOMMIT = 0
2022-05-16T03:13:48.761637Z 207 Query SET AUTOCOMMIT = 1
2022-05-16T03:13:48.766279Z 208 Connect root@localhost on alerter using TCP/IP
2022-05-16T03:13:48.767477Z 208 Query SET AUTOCOMMIT = 0
2022-05-16T03:13:48.768686Z 208 Query SET AUTOCOMMIT = 1
2022-05-16T03:13:48.769913Z 208 Query ROLLBACK
2022-05-16T03:13:48.770811Z 207 Query ROLLBACK
2022-05-16T03:13:48.772879Z 208 Query INSERT INTO `alerter`.`al_play_log` (`src_type`, `src_ip`, `led_style`, `led_color`, `led_flag`, `text`, `play_time`, `tone`) VALUES (4, '192.168.0.210', 15, '["#00FFFF"]', '[0.05, 1]', 'content', 0, NULL)
2022-05-16T03:13:48.783426Z 208 Query ROLLBACK
2022-05-16T03:13:52.059120Z 192 Query show processlist
2022-05-16T03:13:59.223979Z 192 Query kill 207
2022-05-16T03:14:02.462124Z 192 Query kill 208
2022-05-16T03:14:13.903155Z 209 Connect root@localhost on alerter using TCP/IP
***2022-05-16T03:14:13.904506Z 209 Query SET AUTOCOMMIT = 0***
2022-05-16T03:14:13.906474Z 209 Query INSERT INTO `alerter`.`al_play_log` (`src_type`, `src_ip`, `led_style`, `led_color`, `led_flag`, `text`, `play_time`, `tone`) VALUES (4, '192.168.0.210', 15, '["#00FFFF"]', '[0.05, 1]', 'content', 0, NULL)
2022-05-16T03:14:13.908776Z 209 Query ROLLBACK
2022-05-16T03:14:13.956031Z 209 Quit