关于threading的使用
一开始仅仅是考虑到使用多线程,没有注意到共享资源的争用,导致频繁的发生主键冲突的问题(其实不仅是频繁,按照我这种写法估计一定会发生,因为没有使用到全局的唯一Sequence)。
一开始的多线程运行如下:
# 开启新线程
thread1.start()
thread2.start()
thread1.join()
thread2.join()
后来加上了threaning中的 threadLock
threadLock.acquire()
...
threadLock.release()
解决了资源争用的问题。
关于MySQL长连接的使用
代码在跑的过程中发现数据库的会话进程一直在变,这样会增加数据库的一些消耗,于是想到了连接池和长连接的一些操作,这里先使用长连接,连接池后面再慢慢研究吧
# -*- coding:utf-8 -*-
import decimal
import random
import threading
import time
from time import sleep
import pymysql
class mysql_connect:
def __init__(self,
host='',
user='',
passwd='',
db='',
port=3306,
charset='utf8mb4'
):
self.host = host
self.user = user
self.passwd = passwd
self.db = db
self.port = port
self.charset = charset
self.conn = None
self.cursor = None
self._conn()
def _conn(self):
try:
self.conn = pymysql.connect(host=self.host,
user=self.user,
password=self.passwd,
database=self.db,
port=self.port)
return True
except:
print("连接失败")
return False
def _cursor(self):
self.cursor = self.conn.cursor()
self.cursor.execute("set names '{}'".format(self.charset))
def _free(self):
self.cursor.close()
def _reConn(self, num=28800, stime=3): # 重试连接总次数为1天,这里根据实际情况自己设置,如果服务器宕机1天都没发现就......
_number = 0
_status = True
while _status and _number <= num:
try:
self.conn.ping() # cping 校验连接是否异常
_status = False
except:
if self._conn() == True: # 重新连接,成功退出
_status = False
break
_number += 1
time.sleep(stime) # 连接不成功,休眠3秒钟,继续循环,知道成功或重试次数结束
def select(self, sql=''):
try:
self._reConn()
self._cursor()
self.cursor.execute(sql)
result = self.cursor.fetchall()
self._free()
return result
except Exception as e:
print(e)
# print ("Error %d: %s" % (e.args[0], e.args[1]))
return False
def query(self, sql=[]):
try:
self._reConn()
self._cursor()
for text in sql:
result = self.cursor.execute(text)
self.conn.commit()
self._free()
return (True, result)
except Exception as e:
return False
def close(self):
self.conn.close()
class orders():
def __init__(self, conn:mysql_connect):
self.conn = conn
def get_menu(self):
menus = self.conn.select('select menu_id,money from menus;')
return list(menus)
def get_max_id(self):
maxId = self.conn.select("select max(order_id) from orders;")[0]
if not maxId[0]:
return 0
return maxId[0]
def make_order(self):
try:
newId = int(self.get_max_id()) + 1
dish_num = random.randint(1, 3)
menus = self.get_menu()
money = decimal.Decimal(0.00)
sql = []
for i in range(dish_num):
menu = random.choice(menus)
print("客户下单了菜品 :%d, 价格:%.2f。" % (menu[0], menu[1]))
money += menu[1]
# print("insert into orders(order_id,money) values(%d,%.2f)" % (newId, money))
sql.append('insert into order_detail(order_id,menu_id,money) values(%d, %d, %.2f);' % (newId, menu[0], menu[1])
)
sql.append('insert into orders(order_id,order_money) values(%d,%.2f)' % (newId, money)
)
print("该客户下单ID:%d,下单总金额:%.2f。" % (newId, money))
self.conn.query(sql)
except Exception as e:
print(e)
def createOrders(conn:mysql_connect):
i = 0
while i < 1000:
a = orders(conn)
a.make_order()
i += 1
# sleep(5)
class myThread(threading.Thread):
def __init__(self, threadID, name, counter, conn:mysql_connect):
threading.Thread.__init__(self)
self.threadID = threadID
self.name = name
self.counter = counter
self.conn = conn
def run(self):
print("开始线程:" + self.name)
threadLock.acquire()
createOrders(self.conn)
print("退出线程:" + self.name)
threadLock.release()
if __name__ == '__main__':
conn = mysql_connect(host="10.10.100.131",
user="zhu",
passwd="zshu123",
db="test")
# print(random.randint(1,10))
threadLock = threading.Lock()
threads = []
thread1 = myThread(1, "Thread-1", 1, conn)
thread2 = myThread(2, "Thread-2", 2, conn)
# 开启新线程
thread1.start()
thread2.start()
threads.append(thread1)
threads.append(thread2)
for t in threads:
print("线程:", t.name)
t.join()
print("退出主线程")