Python Operates MySQL and Actual Warfare
Course Objectives: Master transactions and locks as well as Python development prerequisites for operating MySQL.
Course Summary:
- Transaction: Atomic operations succeed or fail.
- Locks: If many people are working on the database at the same time, data clutter may occur. MySQL's own locks can solve this problem.
- Database connection pool: If there are many concurrent connection requests for a program, a database connection pool is required.
- SQL tool class: Provides a tool class based on database connection pool, context management, which can be used directly in later development.
- Other: Use of graphical management tools such as navicat.
1. Transactions
Transactions are supported in the innodb engine, not in myisam.
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(32) DEFAULT NULL, `amount` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
For example: Li Jie transfers 100 to Wu Peiqi, which involves two steps.
- Li Jie account minus 100
- Wu Peiqi Account plus 100
These two steps must be completed at the same time before they can be completed, and if the first step completes, the second step fails, or rolls back to the initial state.
Transactions are solutions to this situation. To put it plain: Success is everything; Fail to fail.
Transactions have four characteristics (ACID s) [frequently asked during interviews]:
-
Atomicity
Atomicity means that all operations involved in a transaction are indivisible, either all succeeded or all failed rollbacks.
-
Consistency
Data integrity is consistent before and after execution.
-
Isolation
During the execution of a transaction,Should not be interfered by other transactions.
-
Durability
Once the transaction has ended,The data persists to the database
1.1 MySQL Client
mysql> select * from users; +----+---------+---------+ | id | name | amount | +----+---------+---------+ | 1 | wupeiqi | 5 | | 2 | alex | 6 | +----+---------+---------+ 3 rows in set (0.00 sec) mysql> begin; -- Open Transaction start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update users set amount=amount-2 where id=1; -- Perform operation Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update users set amount=amount+2 where id=2; -- Perform operation Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; -- Submit Transaction rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from users; +----+---------+---------+ | id | name | amount | +----+---------+---------+ | 1 | wupeiqi | 3 | | 2 | ale x | 8 | +----+---------+---------+ 3 rows in set (0.00 sec)
mysql> select * from users; +----+---------+---------+ | id | name | amount | +----+---------+---------+ | 1 | wupeiqi | 3 | | 2 | ale x | 8 | +----+---------+---------+ 3 rows in set (0.00 sec) mysql> begin; -- Open Transaction Query OK, 0 rows affected (0.00 sec) mysql> update users set amount=amount-2 where id=1; -- Perform the operation (at this point the values in the database have been modified) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> rollback; -- Transaction rollback (back to original state) Query OK, 0 rows affected (0.00 sec) mysql> select * from users; +----+---------+---------+ | id | name | amount | +----+---------+---------+ | 1 | wupeiqi | 3 | | 2 | ale x | 8 | +----+---------+---------+ 3 rows in set (0.00 sec)
1.2 Python code
import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb') cursor = conn.cursor() # Open Transaction conn.begin() try: cursor.execute("update users set amount=1 where id=1") int('asdf') cursor.execute("update tran set amount=2 where id=2") except Exception as e: # RollBACK print("RollBACK") conn.rollback() else: # Submit print("Submit") conn.commit() cursor.close() conn.close()
2. Locks
When using MySQL, I don't know if you have any questions: There are many updates, inserts, deletes at the same time, how can MySQL ensure that the data is correct?
MySQL has a built-in lock function that helps us achieve the same data processing that we encounter during development. For locks in databases, there are:
- Table level locks, when A operates on a table, prevent others from operating on the entire table until A has finished.
- Row level locks, that is, when A operates on a table, the specified row data cannot be manipulated by others, and other rows can be manipulated until A has finished.
MYISAM Supports table locks, not row locks; InnoDB The engine supports row and table locks. That is, in MYISAM Next, if you want to lock, it will be a table lock no matter how you add it. stay InnoDB With engine support, row-level locks are used for indexed query-based data, otherwise table locks are used.
So, in general, we choose to use the innodb engine and also use the index (hit index) when searching.
The next steps are based on the innodb engine:
CREATE TABLE `L1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `count` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
In the innodb engine, locks (exclusive locks) are applied for internally by the behavior of update, insert, delete, and then related operations are performed before the locks are released.
So when multiple people are executing at the same time like a database: insert,update,delete When waiting for operation, internal locking will be queued one by one.
select does not apply for locks by default.
select * from xxx;
If you want select to apply for locks, you need to do so in conjunction with transaction + special syntax.
-
for update, exclusive lock, after lock, other cannot read and write.
begin; -- Locking select * from L1 where name="Wu Peiqi" for update; -- name Column is not an index (table lock) commit; -- Release lock
Example:
As shown in the figure above, Figure 1 opens the row lock, while for update, Figure 2 opens another connection terminal, which can insert into, but one row of updates is locked in Figure 1, so update in Figure 2 cannot acquire the lock and is in a waiting state. Figure 2 can only execute after Figure 1 commit.
As shown above, commit releases the lock and Figure 2 executes successfully.
Example 2:
An example of a table lock is shown above.
begin; -- perhaps start transaction; select * from L1 where id=1 for update; -- id Columns are indexes (row locks) commit;
[Summary] Only transactions + for update/insert/delete are neat enough to apply for locks. Otherwise, only the select statement is executed, and it will not itself apply for locks, so it will not affect the execution of the select statement.
-
lock in share mode, shared lock, after lock, other readable but not writable.
begin; select * from L1 where name="Wu Peiqi" lock in share mode; -- hypothesis name Column is not an index (table lock) commit;
Example:
As shown in Figure 2, you can query at this time, but you cannot insert.
begin; -- perhaps start transaction; select * from L1 where id=1 lock in share mode; -- id Columns are indexes (row locks) commit;
2.1 Exclusive Lock Application Scenario
An exclusive lock (for update), after which other transactions cannot be read or written.
Scenario: A total of 100 items, each time you buy one item, you need to reduce the number of items by one.
A: Visit the page to see the remaining 100 items B: Visit the page to see the remaining 100 items here A,B Order at the same time, then they execute at the same time SQL: update goods set count=count-1 where id=3 Because Innodb The engine is locked internally, so even if both of them execute at the same time, the internals will be sequenced step by step. However, when there is one remaining item, attention should be paid to it. A: Visit the page to see remaining 1 B: Visit the page to see remaining 1 here A,B Order at the same time, then they execute at the same time SQL: update goods set count=count-1 where id=3 The remaining quantity will appear -1,Obviously this is incorrect, so what should I do? In this case, you can use exclusive locks to query the remaining quantity, only the quantity, before updating >0 In order to be able to purchase, so the order should be executed: begin; -- start transaction; select count from goods where id=3 for update; -- Get Number to Judge if Number>0: update goods set count=count-1 where id=3; else: -- Out of stock commit;
Based on a Python code sample:
import pymysql import threading def task(): conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb') # By creating a cursor in this way, fetchone gets a dictionary, and fetchall gets a tuple of dictionaries. cursor = conn.cursor(pymysql.cursors.DictCursor) # cursor = conn.cursor(), cursor created this way, fetchone gets a tuple, fetchall gets a tuple of tuples. # Open Transaction conn.begin() cursor.execute("select id,age from tran where id=2 for update") # fetchall ( {"id":1,"age":10},{"id":2,"age":10}, ) ((1,10),(2,10)) # {"id":1,"age":10} (1,10) result = cursor.fetchone() current_age = result['age'] if current_age > 0: cursor.execute("update tran set age=age-1 where id=2") else: print("Out of stock") conn.commit() cursor.close() conn.close() def run(): for i in range(5): t = threading.Thread(target=task) t.start() if __name__ == '__main__': run()
2.2 Shared Lock Application Scenario
Shared locks aren't used much in the development process, so borrow an official example of an application scenario to explain it. [Know it, Wu sir wasn't used in development either.)
A shared lock (lock in share mode) can be read but not written.
After locking, subsequent things can be read, but write (update, delete, insert) is not allowed because the default of write is also locked.
Locking Read Examples
Suppose that you want to insert a new row into a table child, and make sure that the child row has a parent row in table parent. Your application code can ensure referential integrity throughout this sequence of operations.
First, use a consistent read to query the table PARENT and verify that the parent row exists. Can you safely insert the child row to table CHILD? No, because some other session could delete the parent row in the moment between your SELECT and your INSERT, without you being aware of it.
To avoid this potential issue, perform the SELECT using LOCK IN SHARE MODE:
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
After the LOCK IN SHARE MODE query returns the parent 'Jones', you can safely add the child record to the CHILD table and commit the transaction. Any transaction that tries to acquire an exclusive lock in the applicable row in the PARENT table waits until you are finished, that is, until the data in all tables is in a consistent state.
3. Database Connection Pool
A database connection pool is required when operating a database.
pip3.9 install pymysql pip3.9 install dbutils
import threading import pymysql from dbutils.pooled_db import PooledDB MYSQL_DB_POOL = PooledDB( creator=pymysql, # Modules that use linked databases maxconnections=5, # Maximum number of connections allowed in connection pool, 0 and None indicate unlimited number of connections mincached=2, # At least one free link created in the link pool at initialization time, 0 means no link is created maxcached=3, # Maximum number of idle links in link pool, 0 and None unrestricted blocking=True, # Whether to block waiting after no connection is available in the connection pool. True, wait; False, don't wait and error setsession=[], # List of commands executed before starting the session. For example: ["set datestyle to...", "set time zone..."] ping=0, # ping MySQL server, check if the service is available. # For example: 0 = None = never, 1 = default = whenever it is requested, # 2 = when a cursor is created, 4 = when a query is executed, 7 = always host='127.0.0.1', port=3306, user='root', password='root123', database='userdb', charset='utf8' ) def task(): # De-pooling to get a connection conn = MYSQL_DB_POOL.connection() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute('select sleep(2)') result = cursor.fetchall() print(result) cursor.close() # Swap connections to connection pools conn.close() def run(): for i in range(10): t = threading.Thread(target=task) t.start() if __name__ == '__main__': run()
4. SQL Tool Class
Develop a common SQL operation class based on the database connection pool to facilitate future database operations.
4.1 Singletons and Methods
# db.py import pymysql from dbutils.pooled_db import PooledDB class DBHelper(object): def __init__(self): # TODO is configured here and can be read from the configuration file. self.pool = PooledDB( creator=pymysql, # Modules that use linked databases maxconnections=5, # Maximum number of connections allowed in connection pool, 0 and None indicate unlimited number of connections mincached=2, # At least one free link created in the link pool at initialization time, 0 means no link is created maxcached=3, # Maximum number of idle links in link pool, 0 and None unrestricted blocking=True, # Whether to block waiting after no connection is available in the connection pool. True, wait; False, don't wait and error setsession=[], # List of commands executed before starting the session. For example: ["set datestyle to...", "set time zone..."] ping=0, # ping MySQL Server, check if the service is available.# For example: 0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always host='127.0.0.1', port=3306, user='root', password='root123', database='userdb', charset='utf8' ) def get_conn_cursor(self): conn = self.pool.connection() cursor = conn.cursor(pymysql.cursors.DictCursor) return conn, cursor def close_conn_cursor(self, *args): for item in args: item.close() def exec(self, sql, **kwargs): conn, cursor = self.get_conn_cursor() cursor.execute(sql, kwargs) conn.commit() self.close_conn_cursor(conn, cursor) def fetch_one(self, sql, **kwargs): conn, cursor = self.get_conn_cursor() cursor.execute(sql, kwargs) result = cursor.fetchone() self.close_conn_cursor(conn, cursor) return result def fetch_all(self, sql, **kwargs): conn, cursor = self.get_conn_cursor() cursor.execute(sql, kwargs) result = cursor.fetchall() self.close_conn_cursor(conn, cursor) return result db = DBHelper()
from db import db db.exec("insert into d1(name) values(%(name)s)", name="Wupeiqi 666") ret = db.fetch_one("select * from d1") print(ret) ret = db.fetch_one("select * from d1 where id=%(nid)s", nid=3) print(ret) ret = db.fetch_all("select * from d1") print(ret) ret = db.fetch_all("select * from d1 where id>%(nid)s", nid=2) print(ret)
4.2 Context Management
If you want him to also support with context management.
with Get a connection: implement SQL(Automatically return the connection to the connection pool when execution is complete)
# db_context.py import threading import pymysql from dbutils.pooled_db import PooledDB POOL = PooledDB( creator=pymysql, # Modules that use linked databases maxconnections=5, # Maximum number of connections allowed in connection pool, 0 and None indicate unlimited number of connections mincached=2, # At least one free link created in the link pool at initialization time, 0 means no link is created maxcached=3, # Maximum number of idle links in link pool, 0 and None unrestricted blocking=True, # Whether to block waiting after no connection is available in the connection pool. True, wait; False, don't wait and error setsession=[], # List of commands executed before starting the session. For example: ["set datestyle to...", "set time zone..."] ping=0, host='127.0.0.1', port=3306, user='root', password='root123', database='userdb', charset='utf8' ) class Connect(object): def __init__(self): self.conn = conn = POOL.connection() self.cursor = conn.cursor(pymysql.cursors.DictCursor) def __enter__(self): return self def __exit__(self, exc_type, exc_val, exc_tb): self.cursor.close() self.conn.close() def exec(self, sql, **kwargs): self.cursor.execute(sql, kwargs) self.conn.commit() def fetch_one(self, sql, **kwargs): self.cursor.execute(sql, kwargs) result = self.cursor.fetchone() return result def fetch_all(self, sql, **kwargs): self.cursor.execute(sql, kwargs) result = self.cursor.fetchall() return result
from db_context import Connect with Connect() as obj: # print(obj.conn) # print(obj.cursor) ret = obj.fetch_one("select * from d1") print(ret) ret = obj.fetch_one("select * from d1 where id=%(id)s", id=3) print(ret)
[Summary] Either of the above two methods can be used according to your own needs.
5. Other
navicat is a desktop application that allows us to manage MySQL databases more easily.
- mac system: https://www.macdo.cn/17030.html
- win System:
- Links: https://pan.baidu.com/s/13cjbrBquz9vjVqKgWoCQ1w Password: qstp
- Links: https://pan.baidu.com/s/1JULIIwQA5s0qN98KP8UXHA Password: p18f
summary
The content of this section is important and a skill often used in development.
- Transactions to resolve problems where batch operations succeed or fail simultaneously.
- Lock to solve concurrent processing problems.
- Database connection pool to solve the problem of multiple people requesting to connect to the database.
- SQL tool class to solve the problem of duplicate connection database code.
- navicat tool