92 lines
2.8 KiB
Python
92 lines
2.8 KiB
Python
import pymysql
|
|
|
|
conn = pymysql.connect(host='127.0.0.1',user='root',password='123456',db='chen',cursorclass=pymysql.cursors.DictCursor)
|
|
|
|
|
|
# with conn.cursor() as cursor:
|
|
|
|
|
|
dealHosClass = 3
|
|
|
|
def getNh():
|
|
sql = "SELECT COUNT(*) AS num from hos_select a JOIN(SELECT `HosID.x` as hosID, COUNT(*) as num from example_select " \
|
|
"GROUP BY `HosID.x`) b on a.hosID = b.hosID where a.hosBirth > b.num and hosClass = " + repr(dealHosClass)
|
|
with conn.cursor() as cursor:
|
|
cursor.execute(sql)
|
|
vNh = cursor.fetchone()
|
|
return vNh["num"]
|
|
|
|
def getThi(i):
|
|
sql = "select hosBirth from hos_select where hosID = " + repr(i)
|
|
with conn.cursor() as cursor:
|
|
cursor.execute(sql)
|
|
vNh = cursor.fetchone()
|
|
return vNh["hosBirth"]
|
|
|
|
def getTh():
|
|
sql = "SELECT SUM(a.hosBirth) as houBirth from hos_select a JOIN(SELECT `HosID.x` as hosID, COUNT(*) as num " \
|
|
"from example_select GROUP BY `HosID.x`) b on a.hosID = b.hosID where a.hosBirth > b.num " \
|
|
"and hosClass = "+repr(dealHosClass)
|
|
with conn.cursor() as cursor:
|
|
cursor.execute(sql)
|
|
vNh = cursor.fetchone()
|
|
return vNh["houBirth"]
|
|
|
|
def getValue(sql,valName):
|
|
with conn.cursor() as cursor:
|
|
cursor.execute(sql)
|
|
temp = cursor.fetchone()
|
|
return temp[valName]
|
|
|
|
def updateValue(sql,data):
|
|
with conn.cursor() as cursor:
|
|
cursor.execute(sql,data)
|
|
conn.commit()
|
|
|
|
def fPehi(i):
|
|
vNh = getNh()
|
|
vThi = getThi(i)
|
|
vTh = getTh()
|
|
if vNh<=5 or vNh*vThi >= vTh:
|
|
vPehi = 1
|
|
else:
|
|
vPehi = (vNh * vThi)/vTh
|
|
|
|
upPehiSql = "UPDATE hos_select SET Pehi = %s WHERE hosID = %s"
|
|
data = (vPehi,i)
|
|
updateValue(upPehiSql,data)
|
|
|
|
def getHos():
|
|
getAllHosSql = "SELECT a.hosID FROM hos_select a JOIN(SELECT `HosID.x` as hosID, COUNT(*) as num " \
|
|
"from example_select GROUP BY `HosID.x`) b on a.hosID = b.hosID where a.hosBirth > b.num " \
|
|
"and hosClass = "+repr(dealHosClass)
|
|
with conn.cursor() as cursor:
|
|
cursor.execute(getAllHosSql)
|
|
return cursor
|
|
|
|
# calculate Pehi
|
|
def updateAllPehi():
|
|
hospitals = getHos()
|
|
for hos in hospitals.fetchall():
|
|
hosId = hos['hosID']
|
|
fPehi(hosId)
|
|
|
|
def e4(i):
|
|
exampleNumSql = "SELECT exampleNum FROM hos_select Where hosID = " + repr(i)
|
|
vExampleNum = getValue(exampleNumSql,"exampleNum")
|
|
vHosBirth = getThi(i)
|
|
pehiSql = "SELECT Pehi from hos_select where hosID = " + repr(i)
|
|
vPehi = getValue(pehiSql,"Pehi")
|
|
vE4 = (vPehi * vExampleNum)/vHosBirth
|
|
upE4Sql = "update hos_select set e4 = %s where hosID = %s"
|
|
data = (vE4,i)
|
|
updateValue(upE4Sql, data)
|
|
|
|
def updateAllE4():
|
|
hospitals = getHos()
|
|
for hos in hospitals.fetchall():
|
|
hosId = hos['hosID']
|
|
e4(hosId)
|
|
|
|
updateAllPehi()
|
|
updateAllE4() |