cursor.execute("update tran set age=age-1 where id=2")
else:
print("已售罄")
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 共享锁
共享锁（ lock in share mode），可以读，但不答应写。
加锁之后，后续其他事物可以可以进行读，但不答应写（update、delete、insert），由于写的默认也会加锁。 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. 数据库连接池
setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
ping=0,
# ping MySQL服务端，检查是否服务可用。# 如：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="武沛齐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)
