MySQL Database (5/5) Python Operations MySQL and Actual Warfare

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

Tags: MySQL

Posted on Wed, 03 Nov 2021 14:18:17 -0400 by kevinbarker

Programmer Help